SQL Server 2005: building security model based on DDL triggers.

Article
01/14/2007

Last month I’m working with a client to create something like non-standard security model. He asked for the following features:

- server–level management only for sa (server admin).

- database–level management for dbo (one or more dbo per database). Dbo is responsible for database's user management, update / refresh structure etc.; but dbo should not be able to alter database.

- dbo as well as other database persons should not be able to make a database backup (or log file backup). This is necessary because database backups covered by Veritas software and any unwanted backup may hang database recovery process.

- strong security, to restrict sa rights from been captured by dbo.

So we’ve decided to start from server-level permissions. To capture server control somebody can run

GRANT CONTROL SERVER TO test_user.

To run this statement you should be a member of sysadmin server role, but this is not a big problem for dbo at all (see below).

To prevent form this issue happens we’ve decided to use server-level events. Lets have a look :

CREATE TRIGGER [ddl_restrict_any_serverlevelpermission]

ON ALL SERVER

-- i'm using event groups here, but it is possible to track events more precise.

FOR DDL_GDR_SERVER_EVENTS

DECLARE @data XML

SET @data = EVENTDATA()

--SELECT @data

<EVENT_INSTANCE>

<EventType>GRANT_SERVER</EventType>

<Permission>control server</Permission>

</Permissions>

</Grantees>

<CommandText>GRANT CONTROL SERVER TO test_user</CommandText>

</TSQLCommand>

</EVENT_INSTANCE>

DECLARE @event_type nvarchar(200), @login_name sysname, @grantor sysname, @grantee sysname

-- actualy you can find spid here, but this value useless

SELECT @event_type = @data.value('(/EVENT_INSTANCE/EventType)[1]','sysname')

, @login_name = @data.value('(/EVENT_INSTANCE/LoginName)[1]','sysname')

, @grantor = @data.value('(/EVENT_INSTANCE/Grantor)[1]','sysname')

, @grantee = @data.value('(/EVENT_INSTANCE/Grantees/Grantor)[1]','sysname')

-- send email to sa before you will raise error

--EXEC msdb.dbo.sp_send_dbmail @recipients='server_admin@Adventure-Works.com',

-- @subject = 'Attempt to catch server-level permission',

-- @body = @data,

-- @body_format = 'HTML'

-- additional features.

-- 1. we can analyze <grantor> here to add some additional logic here.

-- 2. you can analyze <grantee> list here to restrict the list of persons from been granted server control (for example, by mistake or misprint).

-- 3. for demostration purposes we will restrict any kind of server-level permissions at all.

RAISERROR ('GRANT/DENY/REVOKE SERVER level permission denied! Ask your server administrator.',10, 1)

ROLLBACK

That’s all. This trigger will inform server admin for any attempt to grant / deny / revoke any server level permission.

After that we will prevent dbo from altering database the same way:

CREATE TRIGGER [ddl_restrict_alterdatabase]

ON ALL SERVER

FOR ALTER_DATABASE, DROP_DATABASE

DECLARE @data xml

DECLARE @trigger_name sysname, @LoginName sysname, @UserName sysname, @dbname sysname

SET @data = EVENTDATA()

--SELECT @data

<EVENT_INSTANCE>

<EventType>ALTER_DATABASE</EventType>

<DatabaseName>TestSecurity</DatabaseName>

<CommandText>ALTER DATABASE [TestSecurity] SET RECOVERY SIMPLE WITH NO_WAIT

</CommandText>

</TSQLCommand>

</EVENT_INSTANCE>

-- send email to self before you will raise error

-- EXEC msdb.dbo.sp_send_dbmail @recipients='server_admin@Adventure-Works.com',

-- @subject = 'Attempt to alter database',

-- @body = @data,

-- @body_format = 'HTML'

-- If you want to allow ALTER DATABASE operation to some person – please use <login_name> tag and add some logic here

