Simple use of Oracle audit statements (1)

Audit is used to monitor the database operations performed by users. Oracle stores Audit trail results in OS files or databases.

1. To use audit, you must activate audit.

SQL> conn/as sysdba is connected. SQL> show parameter audit_sys_operations; name type value ------------------------------------------- ------------------------------ audit_sys_operations boolean false SQL> show parameter audit_trail; name type value ------------------------------------ audit_trail string none SQL> alter system set audit_sys_operations = TRUE scope = spfile; the system has changed. SQL> alter system set audit_trail = db scope = spfile; the system has been changed. SQL> startup force ORACLE routine has been started. Total System Global Area 289406976 bytes Fixed Size 1248576 bytes Variable Size 79692480 bytes Database Buffers 201326592 bytes Redo Buffers 7139328 bytes Database load is complete. The database has been opened. SQL> show parameter audit; NAME TYPE VALUE =-------------------------------------- audit_file_dest string D: \ ORACLE \ PRODUCT \ 10.2.0 \ ADMIN \ ORCL \ ADUMP audit_sys_operations boolean TRUE audit_trail string DB

2. Audit The dept table

SQL> audit all on dept; audit successful. SQL> conn mzl/mzl is connected. SQL> select * from dept; deptno dname loc ---------- ------------ --------------- 10 accounting new york 20 research dallas 30 sales chicago 40 OPERATIONS PanJin 80 mengzhaoliang beijing SQL> insert into dept 2 values (90, 'test', 'panjin'); 1 row has been created. SQL> commit; submitted completely. SQL> conn scott/mzl is connected. SQL> insert into dept 2 values (60, 'june', 'shanghai'); 1 row has been created. SQL> commit; submitted completely.

3. View audit results

SQL> conn/as sysdba is connected. SQL> select count (*) from dba_audit_trail; COUNT (*) ---------- 2 SQL> select username, ses_actions, obj_name, 2 to_char (timestamp, 'yyyy-MM-DD HH24: MI: SS ') 3 from dba_audit_trail; USERNAME SES_ACTIONS -------------------------------- ----------------- OBJ_NAME defaults TO_CHAR (TIMESTAMP,' ------------------- MZL ------ S--S ------ DEPT 10:28:08 SCOTT ------ B --------- DEPT 10:29:04 USERNAME SES_ACTIONS =--------------------- OBJ_NAME =to_char (TIMESTAMP, '------------------- SQL> conn mzl/mzl connected.