【database】审计/记录mysql、postgres、sqlserver、oracle数据库的ddl事件和语句

逃离我推掉我的手 2024-02-19 10:04 17阅读 0赞

### 一、mysql ###

很遗憾，mysql不支持database级别的trigger。无法使用触发器记录ddl。  
如果需要获取dll记录可以使用debezium/cdc在mysql的binlog中解析。

### 二、postgres ###

#### 创建trigger ####

能记录：

*  create table
 *  drop table
 *  alter table add/drop/rename/修改类型

不能记录

*  truncate

[参考文章][Link 1]

-- 参考文章：https://blog.csdn.net/weixin_42212488/article/details/127873487
    -- 官方文档：https://www.postgresql.org/docs/13/functions-event-triggers.html#PG-EVENT-TRIGGER-TABLE-REWRITE-FUNCTIONS
    drop table ddl_log;
    create table ddl_log(
          id serial8,
          "user" text,
          txid bigint,
          schema_name text,
          ddl_type text,
          object varchar,
          ddl_query text,
          query_time timestamp
      );
     
    
    truncate audit_ddl;
    select * from audit_ddl;
    
    drop function ddl_listen_common();
    create or replace function ddl_listen_common()
    returns event_trigger
    language plpgsql
    as
    $$
      declare
          query text;
          command record;
      begin
          query := current_query();
          if exists(select * from pg_event_trigger_ddl_commands()) then
              for command in select * from pg_event_trigger_ddl_commands()
              loop
              insert into audit_ddl("user", txid, schema_name, ddl_type, object, ddl_query, query_time)
              VALUES (current_user,txid_current(),command.schema_name,command.command_tag,command.object_identity,query, statement_timestamp());
              end loop ;
          end if;
      end;
    $$
    security definer;
    
    drop event trigger trg_ddl_listen_common;
    create event trigger trg_ddl_listen_common on ddl_command_end execute procedure ddl_listen_common();
    
    drop function ddl_listen_drop;
    create or replace function ddl_listen_drop()
    returns event_trigger
    language plpgsql
    as
    $$
    declare
      query text;
      command record;
    begin
      query := current_query();
      if exists(select * from pg_event_trigger_dropped_objects()) is not null then
          for command in select * from pg_event_trigger_dropped_objects()
          loop
          insert into audit_ddl("user", txid, schema_name, ddl_type, object, ddl_query, query_time)
          VALUES (current_user,txid_current(),command.schema_name,'DROP '||command.object_type,command.object_identity,query, statement_timestamp());
          end loop;
      end if;
    end;
    $$
    security definer;
    
    drop event trigger trg_ddl_listen_drop;
    create event trigger trg_ddl_listen_drop on sql_drop  execute procedure ddl_listen_drop();

#### 测试 ####

drop table if exists ischema.schema_evo;
    create table ischema.schema_evo(
    id bigint primary key
    ,name varchar(30)
    ,address varchar(30)
    );
    
    insert into ischema.schema_evo values(1,'zhangsna','beijing');
    
    alter table ischema.schema_evo add "gender" varchar(10);
    insert into ischema.schema_evo values(2,'lisi','male','shanghai');
    alter table ischema.schema_evo rename column "name" to name_abbr;
    insert into ischema.schema_evo values(3,'lvzhou','male','shanghai');
    alter table ischema.schema_evo alter column "name_abbr" type varchar(50);
    insert into ischema.schema_evo values(4,'wangwu','female','chongqing');
    alter table ischema.schema_evo drop column address;
    insert into ischema.schema_evo values(5,'zhaoliu','male');
    -- 不支持truncate记录。
    truncate ischema.schema_evo;
    
    select * from ischema.schema_evo where 1=1 order by id desc limit 10;

### 三、sqlserver ###

#### 创建trigger ####

能记录

*  create table
 *  drop table
 *  alter table add/drop/修改类型 column

不能记录

*  alter table rename column
 *  truncate

[参考文章][Link 2]