-- If you want to exclude your personal databases from been tracked by this trigger – please pay attention on <DatabaseName> tag.

RAISERROR ('ALTER DATABASE DISABLED!',10, 1)

ROLLBACK

Also we will protect sa from any server-level authorization attemts:

CREATE TRIGGER [ddl_deny_alter_serverauthorization]

ON ALL SERVER

FOR DDL_AUTHORIZATION_SERVER_EVENTS

DECLARE @data XML

SET @data = EVENTDATA()

--SELECT @data

-- send email to self before you will raise error

--EXEC msdb.dbo.sp_send_dbmail @recipients='server_admin@Adventure-Works.com',

-- @subject = 'Attempt to alter server-level object ownership',

-- @body = @data,

-- @body_format = 'HTML'

RAISERROR ('Alter server-level authorization denied. Ask your server administrator.',10, 1)

ROLLBACK

That’s all for server-level section. Below we will describe some database-level triggers. To improve your audit procedures you (server administrator) can use WITH ENCRYPTION option to prevent dbo from viewing the sources.

The first goal is to protect database from any attempt of making backup. How we will cover this requirement:

- sa will create a script, this script will DENY BACKUP DATABASE, BACKUP LOG for each database user. This option will restrict even dbo from making backup. Nobody from db_backupoperator will be able to make backup J. Suddenly dbo or member of db_securityadmin still be able to return this privilege back with using GRANT BACKUP DATABASE TO <account>. Lets restrict these persons from been doing that.

CREATE TRIGGER [ddl_denybackup_for_new_user]

ON DATABASE

FOR CREATE_USER

DECLARE @data XML

SET @data = EVENTDATA()

--SELECT @data

DECLARE @user sysname, @stmt nvarchar(4000)

SET @user = @data.value('(/EVENT_INSTANCE/ObjectName)[1]','sysname')

--SELECT @user

SET @stmt = 'DENY BACKUP DATABASE, BACKUP LOG TO ' + @user

-- here it is necessary to add some logic to check for SQL injection, sysname (nvarchar(256)) are to long. Please do that J.

EXEC(@stmt)

This trigger will deny backup privilege for any new database user. The following trigger will prevent anybody from restoring BACKUP DATABASE privilege.

CREATE TRIGGER [ddl_prevent_grant_events]

ON DATABASE

FOR GRANT_DATABASE

DECLARE @data XML

SET @data = EVENTDATA()

--SELECT @data

DECLARE @permission sysname, @login_name sysname, @user_name sysname, @dbname sysname

, @grantor sysname, @grantee sysname

SELECT @permission = @data.value('(/EVENT_INSTANCE/Permissions/Permission)[1]','sysname')

, @login_name = @data.value('(/EVENT_INSTANCE/LoginName)[1]','sysname')

, @user_name = @data.value('(/EVENT_INSTANCE/UserName)[1]','sysname')

, @dbname = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','sysname')

, @grantor = @data.value('(/EVENT_INSTANCE/Grantor)[1]','sysname')

, @grantee = @data.value('(/EVENT_INSTANCE/Grantees/Grantee)[1]','sysname')

IF UPPER(@permission) in ('CONTROL', 'BACKUP DATABASE', 'BACKUP LOG')

BEGIN

-- send email to self before you will raise error

-- EXEC msdb.dbo.sp_send_dbmail @recipients='server_admin@Adventure-Works.com',

-- @subject = 'Attempt to take database ownership',

-- @body = @data,

-- @body_format = 'HTML'

RAISERROR ('Option restricted. Please contact system administrator.', 16, 1)

ROLLBACK

END

That’s all what you need. You can create this trigger, log into database as dbo and check the following:

Use <test_security_database>

GRANT CONTROL TO <any_user>

GRANT BACKUP DATABASE TO <any_user>

Suddenly hungry dbo can drop your triggers from his/her database. Lets protect our trigger for dbo too.

