Row-Level Security based on Database Roles

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Row-Level Security based on Database Roles

Author

Topic

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2008-08-16 : 15:02:11

I'm experimenting with ways to implement row-level security without having to create views for every possible combination of certain values. I'll try to explain in a generic way, since its more the concept I'm interested in, rather than a specific application of it.
Table 1
ID(PK, int, Identity)
Col1(varchar)
SupervisorID(int, FK)

Table 2
SupervisorID(PK, int)
col1 (varchar)

Sample data:
table1
---------
1, 'Tom', 1
2, 'Dick', 2
3, 'Harry', 1

table2
----------
1, 'SalesMgr'
2, 'ShopMgr'

Roles in the database 'SalesMgr' and 'ShopMgr'
Is there a way to check role membership other than a whole bunch of IF IS_MEMBER ('SalesMgr')
ELSE IF IS_MEMBER ('ShopMgr') type stuff, and use that to implement row-level security?

Somthing akin to a parameterized view that uses role membership as a parameter?

Does this make any sense at all?

----------------
-Stephen

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-18 : 03:25:49

Can you provide some examples of the kind of row-level logic you are wanting based on security group?

stephenbaer
Yak Posting Veteran

71 Posts

Posted - 2008-08-19 : 11:42:51

Absolutely. Say table1 is a list of employees, and each has a supervisor, as indicated from the foreign key SuervisorID. That supervisor will see only the records that he or she supervises. That part is pretty simple. However, if we add another level of supervisor, like a manufacturing director, that person would see the shop manager, plus everyone he or she supervises, as well as say the quality manager, and the quality employees, but not the sales manager, or anyone below that person.

I was hoping I could just add roles, and go up... so the Mfg Director would be a member of the shopmgr role, as well as one higher up.
The a single view would be parameterized by role membership, allow multiple uses, without having to create new views for different combinations. As one more example, lets say there was a safety manager, as well as a 'safetymgr' role, who could see all of the 1st level employees, but none of the supervisors or above.

Basically, I need a way to check role membership, then filter the view based on that membership.
By the way, what table are role memberships stored in?

----------------
-Stephen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources