code security in SQL Server 2008 (ii) DDL triggers and logon triggers _mssql2008

This paper mainly deals with the application examples of DDL triggers and login triggers.

MicrosoftSQL Server provides two primary mechanisms for enforcing business rules and data integrity: constraints and triggers. Triggers are special types of stored procedures that can take effect automatically when a language event is executed. SQL Server includes three common types of triggers:DML triggers ,DDL triggers , and logon triggers .

1. DML triggers are invoked when data manipulation language (DML) events occur in the database. A DML event includes an INSERT statement, an UPDATE statement, or a DELETE statement that modifies data in a specified table or view. DML triggers can query other tables and can contain complex Transact-SQL statements. Treat the trigger and the statement that triggers it as a single transaction that can be rolled back within the trigger. If an error is detected (for example, if there is not enough disk space), the entire transaction is automatically rolled back.

DML triggers are most widely used. Don't repeat it here. MSDN Official Description: http://msdn.microsoft.com/zh-cn/library/ms189799.aspx

2. DDL triggers are invoked when data definition language (DDL) events occur in the server or database. A DDL trigger is a special trigger that fires when it responds to a data definition language (DDL) statement. They can be used to perform administrative tasks in the database, such as auditing and standardizing database operations.

Here we illustrate the application of DDL triggers (http://technet.microsoft.com/zh-cn/library/ms189799%28SQL.90%29.aspx):

Example one: create a DDL trigger audit Database-level events

Copy Code code as follows:

/***************
Create an audit table where eventdata is an XML data column
3w@live.cn
*******************/

Use master
Go
CREATE TABLE dbo. Changeattempt
(eventdata XML Not NULL,
attemptdate datetime not NULL DEFAULT GETDATE (),
Dbuser char () not NULL)
Go

/***************
Create a trigger on the target database to record the index change action of the database.
including create|alter| Drop
3w@live.cn
*******************/

CREATE TRIGGER Db_trg_restrictindexchanges
On DATABASE
For Create_index, Alter_index, Drop_index
As
SET NOCOUNT on
INSERT dbo. Changeattempt
(EventData, Dbuser)
VALUES (EventData (), USER)
Go

/***************
Create an index to test the trigger
3w@live.cn
*******************/

CREATE nonclustered INDEX Ni_changeattempt_dbuser on
Dbo. Changeattempt (Dbuser)
Go

/***************
View Audit Records
3w@live.cn
*******************/

SELECT EventData
FROM dbo. Changeattempt



--------/***************
--------Delete test triggers and record tables
--------3w@live.cn
--------*******************/

----Drop TRIGGER [db_trg_restrictindexchanges]
----on DATABASE
----Go
----DROP TABLE dbo. Changeattempt
----Go

Execution results:

Example two: create a DDL trigger Audit server-level events

Copy Code code as follows:

--------/***************
--------Create a trigger on the target database server to prevent the login account from being added.
--------3w@live.cn
--------*******************/
Use master
Go
--Disallow new logins on the SQL instance
CREATE TRIGGER Srv_trg_restrictnewlogins
On the All SERVER
For Create_login
As
PRINT ' No Login creations without DBA involvement. '
ROLLBACK
Go

--------/***************
--------trying to create a login account
--------3w@live.cn
--------*******************/
CREATE LOGIN Johny with PASSWORD = ' 123456 '
Go

--------/***************
--------Delete demo triggers
--------3w@live.cn
--------*******************/

Drop TRIGGER Srv_trg_restrictnewlogins
On the All SERVER
Go

Effect:

Note: use DDL triggers with special care. If improperly set, it will cause unpredictable consequences at the database level and even at the server level.

3, the login trigger (http://msdn.microsoft.com/zh-cn/library/bb326598.aspx) fires the stored procedure in response to the logon event. This event is raised when a user session is established with an instance of SQL Server.

If you have the need to allow only one account to log on to the server at a particular time (such as the unit and the home using a different account to telnet to the server), then the login trigger is a good choice.

Example three: Create a login trigger Audit logon event

Copy Code code as follows:

--------/***************
--------Create a login account
--------3w@live.cn
--------*******************/

CREATE LOGIN nightworker with PASSWORD = ' 123b3b4 '
Go

--------/***************
--------Demo database and audit table
--------3w@live.cn
--------*******************/

CREATE DATABASE Exampleauditdb
Go
Use Exampleauditdb
Go

CREATE TABLE dbo. Restrictedlogonattempt
(loginnm sysname not NULL,
ATTEMPTDT datetime not NULL)
Go

--------/***************
--------Create a login trigger, log the audit log if you are not logged on 7:00-17:00, and prompt for failure
--------3w@live.cn
--------*******************/

Use master
Go
Create TRIGGER Trg_logon_attempt
On the All SERVER
With EXECUTE as ' sa '
For LOGON
As
BEGIN
IF original_login () = ' Nightworker ' and
DATEPART (Hh,getdate ()) BETWEEN 7 and 17
BEGIN
ROLLBACK
INSERT ExampleAuditDB.dbo.RestrictedLogonAttempt
(LOGINNM, ATTEMPTDT)
VALUES (Original_login (), GETDATE ())
End
End
Go

--------/***************
--------View Audit Records
--------3w@live.cn
--------*******************/
Use Exampleauditdb
Go
SELECT * FROM dbo. Restrictedlogonattempt
Go

--------/***************
--------Delete Demo database and demo triggers
--------3w@live.cn
--------*******************/
Use master
Go

Drop TRIGGER Trg_logon_attempt
On the All SERVER
Go

Drop Database Exampleauditdb
Go

Results:

Of course, you can also use an application or a log4net logging mechanism to record similar logon events, but SQL Server 2008 has done it for us, and all you have to do is have the courage to try.

Summary: As an audit and monitoring of data DDL operations and logon events, SQL Server provides a more sophisticated event-handling mechanism. This is also part of the SQL Server security mechanism. The following will cover transparent encryption at the SQL Server database level, and stay tuned.
Http://www.cnblogs.com/downmoon