2023年8月1日发(作者：)
SQLServer登陆账户、数据库⽤户（包含⽤户）及权限的⾃动化审计登陆账户及权限变更的审计因为系统视图_permissions没有记录登陆账户权限变更的时间，同时为了更及时、全⾯的了解到登陆账户及权限变更的信息，保障数据库的安全，使⽤DDL触发器事件 DDL_LOGIN_EVENTS、DDL_GDR_SERVER_EVENTS、ADD_SERVER_ROLE_MEMBER、ALTER_SERVER_ROLE、CREATE_SERVER_ROLE（后⾯两个事件适⽤于SQL Server 2012及以上版本）记录登陆账户的变更信息，并邮件通知管理⼈员。下⾯先创建登陆账户变更信息记录表：USE masterGOCREATE TABLE udit_log ( id INT IDENTITY(1, 1) ,Posttime DATETIME NULL ,Servername VARCHAR(30) NULL ,Hostname VARCHAR(30) NULL ,Loginame VARCHAR(30) NULL ,Logintype VARCHAR(20) NULL ,ClientHost VARCHAR(20) NULL ,DDLType VARCHAR(30) NULL ,Grantor SYSNAME NULL ,Grantee SYSNAME NULL ,Permission VARCHAR(30) NULL ,ObjectName SYSNAME NULL ,ObjectType VARCHAR(30) NULL ,GrantOption BIT NULL ,CascadeOption BIT NULL ,TSQLCommand NVARCHAR(800) NULL )接着创建服务器及触发器，记录并警告登陆账户及权限变更：USE mastergoCREATE TRIGGER tr_LoginPermission_Audit ON ALL SERVER WITH EXECUTE AS 'sa' FOR DDL_LOGIN_EVENTS, DDL_GDR_SERVER_EVENTS,ADD_SERVER_ROLE_MEMBER --,ALTER_SERVER_ROLE,CREATE_SERVER_ROLE --适⽤于SQL Server 2012及以上版本ASBEGIN
DECLARE @Data XML
DECLARE @PostTime NVARCHAR(24)
DECLARE @LoginName NVARCHAR(100)
DECLARE @HostName NVARCHAR(100) DECLARE @ServerName NVARCHAR(20)
DECLARE @LoginType NVARCHAR(20)
DECLARE @ClientHost NVARCHAR(20) DECLARE @DDLType VARCHAR(30) DECLARE @Grantor SYSNAME
DECLARE @Grantee SYSNAME DECLARE @Permission VARCHAR(30) DECLARE @ObjectName SYSNAME DECLARE @ObjectType VARCHAR(30) DECLARE @GrantOption BIT DECLARE @CascadeOption BIT DECLARE @TSQLCommand nVARCHAR(800)
SET @Data = EVENTDATA() SET @HostName = HOST_NAME() SET @PostTime = @('(/EVENT_INSTANCE/PostTime)[1]', 'NVARCHAR(24)')
SET @LoginName = @('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(100)') SET @ServerName = @('(/EVENT_INSTANCE/ServerName)[1]', 'NVARCHAR(100)') SET @ClientHost = @('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(100)') SET @LoginType = @('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(100)') SET @DDLType = @('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') SET @Grantor = @('(/EVENT_INSTANCE/Grantor)[1]', 'nvarchar(100)') SET @Grantee = @('(/EVENT_INSTANCE/Grantees/Grantee)[1]', 'nvarchar(100)') SET @Permission = @('(/EVENT_INSTANCE/Permissions/Permission)[1]', 'nvarchar(100)') SET @ObjectName = @('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)') SET @ObjectType = @('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(100)') SET @GrantOption = @('(/EVENT_INSTANCE/GrantOption)[1]', 'nvarchar(100)') SET @CascadeOption = @('(/EVENT_INSTANCE/CascadeOption)[1]', 'nvarchar(5)') SET @TSQLCommand = @('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(800)') --IF @ClientHost <> '
INSERT INTO loginAudit_log ( Posttime, Servername, Hostname, Loginame, Logintype, ClientHost, DDLType, Grantor, Grantee, Permission, ObjectName, ObjectType, GrantOption, CascadeOption, TSQLCommand ) VALUES ( @posttime, @ServerName, @hostname, @loginname, @LoginType, @ClientHost, @DDLType, @Grantor, @Grantee, @Permission, @ObjectName, @ObjectType, @GrantOption, @CascadeOption, @TSQLCommand ) EXEC _send_dbmail @profile_name = 'dbmail', @recipients = 'Jack@', @subject = N'登陆帐户及权限变更', @body = @TSQLCommand --ENDEND;注意：在创建触发器时，要修改邮件的配置⽂件名称，及接收⼈。测试登陆账户及权限变更审计触发器测试脚本CREATE LOGIN [Jack] WITH PASSWORD=N'Password' , DEFAULT_DATABASE=[master] , DEFAULT_LANGUAGE=[简体中⽂] , CHECK_EXPIRATION=OFF , CHECK_POLICY=OFFGOALTER LOGIN [Jack] DISABLEGOALTER LOGIN [Jack] ENABLEGOGRANT VIEW ANY DATABASE TO Jack WITH GRANT OPTIONREVOKE VIEW ANY DATABASE FROM JackDENY VIEW ANY DATABASE TO Jack CASCADEEXEC _addsrvrolemember Jack,sysadminEXEC _dropsrvrolemember Jack,sysadminIF EXISTS (SELECT * FROM _principals WHERE name = N'Jack')DROP LOGIN [Jack]测试结果注意：在SQL Server 2008中，使⽤_dropsrvrolemember删除固定⾓⾊成员⽆法触发。EXEC _dropsrvrolemember Jack,sysadmin在SQL Server 2016中创建触发器tr_LoginPermission_Audit时，启⽤事件ALTER_SERVER_ROLE,CREATE_SERVER_ROLE，并进⾏如下测试：--SQL Server 2016 测试创建服务器⾓⾊、赋予权限、添加成员、删除成员、删除⽤户创建服务器⾓⾊USE [master]GOCREATE SERVER ROLE [serverRoleName]GOGRANT VIEW ANY DATABASE TO serverRoleNameDENY CONTROL SERVER TO serverRoleNameEXEC sp_addsrvrolemember Jack_login, serverRoleName--先删除⽤户创建的⾓⾊的所有成员，再删除⽤户创建的服务器⾓⾊DECLARE @RoleName SYSNAMESET @RoleName = N'serverRoleName'IF @RoleName <> N'public' and (select is_fixed_role from _principals where name = @RoleName) = 0BEGIN DECLARE @RoleMemberName sysname DECLARE Member_Cursor CURSOR FOR select [name] from _principals where principal_id in ( select member_principal_id from _role_members where role_principal_id in ( select principal_id FROM _principals where [name] = @RoleName AND type = 'R' )) OPEN Member_Cursor; FETCH NEXT FROM Member_Cursor into @RoleMemberName DECLARE @SQL NVARCHAR(4000)
WHILE @@FETCH_STATUS = 0 BEGIN
SET @SQL = 'ALTER SERVER ROLE '+ QUOTENAME(@RoleName,'[') +' DROP MEMBER '+ QUOTENAME(@RoleMemberName,'[') EXEC(@SQL)
FETCH NEXT FROM Member_Cursor into @RoleMemberName END; CLOSE Member_Cursor; DEALLOCATE Member_Cursor;ENDDROP SERVER ROLE [serverRoleName]GO测试结果：从测试结果可以看出，删除⽤户创建的服务器⾓⾊、删除⽤户创建的服务器⾓⾊的成员均不能触发。数据库⽤户（包含⽤户）及权限变更的审计同上⾯登陆账户情景⼀样，下⾯给出了数据库⽤户及权限变更信息记录⽇志表：USE masterGOCREATE TABLE dit_Log ( Id INT IDENTITY(1, 1) ,Posttime DATETIME NULL ,Servername sysname NULL ,Hostname sysname NULL ,Loginame sysname NULL ,UserName sysname NULL ,ClientHost VARCHAR(20) NULL ,DBName sysname NULL ,SchemaName sysname NULL ,DDLType VARCHAR(30) NULL ,Grantor SYSNAME NULL ,Grantee SYSNAME NULL ,Permission VARCHAR(30) NULL ,AsGrantor sysname NULL ,ObjectName SYSNAME NULL ,ObjectType VARCHAR(30) NULL ,GrantOption BIT NULL ,CascadeOption BIT NULL ,TSQLCommand NVARCHAR(800) NULL ) --DefaultSchema --RoleName和服务器触发器不同，接下来我们需要在各数据库中创建数据库及触发器，如下给出了在数据库test下创建的触发器Tr_UserPermission_Audit：USE testgoCREATE TRIGGER Tr_UserPermission_Audit ON DATABASE FOR DDL_APPLICATION_ROLE_EVENTS,DDL_GDR_DATABASE_EVENTS ,DDL_ROLE_EVENTS,DDL_USER_EVENTSASBEGIN
DECLARE @Data XML
DECLARE @PostTime NVARCHAR(24)
DECLARE @LoginName sysname
DECLARE @HostName sysname DECLARE @ServerName sysname DECLARE @ClientHost NVARCHAR(20)
DECLARE @UserName sysname DECLARE @DBName sysname DECLARE @SchemaName sysname DECLARE @DDLType VARCHAR(30) DECLARE @Grantor SYSNAME
DECLARE @Grantee SYSNAME DECLARE @AsGrantor sysname DECLARE @Permission VARCHAR(30) DECLARE @ObjectName SYSNAME DECLARE @ObjectType VARCHAR(30) DECLARE @GrantOption BIT DECLARE @CascadeOption BIT DECLARE @TSQLCommand nVARCHAR(800) SET @Data = EVENTDATA() SET @HostName = HOST_NAME() SET @PostTime = @('(/EVENT_INSTANCE/PostTime)[1]', 'NVARCHAR(24)')
SET @LoginName = @('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(100)') SET @ServerName = @('(/EVENT_INSTANCE/ServerName)[1]', 'NVARCHAR(100)') SET @DBName=@('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(100)') SET @SchemaName=@('(/EVENT_INSTANCE/SchemaName )[1]', 'NVARCHAR(100)') IF @SchemaName='' OR @SchemaName is NULL SET @SchemaName=@('(/EVENT_INSTANCE/DefaultSchema)[1]', 'NVARCHAR(100)') SET @UserName=@('(/EVENT_INSTANCE/UserName)[1]', 'NVARCHAR(100)') SET @ClientHost = @('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(100)') SET @DDLType = @('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') SET @Grantor = @('(/EVENT_INSTANCE/Grantor)[1]', 'nvarchar(100)') SET @Grantee = @('(/EVENT_INSTANCE/Grantees/Grantee)[1]', 'nvarchar(100)') SET @AsGrantor=@('(/EVENT_INSTANCE/AsGrantor)[1]', 'nvarchar(100)') SET @Permission = @('(/EVENT_INSTANCE/Permissions/Permission)[1]', 'nvarchar(100)') SET @ObjectName = @('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)') SET @ObjectType = @('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(100)') SET @GrantOption = @('(/EVENT_INSTANCE/GrantOption)[1]', 'nvarchar(100)') SET @CascadeOption = @('(/EVENT_INSTANCE/CascadeOption)[1]', 'nvarchar(5)') SET @TSQLCommand = @('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(800)') --IF @ClientHost <> '
INSERT INTO dit_Log ( Posttime, Servername, Hostname, Loginame, ClientHost,UserName,DBName,SchemaName, DDLType, Grantor, Grantee,AsGrantor, Permission, ObjectName, ObjectType, GrantOption, CascadeOption, TSQLCommand ) VALUES ( @posttime, @ServerName, @hostname, @loginname, @ClientHost,@UserName,@DBName,@SchemaName, @DDLType, @Grantor, @Grantee,@AsGrantor, @Permission, @ObjectName, @ObjectType, @GrantOption, @CascadeOption, @TSQLCommand ) DECLARE @bodytxt nvarchar(MAX) SET @bodytxt=N'数据库'+@DBName+N'变更TSQL:'+CHAR(10)+@TSQLCommand EXEC _send_dbmail @profile_name = 'dbmail', @recipients = 'Jack@', @subject = N'数据库⽤户⾓⾊及权限变更', @body = @bodytxt --ENDEND;
注意：1. 在创建此触发器时同样需要修改邮件的配置⽂件名称及接收⼈ 2. 在每个需要监控的数据库下均要创建此触发器如下给出简单的测试样例--SQL Server 2008测试样例USE testGOCREATE USER Jack FOR LOGIN JackCREATE USER Jack1 WITHOUT LOGINALTER USER Jack1 WITH DEFAULT_SCHEMA=testGRANT ALTER TO Jack WITH GRANT OPTIONREVOKE ALTER TO Jack CASCADEDENY EXEC ON OBJECT::Proc TO JackGRANT SELECT ON OBJECT::test(a,b) TO JackCREATE ROLE [testRoleIntest] AUTHORIZATION [dbo]GODENY EXEC ON OBJECT::Proc TO [testRoleIntest]EXEC _addrolemember [testRoleIntest],JackEXEC _droprolemember [testRoleIntest], JackALTER ROLE testRoleIntest WITH NAME=testRole;CREATE APPLICATION ROLE [AppRole] WITH DEFAULT_SCHEMA = [dbo], PASSWORD =N'Pass,12word'GRANT CONTROL TO [AppRole]GOGRANT CONTROL ON APPLICATION ROLE::[AppRole] TO [Jack] AS [AppRole]GOALTER APPLICATION ROLE AppRole WITH PASSWORD=N'New!2Pwd'DROP USER JackDROP USER Jack1DROP ROLE [testRole]DROP APPLICATION ROLE [AppRole]SQL Server 2012 及以上测试环境测试样例：--SQL Server 2012及以上环境CREATE USER Jack WITH password='Password,@1'ALTER USER Jack WITH ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = ONDROP USER JackCREATE ROLE testRoleALTER ROLE testRole ADD member JackALTER ROLE testRole DROP member Jack测试结果从测试结果可以看到，创建完触发器后，我们可以及时收到登陆账户、数据库⽤户及权限变更的预警，确保数据库的安全。
2023年8月1日发(作者：)
SQLServer登陆账户、数据库⽤户（包含⽤户）及权限的⾃动化审计登陆账户及权限变更的审计因为系统视图_permissions没有记录登陆账户权限变更的时间，同时为了更及时、全⾯的了解到登陆账户及权限变更的信息，保障数据库的安全，使⽤DDL触发器事件 DDL_LOGIN_EVENTS、DDL_GDR_SERVER_EVENTS、ADD_SERVER_ROLE_MEMBER、ALTER_SERVER_ROLE、CREATE_SERVER_ROLE（后⾯两个事件适⽤于SQL Server 2012及以上版本）记录登陆账户的变更信息，并邮件通知管理⼈员。下⾯先创建登陆账户变更信息记录表：USE masterGOCREATE TABLE udit_log ( id INT IDENTITY(1, 1) ,Posttime DATETIME NULL ,Servername VARCHAR(30) NULL ,Hostname VARCHAR(30) NULL ,Loginame VARCHAR(30) NULL ,Logintype VARCHAR(20) NULL ,ClientHost VARCHAR(20) NULL ,DDLType VARCHAR(30) NULL ,Grantor SYSNAME NULL ,Grantee SYSNAME NULL ,Permission VARCHAR(30) NULL ,ObjectName SYSNAME NULL ,ObjectType VARCHAR(30) NULL ,GrantOption BIT NULL ,CascadeOption BIT NULL ,TSQLCommand NVARCHAR(800) NULL )接着创建服务器及触发器，记录并警告登陆账户及权限变更：USE mastergoCREATE TRIGGER tr_LoginPermission_Audit ON ALL SERVER WITH EXECUTE AS 'sa' FOR DDL_LOGIN_EVENTS, DDL_GDR_SERVER_EVENTS,ADD_SERVER_ROLE_MEMBER --,ALTER_SERVER_ROLE,CREATE_SERVER_ROLE --适⽤于SQL Server 2012及以上版本ASBEGIN
DECLARE @Data XML
DECLARE @PostTime NVARCHAR(24)
DECLARE @LoginName NVARCHAR(100)
DECLARE @HostName NVARCHAR(100) DECLARE @ServerName NVARCHAR(20)
DECLARE @LoginType NVARCHAR(20)
DECLARE @ClientHost NVARCHAR(20) DECLARE @DDLType VARCHAR(30) DECLARE @Grantor SYSNAME
DECLARE @Grantee SYSNAME DECLARE @Permission VARCHAR(30) DECLARE @ObjectName SYSNAME DECLARE @ObjectType VARCHAR(30) DECLARE @GrantOption BIT DECLARE @CascadeOption BIT DECLARE @TSQLCommand nVARCHAR(800)
SET @Data = EVENTDATA() SET @HostName = HOST_NAME() SET @PostTime = @('(/EVENT_INSTANCE/PostTime)[1]', 'NVARCHAR(24)')
SET @LoginName = @('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(100)') SET @ServerName = @('(/EVENT_INSTANCE/ServerName)[1]', 'NVARCHAR(100)') SET @ClientHost = @('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(100)') SET @LoginType = @('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(100)') SET @DDLType = @('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') SET @Grantor = @('(/EVENT_INSTANCE/Grantor)[1]', 'nvarchar(100)') SET @Grantee = @('(/EVENT_INSTANCE/Grantees/Grantee)[1]', 'nvarchar(100)') SET @Permission = @('(/EVENT_INSTANCE/Permissions/Permission)[1]', 'nvarchar(100)') SET @ObjectName = @('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)') SET @ObjectType = @('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(100)') SET @GrantOption = @('(/EVENT_INSTANCE/GrantOption)[1]', 'nvarchar(100)') SET @CascadeOption = @('(/EVENT_INSTANCE/CascadeOption)[1]', 'nvarchar(5)') SET @TSQLCommand = @('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(800)') --IF @ClientHost <> '
INSERT INTO loginAudit_log ( Posttime, Servername, Hostname, Loginame, Logintype, ClientHost, DDLType, Grantor, Grantee, Permission, ObjectName, ObjectType, GrantOption, CascadeOption, TSQLCommand ) VALUES ( @posttime, @ServerName, @hostname, @loginname, @LoginType, @ClientHost, @DDLType, @Grantor, @Grantee, @Permission, @ObjectName, @ObjectType, @GrantOption, @CascadeOption, @TSQLCommand ) EXEC _send_dbmail @profile_name = 'dbmail', @recipients = 'Jack@', @subject = N'登陆帐户及权限变更', @body = @TSQLCommand --ENDEND;注意：在创建触发器时，要修改邮件的配置⽂件名称，及接收⼈。测试登陆账户及权限变更审计触发器测试脚本CREATE LOGIN [Jack] WITH PASSWORD=N'Password' , DEFAULT_DATABASE=[master] , DEFAULT_LANGUAGE=[简体中⽂] , CHECK_EXPIRATION=OFF , CHECK_POLICY=OFFGOALTER LOGIN [Jack] DISABLEGOALTER LOGIN [Jack] ENABLEGOGRANT VIEW ANY DATABASE TO Jack WITH GRANT OPTIONREVOKE VIEW ANY DATABASE FROM JackDENY VIEW ANY DATABASE TO Jack CASCADEEXEC _addsrvrolemember Jack,sysadminEXEC _dropsrvrolemember Jack,sysadminIF EXISTS (SELECT * FROM _principals WHERE name = N'Jack')DROP LOGIN [Jack]测试结果注意：在SQL Server 2008中，使⽤_dropsrvrolemember删除固定⾓⾊成员⽆法触发。EXEC _dropsrvrolemember Jack,sysadmin在SQL Server 2016中创建触发器tr_LoginPermission_Audit时，启⽤事件ALTER_SERVER_ROLE,CREATE_SERVER_ROLE，并进⾏如下测试：--SQL Server 2016 测试创建服务器⾓⾊、赋予权限、添加成员、删除成员、删除⽤户创建服务器⾓⾊USE [master]GOCREATE SERVER ROLE [serverRoleName]GOGRANT VIEW ANY DATABASE TO serverRoleNameDENY CONTROL SERVER TO serverRoleNameEXEC sp_addsrvrolemember Jack_login, serverRoleName--先删除⽤户创建的⾓⾊的所有成员，再删除⽤户创建的服务器⾓⾊DECLARE @RoleName SYSNAMESET @RoleName = N'serverRoleName'IF @RoleName <> N'public' and (select is_fixed_role from _principals where name = @RoleName) = 0BEGIN DECLARE @RoleMemberName sysname DECLARE Member_Cursor CURSOR FOR select [name] from _principals where principal_id in ( select member_principal_id from _role_members where role_principal_id in ( select principal_id FROM _principals where [name] = @RoleName AND type = 'R' )) OPEN Member_Cursor; FETCH NEXT FROM Member_Cursor into @RoleMemberName DECLARE @SQL NVARCHAR(4000)
WHILE @@FETCH_STATUS = 0 BEGIN
SET @SQL = 'ALTER SERVER ROLE '+ QUOTENAME(@RoleName,'[') +' DROP MEMBER '+ QUOTENAME(@RoleMemberName,'[') EXEC(@SQL)
FETCH NEXT FROM Member_Cursor into @RoleMemberName END; CLOSE Member_Cursor; DEALLOCATE Member_Cursor;ENDDROP SERVER ROLE [serverRoleName]GO测试结果：从测试结果可以看出，删除⽤户创建的服务器⾓⾊、删除⽤户创建的服务器⾓⾊的成员均不能触发。数据库⽤户（包含⽤户）及权限变更的审计同上⾯登陆账户情景⼀样，下⾯给出了数据库⽤户及权限变更信息记录⽇志表：USE masterGOCREATE TABLE dit_Log ( Id INT IDENTITY(1, 1) ,Posttime DATETIME NULL ,Servername sysname NULL ,Hostname sysname NULL ,Loginame sysname NULL ,UserName sysname NULL ,ClientHost VARCHAR(20) NULL ,DBName sysname NULL ,SchemaName sysname NULL ,DDLType VARCHAR(30) NULL ,Grantor SYSNAME NULL ,Grantee SYSNAME NULL ,Permission VARCHAR(30) NULL ,AsGrantor sysname NULL ,ObjectName SYSNAME NULL ,ObjectType VARCHAR(30) NULL ,GrantOption BIT NULL ,CascadeOption BIT NULL ,TSQLCommand NVARCHAR(800) NULL ) --DefaultSchema --RoleName和服务器触发器不同，接下来我们需要在各数据库中创建数据库及触发器，如下给出了在数据库test下创建的触发器Tr_UserPermission_Audit：USE testgoCREATE TRIGGER Tr_UserPermission_Audit ON DATABASE FOR DDL_APPLICATION_ROLE_EVENTS,DDL_GDR_DATABASE_EVENTS ,DDL_ROLE_EVENTS,DDL_USER_EVENTSASBEGIN
DECLARE @Data XML
DECLARE @PostTime NVARCHAR(24)
DECLARE @LoginName sysname
DECLARE @HostName sysname DECLARE @ServerName sysname DECLARE @ClientHost NVARCHAR(20)
DECLARE @UserName sysname DECLARE @DBName sysname DECLARE @SchemaName sysname DECLARE @DDLType VARCHAR(30) DECLARE @Grantor SYSNAME
DECLARE @Grantee SYSNAME DECLARE @AsGrantor sysname DECLARE @Permission VARCHAR(30) DECLARE @ObjectName SYSNAME DECLARE @ObjectType VARCHAR(30) DECLARE @GrantOption BIT DECLARE @CascadeOption BIT DECLARE @TSQLCommand nVARCHAR(800) SET @Data = EVENTDATA() SET @HostName = HOST_NAME() SET @PostTime = @('(/EVENT_INSTANCE/PostTime)[1]', 'NVARCHAR(24)')
SET @LoginName = @('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(100)') SET @ServerName = @('(/EVENT_INSTANCE/ServerName)[1]', 'NVARCHAR(100)') SET @DBName=@('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(100)') SET @SchemaName=@('(/EVENT_INSTANCE/SchemaName )[1]', 'NVARCHAR(100)') IF @SchemaName='' OR @SchemaName is NULL SET @SchemaName=@('(/EVENT_INSTANCE/DefaultSchema)[1]', 'NVARCHAR(100)') SET @UserName=@('(/EVENT_INSTANCE/UserName)[1]', 'NVARCHAR(100)') SET @ClientHost = @('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(100)') SET @DDLType = @('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') SET @Grantor = @('(/EVENT_INSTANCE/Grantor)[1]', 'nvarchar(100)') SET @Grantee = @('(/EVENT_INSTANCE/Grantees/Grantee)[1]', 'nvarchar(100)') SET @AsGrantor=@('(/EVENT_INSTANCE/AsGrantor)[1]', 'nvarchar(100)') SET @Permission = @('(/EVENT_INSTANCE/Permissions/Permission)[1]', 'nvarchar(100)') SET @ObjectName = @('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)') SET @ObjectType = @('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(100)') SET @GrantOption = @('(/EVENT_INSTANCE/GrantOption)[1]', 'nvarchar(100)') SET @CascadeOption = @('(/EVENT_INSTANCE/CascadeOption)[1]', 'nvarchar(5)') SET @TSQLCommand = @('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(800)') --IF @ClientHost <> '
INSERT INTO dit_Log ( Posttime, Servername, Hostname, Loginame, ClientHost,UserName,DBName,SchemaName, DDLType, Grantor, Grantee,AsGrantor, Permission, ObjectName, ObjectType, GrantOption, CascadeOption, TSQLCommand ) VALUES ( @posttime, @ServerName, @hostname, @loginname, @ClientHost,@UserName,@DBName,@SchemaName, @DDLType, @Grantor, @Grantee,@AsGrantor, @Permission, @ObjectName, @ObjectType, @GrantOption, @CascadeOption, @TSQLCommand ) DECLARE @bodytxt nvarchar(MAX) SET @bodytxt=N'数据库'+@DBName+N'变更TSQL:'+CHAR(10)+@TSQLCommand EXEC _send_dbmail @profile_name = 'dbmail', @recipients = 'Jack@', @subject = N'数据库⽤户⾓⾊及权限变更', @body = @bodytxt --ENDEND;
注意：1. 在创建此触发器时同样需要修改邮件的配置⽂件名称及接收⼈ 2. 在每个需要监控的数据库下均要创建此触发器如下给出简单的测试样例--SQL Server 2008测试样例USE testGOCREATE USER Jack FOR LOGIN JackCREATE USER Jack1 WITHOUT LOGINALTER USER Jack1 WITH DEFAULT_SCHEMA=testGRANT ALTER TO Jack WITH GRANT OPTIONREVOKE ALTER TO Jack CASCADEDENY EXEC ON OBJECT::Proc TO JackGRANT SELECT ON OBJECT::test(a,b) TO JackCREATE ROLE [testRoleIntest] AUTHORIZATION [dbo]GODENY EXEC ON OBJECT::Proc TO [testRoleIntest]EXEC _addrolemember [testRoleIntest],JackEXEC _droprolemember [testRoleIntest], JackALTER ROLE testRoleIntest WITH NAME=testRole;CREATE APPLICATION ROLE [AppRole] WITH DEFAULT_SCHEMA = [dbo], PASSWORD =N'Pass,12word'GRANT CONTROL TO [AppRole]GOGRANT CONTROL ON APPLICATION ROLE::[AppRole] TO [Jack] AS [AppRole]GOALTER APPLICATION ROLE AppRole WITH PASSWORD=N'New!2Pwd'DROP USER JackDROP USER Jack1DROP ROLE [testRole]DROP APPLICATION ROLE [AppRole]SQL Server 2012 及以上测试环境测试样例：--SQL Server 2012及以上环境CREATE USER Jack WITH password='Password,@1'ALTER USER Jack WITH ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = ONDROP USER JackCREATE ROLE testRoleALTER ROLE testRole ADD member JackALTER ROLE testRole DROP member Jack测试结果从测试结果可以看到，创建完触发器后，我们可以及时收到登陆账户、数据库⽤户及权限变更的预警，确保数据库的安全。
发布评论