{"id":604,"date":"2007-07-24T13:31:00","date_gmt":"2007-07-24T13:31:00","guid":{"rendered":"\/blogs\/kimberly\/post\/EXECUTE-AS-and-an-important-update-your-DDL-Triggers-(for-auditing-or-prevention).aspx"},"modified":"2007-07-24T13:31:00","modified_gmt":"2007-07-24T13:31:00","slug":"execute-as-and-an-important-update-your-ddl-triggers-for-auditing-or-prevention","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/execute-as-and-an-important-update-your-ddl-triggers-for-auditing-or-prevention\/","title":{"rendered":"“EXECUTE AS” and an important update your DDL Triggers (for auditing or prevention)"},"content":{"rendered":"

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. <\/span>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… <\/span>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). <\/span><\/p>\n

\nHaving 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(). <\/span>\n<\/p>\n

\n(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”<\/em>).<\/span>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!).<\/span>\n<\/p>\n

\n<\/span>USE<\/span> AdventureWorks;
\n<\/span><\/span>go<\/span>\n<\/p>\n

\n<\/span>–Create a login\/user – just for this exercise<\/span>\n<\/p>\n

\n<\/span>CREATE<\/span> LOGIN<\/span> Paul WITH<\/span> PASSWORD =<\/span> 'PxKoJ29!07'<\/span>;
\n<\/span><\/span>go<\/span>\n<\/p>\n

\n<\/span>CREATE<\/span> USER<\/span> Paul FOR<\/span> LOGIN<\/span> Paul;
\n<\/span><\/span>go<\/span>\n<\/p>\n

\n<\/span>sp_addrolemember<\/span> 'db_ddladmin'<\/span>,<\/span> 'Paul'
\n<\/span><\/span>go<\/span> <\/span>\n<\/p>\n

\n<\/span>CREATE<\/span> SCHEMA<\/span> SecurityAdministration
\n<\/span>go<\/span>\n<\/p>\n

\n<\/span>CREATE<\/span> TABLE<\/span> SecurityAdministration.<\/span>AuditDDLOperations
\n<\/span>(<\/span>            <\/span>OpID                <\/span>int<\/span>               <\/span>NOT<\/span> NULL<\/span> identity
\n<\/span>     <\/span><\/span>                                                  <\/span>CONSTRAINT<\/span> AuditDDLOperationsPK
\n<\/span>                                                           <\/span>PRIMARY<\/span> KEY<\/span> CLUSTERED<\/span>,
\n<\/span><\/span>            <\/span>OriginalLoginName    <\/span>sysname<\/span>           <\/span>NOT<\/span> NULL,
\n<\/span><\/span>            <\/span>LoginName            <\/span>sysname<\/span>           <\/span>NOT<\/span> NULL,
\n<\/span><\/span>            <\/span>UserName             <\/span>sysname<\/span>           <\/span>NOT<\/span> NULL,
\n<\/span><\/span>            <\/span>PostTime             <\/span>datetime<\/span>          N<\/span><\/span>OT<\/span> NULL,
\n<\/span><\/span>            <\/span>EventType            <\/span>nvarchar<\/span>(<\/span>100)<\/span> <\/span>    <\/span>NOT<\/span> NULL,
\n<\/span><\/span>            <\/span>DDLOp                <\/span>nvarchar<\/span>(<\/span>2000)<\/span>    <\/span>NOT<\/span> NULL
\n<\/span><\/span>);
\n<\/span>go<\/span>\n<\/p>\n

\n<\/span>GRANT<\/span> INSERT<\/span> ON<\/span> SecurityAdministration.<\/span>AuditDDLOperations TO<\/span> public<\/span>;
\n<\/span><\/span>go<\/span>\n<\/p>\n

