Click here to monitor SSC
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
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 287
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.

R Glen Cooper
david_wendelken
david_wendelken
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 480
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
Glen Cooper
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 287
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.

R Glen Cooper
david_wendelken
david_wendelken
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 480
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
Paul White
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11222 Visits: 11354
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
SQLblog.com
@SQL_Kiwi
Knight
Knight
Right there with Babe
Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)

Group: General Forum Members
Points: 741 Visits: 535
I think its worth clarifying that this is the sort of db you never expect to see under any sort of serious load. With that in mind ppl may well review this differently but you code would not really suit a 'under stress' production load Wink Otherwise its always interesting to see how ppl deal with business issues in different ways

w00t



Glen Cooper
Glen Cooper
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 287
Hi Paul,

The MS example uses pre-defined labels and associated categories to define access, whereas this project uses ordinary filters without any reference to security. So administrators can write any filter specifying row level access, and users (or roles, actually) have access to the union of whatever filters they've been assigned. Such assignments are dynamic, in that adding/deleting filters/roles is automatically reflected in what each user sees. This made the model intuitive for administrators, and trivial to implement for legacy apps.

High volume transactions are irrelevant since security is only applied to a gateway table and users only update single records at a time on that table.

R Glen Cooper
Trey Staker
Trey Staker
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1208 Visits: 2788
Interesting solution to a business problem.

You mentioned in your artice: "The model is independent of SQL Server's own security, and may be used in other database products with minimal change."

Have you implemented this methodology in other RDBMS? I seem to recall a similar approach years ago in informix to an accounting system I supported but I don't recall the details.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
Glen Cooper
Glen Cooper
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 287
The model has been implemented in SQL Server 2000/2005, but requires no special features of that product.

Essentially, the model presents to the user a set of records from a single table that's generated by the union of a set of filters, and which is maintained by triggers. So changes to the underlying data (which affects each filter's output), or even changing the set of filters, is automatically witnessed by each user in a way that depends on what filters they've been assigned by way of their roles.

A similar thing could be written for intersections, so the model's core idea may be useful elsewhere.

R Glen Cooper
doofledorfer
doofledorfer
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 135
Did I understand that you have Administrators writing rules that are passed through triggers? Given the inherent difficulty of debugging triggers isn't that an issue?

Don't you have an issue where developers write queries against these tables and are surprised because things are happening that aren't explained between their SQL and the table schema?

I understand why you only apply it to single-row queries. The ability to grasp how triggers work on multi-row selects is more than I would expect from many programmers, much less Administrators.
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