N2

Meh

SQL Server DDL Trigger Hack

without comments

Here we go again. This time I’m going to discuss a new security “feature” implemented in SQL Server 2005. If you are working with SQL Server 2005 you might have noticed that it has this cool new thing called DDL triggers. It’s very helpful in auditing databases and monitoring unauthorized schema changes.

First let’s get a brief on what exactly is a DDL trigger & how it is helpful. A DDL trigger is fired when a DDL statement ( eg : Alter Table ) is executed. A system administrator can write such a trigger that would prevent ( rollback ) such changes or make a log of who did what and when. The syntax for creating a DDL Trigger as documented in SQL Server 2005 books online is ..

1
2
3
4
5
6
7
8
9
10
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH
 [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME < method specifier > [ ; ] }
 ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ] ::=
assembly_name.class_name.method_name

So, let’s create a simple DDL Trigger to audit Create Table & Drop Table DDL Statements.
First, Create a simple audit table where We will Log the Entries.

1
2
3
4
5
6
7
Create table AUDIT_LOG
(
ID int identity(1,1),
c_who varchar(100) NOT NULL,
c_when dateTime NOT Null,
c_what varchar(1024) NOT NULL
)

This table will store the who-did-what-and-when information. Next, create the DDL Trigger.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE trigger trig_Audit_alter
on database
for CREATE_TABLE, DROP_TABLE
AS
Insert Into AUDIT_LOG 
(c_who, c_when, c_what)
VALUES
(
EVENTDATA().value(
'(/EVENT_INSTANCE/UserName)[1]',
'nvarchar(max)'),
 
EVENTDATA().value(
'(/EVENT_INSTANCE/PostTime)[1]',
'nvarchar(max)'),
 
EVENTDATA().value(
'(/EVENT_INSTANCE/TSQLCommand)[1]',
'nvarchar(max)')
)

Next, try creating any table in this database & a row for each such operation will be created in the table AUDIT_LOG. This is a fairly simple example but it can be implemented to enforce security and or auditing capabilities, not available previously.

Now that we get the picture of how it is done, now we hack it. The basic idea that we will be using is that DDL trigger executes in the security context of the user logged in. A “geek” with enough permissions to create a DDL Trigger will be able to bump up his permissions level by creating a trigger that executes statement such as “GRANT CONTROL SERVER TO Geek”. Having done that the geek waits till a member of the sysAdmin group issues any DDL statement.

Microsoft is aware of the issue & has documented in MSDN and recommends certain security best practices for DDL Triggers.

Written by Singh

January 9th, 2007 at 8:10 am