Row Oriented Security Using Triggers

Question

Post reply

Row Oriented Security Using Triggers

Glen Cooper

SSCommitted

Points: 1917
More actions
April 6, 2010 at 12:05 am

#220042

Comments posted to this topic are about the item Row Oriented Security Using Triggers

Viewing 15 posts - 1 through 15 (of 28 total)

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

david_wendelken SSCrazy Points: 2388 More actions · Answer 1

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 SSCommitted Points: 1917 More actions · Answer 2

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

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 3

Really interesting article. But wouldn't this tend to bog down a high volume transactional database?

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 4

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 SSCommitted Points: 1917 More actions · Answer 5

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.

SanjayAttray SSChampion Points: 13157 More actions · Answer 6

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 SSCommitted Points: 1917 More actions · Answer 7

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.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

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[/url]
Learn Extended Events

david_wendelken SSCrazy Points: 2388 More actions · Answer 9

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...

Glen Cooper SSCommitted Points: 1917 More actions · Answer 10

Hi David,

I may not have expressed myself clearly, so let me take another stab at it.

Using the article's simple example, we may have several dozen overlapping filters on the gateway table (eg. F1=those who work in Vancouver, F2=those who work in Chicago, F3=those who work in Oregon, F4=those who work in Canada, etc.).

If Bob is restricted to viewing those who work in Vancouver or Chicago, then we could write for him the view F1 U F2 (where U means union). If he later gets clearance for Oregon, then a new view F1 U F2 U F3 could be assigned to him. But instead, we (effectively) pass him the security table tblRoleAccessSelect where the rows he can see have already been selected (ie flagged) by the triggers. That way, we don't have to re-create views for him every time his security changes since the triggers do all the work.

(Strictly speaking, we're working with the roles assigned to users, but the idea is the same).

If I understand you correctly, you're suggesting that dynamically-generated views could be passed to users as they sign on to any app (depending on their current security in the database). That may be cleaner, actually. On the other hand, the "fixed set" approach has the advantage that you only need to decide once whether a given gateway record is captured by a given filter (which may be assigned to several concurrent users). And you still need to take distinct unions of all those filters assigned to any user if we're dynamically building views for them at run time.

I don't know which approach is more efficient.

david_wendelken SSCrazy Points: 2388 More actions · Answer 11

I'm clearly not communicating my approach to you.

one data table, one view to go with it.

The view has a where clause in it.

That where clause would apply whatever filtering is needed.

For example, you want to filter on city.

You would need a security access table called, for example, person_city_approved_access.

Columns in that table would include a column to identify the person and another to identify the city. I am assuming that a database function the view can use in the where clause can identify the person's id from the user running the query.

So, the view needs to filter the data table using the city code in the table via a join to the person_city_approved_access table.

Hope that is clearer.

Glen Cooper SSCommitted Points: 1917 More actions · Answer 12

Hi David,

Sorry, that's not what I thought you were suggesting.

Do we really want software that builds, indexes, and maintains a new table for each new filter? There could be hundreds of them which come and go over time.

I should emphasize that the filters are meant to be very dynamic - administrators write their own on the fly, and put them in the tblRoleAccessSelectFilter table when they want them assigned to various roles. At that point the security table tblRoleAccessSelect is automatically re-computed for the benefit of those users who are assigned roles attached to the new filter.

Regarding efficiency, take the extreme example of filtering those people whose ID is the product of two prime numbers. If 50 people use that filter, do we really want the server doing that kind of computation for each of them every time they sign on? With the fixed set, it's only done once for each role using that filter.

Still, I believe that filters silently built for each user at run time is an excellent approach (I think Oracle does something along those lines). I just don't know about the relative efficiency of each method.

david_wendelken SSCrazy Points: 2388 More actions · Answer 13

Glenn,

If you have one table that holds all your filters, why can't the views just join to it? It's that simple.

Paul White SSC Guru Points: 150467 More actions · Answer 14

Glen,

What are your thoughts on how the approach detailed in your article compares to:

Implementing Row- and Cell-Level Security in Classified Databases Using SQL Server 2005

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi