Below is sample code for a DDL Trigger that writes to the alert log when a specific condition is encountered.
SQL> show user USER is "SYS" SQL> alter session set container=orclpdb1; Session altered. SQL> @Drop_Table_not_permitted.sql SQL> create or replace trigger DROP_TABLE_NOT_PERMITTED 2 after ddl 3 on database 4 begin 5 if (ora_sysevent='DROP' and ora_dict_obj_type = 'TABLE') 6 then 7 --- code for INSERT into an audit log table 8 --- INSERT INTO .... 9 --- 10 --- code below is to write to the alert log file 11 --- dbms_log is undocumented but available since 12c or 11.2.0.4 12 --- see https://jonathanlewis.wordpress.com/2018/10/12/dbms_log/ 13 --- dbms_log.ksdwrt is the same as dbms_system.ksdwrt 14 --- see https://hemantoracledba.blogspot.com/2008/11/database-event-trigger-and-sysoper.html 15 dbms_log.ksdwrt(2,' Warning : ' || sys_context('USERENV','SESSION_USER') 16 || ' tried to drop ' || ora_dict_obj_name 17 || ' in SID ' || sys_context('USERENV','SID') 18 || ', AUDSID ' || sys_context('USERENV','SESSIONID') 19 || ' and service name ' || sys_context('USERENV','SERVICE_NAME')); 20 raise_application_error(-20001,'You are not permitted to Drop Tables'); 21 end if; 22 end; 23 / Trigger created. SQL>
When I connect to the PDB and attempt to execute a DROP TABLE ...
SQL> connect hemant/hemant@orclpdb1 Connected. SQL> desc dummy_tab Name Null? Type -------------------------------------------------------------- -------- ------------------------ ID_COL NUMBER SQL> drop table dummy_tab; drop table dummy_tab * ERROR at line 1: ORA-04088: error during execution of trigger 'SYS.DROP_TABLE_NOT_PERMITTED' ORA-00604: error occurred at recursive SQL level 1 ORA-20001: You are not permitted to Drop Tables ORA-06512: at line 17 SQL>
The alert log shows this entry :
2021-05-27T21:38:11.859746+08:00 ORCLPDB1(3): Warning : HEMANT tried to drop DUMMY_TAB in SID 272, AUDSID 1061583 and service name orclpdb1
You could use more information from SYS_CONTEXT ( I have retrieved only some pieces of information in my PL/SQL Trigger code above) and populate an Audit Log table with more information.
Comment : The "2" as the first parameter to dbms_log.ksdwrt references the instance's alert log. "1" would reference the session's trace file. "3" would reference both files.
No comments:
Post a Comment