I would like to know if there is a practical way to create an admin role in SQL Server that can essentially do anything except alter row level security policy.
It is simple enough to create row level security policy that will hide data from admins, but much harder of course to prevent the same admins from altering or circumventing the policy.
I have (I think) a working scenario using CONTROL SERVER and some user-level permissions, but there are caveats (see below) and there may also be vulnerabilities that I haven't thought of. One complicating factor is the fact that "ALTER ANY SECURITY POLICY" is a database level permission, and cannot be denied at the server level (I don't think?).
Here is what I have so far (assume security policy in question pertains to a db called "SecurityTest"):
USE [SecurityTest] GO CREATE LOGIN [LimitedAdmin] WITH PASSWORD = '...'; CREATE USER [LimitedAdmin_usr] FROM LOGIN [LimitedAdmin] /* Server role */ CREATE SERVER ROLE [srvrole_LimitedAdmin] GRANT CONTROL SERVER TO [srvrole_LimitedAdmin] DENY IMPERSONATE ANY LOGIN TO [srvrole_LimitedAdmin]; -- Cannot impersonate a sysadmin or other more priviledged principal DENY ALTER ANY LOGIN TO [srvrole_LimitedAdmin]; -- Cannot create or modify another login or user (i.e. to create a higher-priviledged login for themselves to use) DENY ALTER ANY SERVER ROLE TO [srvrole_LimitedAdmin]; -- Cannot tamper with this or other server roles ALTER SERVER ROLE [srvrole_LimitedAdmin] ADD MEMBER [LimitedAdmin] /* Database restrictions */ DENY ALTER ANY SECURITY POLICY TO [LimitedAdmin_usr] DENY ALTER ANY ROLE TO [LimitedAdmin_usr]
It appears that this configuration will prevent "LimitedAdmin" from
- Disabling the security policy, i.e.
ALTER SECURITY POLICY ... WITH (STATE=OFF)
- Impersonating a login or user with elevated permissions
- Creating a login, database role, or user (i.e. to obtain elevated permissions)
- Adding self to a role with higher privileges, i.e
ALTER ROLE [db_owner] ADD MEMBER LimitedAdmin
- Creating a stored procedure that will execute as a higher-priviledged user, i.e.
CREATE PROCEDURE usp_Malicious WITH EXECUTE AS OWNER...
- Everything listed in this blog post by Andreas Wolter
- Admins defined this way would not be able to manage users, logins and roles, or impersonate other logins. This seems like the biggest "Catch 22". Perhaps stored procedures could be used to allow pre-defined user management tasks within specific parameters.
- LimitedAdmin could collude with another limited admin by granting him/her permission to alter the security policy, i.e.
GRANT ALTER ANY SECURITY POLICY TO [AnotherLimitedAdmin].
Any thoughts on improving (or abandoning) this effort would be greatly appreciated.