Oracle uses system-level triggers to audit DDL statements of important accounts

To audit DDL operations in the database, you can use DDL triggers. This section describes an example to record all DDL operations in the database. This example can be

To audit DDL operations in the database, you can use DDL triggers. This section describes an example to record all DDL operations in the database. This example can be

To audit DDL operations in the database, you can use DDL triggers. This section describes an example to record all DDL operations in the database. This example can be used in Oracle 8i or later versions.

Step 1: Create a tablespace and related log tables:

Create tablespace statlog datafile '/oradata/statlog. dbf' size 200 m;

Create table stat $ log_ddl

(

Ddl_date date,

User_name varchar2 (30 ),

Ip_addr VARCHAR2 (30 ),

Obj_name VARCHAR2 (50 ),

Ddl_type VARCHAR2 (30 ),

Object_type VARCHAR2 (18 ),

Owner VARCHAR2 (30 ),

SQL _TEXT VARCHAR2 (1000)

) Tablespace statlog;

Step 2: Create Database-level DDL triggers and record all DDL operations

Create or replace trigger DDL_audit AFTER CREATE OR ALTER OR DROP OR TRUNCATE OR

GRANT OR REVOKE OR RENAME

On DATABASE

Declare

Ipaddr varchar2 (20 );

STEXT VARCHAR2 (1000 );

BEGIN

Begin

Select sys_context ('userenv', 'IP _ address') into ipaddr FROM dual;

Exception when others then

Ipaddr: = '-';

End;

Begin

Select SQL _TEXT into stext from v $ open_cursor where upper (SQL _text) LIKE 'alter % ';

Exception when others then

STEXT: = '-';

End;

Insert into sys. stat $ log_DDL values

(Sysdate,

User,

Nvl (ipaddr ,'-'),

NVL (ora_dict_obj_name ,'-'),

NVL (ORA_SYSEVENT ,'-'),

NVL (ora_dict_obj_type ,'-'),

NVL (ora_dict_obj_owner ,'-'),

STEXT

);

Exception when others then

Null;

End;

/

,