Keith Combs' Blahg

Ramblings from another nerd on the grid

Blogs

SQL Server 2005 Trip Wires

  • Comments 4
  • Likes

A number of changes and improvements have been made to SQL Server 2005.  Did I really need to state that?  One of the big improvements is in the management and security area.  The SQL Management Object (SMO) infrastructure replaces much of what used to be accomplished using SQL-DMO.  SMO is tightly integrated with Windows Management Instrumentation (WMI).  This linkage is so tight, new capabilities now exist to take advantage of the eventing taking place.Wmi_provider

One way to take advantage of this instrumentation is to use Data Definition Language (DDL) or Data Manipulation Language (DML) via T-SQL.  Last week, Kai Axford delivered a webcast on SQL Security as part of the “A More Secure and Well-Managed Infrastructure” series.  In his webcast (Part 11), he did some DDL and DML demos.  A number of people wanted me to post the scripts, so look down at the bottom of this post for those.

My team is also delivering live seminars as part of the “Best Of SQL Server 2005 Launch.”  One of those demos is DDL.  It’s a pretty simple demo, but to understand the power, you really need to look as the available events and build some automation around them.  The SQL Server 2005 books are now online.  If you look at the DDL_EVENTS hierarchy, you’ll notice we have database and server levels events we can trap.  Once we trap an event, we can setup a tripwire to take the appropriate “corrective” action.

In our demo, we use DDL_DATABASE_LEVEL_EVENTS to see what is occuring on the virtual machine demo environment.  Our script will detect unapproved actions and roll them back while logging the time, date, user ids and stuff to an audit table.  Our script will also post a nice little message indicating to the user that action was prohibited.  In reality, you might take advantage of other automation like sending a page to a pager/cellphone, sending an email message to the DBA’s or security professionals, etc.  The automation is only limited by your imagination and programming skills. 

See the full Data Definition Language (DDL) demo to get an idea on how to do this.

The script we demo is as follows:

USE AdventureWorks;
GO

CREATE TABLE AuditDDLOperations
(
 OpID  int    NOT NULL identity 
        CONSTRAINT AuditDDLOperationsPK
         PRIMARY KEY CLUSTERED,
 LoginName sysname   NOT NULL,
 UserName sysname   NOT NULL,
 PostTime datetime  NOT NULL,
 EventType nvarchar(100) NOT NULL,
 DDLOp  nvarchar(2000) NOT NULL
);
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 AuditDDLOperations
  (LoginName,
   UserName,
   PostTime,
   EventType,
   DDLOp)
VALUES   (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 AuditDDLOperations;
GO

SELECT * FROM AuditDDLOperations;
GO

--Drop the trigger.
DROP TRIGGER PreventAllDDL
ON DATABASE;
GO

DROP TABLE AuditDDLOperations;
GO

As you can see from the example and demo above, it’s a simple script but has a lot of power.  If you are interested in other examples and procedures, see the “Designing DDL Triggers” online help page.  Of course you should also have a local copy of this page since I know you were a good doggie and installed SQL Server 2005.  If you don’t have a copy, shame on you.  Come to one of the remaining launch events if you can and get one.  If registration is blocked because it is full, you can still come by.  However, walkins aren’t guaranteed to get in and you’ll only get a free copy of SQL Server 2005 and Visual Studio 2005 if you get in. 

Here are the scripts from Kai’s webcast:

--Step 1: Create a table to log audited events to
USE LucernePublishing
GO
CREATE TABLE DDL_Audit (id INT PRIMARY KEY IDENTITY,
  AuditText VARCHAR(MAX))
GO

--Step 2: Create a table for testing the trigger
CREATE TABLE LPTriggerTest (id INT PRIMARY KEY)
GO

--Step 3: Create a trigger to fire when
-- an ALTER TABLE command is issued
CREATE TRIGGER LPTableAlter
ON DATABASE
AFTER ALTER_TABLE
AS
INSERT INTO DDL_Audit VALUES('A database table was altered.')
GO

--Step 4:Add a new column to the test table
ALTER TABLE LPTriggerTest
   ADD TestText VARCHAR(MAX)
GO

--Step 5:Verify that the trigger fired
SELECT * FROM DDL_Audit
GO

============

--Step 1: Create a trigger to prevent
-- tables from being dropped.
USE LucernePublishing
GO

CREATE TRIGGER Safety
ON DATABASE
FOR DROP_TABLE
AS
 PRINT 'You must disable the Safety trigger
       before you drop a table.'
 ROLLBACK
GO

--Step 2: Test the trigger by trying to drop a
-- table from the database
DROP TABLE dbo.LPBuildVersion
GO

--Step 3: Drop the trigger to allow database
-- tables to be dropped
DISABLE TRIGGER [Safety] ON DATABASE
GO

=============

--Step 1: Create a new table for auditing
--with the eventdata() function
USE LucernePublishing
GO
CREATE TABLE DDL_Eventdata (
 PostTime datetime,
 DB_User nvarchar(100),
 Event nvarchar(100),
 TSQL nvarchar(2000)
)
GO

--Step 2: Modify the trigger
-- to use eventdata() function
ALTER TRIGGER LPTableAlter
ON DATABASE
AFTER ALTER_TABLE
AS
DECLARE @data XML
SET @data = EVENTDATA()
INSERT INTO DDL_EventData
   (PostTime, DB_User, Event, TSQL)
 VALUES   
 (GETDATE(),   
 CONVERT(nvarchar(100), CURRENT_USER),   
 @data.value('(/EVENT_INSTANCE/EventType)[1]',
  'nvarchar(100)'),   
 @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]',
  'nvarchar(2000)') ) ;
GO

--Step 3: Modify the test table again to
-- cause the trigger to fire
ALTER TABLE LPTriggerTest
   ADD TestText2 VARCHAR(MAX)
GO

--Step 4: Verify that the trigger fired and wrote
-- eventdata() information to the table.
SELECT * FROM DDL_EventData
GO

 

 

 

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • One thing we'd like to be able to control is not just who has access to a database but how. It would have been very desirable to use the DDL_Server_Level_Event against the Logon event but that's not the case. However...I this can be accomplished with the WMI provider and the login server event & I've attempted to create some code to do this but frankly I've stumbled. I would be very grateful if you would you do the following:

    1. Create a script that designs a WMI trigger to recognize a successful login.
    2. Have this trigger fire a job that runs an sp_who type query (or uses a new dm view) to find the application name. If the application name is not permitted against the target database it kills the SPID.

    Thanks very much for considering, would be a great blog entry!!

  • Hmmmm... I'll put that on my list of things to do. I'm working on an encryption post right now. After I get done with it, I'll see what I can do. Cheers!!!

  • FYI, someone asked how do you drop the trigger since we are using the trigger for rollbacks. See http://channel9.msdn.com/ShowPost.aspx?PostID=139337#139337 for my answer!!!

  • Thank you for attending the web cast on SQL Server 2005 Security, as promised here is the scrubbed Q/A...