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

不负韶华，只争朝夕！在抗击新冠疫情的日子里，从每天关注疫情动态的恐慌，在家中的彷徨，到坚定自己的信念。也许上天认为我们走的太匆忙，希望我们静下来，重新规划一下未来；希望可以进行沉淀，以让我们走的更高、更远。在最近的日子里，在完成线上工作之余，我开始总结，并把一些经验记录下来，分享出来，和大家共勉。

闲话不多说，接下来还是直接上正文。

在《SQL Server登陆账户、数据库用户（包含用户）及权限的自动化审计》一文中我分享了对登陆账户、数据库用户及权限变更的即时预警，除此之外，我们还需要定期全面检查登陆账户、数据库用户及权限，收回不必要的登陆账户、数据库用户及权限，以减少数据库安全风险。要对历史的登录账户/数据库用户及权限进行比较，首先需要记录历史登录账户/数据库用户及权限信息。下面创建两个表分别记录登录账户及权限信息，数据库用户及权限信息：

--创建登陆帐户权限信息表
USE master
GO
CREATE TABLE DBA_LoginsPermissions_Info
    (class_desc NVARCHAR(60)
    ,permission_name NVARCHAR(128)
    ,GDR nvarchar(60)
    ,granteeName sysname NULL
    ,objectName NVARCHAR(60)
    ,TSQLTxt NVARCHAR(800)
    ,InsertDate DATETIME DEFAULT(GETDATE()))
--创建数据库用户权限信息表
CREATE TABLE DBA_UsersPermissions_Info
    (dbName sysname NULL
    ,class_desc NVARCHAR(60)
    ,permission_name NVARCHAR(128)
    ,GDR nvarchar(60)
    ,granteeName sysname NULL
    ,objectName NVARCHAR(60)
    ,TSQLTxt NVARCHAR(800)
    ,InsertDate DATETIME DEFAULT(GETDATE()))

注意，如果留意的话，可能已经发现表DBA_UsersPermissions_Info 在前文已经创建，如果你已经创建它，本文中的创建该表的​脚本可以忽略。

下面我们来创建获取登陆账户、数据库用户及权限信息的过程：

