Database DDL Trigger Security-Catch 22

Question

Database DDL Trigger Security-Catch 22

Hollyz

SSCommitted

Points: 1763
More actions
December 17, 2008 at 1:58 pm

#383599

in sql 2005, there is a new feature called database ddl trigger which track changes made to the database by whom, etc..
1. There seems to be something conflicting about this: The database ddl trigger produces a log file which you can name it as you like for that database.
- So if a user called: Gina - who has db_ownership to that database and execute changes to the structure of a table on that database, that database ddl trigger will capture the changes to that log table that you defined.
- However, if you want to deny Gina to be able to make changes to that log table (obviously for security purpose), it doesnot work.
I try : Deny Insert/update/delete to that log table from Gina.
- Then when Gina tries now to alter the structure of another table for that database, she is able to proceed to alter that structure however, it will not be captured into the database ddl trigger because of that Deny permission.
Sounds like a Catch-22 to me.
Does anyone know of a solution to this?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

HKwai (12/17/2008)
1. There seems to be something conflicting about this: The database ddl trigger produces a log file which you can name it as you like for that database.
- So if a user called: Gina - who has db_ownership to that database and execute changes to the structure of a table on that database, that database ddl trigger will capture the changes to that log table that you defined.

That really depends on how you wrote the DDL trigger.

HKwai (12/17/2008)
- However, if you want to deny Gina to be able to make changes to that log table (obviously for security purpose), it doesnot work.
I try : Deny Insert/update/delete to that log table from Gina.
- Then when Gina tries now to alter the structure of another table for that database, she is able to proceed to alter that structure however, it will not be captured into the database ddl trigger because of that Deny permission.
Sounds like a Catch-22 to me.
Does anyone know of a solution to this?

Why would you allow a user to be db_owner in your audit database if you don't trust them enough? Or do you have a audit table in each database?

Anyway you can deny any permissions on your logging table and still get other ddl activities logged. Just use the EXECUTE AS clause in your DDL trigger before doing the INSERT.

Personally I prefer using EVENT Notifications in combination wwith Service Broker to log all DDL activities to a central DBA database. Jasper Smith has a neat utility which does the setup for you. See at his site

http://www.sqldbatips.com/showarticle.asp?ID=113

[font="Verdana"]Markus Bohse[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

HKwai (12/17/2008)
Does anyone know of a solution to this?

EXECUTE AS

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Hollyz SSCommitted Points: 1763 More actions · Answer 3

Thank you for your reply. I am not too familiar with Notification and Service Brokers which they seem so Foreign to me. But I will try to look into that.

But in the meantime, I have tried EXECUTE as and I know that will solve the dilema.

However, the sql login will be captured as example, If I place EXECUTE AS 'DBO', the the sql login will be captured as 'DBO', and not the actual sql user id.

I know the DDL Trigger log also does capture the Hostname, which mean, it will be the only option that will tell you who actually change the structure of a table from that database.

Just hope that the Hostname will always capture the host information, otherwise the DDL Trigger will defeat its purpose.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

HKwai (12/22/2008)
However, the sql login will be captured as example, If I place EXECUTE AS 'DBO', the the sql login will be captured as 'DBO', and not the actual sql user id.

Not if you use the ORIGINAL_LOGIN function, or the original_login_name from sys.dm_exec_sessions.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Hollyz SSCommitted Points: 1763 More actions · Answer 5

Thank you Gail. The Original_function worked very well.