SQL SERVER 审计(核)案例

╰半夏微凉° 2022-06-18 01:16 173阅读 0赞

---创建审计及审计规范
    USE master
    CREATE SERVER AUDIT my_first_audit TO FILE(FILEPATH='D:\SQL\TEST\',MAXSIZE=10MB)
    GO
    ---创建和配置可以用于审计的 SQL Server 审计对象
    USE Test
    CREATE DATABASE AUDIT SPECIFICATION my_aud_spec FOR SERVER AUDIT my_first_audit ADD (SELECT,UPDATE,INSERT,DELETE,EXEC,REFERENCES ON dbo.T_A BY Public) ---本次使用的数据库级别的审核操作(审核级别操作有;服务器级别、数据库级别、审核级别)
    go
    ----查看审计是否存在，并存在的路径
    USE master
    SELECT * FROM sys.server_file_audits
    USE TEST
    --审计规范是否存在及其状态
    SELECT * FROM sys.database_audit_specifications
    -- 启用审计和审计规范（即 是否允许或禁止审核收集此审核规范的记录。）
    USE master
    ALTER SERVER AUDIT my_first_audit WITH (STATE=ON)
    GO
    USE Test
    ALTER DATABASE AUDIT SPECIFICATION my_aud_spec WITH (STATE=ON)
    go
    ------------执行相关的SQL语句
    SELECT * FROM t_a 
    DELETE FROM t_a WHERE ID=3
    update t_a set name='审计' where id=2
    
    ---查看审计收集的记录数据
    SELECT * FROM fn_get_audit_file ('D:\SQL\TEST\*',NULL, NULL)
    go
    
    
    一个完整的审核案例
    
    ---创建审核对象
    USE [master]
    
    GO
    
    CREATE SERVER AUDIT [my_first_audit]
    TO FILE 
    ( FILEPATH = N'D:\test_audit'
     ,MAXSIZE = 0 MB  ---文件大小无限制
     ,MAX_ROLLOVER_FILES = 2147483647 --滚动文件大小无限制
     ,RESERVE_DISK_SPACE = OFF--关闭保留硬盘空间
    )
    WITH
    ( QUEUE_DELAY = 1000  ---队列延迟时间 MS为单位
     ,ON_FAILURE = CONTINUE ---审核日志故障时不关闭服务器（continue|shutdown）
    )
    ---审核目标：FILE\SECURITY LOG\APPLICATION LOG
    GO
    ---服务器审核规范
    USE [master]
    
    GO
    
    CREATE SERVER AUDIT SPECIFICATION [Server_AuditSpecification]
    FOR SERVER AUDIT [my_first_audit]
    ADD (BACKUP_RESTORE_GROUP), ---审核操作类型
    ADD (DATABASE_OBJECT_CHANGE_GROUP),
    ADD (TRACE_CHANGE_GROUP)
    
    ---具体审核操作类型：见联机丛书
    GO
    
    ---启用服务器审核规范
    ALTER SERVER AUDIT SPECIFICATION [Server_AuditSpecification]
    WITH (STATE = ON)
    ----关闭或禁用服务器审核规范
    ALTER SERVER AUDIT SPECIFICATION [Server_AuditSpecification]
    WITH (STATE = OFF)
    
    ---启用服务器审核对象
    ALTER SERVER AUDIT [my_first_audit] WITH (STATE = ON)
    
    ---关闭或禁用服务器审核对象
    ALTER SERVER AUDIT [my_first_audit] WITH (STATE = OFF)
    
    /*创建数据库审核规范*/
    USE [db_test]
    
    GO
    
    CREATE DATABASE AUDIT SPECIFICATION [Database_AuditSpecification]
    FOR SERVER AUDIT [my_first_audit]
    ADD (SELECT ON OBJECT::[dbo].[a] BY [audit])---制定审核操作类型、对象类（object、schema、database）、对象架构（DBO)）对象名称（a）、 主体名称（登录名或用户名）
    ---具体审核操作类型：见联机丛书
    GO
    ---启用数据库审核规范
    ALTER DATABASE AUDIT SPECIFICATION [Database_AuditSpecification]
    WITH (STATE = ON)
    ---关闭或禁用数据库审核规范
    ALTER DATABASE AUDIT SPECIFICATION [Database_AuditSpecification]
    WITH (STATE = OFF)