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 6, 2010 5:46 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:10 PM
Points: 52, Visits: 263
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
Post #898024
Posted Tuesday, April 6, 2010 6:19 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

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.
Post #898036
Posted Tuesday, April 6, 2010 7:10 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:10 PM
Points: 52, Visits: 263
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
Post #898053
Posted Tuesday, April 6, 2010 7:16 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

Glenn,

If you have one table that holds all your filters, why can't the views just join to it? It's that simple.
Post #898055
Posted Friday, April 23, 2010 2:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 11,192, Visits: 11,098
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #909264
Posted Friday, April 23, 2010 4:41 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, April 25, 2014 1:07 AM
Points: 699, Visits: 479
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 ;) Otherwise its always interesting to see how ppl deal with business issues in different ways




Post #909307
Posted Friday, April 23, 2010 10:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:10 PM
Points: 52, Visits: 263
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
Post #909669
Posted Friday, April 23, 2010 11:52 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 11, 2014 12:42 PM
Points: 1,380, Visits: 2,682
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
Post #909713
Posted Friday, April 23, 2010 12:56 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:10 PM
Points: 52, Visits: 263
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
Post #909768
Posted Friday, April 23, 2010 1:41 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, April 26, 2013 10:40 AM
Points: 103, Visits: 129
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.
Post #909788
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse