笔记40 SQL Server中的代码安全：DDL触发器与登录触发器 数据库级事件 和服务器级事件

笔记40 SQL Server中的代码安全：DDL触发器与登录触发器 数据库级事件 和服务器级事件

--SQL Server中的代码安全：DDL触发器与登录触发器 数据库级事件 和服务器级事件


--创建一个DDL触发器审核数据库级事件

/***************
创建一个审核表,其中EventData是一个XML数据列
w@live.cn
*******************/
--msdn:http://technet.microsoft.com/zh-cn/library/ms189799%28SQL.90%29.aspx


--这个表应该在master库中创建，这里为了演示创建在[pratice]库


USE [pratice]

GO

CREATE TABLE dbo.ChangeAttempt
(EventData xml NOT NULL,
AttemptDate datetime NOT NULL DEFAULT GETDATE(),
DBUser char(50) NOT NULL)
GO

SELECT * FROM [dbo].[ChangeAttempt]
/***************
在目标数据库上创建一个触发器,以记录该数据库的索引变化动作，
包括Create|alter|Drop
w@live.cn
*******************/
USE [pratice]
GO

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

/***************
创建一个索引，以测试触发器
w@live.cn
*******************/
USE [pratice]
GO

CREATE NONCLUSTERED INDEX ni_ChangeAttempt_DBUser ON [pratice].[dbo].[test]([a])
GO
--
--/***************
--查看审核记录
--w@live.cn
--*******************/
--
USE [pratice]
GO

SELECT * FROM [dbo].[ChangeAttempt]
--
--

------/***************
------删除测试触发器和记录表
------3w@live.cn
------*******************/
USE [pratice]
GO

drop TRIGGER db_trg_RestrictINDEXChanges
ON DATABASE
go
drop table dbo.[ChangeAttempt]
GO

<EVENT_INSTANCE>
  <EventType>CREATE_INDEX</EventType>
  <PostTime>2013-01-26T01:34:07.590</PostTime>
  <SPID>54</SPID>
  <ServerName>JOE</ServerName>
  <LoginName>JOE\Administrator</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>pratice</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>ni_ChangeAttempt_DBUser</ObjectName>
  <ObjectType>INDEX</ObjectType>
  <TargetObjectName>test</TargetObjectName>
  <TargetObjectType>TABLE</TargetObjectType>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>CREATE NONCLUSTERED INDEX ni_ChangeAttempt_DBUser ON [pratice].[dbo].[test]([a])
</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

-------------------------------------------------------------------------------------------------------
--经理前几天要求记录数据库的最后一次访问时间，不管是我们自己的系统还是使用SSMS还是其他第三方工具
--以便对数据库进行审核，因为之前发现有人更改了sa的密码，导致我们的系统连接不上数据库
--我们的系统是winform，联网的C/S架构


--相信触发器大家并不陌生，做INSERT, UPDATE, or DELETE 这些操作的时候保证数据完整性或者数据同步
--都会创建DML触发器，使用inserted表或者deleted表

--刚才我提到的是DML触发器，就是做数据操作的时候就触发的，网上资料很多，但是对于

--DDL触发器 和登录触发器的资料就比较少了，正好经理提的这个需求可以使用登录触发器和DDL触发器来做


--我这里做的是当有人登录数据库或者服务器就做一个日志记录以便遇到登录不上或者其他情况的时候可以查日志，
--但是并没有阻止他登录或者继续做其他操作



--DDL 触发器用于响应各种数据定义语言 (DDL) 事件。这些事件主要对应于 Transact-SQL CREATE、ALTER 和 DROP 语句，
--以及执行类似 DDL 操作的某些系统存储过程。

--登录触发器在遇到 LOGON 事件时触发。LOGON 事件是在建立用户会话时引发的。

Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<ddl_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]
   
   
   
   
-----------------------------------------------------------------------------------
Trigger on a LOGON event (Logon Trigger)
CREATE TRIGGER trigger_name
ON ALL SERVER
[ WITH <logon_trigger_option> [ ,...n ] ]
{ FOR| AFTER } LOGON 
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<logon_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]


