May 12, 2010 at 6:53 am
Hi there,
What I need is a solution for the following:
I have a table with data for various regions, say:
"North America"
"Latin America"
"Europe"
"Africa"
"Middle East"
"Far East"
"South East"
Depending on the roles a user has he/she is only supposed to see data for his own region, or in some cases multiple regions. The original table contains all regions. (and obviously contains a field with the region in it)
How can I set-up the views (or should I use stored procedures) in such a way that only one view/stored procedure is needed to meet theses demands?
Many thanks in advance,
Henk
May 12, 2010 at 8:49 am
Not knowing the precise structure, I can't supply you with precise SQL code, but you can do this a few different ways. Let's assume that you have three tables, the interesting data, the user table that lists the region for the user and an interesting data to region table. Then you can create a query that does an inner join to all the tables and only needs the user name as a parameter to limit the results to that region.
With more details, a more detailed approach can be suggested.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 13, 2010 at 3:27 am
Grant,
First of all thanks for your reply. I hope you, or some one else can help me a bit further though.
Having said that I have to say I'm a SQL Server Newbie (so maybe I should have posted it in the newbie section). I do have a lot of programming experience in VB and Ms Access though. I would know how to solve it in Access, but quite frankly not in SQL Server.
The table I'm talking about is a starting point for me.
Like I stated a user should have the right to see one, two or more regions.
So ideally I want to create roles for each region and a user can be a member of one or more roles.
So for starters:
I do not have a clue how to figure out whether the user that is executing the query is a member of a group (role) and how to combine this with a query. Could you help me out with that, or give me a hint in the right the direction?
Henk
May 13, 2010 at 6:18 am
Henk,
here's a starting point for you.
-- Create and Populate Regions Table
declare @Regions table (RegionName varchar(30) not null)
insert into @Regions
select 'North America' union all
select 'Latin America' union all
select 'Europe' union all
select 'Africa' union all
select 'Middle East' union all
select 'Far East' union all
select 'South East'
-- create table to hold assignments based on user and Region Name
-- The UserName in this table should be the login that the users
-- are using to connect ot SQL Server. Ideally it would be their
-- Windows Login account, but it could also be their SQL Login
-- account if not using Windows Authentication
declare @users table (UserName varchar(30) not null, RegionName varchar(30))
insert into @users
select 'SQLLogin','North America' union all
select 'SQLLogin','Africa'
-- syntax for view. This will use the system function suser_name
--for the login name to compare to what is in the table
-- Create view UserRegions as
select r.RegionName
from @Regions R inner join @Users U
on R.RegionName = u.RegionName
where u.UserName = suser_name()
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 13, 2010 at 10:42 am
Thanks,
I'm one step further now. Knowing that suser_name() returns the user that is executing the query really helps. I'm looking for a pretier solution though. I would like to know whether the current user has a certain role. Say does he have the Role "Africa".
Do you happen to know what the syntax is for that? Someting like Role(suser_name(), "Africa")?
May 13, 2010 at 11:03 am
well, i was looking at using the function is_rolemember(which returns 1 or NULL, not 1 or 0) and IS_MEMBER('af_developer'), but i think it finds explicit grants to roles, and not inherited/inffered, like if you are logged in as an admin/sa:
select isnull(is_rolemember('db_owner', suser_name()) ,0)
that returned zero, even though i'm logged in as 'sa', who should have dbo rights via sysadmin.
Lowell
May 13, 2010 at 11:27 am
Well, it looks like that this is what I'm after.
is_rolemember, ok only explicetly assigned will work, which is just fine. Definitely another step forwards.
I am going to test this tomorrow, see how it works.
Yep, being a newbie is hard.....
Thanks!
May 14, 2010 at 12:22 am
Had a good night sleep over it: Changed my mind a bit. It's really good to know that the function exists and might use it occasionally. But all together it is probably more efficient/less costly to simply administer the region rights in a table.
May 17, 2010 at 6:09 am
I'm still not getting a total picture with details, but based on what else you've said, I'm going to stand by the original post. A table that stores relationships, user to region, seems like the right approach. You can then put a given user in X number of regions.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply