查看是否开启审计
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string E:\ORACLE\DATABASE\ADMIN\ORCL\ADUMP
audit_sys_operations boolean FALSE
audit_trail
开启审计
> alter system set audit_sys_operations=TRUE scope=spfile;
> alter system set audit_trail=db,extended scope=spfile;
开启审计后需要重启服务
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string E:\ORACLE\DATABASE\ADMIN\ORCL\ADUMP
audit_sys_operations boolean TRUE
audit_trail string DB, EXTENDED
审计某表
audit insert, update,delete on 用户.表名 by access whenever successful;
查看用操作行为
SQL> select OS_USERNAME,username,USERHOST,TERMINAL,TIMESTAMP,OWNER,obj_name,ACTION_NAME,sessionid,os_process,sql_text from dba_audit_trail where sql_text is not null order by timestamp desc;
OS_USERNAME USERNAME USERHOST TERMINAL TIMESTAMP OWNER OBJ_NAME ACTION_NAME SESSIONID OS_PROCESS SQL_TEXT
-------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------- ------------------------------ -------------------------------------------------------------------------------- ---------------------------- ---------- ---------------- --------------------------------------------------------------------------------
root UAC_TEST jx-web unknown 2020/9/27 1 UAC_TEST SSO_ADMIN_USER UPDATE 11696312 149848:57636 update SSO_ADMIN_USER
SET LAST_TIME = :1
where ID = :2
root UAC_TEST jx-web unknown 2020/9/27 1 UAC_TEST SSO_ADMIN_USER UPDATE 11696288 149848:109348 update SSO_ADMIN_USER
SET LAST_TIME = :1
where ID = :2
lenovo UAC_TEST WORKGROUP\GYU GYU 2020/9/27 1 SYS AUD$ DELETE 11693202 149848:115300 delete sys.aud$ where NTIMESTAMP# < trunc(sysdate, 'HH') - 2
lenovo UAC_TEST WORKGROUP\GYU GYU 2020/9/27 1 UAC_TEST SSO_ADMIN_USER DELETE 11693202 149848:115300 delete from sso_admin_user where id='111'
lenovo UAC_TEST WORKGROUP\GYU GYU 2020/9/27 1 UAC_TEST SSO_ADMIN_USER INSERT 11693202 149848:115300 insert into sso_admin_user (ID, USER_NAME, SEX, ACCOUNT, PASSWORD, PHONE, REMARK
values ('111', '伍睿', '1', 'wurui', 'f505dc6cfa79ef2f1dddcc9381c25c08', '17681186
lenovo UAC_TEST WORKGROUP\GYU GYU 2020/9/27 1 UAC_TEST SSO_USER_DATA_PERMISSION DELETE 11693202 149848:115300 DELETE FROM SSO_USER_DATA_PERMISSION WHERE ID='2'
lenovo UAC_TEST WORKGROUP\GYU GYU 2020/9/27 1 SYS AUD$ DELETE 11693202 149848:115300 delete sys.aud$ where NTIMESTAMP# < trunc(sysdate, 'HH') - 90
lenovo UAC_TEST WORKGROUP\GYU GYU 2020/9/27 1 UAC_TEST SSO_USER_DATA_PERMISSION INSERT 11693202 149848:115300 INSERT INTO SSO_USER_DATA_PERMISSION(ID,USER_ID,SYSTEM_ID,ORGAN_ID,DATA_ID) VALU
lenovo UAC_TEST WORKGROUP\GYU GYU 2020/9/27 1 UAC_TEST SSO_USER_DATA_PERMISSION DELETE 11693202 149848:115300 DELETE FROM SSO_USER_DATA_PERMISSION WHERE ID='2'
NT AUTHORITY\SYSTEM DBSNMP WORKGROUP\WIN-E1H0VHLSHN9 WIN-E1H0VHLSHN9 2020/9/27 1 ALL SET ROLE 11693237 149848:119380 set role all
NT AUTHORITY\SYSTEM DBSNMP WORKGROUP\WIN-E1H0VHLSHN9 WIN-E1H0VHLSHN9 2020/9/27 1 ALL SET ROLE 11692933 148744:69348 set role all
清空sys.aud$
表数据(可以写个定时任务定时清理不然数据很大保留多少天数据看自己需求)
SQL> delete sys.aud$ where NTIMESTAMP# < trunc(sysdate, 'HH') - 2;