------------------------------------------------------------------------------------   

DATABASE
--将 DDL 触发器的作用域应用于当前数据库。如果指定了此参数，则只要当前数据库中出现 event_type 或 event_group，就会激发该触发器。

ALL SERVER
--将 DDL 或登录触发器的作用域应用于当前服务器。如果指定了此参数，则只要当前服务器中的任何位置上出现 event_type 或 event_group，就会激发该触发器。


event_type
--执行之后将导致激发 DDL 触发器的 Transact-SQL 语言事件的名称。DDL 事件中列出了 DDL 触发器的有效事件。




--DDL 和登录触发器通过使用 EVENTDATA (Transact-SQL) 函数来获取有关触发事件的信息

 
---------------------------------------------------------------------------------------------------------
--触发器限制


--CREATE TRIGGER 必须是批处理中的第一条语句，并且只能应用于一个表。

--触发器只能在当前的数据库中创建，但是可以引用当前数据库的外部对象。
--
--如果指定了触发器架构名称来限定触发器，则将以相同的方式限定表名称。
--
--在同一条 CREATE TRIGGER 语句中，可以为多种用户操作（如 INSERT 和 UPDATE）定义相同的触发器操作。
--
--如果一个表的外键包含对定义的 DELETE/UPDATE 操作的级联，则不能对为表上定义 INSTEAD OF DELETE/UPDATE 触发器。
--
--在触发器内可以指定任意的 SET 语句。选择的 SET 选项在触发器执行期间保持有效，然后恢复为原来的设置。
--
--如果触发了一个触发器，结果将返回给执行调用的应用程序，就像使用存储过程一样。若要避免由于触发器触发而向应用程序返回结果，请不要包含返回结果的 SELECT 语句，也不要包含在触发器中执行变量赋值的语句。包含向用户返回结果的 SELECT 语句或进行变量赋值的语句的触发器需要特殊处理；这些返回的结果必须写入允许修改触发器表的每个应用程序中。如果必须在触发器中进行变量赋值，则应该在触发器的开头使用 SET NOCOUNT 语句以避免返回任何结果集。
--
--虽然 TRUNCATE TABLE 语句实际上就是 DELETE 语句，但是它不会激活触发器，因为该操作不记录各个行删除。
--然而，仅那些具有执行 TRUNCATE TABLE 语句的权限的用户才需要考虑是否无意中因为此方式而导致没有使用 DELETE 触发器。
--
--无论有日志记录还是无日志记录，WRITETEXT 语句都不触发触发器。


----------------------------------------------------------------------------------------------

--DDL 触发器


--DDL 触发器像标准触发器一样，在响应事件时执行存储过程。但与标准触发器不同的是，它们并不在响应对表或视图的 UPDATE、INSERT 或 DELETE 语句时执行存储过程。它们主要在响应数据定义语言 (DDL) 语句执行存储过程。这些语句包括 CREATE、ALTER、DROP、GRANT、DENY、REVOKE 和 UPDATE STATISTICS 等语句。执行 DDL 式操作的系统存储过程也可以激发 DDL 触发器。

--对于影响局部或全局临时表和存储过程的事件，不会触发 DDL 触发器。
--
--与 DML 触发器不同，DDL 触发器的作用域不是架构。因此，不能将 OBJECT_ID、OBJECT_NAME、OBJECTPROPERTY 和 OBJECTPROPERTYEX 之类的函数用于查询有关 DDL 触发器的元数据。请改用目录视图。


--------------------------------------------------------------------------------------------

--登录触发器
--
--
--登录触发器将为响应 LOGON 事件而执行存储过程。与 SQL Server 实例建立用户会话时将引发此事件。登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。因此，来自触发器内部且通常将到达用户的所有消息（例如错误消息和来自 PRINT 语句的消息）会传送到 SQL Server 错误日志。有关详细信息，请参阅登录触发器。
--
--如果身份验证失败，将不激发登录触发器。
--
--在登录触发器中不支持分布式事务。在激发包含分布式事务的登录触发器时，将返回错误 3969。



--禁用登录触发器
--
--
--登录触发器可以有效地阻止所有用户（包括 sysadmin 固定服务器角色的成员）与数据库引擎的成功连接。在登录触发器正在阻止连接时，sysadmin 固定服务器角色的成员可通过使用专用管理员连接，或者通过以最小配置模式 (-f) 启动数据库引擎，来进行连接


--------------------------------示例--------------------------------------------
--下面的示例使用 DDL 触发器来防止从数据库中删除任何同义词。
USE [AdventureWorks];
GO
IF EXISTS (SELECT * FROM sys.triggers
    WHERE parent_class = 0 AND name = 'safety')
DROP TRIGGER safety
ON DATABASE;
GO
CREATE TRIGGER safety
ON DATABASE
FOR DROP_SYNONYM
AS
   RAISERROR ('You must disable Trigger "safety" to drop synonyms!',10, 1)
   ROLLBACK
GO
DROP TRIGGER safety
ON DATABASE;
GO

------------------------------------------------------------------------------
--E.使用具有服务器范围的 DDL 触发器
--
--
--在以下示例中，如果当前服务器实例上出现任何 CREATE DATABASE 事件，则使用 DDL 触发器输出一条消息，
--并使用 EVENTDATA 函数检索对应 Transact-SQL 语句的文本
USE [AdventureWorks]
GO

IF EXISTS (SELECT * FROM sys.server_triggers
    WHERE name = 'ddl_trig_database')
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO
CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE   --CREATE_DATABASE是其中一个DDL事 具体看SQL提供了哪些DDL事件 --http://msdn.microsoft.com/zh-cn/library/bb522542.aspx
AS
    PRINT 'Database Created.'
    SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO

--XML在SQL中的使用只有 XML FOR PATH

-------------------------------------------------------------------------------
--使用登录触发器
--
--
--下面的登录触发器示例拒绝了作为 login_test 登录名的成员登录 SQL Server 的尝试（如果在此登录名下已运行三个用户会话）。

USE master;
GO
CREATE LOGIN login_test WITH PASSWORD = '123456' --MUST_CHANGE,CHECK_EXPIRATION = ON;
GO
GRANT VIEW SERVER STATE TO login_test;
GO
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'sa'   ---这里都要为sa用户，如果是login_test 他自己不能判断自己
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'login_test') > 1
    ROLLBACK;
END;


USE MASTER
go

DROP TRIGGER connection_limit_trigger
ON ALL SERVER
go

DROP USER login_test
GO

-------------------------------------------------------------------------------
--查看导致触发器触发的事件
--
--
--以下示例将查询 sys.triggers 和 sys.trigger_events 目录视图，以确定是哪个 Transact-SQL 语言事件导致触发了 safety。safety 是在前一个示例中创建的


USE pratice
GO

SELECT TE.*
FROM sys.trigger_events AS TE
JOIN sys.triggers AS T
ON T.object_id = TE.object_id
WHERE T.parent_class = 0
AND T.name = 'db_trg_RestrictINDEXChanges'
GO

SELECT *  FROM sys.triggers
SELECT *  FROM sys.trigger_events



----------------------------邀月博客------------------------------
--------/***************
--------在目标数据库服务器上创建一个触发器,以防止添加登录账号，
--------3w@live.cn
--------*******************/
USE master
GO
-- Disallow new Logins on the SQL instance
CREATE TRIGGER srv_trg_RestrictNewLogins
ON ALL SERVER
FOR CREATE_LOGIN
AS
PRINT'No login creations without DBA involvement.'
ROLLBACK
GO

--------/***************
--------试图创建一个登录账号
--------3w@live.cn
--------*******************/
CREATE LOGIN johny WITH PASSWORD ='123456'
GO

--------/***************
--------删除演示触发器
--------3w@live.cn
--------*******************/

DROP TRIGGER srv_trg_RestrictNewLogins
ON ALL SERVER
go


------------------------------------------------------------------------------


--创建一个DDL触发器审核数据库级事件
/***************
创建一个审核表,其中EventData是一个XML数据列
w@live.cn
*******************/
--这个表应该在master库中创建，这里为了演示创建在[pratice]库

USE [pratice]
GO
CREATE TABLE dbo.ChangeAttempt
(EventData xml NOT NULL,
AttemptDate datetime NOT NULL DEFAULT GETDATE(),
DBUser char(50) NOT NULL)
GO

SELECT * FROM [dbo].[ChangeAttempt]
/***************
在目标数据库上创建一个触发器,以记录该数据库的索引变化动作，
包括Create|alter|Drop
w@live.cn
*******************/
USE [pratice]
GO

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

/***************
创建一个索引，以测试触发器
w@live.cn
*******************/
USE [pratice]
GO

CREATE NONCLUSTERED INDEX ni_ChangeAttempt_DBUser ON [pratice].[dbo].[test]([a])
GO
--
--/***************
--查看审核记录
--w@live.cn
--*******************/
--
USE [pratice]
GO

SELECT * FROM [dbo].[ChangeAttempt]
--
--

------/***************
------删除测试触发器和记录表
------3w@live.cn
------*******************/
USE [pratice]
GO

drop TRIGGER db_trg_RestrictINDEXChanges
ON DATABASE
go
drop table dbo.[ChangeAttempt]
GO
-----------------------------------------------------------------------------------------
--创建一个登录触发器审核登录事件
--------------------------------------------------------
--------/***************
--------创建登录账号
--------3w@live.cn
--------*******************/

CREATE LOGIN nightworker WITH PASSWORD ='123b3b4'
GO

--------/***************
--------演示数据库和审核表
--------3w@live.cn
--------*******************/

CREATE DATABASE ExampleAuditDB
GO
USE ExampleAuditDB
GO

CREATE TABLE dbo.RestrictedLogonAttempt
(LoginNM sysname NOT NULL,
AttemptDT DATETIME NOT NULL)
GO

--------/***************
--------创建登录触发器,如果不是在7:00-17:00登录，则记录审核日志，并提示失败
--------3w@live.cn
--------*******************/

USE [ExampleAuditDB]
GO
CREATE TRIGGER trg_logon_attempt
ON ALL SERVER
WITH EXECUTE AS'sa'  --这里都要为sa用户，如果是nightworker 他自己不能判断自己
FOR LOGON,ALTER_LOGIN
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

--------/***************
--------查看审核记录
--------3w@live.cn
--------*******************/
USE ExampleAuditDB
GO
SELECT * from dbo.RestrictedLogonAttempt
go

--------/***************
--------删除演示数据库及演示触发器
--------3w@live.cn
--------*******************/
USE [ExampleAuditDB]
go

DROP TRIGGER trg_logon_attempt
ON ALL SERVER
go

DROP database ExampleAuditDB
go

DROP USER nightworker
GO






登录过，改过密码记录日志





SELECT  *
FROM    sysobjects
WHERE   xtype = 'TR'
        AND parent_obj = OBJECT_ID('表名')

SELECT  *
FROM    sysobjects
WHERE   xtype = 'TR'
 
 
SELECT  *
FROM    sysobjects
WHERE   id IN ( SELECT  parent_obj
                FROM    sysobjects
                WHERE   xtype = 'TR'
                        AND name = 'db_trg_RestrictINDEXC')
     
--触发 DDL 触发器的 DDL 事件                  
-- http://technet.microsoft.com/zh-cn/library/ms189871(v=sql.90).aspx


--设计 DDL 触发器
--http://technet.microsoft.com/zh-cn/library/ms186406(v=sql.90).aspx