Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Row Oriented Security Using Triggers Expand / Collapse
Author
Message
Posted Tuesday, April 06, 2010 12:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 11, 2014 3:20 PM
Points: 51, Visits: 255
Comments posted to this topic are about the item Row Oriented Security Using Triggers

R Glen Cooper
glencooper.tel
Post #897330
Posted Tuesday, April 06, 2010 6:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 19, 2013 2:00 PM
Points: 183, Visits: 479
Hi,

Thanks for sharing this idea.

I'm confused about one point, and have a design question on another.

As for the confusion, it appears that you've disabled the security feature if someone inserts more than one row at a time in the same statement. Example: insert into some_table (col1,col2) select cola,colb from some_other_table.

Did I misunderstand that? If not, why limit the security?

As for the design question, why not create views to do the filtering against the table, and write the applicaitons to use the views, not the tables? The same filtering mechanism, the key table, would work.

Post #897490
Posted Tuesday, April 06, 2010 7:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 11, 2014 3:20 PM
Points: 51, Visits: 255
Good questions.

Security is disabled for multiple records in this article to keep things simple.

The model allows one to dynamically change the security of any user without explicitly changing any view, since it's the tblRoleAccessSelect table that points to what records one may access (rather than a view).

To use the article's example, suppose two users have a single role that allows them to see anyone in Vancouver. To allow one, but not the other, to also see anyone in Chicago, we create a filter for that city and then add that filter to a new role that the user is then assigned. The tblRoleAccessSelect table is then automatically changed by the security triggers, which is the only thing that the apps "see".

Glen


R Glen Cooper
glencooper.tel
Post #897534
Posted Tuesday, April 06, 2010 7:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:16 PM
Points: 2,812, Visits: 2,543
Really interesting article. But wouldn't this tend to bog down a high volume transactional database?
Post #897536
Posted Tuesday, April 06, 2010 7:32 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:46 PM
Points: 1,413, Visits: 4,531
very interesting

we've used something similar to this but a lot simpler for almost 10 year to store business rules for applications and users.


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #897542
Posted Tuesday, April 06, 2010 7:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 11, 2014 3:20 PM
Points: 51, Visits: 255
We haven't found that to be a problem, but then we're only allowing single record operations by the apps (add a record, change an existing record, etc.).

This model was originally deployed in a large faculty where about 100 users in 17 departments were assigned various combinations of several dozen constraints. The security table tblRoleAccessSelect has 500,000 records (on average) with no performance problems.

Currently we're using this for an ice road company in Nunavik where each camp can "see" only those workers onsite, and where each site administrator changes records for just one worker at a time. As soon as an employee changes sites, he automatically disappears from his current site.


R Glen Cooper
glencooper.tel
Post #897555
Posted Tuesday, April 06, 2010 9:14 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 11:58 AM
Points: 3,924, Visits: 1,588
Good reading. But, how about the performance on high availability high volume application ? Had you tested it and got results, then please post it. Would be interesting to implement.

Managers always loves the something different approach than the conventional (which is tried and tested by all). Would be a good presentation material.

Thanks.


SQL DBA.
Post #897622
Posted Tuesday, April 06, 2010 9:34 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 11, 2014 3:20 PM
Points: 51, Visits: 255
Performance wasn't an issue since multiple record changes weren't allowed in the apps (nor were they needed for our purposes since users only edit one record at a time). The original database had about 8000 gateway records (faculty, students, staff), 100 users, and 75 filters (where each role is assigned about 1-5 of them).

It would be a nightmare to maintain individual filters for each user, so we used a master set of pointers to the gateway table where each role is effectively mapped to its own subset of it by the security triggers which are sensitive to any subsequent data/filter changes.


R Glen Cooper
glencooper.tel
Post #897640
Posted Tuesday, April 06, 2010 2:25 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:16 PM
Points: 20,462, Visits: 14,091
Thanks Glen for providing us with this article.







Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #897897
Posted Tuesday, April 06, 2010 4:29 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 19, 2013 2:00 PM
Points: 183, Visits: 479
Thanks for clarifying.

But if I did the view, the query inside the view definition would be joined to the filter table, so the view wouldn't need to be changed.

At least, as long as the database knew who was running the query...
Post #897998
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse