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

语句"/>

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

一、mysql

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

二、postgres

创建trigger

能记录：

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

不能记录

• truncate

参考文章

-- 参考文章：
-- 官方文档：.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
$$declarequery text;command record;beginquery := current_query();if exists(select * from pg_event_trigger_ddl_commands()) thenfor command in select * from pg_event_trigger_ddl_commands()loopinsert into audit_ddl("user", txid, schema_name, ddl_type, object, ddl_query, query_time)VALUES (current_user,txid_current(),command.schema_name,commandmand_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
$$
declarequery text;command record;
beginquery := current_query();if exists(select * from pg_event_trigger_dropped_objects()) is not null thenfor command in select * from pg_event_trigger_dropped_objects()loopinsert 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

参考文章

-- 参考文章： table ischema.audit_ddl;
truncate ischema.audit_ddl;
select * from ischema.audit_ddl;drop trigger trg_ddl_listen ON DATABASE;
-- 以下均为触发器内容，一次执行即可。
CREATE TRIGGER trg_ddl_listenON DATABASEFOR 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);
endDECLARE
@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 INTSET @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命令。
-- 参考：=sql-server-ver16&redirectedfrom=MSDN
EXEC sys.sp_cdc_get_ddl_history  @capture_instance = 'ischema_schema_evo';

四、oracle

创建trigger

参考文章

能记录

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

不能记录

-- 参考文章：
-- 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_listenafter ddl on database
DECLAREPRAGMA AUTONOMOUS_TRANSACTION;n        NUMBER;stmt     clob := NULL;sql_text ora_name_list_t;
BEGINn := ora_sql_txt(sql_text);FOR i IN 1 .. n LOOPstmt := 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;