oracle11g 开启审计

查看是否开启审计

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;