A bit OT: Database security

ajj3085 posted on Monday, December 15, 2008

Hi,

I'm at a point where I need to be able to build an admin interface, so that access to types and permissions can be managed by another person, probably my IT guy.  Right now allowed roles are hard coded into classes.   This part is easy enough to build.. I already have a way to map Windows AD security groups to roles within my application. 

The tricky part is the database; I'm using the Windows AD security groups to grant permissions there too.  Each group is assigned to a database role (which matches with the roles defined in my system currently.. all managed by me), and the database role is given permission to select from certain views and execute certain stored procedures.

I have a couple of thoughts on this, neither I'm really keen on.

One is to drop the database role concept entirely, which then opens up everything to everyone.  The problem is I already know of some rouge users that have attempted to directly communicate with the database (even though the API I provide could have done what they wanted).  I'm leary of these users finding out they can call directly into the database. 

The other option is to include within my new security management API code that will also create appropriate database roles.  I'm also not too sure about that. Also, the roles would have to be granted permissions on database objects, and I'm not sure how the API would "know" which objects to affect.  I suppose I might be able to do this as well, storing some kind of meta data in the database..

I'm sure this has come up before.. what have others done?

Jack replied on Wednesday, December 17, 2008

I'm assuming that roles in SQL Server are much the same as roles in Oracle but for an inhouse product I don't think that is an issue - especially if each user is logging into the database.  I've worked on plenty of apps that had a commonLogin interface that pulled the list of roles from the database for the current user and then activated them.

Oracle has the concept of a default role - which means it is on/off by default.  If it is off then you have to actually turn it on with a password.  This stops the users from logging into the database on their own because they don't have the ability to 'activate' the role.  I would guess SQLServer has a similar ability.

Other than that method (which can be a pain with a large user base) I've always stuck with role_users that are different than the database owner.  So app_user, app_batch, app_admin sort of thing and then depending on the functionality log into the dbase with that role_user_id.  Then the access to those features is managed with AD groups.

My current project, which is hosted and maintained only by me, I'm falling back to the manage a list of permissions (or groups of permissions by a role).  I have a add/remove permissions interface by user_id and then I restrict app access by permissions.  Each user then logs into that app as user_id but most the database access is via a client_app_user id that has appropriate roles/rights in the database. 

My scenario is a bit more complicated as I've got a single schema for different clients but the concept works with a single client.

Not sure if that helps or just re-enforces your plan.  I find the biggest issue is how the persmissions have to be maintained / administered.  A lot depends on the politics of the site about security etc.  It is far quicker to get some database access than it is to get new AD roles setup but the HelpDesk can assign AD roles at our site so the user community can add/remove new users without our help when it is AD based.  If you can get out of the way all the better.

Jack