USE master
GO
/*记录登陆帐户、数据库用户及权限信息*/
CREATE PROC DBA_GetLoginsUsersPermissions_Info AS     
BEGIN
      SET NOCOUNT ON
      --记录当前登陆帐户权限信息
      DECLARE @LoginName sysname
      DECLARE @sid VARBINARY(85)
      DECLARE cur CURSOR FOR
      SELECT name,sid FROM sys.sql_logins
      WHERE is_disabled=0
      OPEN cur
      FETCH NEXT FROM cur INTO @LoginName,@sid
      WHILE @@FETCH_STATUS=0
      BEGIN
            INSERT INTO DBA_LoginsPermissions_Info(class_desc,permission_name,GDR,granteeName,objectName,TSQLTxt)
            EXEC sp_DBA_LoginPermissionsClone @LoginName=@LoginName,@NewLoginName=@LoginName,@print=0
            --记录当前数据库用户权限信息
            DECLARE @DBName sysname
            DECLARE @UserName sysname
            DECLARE @tsql NVARCHAR(200)
            DECLARE db CURSOR FOR
            SELECT name FROM sys.databases
            OPEN db
            FETCH NEXT FROM db INTO @DBName
            WHILE @@FETCH_STATUS=0
            BEGIN
                  SET @tsql=N'SELECT @UserName=name from '+QUOTENAME(@dbName,']')+N'.sys.sysusers WHERE sid=@sid'
                  EXEC sys.sp_executesql @tsql,N'@UserName sysname output,@sid VARBINARY(85)',@UserName OUTPUT,@sid
                  --INSERT INTO master.dbo.DBA_UsersPermissions_Info(dbName,class_desc,permission_name,GDR,granteeName,objectName,TSQLTxt)
                  exec sp_DBA_userPermisionsClone @dbName=@DBName,@userName=@UserName,@print=0
                  FETCH NEXT FROM db INTO @DBName
            END
            CLOSE db
            DEALLOCATE db
            FETCH NEXT FROM cur INTO @LoginName,@sid
      END
      CLOSE cur
      DEALLOCATE cur
      --增加包含数据库中包含用户权限信息获取及监控
      DECLARE @versionNum AS INT    --SQL Server 的版本号
      SET @versionNum = SUBSTRING(@@VERSION, 22, 4)
      IF @versionNum>=2012
      BEGIN
            DECLARE @db TABLE(NAME sysname)
            INSERT INTO @db ( NAME )
            EXEC ('SELECT  name
                  FROM    sys.databases
                  WHERE   containment = 1')
            DECLARE cur CURSOR FOR
            SELECT  name FROM @db
            OPEN cur
            FETCH NEXT FROM cur INTO @DBName
            WHILE @@FETCH_STATUS=0
            BEGIN
                  DECLARE @sql VARCHAR(200)
                  DECLARE @t TABLE (NAME sysname)
                  SET @sql='SELECT name from '+QUOTENAME(@DBName,']')+'.sys.database_principals WHERE authentication_type=2 '
                  INSERT INTO @t ( NAME )
                  EXEC(@sql)
                  DECLARE users CURSOR FOR
                  SELECT name from [@t]
                  OPEN users
                  FETCH NEXT FROM users INTO @UserName
                  WHILE @@FETCH_STATUS=0
                  BEGIN
                        exec sp_DBA_userPermisionsClone @dbName=@DBName,@userName=@UserName,@print=0
                        FETCH NEXT FROM users INTO @UserName
                  END
                  CLOSE users
                  DEALLOCATE users
                  FETCH NEXT FROM cur INTO @DBName
            END
      END
END
GO

注意，在创建这个过程之前需要先创建 sp_DBA_LoginPermissionsClone 及 sp_DBA_userPermisionsClone 。

下面我们直接执行过程，获取当前的登陆账户、数据库用户及权限信息，脚本如下：

--记录当前登陆帐户、数据库用户及权限信息
EXEC DBA_GetLoginsUsersPermissions_Info
GO

检查登陆账户及权限信息​：

SELECT * FROM DBA_LoginsPermissions_Info

检查用户权限及信息​：

SELECT * FROM DBA_UsersPermissions_Info

接下来我们创建审计登陆账户、数据库用户权限的过程：

/*创建定期检查过程*/
CREATE PROC DBA_LoginsUsersPermissions_Audit AS
BEGIN
      SET NOCOUNT ON
      --记录当前登陆帐户、数据库用户及权限信息
      EXEC DBA_GetLoginsUsersPermissions_Info
      DECLARE @bodyHTML nvarchar(MAX)
      --删除已经回收的登陆帐户、权限信息
      DELETE master.dbo.DBA_LoginsPermissions_Info
      WHERE TSQLTxt NOT IN( SELECT TSQLTxt FROM master.dbo.DBA_LoginsPermissions_Info WHERE insertdate>=CAST(GETDATE() AS date))
            AND insertdate<CAST(GETDATE() AS date)
      --保存新增登陆帐户、权限信息
      DELETE master.dbo.DBA_LoginsPermissions_Info
      WHERE TSQLTxt IN( SELECT TSQLTxt FROM master.dbo.DBA_LoginsPermissions_Info WHERE insertdate<CAST(GETDATE() AS date))
            AND insertdate>CAST(GETDATE() AS date)
            
      --删除已经回收的数据库用户、权限信息
      DELETE master.dbo.DBA_UsersPermissions_Info
      WHERE TSQLTxt NOT IN( SELECT TSQLTxt FROM master.dbo.DBA_UsersPermissions_Info WHERE insertdate>=CAST(GETDATE() AS date))
            AND insertdate<CAST(GETDATE() AS date)
      --保存新增数据库用户、权限信息
      DELETE master.dbo.DBA_UsersPermissions_Info
      WHERE TSQLTxt IN( SELECT TSQLTxt FROM master.dbo.DBA_UsersPermissions_Info WHERE insertdate<CAST(GETDATE() AS date))
            AND insertdate>CAST(GETDATE() AS date)
      IF EXISTS(SELECT TSQLTxt FROM master.dbo.DBA_LoginsPermissions_Info WHERE insertdate>=CAST(GETDATE() AS date))
      BEGIN
            SET @bodyHTML = N'<style>td{FONT-FAMILY: 宋体, Helvetica, sans-serif; FONT-SIZE: 12px;
            LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;
            BORDER-RIGHT: black 1px solid;}th{FONT-FAMILY: 宋体, Helvetica, sans-serif; FONT-SIZE: 12px;
            LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}</style>' 
            + N'<H1>[登陆帐户及权限变更]</H1>' 
            + N'<table border="0" cellspacing="0" cellpadding="0" style="BORDER-BOTTOM: black 1px solid;
                  BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid;BORDER-TOP: black 1px solid">' 
            + N'<tr><th>对象类型</th><th>权限</th>'
            + N'<th>GRANT OR DENY</th>'
            + N'<th>授权者</th>'
            + N'<th>授权对象</th><th>执行脚本</th></tr>' 
            + CAST(( 
             SELECT td = ISNULL(class_desc,'') ,  '' , 
                        td = ISNULL(permission_name ,''),  '' , 
                        td = ISNULL(GDR,''),  '' , 
                        td = ISNULL(granteeName ,''),  '' , 
                        td = ISNULL(objectName,'') ,  '' , 
                        td = ISNULL(TSQLTxt,'')
            FROM  master.dbo.DBA_LoginsPermissions_Info h
            WHERE insertdate>=CAST(GETDATE() AS date) 
                     FOR 
                         XML PATH('tr') , 
                               TYPE 
                     ) AS NVARCHAR(MAX)) + N'</table>'; 
      END
      IF EXISTS (SELECT TSQLTxt FROM master.dbo.DBA_UsersPermissions_Info WHERE insertdate>=CAST(GETDATE() AS date))
      BEGIN
            SET @bodyHTML =@bodyHTML+ N'<style>td{FONT-FAMILY: 宋体, Helvetica, sans-serif; FONT-SIZE: 12px;
            LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;
            BORDER-RIGHT: black 1px solid;}th{FONT-FAMILY: 宋体, Helvetica, sans-serif; FONT-SIZE: 12px;
            LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}</style>' 
            + N'<H1>[数据库用户及权限变更]</H1>' 
            + N'<table border="0" cellspacing="0" cellpadding="0" style="BORDER-BOTTOM: black 1px solid;
                  BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid;BORDER-TOP: black 1px solid">' 
            + N'<tr><th>数据库</th><th>对象类型</th><th>权限</th>'
            + N'<th>GRANT OR DENY</th>'
            + N'<th>授权者</th>'
            + N'<th>授权对象</th><th>执行脚本</th></tr>' 
            + CAST(( 
             SELECT td = ISNULL(dbName,'') ,  '' , 
                        td = ISNULL(class_desc,'') ,  '' , 
                        td = ISNULL(permission_name,'') ,  '' , 
                        td = ISNULL(GDR,''),  '' , 
                        td = ISNULL(granteeName,'') ,  '' , 
                        td = ISNULL(objectName,'') ,  '' , 
                        td = ISNULL(TSQLTxt,'')
            FROM  master.dbo.DBA_UsersPermissions_Info h
            WHERE insertdate>=CAST(GETDATE() AS date)
                     FOR 
                         XML PATH('tr') , 
                               TYPE 
                     ) AS NVARCHAR(MAX)) + N'</table>'; 
      END
      --发送邮件预警
      DECLARE @profile_name sysname
      SELECT @profile_name=name
      from msdb.dbo.sysmail_profile
      EXEC msdb.dbo.sp_send_dbmail @profile_name = @profile_name,
                    @recipients = 'Jack@dba.com',
                    @subject = N'登陆帐户及权限变更',
                    @body = @bodyHTML,
                    @body_format = 'html';
