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;
/
,