If the CIS standard for 19c DB is strictly followed which asks to revoke execute privilege on dbms_lob from public then execute privilge on dbms_lob must be granted to either public of dbsnmp user (for all containers) for ash package update to work (refer 2699059.1).
Once the ash package is updated, activities on ash analytics page (simply monitroing and automatic page refreshes) results in huge amount of audit records being generated. For 24 hour period nearly 32GB of audit records were generated.
Majority of records are as below.
EVENT_TIMESTAMP ACTION_NAM OBJECT_NAME DBUSERNAME OBJECT_SCHEMA OS_USERNAM SQL_TEXT
------------------------------ ---------- -------------------- -------------------- -------------------- ---------- ----------------------------------------------------------------------------------------------------
FROM TABLE(GV$(CURSOR(
19-AUG-21 02.51.57.585441 PM SELECT USER_NAME_XML C##MONITOR DBSNMP oracle WITH unified_ash AS (
SELECT /*+ NO_MERGE */ *
FROM TABLE(GV$(CURSOR(
19-AUG-21 02.51.57.594962 PM SELECT MAP_WAITCLASS_XML C##MONITOR DBSNMP oracle WITH unified_ash AS (
SELECT /*+ NO_MERGE */ *
FROM TABLE(GV$(CURSOR(
19-AUG-21 02.51.57.606929 PM SELECT MAP_XID_XML C##MONITOR DBSNMP oracle WITH unified_ash AS (
SELECT /*+ NO_MERGE */ *
FROM TABLE(GV$(CURSOR(Everytime the performance page is refreshed (manual or auto) new set of audit records are added to the unified audit table.
The main cause of the audit record generation is the auditing of privilege "SELECT ANY DICTIONARY", which is also part of CIS standard. The auditing of this privilege was present even before the ash package update. What seem to have changed is now the user used for monitoring activity (c##monitor) seem to access the dbsnmp objects. c##montior user is granted only one role and that is EM_EXPRESS_BASIC.
To reduce the audit records and keep the ash package update, modify the audit policy by dropping the auditing of select any dictionary privilege. This will break the strict adherence to CIS.
Or simply do not update the ash package and ignore the warning. This was the case before patching and warning was shown. The number of audit records generated was low even with the use of select any dictionary privilege was being audited.
Only way to have the update and reduce the number of audit records being generated is to login to EM with dbsnmp user for monitoring instead of another user. DBSNMP user has more roles and privileges granted to it than EM_EXPRESS_BASIC. As such it may not be an option that is always be feasible.
