SQL Server 2005: building security model based on DDL triggers.
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
AS
DECLARE @data XML
SET @data = EVENTDATA()
--SELECT @data
/*
<EVENT_INSTANCE>
<EventType>GRANT_SERVER</EventType>
<PostTime>2007-01-12T19:28:39.580</PostTime>
<SPID>63</SPID>
<ServerName>NAME</ServerName>
<LoginName>sa</LoginName>
<Grantor>sa</Grantor>
<Permissions>
<Permission>control server</Permission>
</Permissions>
<Grantees>
<Grantee>test_user</Grantee>
</Grantees>
<AsGrantor />
<GrantOption>0</GrantOption>
<CascadeOption>0</CascadeOption>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<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
GO
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
AS
DECLARE @data xml
DECLARE @trigger_name sysname, @LoginName sysname, @UserName sysname, @dbname sysname
SET @data = EVENTDATA()
--SELECT @data
/*
<EVENT_INSTANCE>
<EventType>ALTER_DATABASE</EventType>
<PostTime>2007-01-12T20:05:27.527</PostTime>
<SPID>65</SPID>
<ServerName>NAME</ServerName>
<LoginName>sa</LoginName>
<DatabaseName>TestSecurity</DatabaseName>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<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
GO
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
AS
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
GO
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
AS
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)
GO
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
AS
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>
GO
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
AS
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
GO
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
AS
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
GO
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
AS
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
GO
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.
Comments
Anonymous
March 26, 2007
When we should expect this to be fixed...?Anonymous
March 27, 2007
Well, I'm not selling any tool to fix any issues or threats you have alread in your SQL Server environment.