-- 参考文章：https://blog.csdn.net/qq_40205468/article/details/88845215
    
    drop table ischema.audit_ddl;
    truncate ischema.audit_ddl;
    select * from ischema.audit_ddl;
    
    drop trigger trg_ddl_listen ON DATABASE;
    -- 以下均为触发器内容，一次执行即可。
    CREATE TRIGGER trg_ddl_listen
        ON DATABASE
        FOR CREATE_TABLE, DROP_TABLE, ALTER_TABLE
    AS
    /* 本触发器只针对触发器的增删改,进行记录触发器的相关信息 */
    IF OBJECT_ID(N'ischema.audit_ddl', N'U') is NULL
    begin
    CREATE TABLE ischema.audit_ddl
        (
          rowid INT IDENTITY ,
          EventType VARCHAR(20) ,
          PostTime DATETIME ,
          SPID INT ,
          ServerName VARCHAR(255) ,
          LoginName VARCHAR(255) ,
          DatabaseName VARCHAR(255) ,
    	  UserName VARCHAR(255),
          SchemaName VARCHAR(20) ,
          ObjectName VARCHAR(255) ,
          ObjectType VARCHAR(20) ,
          CommandText NVARCHAR(MAX) ,
          remark NVARCHAR(MAX) ,
          commandtext_check INT
        );
    end
    
    DECLARE
    @EeventType VARCHAR(20),
    		@PostTime DATETIME,
    		@SPID INT,
    		@ServerName VARCHAR(255),
    		@LoginName VARCHAR(255),
    		@DatabaseName VARCHAR(255),
    		@UserName VARCHAR(255),
    		@SchemaName VARCHAR(255),
    		@ObjectName VARCHAR(255),
    		@ObjectType VARCHAR(20),
    		@CommandText NVARCHAR(MAX),
    		@Remarks NVARCHAR(MAX),
    		@Commandtest_check INT
     
    SET @EeventType=EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','VARCHAR(20)')
    SET @PostTime=EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','DATETIME')
    SET @SPID=EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]','INT')
    SET @ServerName=EVENTDATA().value('(/EVENT_INSTANCE/ServerName)[1]','VARCHAR(255)')
    SET @LoginName=EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','VARCHAR(255)')
    SET @DatabaseName=EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','VARCHAR(255)')
    SET @UserName=EVENTDATA().value('(EVENT_INSTANCE/UserName)[1]','VARCHAR(255)')
    SET @SchemaName=EVENTDATA().value('(EVENT_INSTANCE/SchemaName)[1]','VARCHAR(255)')
    SET @ObjectName=EVENTDATA().value('(EVENT_INSTANCE/ObjectName)[1]','VARCHAR(255)')
    SET @ObjectType=EVENTDATA().value('(EVENT_INSTANCE/ObjectType)[1]','VARCHAR(255)')
    SET @CommandText=EVENTDATA().value('(EVENT_INSTANCE/TSQLCommand/CommandText)[1]','NVARCHAR(MAX)')
     
    INSERT  INTO ischema.audit_ddl
            ( EventType ,
              PostTime ,
              SPID ,
              ServerName ,
              LoginName ,
              DatabaseName ,
              UserName ,
              SchemaName ,
              ObjectName ,
              ObjectType ,
              CommandText ,
              remark ,
              commandtext_check
            )
    SELECT @EeventType,
           @PostTime,
           @SPID,
           @ServerName,
           @LoginName,
           @DatabaseName,
           @UserName,
           @SchemaName,
           @ObjectName,
           @ObjectType,
           @CommandText,
           '',
           0;

#### 测试 ####

drop table ischema.schema_evo;
    create table ischema.schema_evo(
    id bigint primary key
    ,name varchar(30)
    ,address varchar(30)
    );
    -- 启动表的CDC跟踪。
    EXEC sys.sp_cdc_enable_table @source_schema = 'ischema', @source_name = 'schema_evo', @role_name = NULL, @supports_net_changes = 0;
    
    insert into ischema.schema_evo values(1,'test','xinjiang');
    
    alter table ischema.schema_evo add gender varchar(10);
    insert into ischema.schema_evo values(2,'test','beijing','female');
    
    alter table ischema.schema_evo alter column "name" varchar(50);
    insert into ischema.schema_evo values(3,'test','hangzhou','male');
    
    -- 已经启用cdc的表无法修改列名称：Cannot alter column 'name' because it is 'REPLICATED'.
    exec sp_rename 'ischema.schema_evo.name','name_abbr';
    
    alter table ischema.schema_evo drop column address;
    insert into ischema.schema_evo values(4,'test','female');
    
    -- 已经启用cdc的表无法truncate：Cannot truncate table 'ischema.schema_evo' because it is published for replication or enabled for Change Data Capture.
    TRUNCATE table ischema.schema_evo;
    
    select * from ischema.schema_evo;
    
    -- 获取ddl, 可以通过 "ddl_command"列 获取到具体的ddl命令。
    -- 参考：https://learn.microsoft.com/zh-cn/sql/relational-databases/system-stored-procedures/sys-sp-cdc-get-ddl-history-transact-sql?view=sql-server-ver16&redirectedfrom=MSDN
    EXEC sys.sp_cdc_get_ddl_history  @capture_instance = 'ischema_schema_evo';

### 四、oracle ###

#### 创建trigger ####

[参考文章][Link 3]

能记录

*  create table
 *  drop table
 *  alter table add/drop/rename/修改类型 column
 *  truncate

不能记录

-- 参考文章：https://blog.csdn.net/u014257861/article/details/80182067
    -- DBA账号授权。
    grant administer DATABASE TRIGGER to cdcuser;
    SELECT * FROM USER_SYS_PRIVS;  -- 要有：ADMINISTER DATABASE TRIGGER 权限 
    
    DROP TABLE audit_ddl;
    create table audit_ddl (
    opertime timestamp PRIMARY KEY,
    ip varchar2(20),
    hostname varchar2(30),
    operation varchar2(30),
    object_type varchar2(30),
    object_name varchar2(30),
    sql_stmt clob,
    db_schema varchar2(30)
    );
    
    
    TRUNCATE TABLE audit_ddl;
    SELECT * FROM audit_ddl;
    
    DROP TRIGGER trg_ddl_listen;
    
    create or replace trigger trg_ddl_listen
      after ddl on database
    DECLARE
      PRAGMA AUTONOMOUS_TRANSACTION;
      n        NUMBER;
      stmt     clob := NULL;
      sql_text ora_name_list_t;
    BEGIN
      n := ora_sql_txt(sql_text);
      FOR i IN 1 .. n LOOP
        stmt := stmt || sql_text(i);
      END LOOP;
      INSERT INTO audit_ddl
        (opertime, ip, hostname, operation, object_type, object_name, sql_stmt,db_schema)
      VALUES
        (systimestamp,
         sys_context('userenv', 'ip_address'),
         sys_context('userenv', 'HOST'),
         ora_sysevent,
         ora_dict_obj_type,
         ora_dict_obj_name,
         stmt,
         user
       );
      COMMIT;
    END;

#### 测试 ####

drop table DBO.SCHEMA_EVO;
    create table DBO.SCHEMA_EVO(
    id numeric(10,0) primary key
    ,name varchar(30)
    ,address varchar(30)
    );
    INSERT INTO DBO.SCHEMA_EVO VALUES(1,'TEST','BEIJING');
    
    ALTER TABLE DBO.SCHEMA_EVO ADD gender varchar(10);
    INSERT INTO DBO.SCHEMA_EVO VALUES(2,'TEST','SHANGHAI','FEMALE');
    ALTER TABLE DBO.SCHEMA_EVO MODIFY name varchar(50) default 'abbr';
    INSERT INTO DBO.SCHEMA_EVO VALUES(3,'TEST','SHANGHAI','MALE');
    ALTER TABLE DBO.SCHEMA_EVO RENAME COLUMN name TO name_abbr;
    INSERT INTO DBO.SCHEMA_EVO VALUES(4,'TEST','SUZHOU','FEMALE');
    ALTER TABLE DBO.SCHEMA_EVO DROP COLUMN address;
    INSERT INTO DBO.SCHEMA_EVO VALUES(5,'TEST','FEMALE');
    -- 支持记录TRUNCATE操作.
    TRUNCATE TABLE DBO.SCHEMA_EVO;
    
    SELECT * FROM DBO.SCHEMA_EVO;

[Link 1]: https://blog.csdn.net/weixin_42212488/article/details/127873487
[Link 2]: https://blog.csdn.net/qq_40205468/article/details/88845215
[Link 3]: https://blog.csdn.net/u014257861/article/details/80182067