April 23, 2010 at 4:41 am
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:
April 23, 2010 at 10:37 am
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.
April 23, 2010 at 11:52 am
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
April 23, 2010 at 12:56 pm
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.
April 23, 2010 at 1:41 pm
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.
April 23, 2010 at 3:16 pm
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.).
April 27, 2010 at 1:39 pm
I am a little confused as using client-based code to implement any 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?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 27, 2010 at 2:08 pm
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.
April 27, 2010 at 3:13 pm
Glen Cooper (4/27/2010)
...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.
...
How is that accomplished?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 27, 2010 at 4:04 pm
Glen Cooper (4/27/2010)
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.
That probably would have been useful to mention in your article. It's not a typical architecture.
April 27, 2010 at 4:29 pm
Yes, I should have been more explicit when I indicated that users may access the database through approved applications only.
April 27, 2010 at 4:44 pm
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).
July 30, 2011 at 9:01 am
Glen Cooper (4/27/2010)
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).
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?
Tom
July 30, 2011 at 9:20 am
message in wrong forum. Deleted. Sorry
Tom
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply