0

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

  1. Disabling the security policy, i.e. ALTER SECURITY POLICY ... WITH (STATE=OFF)
  2. Impersonating a login or user with elevated permissions
  3. Creating a login, database role, or user (i.e. to obtain elevated permissions)
  4. Adding self to a role with higher privileges, i.e ALTER ROLE [db_owner] ADD MEMBER LimitedAdmin
  5. Creating a stored procedure that will execute as a higher-priviledged user, i.e. CREATE PROCEDURE usp_Malicious WITH EXECUTE AS OWNER...

Caveats

  1. Everything listed in this blog post by Andreas Wolter
  2. 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.
  3. 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.

  • Statement 3 in caveats is false - since you denied ALTER ANY SECURITY POLICY TO [LimitedAdmin_usr], then they can't alter [AnotherLimitedAdmin]'s security policy. This would only be an issue if you missed setting this DENY on some limited_admin user. – Laughing Vergil Jan 9 at 19:19
1

A better approach to me would seem to be setting up the LimitedAdmin group at the server level, then set up triggers at the server and/or or DB level that check when the command(s) you want to block is run.

In each trigger, check to see if the user is in the LimitedAdmin group, then abort the DDL trigger if they are. This handles multiple different conditions with multiple similar triggers (i.e. ALTER SERVER ROLE and ALTER ROLE are two different triggers you would need). This would seem to offer finer control than the simple DENY statements.

As an example, suppose LimitedAdmin user [bob] was to trigger an ALTER SERVER ROLE event. The trigger could check what role was being altered, and decide if it was okay for LimitedAdmin users could alter that role in that way for the target user.

For example, adding another LimitedAdmin user should be disallowed for a LimitedAdmin user to perform, but they might be allowed to add an 'AccountingSystem' user. One trigger could be used to check all of the possibilities. This would mean that once the triggers are configured, adding a user to the LimitedAdmin group should be all of the configuration that you need.

| improve this answer | |
  • Thank you for this suggestion. How would I prevent LimitedAdmin from altering or dropping the trigger? – mlattis Jan 9 at 20:27
  • Use a trigger. if you have a CREATE, ALTER, or DROP TRIGGER command, check to see if it is one of your restricted triggers. Using a specific naming convention or a specific schema for these triggers, separate from any used in any production code, will allow you to identify them, i.e. assign them all to the schema admin, then have a trigger that checks if a LimitedAdmin member is attempting to act on a trigger in the admin schema. – Laughing Vergil Jan 9 at 22:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.