END

注意：在创建DBA_LoginsUsersPermissions_Audit 时需要将邮件接收人修改为相关责任人。

下面创建周期检验作业​：​

USE [msdb]
GO
EXEC msdb.dbo.sp_add_job
      @job_name = N'DBA_登陆账户数据库用户权限周期性审计'
      , @owner_login_name = N'Jack'
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep
      @job_name = N'DBA_登陆账户数据库用户权限周期性审计'
      , @step_name = N'登陆账户数据库用户权限周期性审计'
      , @step_id = 1
    , @subsystem = N'TSQL'
    , @command = N'EXEC DBA_LoginsUsersPermissions_Audit'
    , @database_name = N'master'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobschedule
      @job_name = N'DBA_登陆账户数据库用户权限周期性审计'
      , @name = N'登陆账户数据库用户权限周期性审计周期'
      , @enabled = 1
    , @freq_type = 8
    , @freq_interval = 2
    , @freq_subday_type = 1
    , @freq_subday_interval = 0
    , @freq_relative_interval = 0
    , @freq_recurrence_factor = 1
    , @active_start_date = 20200206
    , @active_end_date = 99991231
    , @active_start_time =80000
    , @active_end_time = 235959

这里的作业执行周期为一周执行一次（每周一早上8点执行）。

最后我们来做两个简单测试，以检验作业的有效性​：

--测试样例
USE master
GO
GRANT VIEW ANY DATABASE TO Jack
​
USE test
GRANT SELECT TO Jack

测试结果：

 

至此，我们既可以即时预警登陆账户、数据库用户及权限变更，也可以周期全面的检查登陆账户、数据库用户及权限。