mysql触发器自治事务_使用触发器和自治事务做了个DDL审计

港控/mmm° 2023-01-11 13:12 143阅读 0赞

目的：禁止对ZFPT用户的表执行ALTER TABLE的操作，并审计操作行为。--创建ZFPTADM用户用于管理触发器CREATE USER ZFPTADM IDENTI

目的：禁止对ZFPT用户的表执行ALTER TABLE的操作，并审计操作行为。

\--创建ZFPTADM用户用于管理触发器

CREATE USER ZFPTADM IDENTIFIED BY ZFPTADMDEFAULT TABLESPACE ZFPT\_CDATA TEMPORARY TABLESPACE ZFPT\_TEMP;

\--授予ZFPTADM用户创建会话、触发器、表、查询字典视图以及不限制空间的权限

GRANT CREATE SESSION,CREATE TRIGGER,CREATETABLE,UNLIMITED TABLESPACE,SELECT ANY DICTIONARY TO ZFPTADM;

\--创建审计日志表

DROP TABLE ZFPTADM.ZFPT\_DDL\_LOGS PURGE;

CREATE TABLE ZFPTADM.ZFPT\_DDL\_LOGS (

TIMESTAMP VARCHAR2(32),

USERNAME VARCHAR2(32),

OSUSER VARCHAR2(32),

LOGONTIME VARCHAR2(32),

MACHINE VARCHAR2(64),

PROGRAM VARCHAR2(64),

ERRMSG VARCHAR2(4000));

COMMENT ON COLUMNZFPTADM.ZFPT\_DDL\_LOGS.TIMESTAMP IS '操作时间';

COMMENT ON COLUMNZFPTADM.ZFPT\_DDL\_LOGS.USERNAME IS '使用的DB用户';

COMMENT ON COLUMN ZFPTADM.ZFPT\_DDL\_LOGS.OSUSERIS '使用的OS用户';

COMMENT ON COLUMNZFPTADM.ZFPT\_DDL\_LOGS.LOGONTIME IS '回话登陆时间';

COMMENT ON COLUMNZFPTADM.ZFPT\_DDL\_LOGS.MACHINE IS '客户端';

COMMENT ON COLUMNZFPTADM.ZFPT\_DDL\_LOGS.PROGRAM IS '使用的工具';

COMMENT ON COLUMNZFPTADM.ZFPT\_DDL\_LOGS.ERRMSG IS '组装信息';

\--创建触发器

CREATE OR REPLACE TRIGGERZFPTADM.ZFPTDDLMONITOR

BEFORE ALTER ON ZFPT.SCHEMA

DECLARE

PRAGMA AUTONOMOUS\_TRANSACTION;

V\_TIMESTAMP VARCHAR2(32);

V\_USERNAME VARCHAR2(32);

V\_OSUSER VARCHAR2(32);

V\_LOGONTIME VARCHAR2(32);

V\_MACHINE VARCHAR2(64);

V\_PROGRAM VARCHAR2(64);

V\_ERRMSG VARCHAR2(4000);

BEGIN

\--判断所操作的对象类型是否为TABLE类型，只有TABLE类型才需要审计和禁止，，其他类型以来方行

IF(ORA\_DICT\_OBJ\_TYPE='TABLE')

THEN

SELECTUSERNAME,MACHINE,OSUSER,PROGRAM,TO\_CHAR(LOGON\_TIME,'YYYY-MM-DD HH24:MI:SS'),TO\_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS')

INTOV\_USERNAME,V\_MACHINE,V\_OSUSER,V\_PROGRAM,V\_LOGONTIME ,V\_TIMESTAMP

FROMV$SESSION WHERE SID=(SELECT USERENV('SID') FROM DUAL);

V\_ERRMSG:= '来自客户端【'||V\_MACHINE||'】的OS用户【'||V\_OSUSER||'】使用【'||V\_PROGRAM||'】程序以DB用户【'||V\_USERNAME||'】连接，对【'||ORA\_DICT\_OBJ\_OWNER|| '.' || ORA\_DICT\_OBJ\_NAME || ' 】对象执行了【'||ORA\_SYSEVENT||'】操作！';

INSERTINTO ZFPTADM.ZFPT\_DDL\_LOGS(TIMESTAMP,USERNAME,OSUSER,LOGONTIME,MACHINE,PROGRAM,ERRMSG)

VALUES(V\_TIMESTAMP,V\_USERNAME,V\_OSUSER,V\_LOGONTIME,V\_MACHINE,V\_PROGRAM,V\_ERRMSG);

COMMIT;

RAISE\_APPLICATION\_ERROR(-20001,'注意：客户端【'||V\_MACHINE||'】，OS用户为【'||V\_OSUSER||'】，DB用户为【'||V\_USERNAME||'】，你无权对【'||ORA\_DICT\_OBJ\_OWNER || '.' || ORA\_DICT\_OBJ\_NAME || ' 】对象执行【'||ORA\_SYSEVENT||'】操作，已记录你的违规操作行为！');

ENDIF;

END ;

\--启用触发器就会继续审计，不能执行ALTER操作

ALTER TRIGGER ZFPTADM.ZFPTDDLMONITORENABLE;

\--禁用触发器就会停止审计，可以执行ALERT操作

ALTER TRIGGER ZFPTADM.ZFPTDDLMONITORDISABLE;

![logo.gif][]

![f68f2add0b68e4f9810432fce46917b7.png][]

本文原创发布php中文网，转载请注明出处，感谢您的尊重！

[logo.gif]: https://www.php.cn/linuxfile/logo.gif
[f68f2add0b68e4f9810432fce46917b7.png]: /images/20221119/6c943b224a6040c2baae95ac91fa5d3b.png