SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by R Glen Cooper / Row Oriented Security Using Triggers / Latest Posts

RE: Row Oriented Security Using Triggers

L' Eomot Inversé — Sat, 30 Jul 2011 09:20:59 GMT

message in wrong forum. Deleted. Sorry

RE: Row Oriented Security Using Triggers

L' Eomot Inversé — Sat, 30 Jul 2011 09:01:32 GMT

[quote][b]Glen Cooper (4/27/2010)[/b][hr]The app's fixed login string is not accessible to the user.The lkpUser table checks user name/pwd which are passed by the app after the user enters a name/pwd when starting it (not part of the demo program).[/quote]Means you trust your users never to run a network monitor, never to write a proxy for the tcp api, etcetera (since you've said current deployment has the apps on desktops but only the apps, not the users, are allowed to connnect). It is very easy to get a connection string! If you are going to trust the users that much, why not just trust them not to access data they are not supposed to? I imagine these users are not professional programmers, but are none of them amateur computer enthusiasts with skills that would allow them to drive cart and horses through the obvious security hole?

RE: Row Oriented Security Using Triggers

Glen Cooper — Tue, 27 Apr 2010 16:44:44 GMT

The app's fixed login string is not accessible to the user.The lkpUser table checks user name/pwd which are passed by the app after the user enters a name/pwd when starting it (not part of the demo program).

RE: Row Oriented Security Using Triggers

Glen Cooper — Tue, 27 Apr 2010 16:29:15 GMT

Yes, I should have been more explicit when I indicated that users may access the database through approved applications only.

RE: Row Oriented Security Using Triggers

doofledorfer — Tue, 27 Apr 2010 16:04:05 GMT

[quote][b]Glen Cooper (4/27/2010)[/b][hr]The model was originally developed for a Citrix farm, where the front-end apps aren't available on anyone's desktop and where users aren't able to see behind the data entry forms. In the deployment currently under development, users do have the apps on their desktops but only the apps (not users) may connect to the database that's on a remote server.[/quote]That probably would have been useful to mention in your article. It's not a typical architecture.

RE: Row Oriented Security Using Triggers

RBarryYoung — Tue, 27 Apr 2010 15:13:18 GMT

[quote][b]Glen Cooper (4/27/2010)[/b][hr]...In the deployment currently under development, users do have the apps on their desktops but only the apps (not users) may connect to the database that's on a remote server....[/quote]How is that accomplished?

RE: Row Oriented Security Using Triggers

Glen Cooper — Tue, 27 Apr 2010 14:08:09 GMT

The model was originally developed for a Citrix farm, where the front-end apps aren't available on anyone's desktop and where users aren't able to see behind the data entry forms. In the deployment currently under development, users do have the apps on their desktops but only the apps (not users) may connect to the database that's on a remote server.

RE: Row Oriented Security Using Triggers

RBarryYoung — Tue, 27 Apr 2010 13:39:35 GMT

I am a little confused as using client-based code to implement [i]any[/i] security is strictly contra-indicated in any valid security model that I know of.For instance, what keeps your desktop users from firing up Excel or worse, Management Studio, connecting directly to your database, and bypassing this security scheme?

RE: Row Oriented Security Using Triggers

Glen Cooper — Fri, 23 Apr 2010 15:16:28 GMT

The database coordinator doesn't write triggers - just filters.For example, the coordinator in the Dean's office might want to allow Department heads in Pathology and Pediatrics to view those faculty that have appointments in both departments (all departments share the same database).So she'd write a filter on the demographic table selecting those faculty belonging to both departments, set up a new role, assign it to that filter, and then add that role to the roles each Department head already has.The fixed triggers do all the rest. So if someone is no longer a member of both departments, or the filter is replaced with another one that just selects recent appointments to both departments, then the two department heads will see different people when they log in again.Only single-record changes are triggered, because only single-record changes are made by the users (eg. adding new person, changing existing address, etc.).

RE: Row Oriented Security Using Triggers

doofledorfer — Fri, 23 Apr 2010 13:41:12 GMT

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.

RE: Row Oriented Security Using Triggers

Glen Cooper — Fri, 23 Apr 2010 12:56:29 GMT

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.

RE: Row Oriented Security Using Triggers

Trey Staker — Fri, 23 Apr 2010 11:52:00 GMT

Interesting solution to a business problem. You mentioned in your artice: "[i]The model is independent of SQL Server's own security, and may be used in other database products with minimal change."[/i]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.

RE: Row Oriented Security Using Triggers

Glen Cooper — Fri, 23 Apr 2010 10:37:42 GMT

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.

RE: Row Oriented Security Using Triggers

Knight — Fri, 23 Apr 2010 04:41:05 GMT

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:w00t:

RE: Row Oriented Security Using Triggers

Paul White — Fri, 23 Apr 2010 02:45:04 GMT

Glen,What are your thoughts on how the approach detailed in your article compares to:[url=http://msdn.microsoft.com/en-us/library/cc966395.aspx]Implementing Row- and Cell-Level Security in Classified Databases Using SQL Server 2005[/url]Paul

RE: Row Oriented Security Using Triggers

david_wendelken — Tue, 06 Apr 2010 19:16:17 GMT

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

RE: Row Oriented Security Using Triggers

Glen Cooper — Tue, 06 Apr 2010 19:10:34 GMT

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.

RE: Row Oriented Security Using Triggers

david_wendelken — Tue, 06 Apr 2010 18:19:56 GMT

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.

RE: Row Oriented Security Using Triggers

Glen Cooper — Tue, 06 Apr 2010 17:46:23 GMT

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.

RE: Row Oriented Security Using Triggers

david_wendelken — Tue, 06 Apr 2010 16:29:52 GMT

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

RE: Row Oriented Security Using Triggers

SQLRNNR — Tue, 06 Apr 2010 14:25:38 GMT

Thanks Glen for providing us with this article.

RE: Row Oriented Security Using Triggers

Glen Cooper — Tue, 06 Apr 2010 09:34:26 GMT

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.

RE: Row Oriented Security Using Triggers

SanjayAttray — Tue, 06 Apr 2010 09:14:16 GMT

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.

RE: Row Oriented Security Using Triggers

Glen Cooper — Tue, 06 Apr 2010 07:44:40 GMT

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.

RE: Row Oriented Security Using Triggers

alen teplitsky — Tue, 06 Apr 2010 07:32:07 GMT

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

RE: Row Oriented Security Using Triggers

Daniel Bowlin — Tue, 06 Apr 2010 07:28:42 GMT

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

RE: Row Oriented Security Using Triggers

Glen Cooper — Tue, 06 Apr 2010 07:25:39 GMT

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

RE: Row Oriented Security Using Triggers

david_wendelken — Tue, 06 Apr 2010 06:47:31 GMT

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.

Row Oriented Security Using Triggers

Glen Cooper — Tue, 06 Apr 2010 00:05:55 GMT

Comments posted to this topic are about the item [B]Row Oriented Security Using Triggers[/B]