our security team aim to develop and apply SOD Concept on SQL server Data Base , is there any ideas to start , also i want your experience on that field
Basic ideas:
- Principle of least permission. Only grant permissions needed and nothing else
- Principle of double abstraction:
a. Create database and/or instance roles for certain types of access
b. Grant permissions according to the PoLP above to the roles
c. Add AD groups to the roles
d. Add users to the groups.
1 Like
Another thing one can do is to create a login without a password or with a password only known to the DBAs with DML permissions through procedures with execute as the login created above. Then grant execute on procedures to users and application users. This effectively add a security layer further insulating you data from unwanted changes.
Thanks a lot for that ideas , i have concern about object ownership which i mean is when user create Database object , that user can grant privileges to the others is it exist on SQL ??
Sure, but you can deny the create database permission
you need a plan boss. write it down, document it. some security governance. don't let it grow organically willy-nilly. especially if you work with some super confidential data (aka gov, finance, health care) and you get audited or hacked you do not want to have something that will get you dinged.
If Create Database permission implicitly included by SYSADM , can i revoke it from SYSADM
Unless you have total control of the AD groups I would not assign groups in SQL Server, I have seen it happen where admins, just wanting it to work, have added further groups to the AD group, in one case it ended up with 9500 unauthorised users with access to a database...
That said, the downside to adding users individually is an overhead on the management of granting and revo
king access...but then we are DBA's and keeping the data safe is our job...
2 Likes
Good point. Where I work there's a policy disallowing nested groups and an independent control point policing that
1 Like