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
5 thoughts on ““EXECUTE AS” and an important update your DDL Triggers (for auditing or prevention)”
Auditing changes is great but will this still work from a distributed transaction (e.g. System.Transactions)?
All ORIGINAL_LOGIN will produce is the login at the local server. Since the local server was accessed through a distributed transaction (using linked servers/three-part naming), you should consider using a stored procedure with three-part naming…ideally, you’ll call ORIGINAL_LOGIN as a parameter into the remote procedure call and then you can track: original_login at calling remote server, login at calling remote server, original_login at called local server, and login at called local server. That should give you the full set of information!
hth,
kt
I’m really glad to see someone question the EXECUTE AS clause. I know its only a comment in this post, but even with ORIGINAL_LOGIN it worries me – especially if AS SELF is used. What I’d like is a way to remove that period. Know anything I don’t that can block EXECUTE AS?
Hello Kimberly,
This comment is not directly related to DDL triggers but to the triggers used in merge replication. We have a merge replication scenario where the publication is on a sql server 2005 and the subscriptions are in MSDE. We previously had the same replication working with SQL Server 2000 server as our publisher. We also have parameterized row filtering going on. After copying our database to the 2005 instance we proceeded to improve the join filters as we could only filter our data partially in 2000. We added extra join filters to our publication in 2005.
We now have several conflicts and data loss issues, so far the conflicts say ther server row has been updated first and is selected as the winner. We know in the testing environment there is a single subscriber replicating at the same time and the row is not updated by any other application. We presume that particular table is updated twice due to CRI (referential integrity constraints).
In order to see the order of the updates, we would like to see the order in which the update replication trigger is launched and the FK that is updated at the time. If you could please indicate a white paper or reference on how to set up this type of Trace in Profiler, we will strongly appreciate it. Any suggestions are also welcome.
Thanks in advance,
Lizet