DDL audits are performed using triggers and autonomous transactions.

Objective: To prohibit ALTERTABLE operations on a ZFPT user's table and audit the operations. -- Create a ZFPTADM user to manage the trigger CREATEUSERZFPTADMIDENTI

Objective: To prohibit the alter table operation on a ZFPT user's TABLE and audit the operation. -- CREATE a zfptadm user to manage the trigger CREATE USER ZFPTADM IDENTI

Objective: To prohibit the alter table operation on a ZFPT user's TABLE and audit the operation.

-- Create a ZFPTADM user to manage the trigger

Create user zfptadm identified by zfptadmdefault tablespace ZFPT_CDATA temporary tablespace ZFPT_TEMP;

-- Grant ZFPTADM users the permission to create sessions, triggers, tables, query dictionary views, and unlimited spaces.

Grant create session, create trigger, CREATETABLE, unlimited tablespace, select any dictionary to zfptadm;

-- Create an audit log table

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 'Operation time ';

Comment on columnzfptadm. ZFPT_DDL_LOGS.USERNAME IS 'db user used ';

Comment on column zfptadm. ZFPT_DDL_LOGS.OSUSERIS 'OS user used ';

Comment on columnzfptadm. ZFPT_DDL_LOGS.LOGONTIME IS 'session login time ';

Comment on columnzfptadm. ZFPT_DDL_LOGS.MACHINE IS 'client ';

Comment on columnzfptadm. ZFPT_DDL_LOGS.PROGRAM IS 'tool used ';

Comment on columnzfptadm. ZFPT_DDL_LOGS.ERRMSG IS 'assembly information ';

-- Create a trigger

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

-- Determines whether the operated object type is of the TABLE type. Only the TABLE type needs to be audited and disabled.

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 ')

Using v_username, V_MACHINE, V_OSUSER, V_PROGRAM, V_LOGONTIME, V_TIMESTAMP

FROMV $ session where sid = (select userenv ('sid ') from dual );

V_ERRMSG: = 'OS user from the client [' | V_MACHINE | '] [' | V_OSUSER | '] use the [' | V_PROGRAM | '] program to DB user 【 '| V_USERNAME |'] connection, for '| ORA_DICT_OBJ_OWNER | '. '| ORA_DICT_OBJ_NAME |'] The object performs the ['| ORA_SYSEVENT |'] operation! ';

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, 'note: Client ['| V_MACHINE |'], OS User: '| V_OSUSER | '], the DB user is '| V_USERNAME |'. You do not have permission to access '| ORA_DICT_OBJ_OWNER | '. '| ORA_DICT_OBJ_NAME |'] The object executes the ['| ORA_SYSEVENT |'] operation. Your illegal operations have been recorded! ');

ENDIF;

END;

/

-- If the trigger is enabled, the audit will continue and the ALTER operation cannot be performed.

Alter trigger zfptadm. ZFPTDDLMONITORENABLE;

-- If the trigger is disabled, the audit will be stopped. You can perform the ALERT operation.

Alter trigger zfptadm. ZFPTDDLMONITORDISABLE;