March 22, 2013 at 9:57 am
All,
We are implementing RBAC(Role Based Access Control)....and I have to create number of customized DB roles with permission in all the instances/databases. Anyone have suggestions/script to accomplish this? Please advise.
Thanks,
SueTons.
Regards,
SQLisAwe5oMe.
March 22, 2013 at 10:06 am
SQL Server roles won't help here. Row based access means you're using joins to help accomplish this.
A couple links that might help:
http://technet.microsoft.com/en-us/library/cc966395.aspx
http://stackoverflow.com/questions/1122513/how-to-implement-database-access-control-on-row-basis
March 22, 2013 at 10:10 am
Steve Jones - SSC Editor (3/22/2013)
SQL Server roles won't help here. Row based access means you're using joins to help accomplish this.A couple links that might help:
http://technet.microsoft.com/en-us/library/cc966395.aspx
http://stackoverflow.com/questions/1122513/how-to-implement-database-access-control-on-row-basis
I think you misunderstood my question, since you mentioned row based and I said role based? Are we talking about the same here?
SueTons.
Regards,
SQLisAwe5oMe.
March 22, 2013 at 10:42 am
You are right, I did. I was thinking row and misread that. I've seen RBAC mostly as row based, not role.
In terms of roles, there isn't a script to do this because the roles will have disparate requirements. Ultimately you need to map roles to specific tables and rights. Build a grid of the roles (admin, manager, developer, reporting user, data entry, etc), the tables, and rights( select, insert, update, delete).
You can do this a few ways, but essentially drop two of these on the axis and the third in the middle.
Once you've determined who gets what rights, it's easy. Create the roles. Add the rights to the roles for the objects, move people into roles.
March 22, 2013 at 11:07 am
Steve Jones - SSC Editor (3/22/2013)
You are right, I did. I was thinking row and misread that. I've seen RBAC mostly as row based, not role.In terms of roles, there isn't a script to do this because the roles will have disparate requirements. Ultimately you need to map roles to specific tables and rights. Build a grid of the roles (admin, manager, developer, reporting user, data entry, etc), the tables, and rights( select, insert, update, delete).
You can do this a few ways, but essentially drop two of these on the axis and the third in the middle.
Once you've determined who gets what rights, it's easy. Create the roles. Add the rights to the roles for the objects, move people into roles.
Maybe I am still not clear with my requirement, I think there has to be a way that I can do this using script....because we have hundreds of insances and maybe 1000 or more databases, and I need to create these roles in every database.
Basically, we are looking to create some customized roles that will replace the SQL standard roles, for example db_datareader, etc......
So, you are saying this won't be doable?
SueTons.
Regards,
SQLisAwe5oMe.
March 22, 2013 at 11:19 am
SQLCrazyCertified (3/22/2013)
Steve Jones - SSC Editor (3/22/2013)
You are right, I did. I was thinking row and misread that. I've seen RBAC mostly as row based, not role.In terms of roles, there isn't a script to do this because the roles will have disparate requirements. Ultimately you need to map roles to specific tables and rights. Build a grid of the roles (admin, manager, developer, reporting user, data entry, etc), the tables, and rights( select, insert, update, delete).
You can do this a few ways, but essentially drop two of these on the axis and the third in the middle.
Once you've determined who gets what rights, it's easy. Create the roles. Add the rights to the roles for the objects, move people into roles.
Maybe I am still not clear with my requirement, I think there has to be a way that I can do this using script....because we have hundreds of insances and maybe 1000 or more databases, and I need to create these roles in every database.
Basically, we are looking to create some customized roles that will replace the SQL standard roles, for example db_datareader, etc......
So, you are saying this won't be doable?
SueTons.
What you want to do is doable, you just have to write the scripts. There is no magic script that is going to create the roles you need with the proper permissions for each role on each of the objects in each of the databases on hundreds of instances on an unknown number of servers. This is something you will need to determine. It will take time and effort to implement.
March 22, 2013 at 12:10 pm
I know this is doable by using sp_msforeachdb and a loop, but not really sure where to begin with.
SueTons.
Regards,
SQLisAwe5oMe.
March 22, 2013 at 1:05 pm
The way I would start, is do the first one manually creating the scripts as I went along using SSMS. Then it is just a matter of automating those scripts.
But you still need to start where Steve said before you even start appling these changes.
March 22, 2013 at 1:34 pm
Lynn Pettis (3/22/2013)
The way I would start, is do the first one manually creating the scripts as I went along using SSMS. Then it is just a matter of automating those scripts.But you still need to start where Steve said before you even start appling these changes.
Ok, Thank you, I will look into it.
SueTons.
Regards,
SQLisAwe5oMe.
March 22, 2013 at 6:17 pm
Are you saying you want a read only and a read/write role? Separate from db_Datereader/writer?
That's easy to script.
loop through all tables in all databases, grant rights to a standard named role (MyReadRole).
Add users to the role.
However if you want something that's not a pattern, you have to do it manually.
March 22, 2013 at 10:48 pm
Steve Jones - SSC Editor (3/22/2013)
Are you saying you want a read only and a read/write role? Separate from db_Datereader/writer?That's easy to script.
loop through all tables in all databases, grant rights to a standard named role (MyReadRole).
Add users to the role.
However if you want something that's not a pattern, you have to do it manually.
Agreed on the manual aspect for the roles that don't follow a pattern.
If a decision is made to grant access via stored procedures, the manual labor becomes a little easier.
But I have to wonder - if you are looking to recreate db_datareader, why?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 15, 2014 at 8:34 am
If you want to comply with the principle of Least Privilege, then you should start by talking with business.
I usually take the time to talk with business about their business roles, and what they do.
This I use to make a logical model, and when we agree on the functionality and the principles then I can make a physical model.
It is very important if you work in a high-security organisation to know the demands of security and audit.
With a physical model, that is acceptepted by business, I can implement roles by AD-groups and user defined database- or server-roles. I do not use the default roles, as they do not comply with the principle of Least Privilege.
The roles I usually named by their function, which helps business, operations and service disk in the daily administration.
This is a huge task, but you will get new and unique knowledge about the business. In the long run your work will pay off.
/Niels Grove-Rasmussen
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply