We have noticed a change in the behavior of READ ANY TABLE auditing in 12.2.
The following initial setup is performed as SYS:
SQL> AUDIT 2 SELECT ANY TABLE; Audit succeeded. SQL> SQL> AUDIT 2 READ ANY TABLE; Audit succeeded. SQL> SQL> CREATE USER tst_table_owner IDENTIFIED BY tst_table_owner 2 ACCOUNT LOCK; User TST_TABLE_OWNER created. SQL> SQL> CREATE USER tst_view_owner IDENTIFIED BY tst_view_owner 2 ACCOUNT LOCK; User TST_VIEW_OWNER created. SQL> SQL> GRANT 2 READ ANY TABLE 3 TO tst_view_owner WITH ADMIN OPTION; Grant succeeded. SQL> SQL> GRANT 2 SELECT ANY TABLE 3 TO tst_view_owner WITH ADMIN OPTION; Grant succeeded. SQL> SQL> CREATE USER tst_login IDENTIFIED BY tst_login; User TST_LOGIN created. SQL> SQL> GRANT 2 CREATE SESSION 3 TO tst_login; Grant succeeded. SQL> SQL> CREATE TABLE tst_table_owner.tst_table 2 AS 3 SELECT 4 * 5 FROM 6 dual 7 WHERE 8 1 = 0; Table TST_TABLE_OWNER.TST_TABLE created. SQL> SQL> CREATE VIEW tst_view_owner.tst_view AS 2 SELECT 3 * 4 FROM 5 tst_table_owner.tst_table; View TST_VIEW_OWNER.TST_VIEW created. SQL> SQL> GRANT SELECT ON tst_view_owner.tst_view TO tst_login; Grant succeeded. SQL> SQL> GRANT SELECT ON dba_capture TO tst_login; Grant succeeded.
The following select statements are then run as newly created tst_login user:
SELECT
COUNT(*)
FROM
tst_view_owner.tst_view;
SELECT
COUNT(*)
FROM
dba_capture;
Querying the audit trail in 12.1 shows no entries, however in 12.2:
SQL> SELECT 2 db_user, 3 object_schema, 4 object_name, 5 sql_text, 6 priv_used 7 FROM 8 dba_common_audit_trail 9 WHERE 10 extended_timestamp > SYSDATE - 2 / 24 / 60 11 AND 12 statement_type = 'SELECT'; DB_USER OBJECT_SCHEMA OBJECT_NAME SQL_TEXT PRIV_USED TST_LOGIN TST_TABLE_OWNER TST_TABLE READ ANY TABLE TST_LOGIN TST_VIEW_OWNER TST_VIEW TST_LOGIN SYS XSTREAM$_SERVER TST_LOGIN SYS STREAMS$_APPLY_PROCESS TST_LOGIN SYS STREAMS$_APPLY_MILESTONE TST_LOGIN SYS XSTREAM$_SERVER TST_LOGIN SYS STREAMS$_APPLY_PROCESS TST_LOGIN SYS STREAMS$_APPLY_MILESTONE TST_LOGIN SYS XSTREAM$_SERVER TST_LOGIN SYS XSTREAM$_SERVER TST_LOGIN SYS XSTREAM$_SERVER TST_LOGIN SYS XSTREAM$_SERVER TST_LOGIN SYS XSTREAM$_SERVER TST_LOGIN SYS PROPS$ TST_LOGIN SYS DUAL TST_LOGIN SYS PROPS$ TST_LOGIN SYS DUAL TST_LOGIN SYS STREAMS$_CAPTURE_PROCESS TST_LOGIN SYS _DBA_CAPTURE TST_LOGIN SYSTEM LOGMNR_SESSION$ READ ANY TABLE TST_LOGIN SYS DBA_LOGMNR_SESSION TST_LOGIN SYS USER$ TST_LOGIN SYS _SXGG_DBA_CAPTURE TST_LOGIN SYS PROPS$ TST_LOGIN SYS DUAL TST_LOGIN SYS PROPS$ TST_LOGIN SYS DUAL TST_LOGIN SYS STREAMS$_CAPTURE_PROCESS TST_LOGIN SYS _DBA_CAPTURE TST_LOGIN SYSTEM LOGMNR_SESSION$ READ ANY TABLE TST_LOGIN SYS DBA_LOGMNR_SESSION TST_LOGIN SYS USER$ TST_LOGIN SYS _SXGG_DBA_CAPTURE TST_LOGIN SYS DBA_CAPTURE 34 rows selected.
It appears that READ ANY TABLE is audited when querying a view, if the privilege is required by the view owner to access the base table.
Oracle support have now confirmed that this is not the expected behavior, and have created Bug 26035911 : AUDIT RECORDS GENERATED EVEN WHEN THE SYSTEM PRIVILEGE IS NOT EXERCISED IN 12.2
