Different Views depending on Roles

Question

Different Views depending on Roles

Henk de W

Ten Centuries

Points: 1359
More actions
May 12, 2010 at 6:53 am

#156948

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

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

Henk de W Ten Centuries Points: 1359 More actions · Answer 2

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

Mike01 SSChampion Points: 11381 More actions · Answer 3

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/

Henk de W Ten Centuries Points: 1359 More actions · Answer 4

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")?

Lowell SSC Guru Points: 323482 More actions · Answer 5

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Henk de W Ten Centuries Points: 1359 More actions · Answer 6

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!

Henk de W Ten Centuries Points: 1359 More actions · Answer 7

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.

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 8

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