If a user was granted the DBA role in 19c, they could view rows in ALL_TRIGGERS owned by SYS. In 23c, the DBA role is slightly different, but it can no longer see rows in ALL_TRIGGERS owned by SYS. But tables owned by SYS in ALL_TABLES are still visible by users with the DBA role. What is the specific system privilege, role within the DBA role, or other system security configuration in 23c that is restricting rows in ALL_TRIGGERS? What is the best and least privilege way to restore the 19c behavior? Here are simple steps and output to demonstrate this behavior:
-- both 19c and 23c
grant create session, dba to fred identified by fred;
----------------------------------
-- connecting as FRED on 19c
----------------------------------
show user
USER is "FRED"
select owner,trigger_name from all_triggers where owner='SYS';
OWNER TRIGGER_NAME
---------- ------------------------------
SYS BGTRIGGER
SYS SYSLSBY_EDS_DDL_TRIG
SYS LOGMNRGGC_TRIGGER
SYS XDB_PI_TRIG
SYS OJDS$ROLE_TRIGGER$
SYS DB_PIN
SYS CRLOAD_NLS
SYS HERMES_TRACE_LOGON_TRIGGER
SYS HERMES_TRACE_LOGOFF_TRIGGER
SYS TR_CLARITYDROPTABLESECURITY
SYS AVOID_ORA22818
11 rows selected.
select owner,table_name from all_tables where owner='SYS' fetch first 10 rows only;
OWNER TABLE_NAME
---------- -----------------------------------
SYS SYS_FBA_CONTEXT_LIST
SYS WRI$_ALERT_THRESHOLD_LOG
SYS TS$
SYS FET$
SYS UTL32K_ERRORS
SYS PROXY_ROLE_DATA$
SYS PROXY_DATA$
SYS UTL32K_WARNINGS
SYS FILE$
SYS UNDO$
----------------------------------
-- connecting as FRED on 23c
----------------------------------
show user
USER is "FRED"
select owner,trigger_name from all_triggers where owner='SYS';
no rows selected
select owner,table_name from all_tables where owner='SYS' fetch first 10 rows only;
OWNER TABLE_NAME
---------- -----------------------------------
SYS IND$
SYS CDEF$
SYS CLU$
SYS UNDO$
SYS CCOL$
SYS PROXY_ROLE_DATA$
SYS FET$
SYS CON$
SYS ICOL$
SYS COL$
10 rows selected.
----------------------------------
-- connecting as SYS on 23c
----------------------------------
show user
USER is "SYS"
select owner,trigger_name from all_triggers where owner='SYS';
OWNER TRIGGER_NAME
---------- ------------------------------
SYS LOGMNRGGC_TRIGGER
SYS DBMS_KAFKA_DROP_USER
SYS XDB_PI_TRIG
SYS DELETE_ENTRIES
SYS OJDS$ROLE_TRIGGER$
SYS DB_PIN
6 rows selected.
select owner,table_name from all_tables where owner='SYS' fetch first 10 rows only;
OWNER TABLE_NAME
---------- -----------------------------------
SYS IND$
SYS CDEF$
SYS CLU$
SYS UNDO$
SYS CCOL$
SYS PROXY_ROLE_DATA$
SYS FET$
SYS CON$
SYS ICOL$
SYS COL$
10 rows selected.