Oracle Database SQL Audit

In our daily work, some security-demanding databases need to increase audit operations and which users do what when.

1. Open the audit of the database

alter system set audit_sys_operations=TRUE scope=spfile;  //审计管理用户alter system set audit_trail=db,extended scope=spfile;  //将sql语句写入审计表中

2. Restart the database and view

重启数据库shutdown immediate;startup;show parameter audit;

3. Increase the audit strategy
We need to audit all operations of CAIWU users of the database

// 审计用户caiwu所有成功的操作audit all by caiwu by access whenever successful;

Or

//针对用户的审计(未执行成功的也审计)audit select table by caiwu by access;     //查表审计audit update table by caiwu by access;   //更新审计audit delete table by caiwu by access;    //删除审计audit insert table by caiwu by access;     //插入审计

//针对某表的更新、删除审计(错误也审计)AUDIT UPDATE,DELETE,INSERT ON T_TEST by access;  

//保护审计audit all on sys.aud$ by access；

4. Cancellation of audits

NOAUDIT UPDATE,DELETE,INSERT ON T_TEST by access;  

5. Query Audit Results

select OS_USERNAME,username,USERHOST,TERMINAL,TIMESTAMP,OWNER,obj_name,ACTION_NAME,sessionid,os_process,sql_text from dba_audit_trail;

6. Open the Audit table query to a user
grant select on dba_audit_trail to caiwu;

7. Clear Audit Records
DELETE FROM SYS.AUD$;

Oracle Database SQL Audit