I'm running Oracle 18c, but using standard auditing (not Unified Auditing). The other day, I ran a bunch of statements (that I can't recall, of course) to enable all sorts of auditing. After viewing the contents of DBA_AUDIT_TRAIL, I realized we were auditing way too much and began issuing NOAUDIT commands to pare it down.
This has largely worked, however I'm stumped when it comes to auditing SELECT statements. I have run the following as the SYS user:
NOAUDIT SELECT TABLE;
NOAUDIT SELECT ANY TABLE;
NOAUDIT ALL;
NOAUDIT ALL STATEMENTS;
NOAUDIT ALL PRIVILEGES;
But I am still seeing SELECT statements in DBA_AUDIT_TRAIL. I'm stumped and can't figure out how to turn it off. I've checked the following views to see what's being audited:
SQL> conn sys@sid as sysdba
Enter password:
Connected.
SQL> SELECT * FROM dba_priv_audit_opts ORDER BY 3;
no rows selected
SQL> SELECT * FROM dba_stmt_audit_opts ORDER BY 3;
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE
--------------- --------------- ---------------------------------------- ---------- ----------
ALTER SEQUENCE BY ACCESS BY ACCESS
COMMENT TABLE BY ACCESS BY ACCESS
GRANT DIRECTORY BY ACCESS BY ACCESS
GRANT PROCEDURE BY ACCESS BY ACCESS
GRANT SEQUENCE BY ACCESS BY ACCESS
GRANT TABLE BY ACCESS BY ACCESS
GRANT TYPE BY ACCESS BY ACCESS
7 rows selected.
SQL> SELECT count(*)
2 FROM dba_obj_audit_opts
3 WHERE owner IN ('APP')
4 AND sel != '-/-';
COUNT(*)
----------
0
Where else can I look? I see that these SELECT statements are in DBA_AUDIT_TRAIL with PRIV_USED = 'SELECT ANY TABLE', but I think I've disabled it. Any ideas?
At this point I basically want to get back to ground zero and then put more thought into what I'm going to audit rather than throwing the entire pizza against the wall. I've tried setting AUDIT_TRAIL to none, restarting, then setting it back to DB, but this hasn't fixed anything.
