Our database's security became a problem recently. Here is what we have:
Several basic roles -
- read_only (select, view definition)
- read_write (insert/update/deleted)
- executor (execute SP/function)
- ddl_admin (alter/create table/view/SP/function/trigger/almost any thing, it's a risk role and we are going to discard it)
- several ssis roles for ssis jobs
Those roles are in every database and in all environments (DEV/QA/STG/PROD).
We also have a bunch of user groups such as tech_leads, QA, developers (based on different projects), reporting, BA and so on.
We grant those users different roles on different environments. Usually people have more privileges on DEV/QA than STG/PROD. We almost only give regular user read only in production databases.
However, there were many occasions that users come to ask specific permissions on different servers. For example, the tech_leads may ask for server control on DEV/QA. Or one developer team ask for read_write permission on STG temporarily for trouble shooting.
After a long time, we got a massive database security.
Now our DBAs trying to think of some better ways to re-regulate the users and roles. We got advised that creating some new roles such tech_leads role, QA_readonly role, QA_leads role, developer role, and granting those roles to relevant groups.
The interesting thing here is, in same environment, say QA environment, the same role will have same permissions across all databases. But the role on PROD environment will be different than QA, meaning has much less permissions on PROD. Every time we modify the role in one database, we have to modify the role on all databases in that environment (we will have scripts for this of course).
I can't tell if the advise is good for long term yet. But another opinion would be keeping most of our basic roles, splitting some of the existing roles and create more tiny roles. For example, removing ddl_admin, and add a role to create SP/trigger/function, a role to create table/view, a role to alter, and a role to create synonym. This way, all of our roles will be static across all databases, and all environments.
I want to know how you guys control your database security? And can you give us any suggestions on what I talked above? Thanks very much.