June 13, 2005 at 10:58 am
I am looking for feedback from a more experienced dba. I am trying to implement role-based database access at least five different databases.
I have sixty different applications that I would like to create roles for. If a user is in a functional role that requires the use of six applications, they would belong to 6 database roles in each of the databases.
Does anyone have experience implementing this many roles across this many databases. Am I going to run into problems trying to implement 60 roles in each of the five databases?
This is a Microsoft SQL Server 2000 environment.
June 13, 2005 at 12:56 pm
Nick,
First of all you need to evaluate every one of 60 applications what authentication they use. I did not hear yet that 60 apps use only SQL Server authentication or all use Windows authentication.
Most common case is that you don't have control on what to use, it is a vendor's installation program that creates appropriate roles or it is application authentication that is used and then application uses either application login or application role.
Are you talking about SQL Server Roles? Lokks like it. Who do you add to them, SQL standard logins or Windows logins? How many users? Who will be doing user management? It is not difficult to implement that many roles, but very difficult to maintain accounts in that many roles unless you map Windows Groups to SQL Server Database Roles
Yelena
Regards,Yelena Varsha
June 13, 2005 at 1:00 pm
Would every role need access to each of the five databases? And if so, you should look for the lowest common denominator. It will probably turn out that many of functional roles need exactly the same level of access to one or more of your databases so they can be combined into one database role. The functional roles would be defined by the various database roles they are members of.
Are you by any chance using Windows authentication? Setting up Windows groups and granting those groups membership in the various database roles is the way to go if possible. That way as people come and go, they just need to be added or deleted from the Windows groups and you as a DBA may not even need to be involved (other than on the initial setup of course). I, personally try very hard to stay out of user administration if I can help it.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 13, 2005 at 1:46 pm
Thanks for the feedback!
I am looking at about 100 users growing to 200 users.
We using 60 home grown utility applications that rely on the user's windows security context for authentication.
The users are in abut a dozen windows groups that reflect funtional roles within the company. SQLServer uses windows authentication. I propose to setup a sqlserver login for each functional nt-group and give that login access to each of the databases used by that functional group. I will then make each database login a member of the roles that correspond to the applications that they need to use.
The roles then have access to those views and stored procs used by the each application.
Once all of this is in place, the only administration should be adding and removing users from NT groups (nmj!) or, heaven forbid, we come up with another utility app.
June 13, 2005 at 1:54 pm
Sounds about right... One bit of advice: do your homework and create as few database groups as possible. Again, look for the commonality between them at the database level, while still holding to the principle of least privelege. Doing this may greatly simplify administration and troubleshooting down the road...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply