SQL Server登陆账户、数据库用户（包含用户）及权限的自动化审计

最新推荐文章于 2024-04-17 19:10:59 发布

三空道人

最新推荐文章于 2024-04-17 19:10:59 发布

阅读量1.2k

点赞数

本文链接：https://blog.csdn.net/zhoujunah/article/details/104189341

版权

SQL Server 同时被 3 个专栏收录

63 篇文章 11 订阅

订阅专栏

SQL Server 安全管理

41 篇文章 3 订阅

订阅专栏

SQL Server 自动化管理

8 篇文章 1 订阅

订阅专栏

登陆账户及权限变更的审计

因为系统视图sys.server_permissions没有记录登陆账户权限变更的时间，同时为了更及时、全面的了解到登陆账户及权限变更的信息，保障数据库的安全，使用DDL触发器事件 DDL_LOGIN_EVENTS、DDL_GDR_SERVER_EVENTS、ADD_SERVER_ROLE_MEMBER、ALTER_SERVER_ROLE、CREATE_SERVER_ROLE（后面两个事件适用于SQL Server 2012及以上版本）记录登陆账户的变更信息，并邮件通知管理人员。

下面先创建登陆账户变更信息记录表：

USE master
GO
CREATE TABLE dbo.loginAudit_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 master
go
CREATE 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及以上版本
AS
BEGIN 
    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 = @Data.value('(/EVENT_INSTANCE/PostTime)[1]', 'NVARCHAR(24)') 
    SET @LoginName = @Data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(100)')
    SET @ServerName = @Data.value('(/EVENT_INSTANCE/ServerName)[1]', 'NVARCHAR(100)')
    SET @ClientHost = @Data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(100)')
    SET @LoginType = @Data.value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(100)')
    SET @DDLType = @Data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
    SET @Grantor = @Data.value('(/EVENT_INSTANCE/Grantor)[1]', 'nvarchar(100)')
    SET @Grantee = @Data.value('(/EVENT_INSTANCE/Grantees/Grantee)[1]', 'nvarchar(100)')
    SET @Permission = @Data.value('(/EVENT_INSTANCE/Permissions/Permission)[1]', 'nvarchar(100)')
    SET @ObjectName = @Data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)')
    SET @ObjectType = @Data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(100)')
    SET @GrantOption = @Data.value('(/EVENT_INSTANCE/GrantOption)[1]', 'nvarchar(100)')
    SET @CascadeOption = @Data.value('(/EVENT_INSTANCE/CascadeOption)[1]', 'nvarchar(5)')
    SET @TSQLCommand = @Data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(800)')
    --IF @ClientHost <> '<local machine>'
    --BEGIN
      
        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 msdb.dbo.sp_send_dbmail @profile_name = 'dbmail',
                  @recipients = 'Jack@dba.com',
                  @subject = N'登陆帐户及权限变更',
                  @body = @TSQLCommand
      --END
END;

注意：在创建触发器时，要修改邮件的配置文件名称，及接收人。

测试登陆账户及权限变更审计触发器

测试脚本

CREATE LOGIN [Jack] WITH PASSWORD=N'Password'
   , DEFAULT_DATABASE=[master]
   , DEFAULT_LANGUAGE=[简体中文]
   , CHECK_EXPIRATION=OFF
   , CHECK_POLICY=OFF
GO
ALTER LOGIN [Jack] DISABLE
GO
ALTER LOGIN [Jack] ENABLE
GO
GRANT VIEW ANY DATABASE TO Jack WITH GRANT OPTION
REVOKE VIEW ANY DATABASE FROM Jack
DENY VIEW ANY DATABASE TO Jack CASCADE
EXEC sys.sp_addsrvrolemember Jack,sysadmin
EXEC sys.sp_dropsrvrolemember Jack,sysadmin
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'Jack')
DROP LOGIN [Jack]

测试结果

注意：在SQL Server 2008中，使用sys.sp_dropsrvrolemember删除固定角色成员无法触发。

EXEC sys.sp_dropsrvrolemember Jack,sysadmin

在SQL Server 2016中创建触发器tr_LoginPermission_Audit时，启用事件ALTER_SERVER_ROLE,CREATE_SERVER_ROLE，并进行如下测试：

