DDL Triggers were a new feature of SQL Server 2005 and while seemingly simple, they are very powerful. DDL Triggers allow you to trap an attempted DDL operation to audit it, prevent it, or do anything you want to validate/verify/”authorize”/etc – you write the code. And, since a trigger fires as part of the transaction, you can roll it back.
In many conference demos/webcasts, etc., I have provided a sample script that prevents ddl within a [production] database. That script has been really helpful/useful but recently I thought about an update to it…
SQL Server 2005 has another new feature "execute as". While I definitely see many benefits, I’m also a bit concerned. To a certain extent, I feel that the potential for SQL Injection is actually higher. If a developer creates a poorly written/tested stored procedure (ok, therein lies the problem, really!) that includes dynamic string execution AND then uses "execute as" to essentially elevate a user with minimal privileges to a higher level (so that they don’t need to give the base object rights to the user), a malicious user could “inject” code in and actually succeed if the “execute as” user has rights to the injected code. In prior releases, and with the default behavior (execute as caller), this is not possible (which is good for security but bad for dynamically executed strings within stored procedures as base object rights are necessary).
Having said that, and since security is always a concern, my DDL Trigger only audited for the login of the user who executed the statement, not for the actual user that’s logged in. In other words, if EXECUTE AS is used (or SETUSER is used), then the context of the user executing is actually different then the logged in user. To see this shift in context, SQL Server 2005 added a new function: ORIGINAL_LOGIN().
(reading between the lines is even more frightening in that prior to SQL Server 2005, the original user could not be tracked from SETUSER. The good news is that SETUSER is ONLY allowed to be used by DBOs so it’s not as widespread as the potential for “execute as”).
OK, so how can we put all of this together? We’ll want to add the ORIGINAL_LOGIN function into our audit table in our DDL Trigger. Even if you choose NOT to rollback, at least you’ll know who performed the operation (even if from a dynamically executed string!).
USE AdventureWorks;
go
--Create a login/user - just for this exercise
CREATE LOGIN Paul WITH PASSWORD = 'PxKoJ29!07';
go
CREATE USER Paul FOR LOGIN Paul;
go
sp_addrolemember 'db_ddladmin', 'Paul'
go
CREATE SCHEMA SecurityAdministration
go
CREATE TABLE SecurityAdministration.AuditDDLOperations
(
OpID int NOT NULL identity
CONSTRAINT AuditDDLOperationsPK
PRIMARY KEY CLUSTERED,
OriginalLoginName sysname NOT NULL,
LoginName sysname NOT NULL,
UserName sysname NOT NULL,
PostTime datetime NOT NULL,
EventType nvarchar(100) NOT NULL,
DDLOp nvarchar(2000) NOT NULL
);
go
GRANT INSERT ON SecurityAdministration.AuditDDLOperations TO public;
go
CREATE TRIGGER PreventAllDDL
ON DATABASE
WITH ENCRYPTION
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
SET @data = EVENTDATA()
RAISERROR ('DDL Operations are prohibited on this production database. Please contact ITOperations for proper policies and change control procedures.', 16, -1)
ROLLBACK
INSERT SecurityAdministration.AuditDDLOperations
(OriginalLoginName,
LoginName,
UserName,
PostTime,
EventType,
DDLOp)
VALUES (ORIGINAL_LOGIN(), SYSTEM_USER, CURRENT_USER, GETDATE(),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') )
RETURN;
go
--Test the trigger.
CREATE TABLE TestTable (col1 int);
go
DROP TABLE SecurityAdministration.AuditDDLOperations;
go
EXECUTE AS LOGIN = 'Paul' -- note: Remember, Paul is a DDL_admin
go
DROP TABLE SecurityAdministration.AuditDDLOperations;
go
REVERT;
go
SELECT * FROM SecurityAdministration.AuditDDLOperations;
go
DROP TRIGGER PreventAllDDL ON DATABASE;
go
DROP TABLE SecurityAdministration.AuditDDLOperations;
go
DROP SCHEMA SecurityAdministration;
go
DROP USER Paul;
go
DROP LOGIN Paul;
go
So, have fun testing with this one.
Thanks for reading!
kt