\nCREATE<\/span> TRIGGER<\/span> PreventAllDDL
\n<\/span>ON<\/span> DATABASE
\n<\/span><\/span>WITH<\/span> ENCRYPTION
\n<\/span>FOR<\/span> DDL_DATABASE_LEVEL_EVENTS
\n<\/span>AS<\/span>
\n<\/span>DECLARE<\/span> @data XML
\n<\/span><\/span>SET<\/span> @data =<\/span> EVENTDATA()
\n<\/span><\/span>RAISERROR<\/span> (<\/span>'DDL Operations are prohibited on this production database. Please contact ITOperations for proper policies and change control procedures.'<\/span>,<\/span> 16,<\/span> –<\/span>1)
\n<\/span><\/span>ROLLBACK
\n<\/span>INSERT<\/span> SecurityAdministration.<\/span>AuditDDLOperations
\n<\/span>                        <\/span>(<\/span>OriginalLoginName,
\n<\/span><\/span>                        <\/span> <\/span>LoginName,
\n<\/span><\/span>                        <\/span> <\/span>UserName,
\n<\/span><\/span>                        <\/span> <\/span>PostTime,
\n<\/span><\/span>                        <\/span> <\/span>EventType,
\n<\/span><\/span>                        <\/span> <\/span>DDLOp)
\n<\/span><\/span>VALUES<\/span>   <\/span>(<\/span>ORIGINAL_LOGIN(),<\/span> SYSTEM_USER<\/span>,<\/span> CURRENT_USER<\/span>,<\/span> GETDATE<\/span>(),
\n<\/span><\/span>   <\/span>@data.<\/span>value<\/span>(<\/span>'(\/EVENT_INSTANCE\/EventType)[1]'<\/span>,<\/span> 'nvarchar(100)'<\/span>),
\n<\/span><\/span>   <\/span>@data.<\/span>value<\/span>(<\/span>'(\/EVENT_INSTANCE\/TSQLCommand)[1]'<\/span>,<\/span> 'nvarchar(2000)'<\/span>)<\/span> )<\/span>
\n<\/span>RETURN<\/span>;
\n<\/span>go<\/span> <\/span>\n<\/p>\n

\n<\/span>–Test the trigger.<\/span>\n<\/p>\n

\n<\/span>CREATE<\/span> TABLE<\/span> TestTable (<\/span>col1 int<\/span>);
\n<\/span><\/span>go<\/span>\n<\/p>\n

\n<\/span>DROP<\/span> TABLE<\/span> SecurityAdministration.<\/span>AuditDDLOperations;
\n<\/span><\/span>go<\/span>\n<\/p>\n

\n<\/span>EXECUTE<\/span> AS<\/span> LOGIN<\/span> =<\/span> 'Paul'<\/span> — note: Remember, Paul is a DDL_admin
\n<\/span><\/span>go<\/span>\n<\/p>\n

\n<\/span>DROP<\/span> TABLE<\/span> SecurityAdministration.<\/span>AuditDDLOperations;
\n<\/span><\/span>go<\/span>\n<\/p>\n

\n<\/span>REVERT<\/span>;
\n<\/span>go<\/span> <\/span>\n<\/p>\n

\n<\/span>SELECT<\/span> *<\/span> FROM<\/span> SecurityAdministration.<\/span>AuditDDLOperations;
\n<\/span><\/span>go<\/span>\n<\/p>\n

\n<\/span>DROP<\/span> TRIGGER<\/span> PreventAllDDL ON<\/span> DATABASE<\/span>;
\n<\/span><\/span>go<\/span>\n<\/p>\n

\n<\/span>DROP<\/span> TABLE<\/span> SecurityAdministration.<\/span>AuditDDLOperations;
\n<\/span><\/span>go<\/span>\n<\/p>\n

\n<\/span>DROP<\/span> SCHEMA<\/span> SecurityAdministration;
\n<\/span>go<\/span>\n<\/p>\n

\n<\/span>DROP<\/span> USER<\/span> Paul;
\n<\/span>go <\/span><\/span>\n<\/p>\n

\nDROP<\/span> LOGIN<\/span> Paul;
\ngo<\/span> <\/span>\n<\/p>\n

\n<\/span>So, have fun testing with this one.<\/span> <\/span>\n<\/p>\n

\n<\/span>Thanks for reading!<\/span>
\nkt<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

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, […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26,28,59,62,65,78],"tags":[],"class_list":["post-604","post","type-post","status-publish","format-standard","hentry","category-dynamic-string-execution","category-execute-as","category-security","category-sp_executesql","category-sql-server-2005","category-tips"],"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/604","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/comments?post=604"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/604\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/media?parent=604"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/categories?post=604"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/tags?post=604"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}