--SQL Server 2016 测试创建服务器角色、赋予权限、添加成员、删除成员、删除用户创建服务器角色
USE [master]
GO
CREATE SERVER ROLE [serverRoleName]
GO
GRANT VIEW ANY DATABASE TO serverRoleName
DENY CONTROL SERVER TO serverRoleName
EXEC sp_addsrvrolemember Jack_login, serverRoleName
--先删除用户创建的角色的所有成员，再删除用户创建的服务器角色
DECLARE @RoleName SYSNAME
SET @RoleName = N'serverRoleName'
IF @RoleName <> N'public' and (select is_fixed_role from sys.server_principals where name = @RoleName) = 0
BEGIN
    DECLARE @RoleMemberName sysname
    DECLARE Member_Cursor CURSOR FOR
    select [name]
    from sys.server_principals
    where principal_id in (
        select member_principal_id
        from sys.server_role_members
        where role_principal_id in (
            select principal_id
            FROM sys.server_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;
END
DROP SERVER ROLE [serverRoleName]
GO

测试结果：

从测试结果可以看出，删除用户创建的服务器角色、删除用户创建的服务器角色的成员均不能触发。

数据库用户（包含用户）及权限变更的审计

同上面登陆账户情景一样，下面给出了数据库用户及权限变更信息记录日志表：

USE master
GO
CREATE TABLE dbo.UserAudit_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 test
go
CREATE TRIGGER Tr_UserPermission_Audit ON DATABASE
    FOR DDL_APPLICATION_ROLE_EVENTS,DDL_GDR_DATABASE_EVENTS
            ,DDL_ROLE_EVENTS,DDL_USER_EVENTS
AS
BEGIN 
    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 = @Data.value('(/EVENT_INSTANCE/PostTime)[1]', 'NVARCHAR(24)') 
    SET @LoginName = @Data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(100)')
    SET @ServerName = @Data.value('(/EVENT_INSTANCE/ServerName)[1]', 'NVARCHAR(100)')
    SET @DBName=@Data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(100)')
    SET @SchemaName=@Data.value('(/EVENT_INSTANCE/SchemaName )[1]', 'NVARCHAR(100)')
    IF @SchemaName='' OR @SchemaName is NULL
            SET @SchemaName=@Data.value('(/EVENT_INSTANCE/DefaultSchema)[1]', 'NVARCHAR(100)')
    SET @UserName=@Data.value('(/EVENT_INSTANCE/UserName)[1]', 'NVARCHAR(100)')
    SET @ClientHost = @Data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(100)')
    SET @DDLType = @Data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
    SET @Grantor = @Data.value('(/EVENT_INSTANCE/Grantor)[1]', 'nvarchar(100)')
    SET @Grantee = @Data.value('(/EVENT_INSTANCE/Grantees/Grantee)[1]', 'nvarchar(100)')
    SET @AsGrantor=@Data.value('(/EVENT_INSTANCE/AsGrantor)[1]', 'nvarchar(100)')
    SET @Permission = @Data.value('(/EVENT_INSTANCE/Permissions/Permission)[1]', 'nvarchar(100)')
    SET @ObjectName = @Data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)')
    SET @ObjectType = @Data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(100)')
    SET @GrantOption = @Data.value('(/EVENT_INSTANCE/GrantOption)[1]', 'nvarchar(100)')
    SET @CascadeOption = @Data.value('(/EVENT_INSTANCE/CascadeOption)[1]', 'nvarchar(5)')
    SET @TSQLCommand = @Data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(800)')
    --IF @ClientHost <> '<local machine>'
    --BEGIN
      
        INSERT  INTO MASTER.dbo.UserAudit_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 msdb.dbo.sp_send_dbmail @profile_name = 'dbmail',
                  @recipients = 'Jack@dba.com',
                  @subject = N'数据库用户\角色及权限变更',
                  @body = @bodytxt
      --END
END;

注意：1. 在创建此触发器时同样需要修改邮件的配置文件名称及接收人

2. 在每个需要监控的数据库下均要创建此触发器

如下给出简单的测试样例

--SQL Server 2008测试样例
USE test
GO
CREATE USER Jack FOR LOGIN Jack
CREATE USER Jack1 WITHOUT LOGIN
ALTER USER Jack1 WITH DEFAULT_SCHEMA=test
GRANT ALTER TO Jack WITH GRANT OPTION
REVOKE ALTER TO Jack CASCADE
DENY EXEC ON OBJECT::dbo.SimpleProc TO Jack
GRANT SELECT ON OBJECT::test(a,b) TO Jack
CREATE ROLE [testRoleIntest] AUTHORIZATION [dbo]
GO
DENY EXEC ON OBJECT::dbo.SimpleProc TO [testRoleIntest]
EXEC sys.sp_addrolemember [testRoleIntest],Jack
EXEC sys.sp_droprolemember [testRoleIntest], Jack
ALTER ROLE testRoleIntest WITH NAME=testRole;
CREATE APPLICATION ROLE [AppRole] WITH DEFAULT_SCHEMA = [dbo], PASSWORD =N'Pass,12word'
GRANT CONTROL TO [AppRole]
GO
GRANT CONTROL ON APPLICATION ROLE::[AppRole] TO [Jack] AS [AppRole]
GO
ALTER APPLICATION ROLE AppRole WITH PASSWORD=N'New!2Pwd'
DROP USER Jack
DROP USER Jack1
DROP 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 =  ON
DROP USER Jack
CREATE ROLE testRole
ALTER ROLE testRole ADD member Jack
ALTER ROLE testRole DROP member Jack

测试结果

从测试结果可以看到，创建完触发器后，我们可以及时收到登陆账户、数据库用户及权限变更的预警，确保数据库的安全。

三空道人

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQL Server登陆账户、数据库用户（包含用户）及权限的自动化审计

登陆账户及权限变更的审计因为系统视图sys.server_permissions没有记录登陆账户权限变更的时间，同时为了更及时、全面的了解到登陆账户及权限变更的信息，保障数据库的安全，使用DDL触发器事件DDL_LOGIN_EVENTS、DDL_GDR_SERVER_EVENTS、ADD_SERVER_ROLE_MEMBER、ALTER_SERVER_ROLE、CREATE_SERVER_R...
复制链接

扫一扫