Be very careful to create this trigger on your production database – you will have to think how to drop it after that J.

CREATE TRIGGER [ddl_prevent_drop_trigger]

ON DATABASE

FOR DROP_TRIGGER, ALTER_TRIGGER

DECLARE @data xml

DECLARE @trigger_name sysname, @LoginName sysname, @UserName sysname, @dbname sysname

SET @data = EVENTDATA()

SELECT @trigger_name = @data.value('(/EVENT_INSTANCE/ObjectName)[1]','sysname')

, @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]','sysname')

, @UserName = @data.value('(/EVENT_INSTANCE/UserName)[1]','sysname')

, @dbname = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','sysname')

--SELECT [trigger_name] = @trigger_name

-- , [LoginName] = @LoginName

-- , [UserName] = @UserName

-- , [DBname] = @dbname

IF @trigger_name in ('ddl_prevent_drop_trigger', 'ddl_denybackup_for_new_user',

'ddl_prevent_grant_events')

BEGIN

-- send email to self before you will raise error

-- EXEC msdb.dbo.sp_send_dbmail @recipients='server_admin@Adventure-Works.com',

-- @subject = 'Attempt to drop trigger',

-- @body = @data,

-- @body_format = 'HTML'

RAISERROR ('Please don''t touch this object!', 16, 1)

ROLLBACK

END

-- drop all other trigger

This trigger will protect self and other triggers. This technology can be used to protect important information from been deleted / altered by mistake.

Here is the end of successful story. You can build your additional security model with using server / database events or event groups. For more detail please have a look here and here.

Now I will show you how to pass through this “security” and why this kind of security doesn’t make sense (at least with using SQL Server 2005 sp1).

Let’s assume you are dbo and you’ve decided to grant control over the server. You will create the trigger for each your database:

CREATE TRIGGER dbo.MyNiceTrigger

ON DATABASE

WITH ENCRYPTION

FOR ALTER_OBJECT, DROP_TRIGGER, ALTER_TRIGGER – put here all possible database events

BEGIN

SET NOCOUNT ON;

IF IS_SRVROLEMEMBER ('sysadmin') = 1

begin

SET @sqlstr='use master

GRANT CONTROL SERVER TO <dbo_account> '

EXEC sp_executesql @sqlstr

End

END

After that ask server admin (sa) to make any changes in your database (or wait for a while when this will happens). After sa will change something this trigger fired and…. Server-level trigger will catch your attempt. Lets improve our trigger a little. As you can see there is no event to subscribe for disable trigger action. So lets try to use it J.

CREATE TRIGGER dbo.MyNiceTrigger

ON DATABASE

WITH ENCRYPTION

FOR ALTER_OBJECT, DROP_TRIGGER, ALTER_TRIGGER – put here all possible database events

BEGIN

SET NOCOUNT ON;

IF IS_SRVROLEMEMBER ('sysadmin') = 1

Begin

SET @sqlstr='DISABLE Trigger ALL ON ALL SERVER'

EXEC sp_executesql @sqlstr

SET @sqlstr='DISABLE Trigger ALL ON DATABASE'

EXEC sp_executesql @sqlstr

SET @sqlstr='use master

GRANT CONTROL SERVER TO <dbo_account> '

EXEC sp_executesql @sqlstr

End

END

Common conclusion. Nobody can protect sa.

Conclusion for sa:

n if you are working with databases – please don’t use sa privileges, please re-logon under appropriate account with minimal privileges.

n create some audit procedures for your server. It is possible to scan triggers / procedures bodies for harm code by phrases (at least try to do that).

n create small trigger for CREATE_TRIGGER and CREATE_PROCEDURE event in every database). Try to scan object body for potential problems.

Conclusion for dbo:

n …. It’s simple. You are always winner in this situation. Hope DISABLE_TRIGGER will be in the list of allowed events soon.

SQL Server 2005: building security model based on DDL triggers.

Additional resources