SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Row Oriented Security Using Triggers


Row Oriented Security Using Triggers

Author
Message
Glen Cooper
Glen Cooper
Old Hand
Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)

Group: General Forum Members
Points: 345 Visits: 287
Comments posted to this topic are about the item Row Oriented Security Using Triggers

R Glen Cooper
david_wendelken
david_wendelken
SSC-Addicted
SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)

Group: General Forum Members
Points: 428 Visits: 480
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.
Glen Cooper
Glen Cooper
Old Hand
Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)

Group: General Forum Members
Points: 345 Visits: 287
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
Daniel Bowlin
Daniel Bowlin
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7870 Visits: 2629
Really interesting article. But wouldn't this tend to bog down a high volume transactional database?
alen teplitsky
alen teplitsky
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6990 Visits: 4674
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.
Glen Cooper
Glen Cooper
Old Hand
Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)

Group: General Forum Members
Points: 345 Visits: 287
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
SanjayAttray
SanjayAttray
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5661 Visits: 1619
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.
Glen Cooper
Glen Cooper
Old Hand
Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)Old Hand (345 reputation)

Group: General Forum Members
Points: 345 Visits: 287
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
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65653 Visits: 18570
Thanks Glen for providing us with this article.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

david_wendelken
david_wendelken
SSC-Addicted
SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)

Group: General Forum Members
Points: 428 Visits: 480
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...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search