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 Friday, April 23, 2010 3:16 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 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.).


R Glen Cooper
Post #909837
Posted Tuesday, April 27, 2010 1:39 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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?


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #911415
Posted Tuesday, April 27, 2010 2:08 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 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.



R Glen Cooper
Post #911447
Posted Tuesday, April 27, 2010 3:13 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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?


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #911490
Posted Tuesday, April 27, 2010 4:04 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
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.
Post #911518
Posted Tuesday, April 27, 2010 4:29 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
Yes, I should have been more explicit when I indicated that users may access the database through approved applications only.


R Glen Cooper
Post #911532
Posted Tuesday, April 27, 2010 4:44 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 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).



R Glen Cooper
Post #911542
Posted Saturday, July 30, 2011 9:01 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 7:14 AM
Points: 8,739, Visits: 9,286
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
Post #1151437
Posted Saturday, July 30, 2011 9:20 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 7:14 AM
Points: 8,739, Visits: 9,286
message in wrong forum. Deleted. Sorry

Tom
Post #1151440
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse