- One team sets audit on ALTER USER and later a second team adds the same ALTER USER audit because of their requirement. Then after another short time the second team decides that they made a mistake/design decision and issues NOAUDIT ALTER USER... and this wipes out the first teams audit also of ALTER USER
- A slightly different version of the same problem is that again team 1 sets audit on ALTER USER. Then a DBA is given a requirement by management to audit all system privileges so issues AUDIT ALL PRIVILEGES and this turns on all system privileges. Whilst this is fine to start with someone decides there is too much audit generated and asks him to remove that setting with NOAUDIT ALL PRVILEGES. The problem is that this wipes out team 1's individual audit on ALTER USER
This is not a good situation as our careful audit designs can be changed by accident in traditional audit scenarios. A solution can be created using process rather than technical means and this is to ensure all audit settings go through one team so that they can be monitored. It is also possible to use an NOAUDIT trigger to stop the removal of the audit settings.
I have a 19c database and its in mixed mode:
SQL> select value from v$option where parameter='Unified Auditing';
VALUE
----------------------------------------------------------------
FALSE
SQL>
This means in my 19c database we are in mixed mode, so normal audit and unified audit both fire. This is OK for this demo. Next check what audit records exist in traditional audit and also unified audit:
SQL> select count(*) from sys.aud$;
COUNT(*)
----------
403210
SQL> select count(*) from unified_audit_trail;
COUNT(*)
----------
86218
SQL>
Now truncate the traditional audit and use the DBMS_AUDIT_MGMT to delete the unified audit trail:
SQL> truncate table sys.aud$;
Table truncated.
SQL> begin
2 dbms_audit_mgmt.clean_audit_trail(audit_trail_type=>dbms_audit_mgmt.audit_trail_unified,use_last_arch_timestamp=>false);
3 end;
4 /
PL/SQL procedure successfully completed.
SQL>
Check the counts again:
SQL> select count(*) from sys.aud$;
COUNT(*)
----------
0
SQL> select count(*) from unified_audit_trail;
COUNT(*)
----------
2
SQL>
The standard audit trail is truncated but the unified audit trail still contains 2 records. We will come back to that in a different blog.
Next lets set up audit for ALTER USER or rather lets check if its already there; first for traditional audit:
SQL> @sc_print 'select * from dba_stmt_audit_opts where audit_option=''''ALTER USER'''''
old 32: lv_str:=translate('&&1','''','''''');
new 32: lv_str:=translate('select * from dba_stmt_audit_opts where audit_option=''ALTER USER''','''','''''');
Executing Query [select * from dba_stmt_audit_opts where audit_option='ALTER
USER']
USER_NAME :
PROXY_NAME :
AUDIT_OPTION : ALTER USER
SUCCESS : BY ACCESS
FAILURE : BY ACCESS
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
Yes, we audit the statement, what about the privilege:
SQL> @sc_print 'select * from dba_priv_audit_opts where privilege=''''ALTER USER'''''
old 32: lv_str:=translate('&&1','''','''''');
new 32: lv_str:=translate('select * from dba_priv_audit_opts where privilege=''ALTER USER''','''','''''');
Executing Query [select * from dba_priv_audit_opts where privilege='ALTER USER']
USER_NAME :
PROXY_NAME :
PRIVILEGE : ALTER USER
SUCCESS : BY ACCESS
FAILURE : BY ACCESS
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
So, yes we audit the privilege and the statement. What about unified audit:
SQL> @sc_print 'select * from audit_unified_policies where audit_option=''''ALTER USER'''''
old 32: lv_str:=translate('&&1','''','''''');
new 32: lv_str:=translate('select * from audit_unified_policies where audit_option=''ALTER USER''','''','''''');
Executing Query [select * from audit_unified_policies where audit_option='ALTER
USER']
POLICY_NAME : ORA_ACCOUNT_MGMT
AUDIT_CONDITION : NONE
CONDITION_EVAL_OPT : NONE
AUDIT_OPTION : ALTER USER
AUDIT_OPTION_TYPE : STANDARD ACTION
OBJECT_SCHEMA : NONE
OBJECT_NAME : NONE
OBJECT_TYPE : NONE
COMMON : NO
INHERITED : NO
AUDIT_ONLY_TOPLEVEL : NO
-------------------------------------------
POLICY_NAME : ORA_SECURECONFIG
AUDIT_CONDITION : NONE
CONDITION_EVAL_OPT : NONE
AUDIT_OPTION : ALTER USER
AUDIT_OPTION_TYPE : STANDARD ACTION
OBJECT_SCHEMA : NONE
OBJECT_NAME : NONE
OBJECT_TYPE : NONE
COMMON : NO
INHERITED : NO
AUDIT_ONLY_TOPLEVEL : NO
-------------------------------------------
POLICY_NAME : ORA_CIS_RECOMMENDATIONS
AUDIT_CONDITION : NONE
CONDITION_EVAL_OPT : NONE
AUDIT_OPTION : ALTER USER
AUDIT_OPTION_TYPE : STANDARD ACTION
OBJECT_SCHEMA : NONE
OBJECT_NAME : NONE
OBJECT_TYPE : NONE
COMMON : NO
INHERITED : NO
AUDIT_ONLY_TOPLEVEL : NO
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
We have three Oracle shipped policies that include ALTER USER:
SQL> @sc_print 'select * from audit_unified_policies where audit_option=''''ALTER USER'''''
old 32: lv_str:=translate('&&1','''','''''');
new 32: lv_str:=translate('select * from audit_unified_policies where audit_option=''ALTER USER''','''','''''');
Executing Query [select * from audit_unified_policies where audit_option='ALTER
USER']
POLICY_NAME : ORA_ACCOUNT_MGMT
AUDIT_CONDITION : NONE
CONDITION_EVAL_OPT : NONE
AUDIT_OPTION : ALTER USER
AUDIT_OPTION_TYPE : STANDARD ACTION
OBJECT_SCHEMA : NONE
OBJECT_NAME : NONE
OBJECT_TYPE : NONE
COMMON : NO
INHERITED : NO
AUDIT_ONLY_TOPLEVEL : NO
-------------------------------------------
POLICY_NAME : ORA_SECURECONFIG
AUDIT_CONDITION : NONE
CONDITION_EVAL_OPT : NONE
AUDIT_OPTION : ALTER USER
AUDIT_OPTION_TYPE : STANDARD ACTION
OBJECT_SCHEMA : NONE
OBJECT_NAME : NONE
OBJECT_TYPE : NONE
COMMON : NO
INHERITED : NO
AUDIT_ONLY_TOPLEVEL : NO
-------------------------------------------
POLICY_NAME : ORA_CIS_RECOMMENDATIONS
AUDIT_CONDITION : NONE
CONDITION_EVAL_OPT : NONE
AUDIT_OPTION : ALTER USER
AUDIT_OPTION_TYPE : STANDARD ACTION
OBJECT_SCHEMA : NONE
OBJECT_NAME : NONE
OBJECT_TYPE : NONE
COMMON : NO
INHERITED : NO
AUDIT_ONLY_TOPLEVEL : NO
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
If we check which policies are enabled:
SQL> @sc_print 'select * from audit_unified_enabled_policies'
old 32: lv_str:=translate('&&1','''','''''');
new 32: lv_str:=translate('select * from audit_unified_enabled_policies','''','''''');
Executing Query [select * from audit_unified_enabled_policies]
POLICY_NAME : ORA_SECURECONFIG
ENABLED_OPTION : BY USER
ENTITY_NAME : ALL USERS
ENTITY_TYPE : USER
SUCCESS : YES
FAILURE : YES
-------------------------------------------
POLICY_NAME : ORA_LOGON_FAILURES
ENABLED_OPTION : BY USER
ENTITY_NAME : ALL USERS
ENTITY_TYPE : USER
SUCCESS : NO
FAILURE : YES
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
Only one of the policies that include ALTER USER is enabled. Lets also enable ORA_ACCOUNT_MGMT as that includes ALTER USER:
SQL> audit policy ora_account_mgmt;
Audit succeeded.
SQL>
So, now we should have two policies enabled that include ALTER USER:
SQL> @sc_print 'select * from audit_unified_enabled_policies'
old 32: lv_str:=translate('&&1','''','''''');
new 32: lv_str:=translate('select * from audit_unified_enabled_policies','''','''''');
Executing Query [select * from audit_unified_enabled_policies]
POLICY_NAME : ORA_SECURECONFIG
ENABLED_OPTION : BY USER
ENTITY_NAME : ALL USERS
ENTITY_TYPE : USER
SUCCESS : YES
FAILURE : YES
-------------------------------------------
POLICY_NAME : ORA_LOGON_FAILURES
ENABLED_OPTION : BY USER
ENTITY_NAME : ALL USERS
ENTITY_TYPE : USER
SUCCESS : NO
FAILURE : YES
-------------------------------------------
POLICY_NAME : ORA_ACCOUNT_MGMT
ENABLED_OPTION : BY USER
ENTITY_NAME : ALL USERS
ENTITY_TYPE : USER
SUCCESS : YES
FAILURE : YES
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
Great, so why did we do all of this? Because I wanted to create the same scenario as the standard audit described at the start of this blog. We have two teams that have their own audit of ALTER USER and each is identified in this example by the two policies ORA_ACCOUNT_MGMT and ORA_SECURECONFIG. Lets just see some of the audit trail before we do an action:
SQL> col audit_type for a8
SQL> col sessionid for 9999999999
SQL> col dbusername for a8
SQL> col action_name for a20
SQL> col system_privilege for a20
SQL> col system_privilege_used for a20
SQL> col additional_info for a20
SQL> col unified_audit_policies for a20
SQL> col event_timestamp_utc for a30
SQL> set lines 220
SQL> select event_timestamp_utc,audit_type,sessionid, dbusername,action_name,return_code,system_privilege, system_privilege_used, additional_info,unified_audit_policies from unified_audit_trail;
EVENT_TIMESTAMP_UTC AUDIT_TY SESSIONID DBUSERNA ACTION_NAME RETURN_CODE SYSTEM_PRIVILEGE SYSTEM_PRIVILEGE_USE ADDITIONAL_INFO UNIFIED_AUDIT_POLICI
------------------------------ -------- ----------- -------- -------------------- ----------- -------------------- -------------------- -------------------- --------------------
11-JUL-23 09.34.40.619582 Standard 11970818 SYSTEM LOGOFF BY CLEANUP 0
27-NOV-24 13.13.10.663368 Standard 3261248236 SYS EXECUTE 0 SYSDBA
27-NOV-24 13.46.58.169573 Standard 3261248236 SYS AUDIT 0 SYSDBA
SQL>
What is interesting here is the audit record from 2023 that was not cleared when I cleared the audit trail and the other two records do not show a policy as they are SYSDBA audit. I have saved the above query and formatting as a script aud.sql for subsequent use later. The script is now:
SQL> get aud.sql
1 col audit_type for a8
2 col sessionid for 9999999999
3 col dbusername for a8
4 col action_name for a20
5 col system_privilege for a20
6 col system_privilege_used for a20
7 col additional_info for a20
8 col unified_audit_policies for a20
9 col event_timestamp_utc for a30
10 col object_schema for a10
11 col object_name for a20
12 set lines 220
13 spool aud.lis
14 select event_timestamp_utc,
15 audit_type,sessionid,
16 dbusername,
17 object_schema,
18 object_name,
19 action_name,
20 return_code,
21 system_privilege,
22 system_privilege_used,
23 additional_info,
24 unified_audit_policies
25 from unified_audit_trail
26 /
27* spool off
28
Running it shows:
SQL> @aud
EVENT_TIMESTAMP_UTC AUDIT_TY SESSIONID DBUSERNA OBJECT_SCH OBJECT_NAME ACTION_NAME RETURN_CODE SYSTEM_PRIVILEGE SYSTEM_PRIVILEGE_USE ADDITIONAL_INFO UNIFIED_AUDIT_POLICI
------------------------------ -------- ----------- -------- ---------- -------------------- -------------------- ----------- -------------------- -------------------- -------------------- --------------------
11-JUL-23 09.34.40.619582 Standard 11970818 SYSTEM LOGOFF BY CLEANUP 0
27-NOV-24 13.13.10.663368 Standard 3261248236 SYS SYS DBMS_AUDIT_MGMT EXECUTE 0 SYSDBA
27-NOV-24 13.46.58.169573 Standard 3261248236 SYS SYS ORA_ACCOUNT_MGMT AUDIT 0 SYSDBA
SQL>
Lets now change a password for the ORABLOG schema as ORABLOG:
SQL> connect orablog/orablog@//192.168.56.77:1521/orclpdb.localdomain
Connected.
SQL>
SQL> alter user orablog identified by orablog;
User altered.
SQL>
Lets see the audit trail now:
SQL> @aud
EVENT_TIMESTAMP_UTC AUDIT_TY SESSIONID DBUSERNA OBJECT_SCH OBJECT_NAME ACTION_NAME RETURN_CODE SYSTEM_PRIVILEGE SYSTEM_PRIVILEGE_USE ADDITIONAL_INFO UNIFIED_AUDIT_POLICIES
------------------------------ -------- ----------- -------- ---------- ------------------------------ -------------------- ----------- -------------------- -------------------- -------------------- --------------------------------------------------
11-JUL-23 09.34.40.619582 Standard 11970818 SYSTEM LOGOFF BY CLEANUP 0
27-NOV-24 14.37.13.991751 Standard 522751737 ORABLOG SYS DUAL SELECT 0
27-NOV-24 14.37.14.028856 Standard 522751737 ORABLOG SYSTEM SQLPLUS_PRODUCT_PROFILE SELECT 0
27-NOV-24 14.37.14.030042 Standard 522751737 ORABLOG SYSTEM PRODUCT_PRIVS SELECT 0
27-NOV-24 14.37.14.035820 Standard 522751737 ORABLOG SYSTEM SQLPLUS_PRODUCT_PROFILE SELECT 0
27-NOV-24 14.37.14.036376 Standard 522751737 ORABLOG SYSTEM PRODUCT_PRIVS SELECT 0
27-NOV-24 14.37.14.067515 Standard 522751737 ORABLOG SYS DUAL SELECT 0
27-NOV-24 14.37.28.098112 Standard 522751737 ORABLOG ORABLOG ALTER USER 0 ALTER USER ORA_ACCOUNT_MGMT, ORA_SECURECONFIG
27-NOV-24 13.13.10.663368 Standard 3261248236 SYS SYS DBMS_AUDIT_MGMT EXECUTE 0 SYSDBA
27-NOV-24 13.46.58.169573 Standard 3261248236 SYS SYS ORA_ACCOUNT_MGMT AUDIT 0 SYSDBA
10 rows selected.
SQL>
This captured the use of ALTER USER by the owner of an account, in this case ORABLOG. As we can see both policies ORA_ACCOUNT_MGMT and ORA_SECURECONFIG are recorded as being used so this solves the issue that was present in standard auditing. The use of ALTER USER is captured once but we can see both associated policies for ALTER USER. This example should not use the system privilege ALTER USER as ORABLOG has been granted it BUT its not needed for a user to change their own password. We can see that ORABLOG has ALTER USER:
SQL> @sc_who_has_priv
Enter value for output_method: S
old 118: lv_file_or_screen:= upper('&&output_method');
new 118: lv_file_or_screen:= upper('S');
Enter value for priv_to_find: ALTER USER
old 119: write_op('Privilege => '||upper('&&priv_to_find')||' has been granted to =>');
new 119: write_op('Privilege => '||upper('ALTER USER')||' has been granted to =>');
old 121: get_priv(upper('&&priv_to_find'));
new 121: get_priv(upper('ALTER USER'));
Privilege => ALTER USER has been granted to =>
====================================================================
User => APEX_180200 (ADM = NO)
User => SYS (ADM = NO)
Role => DBA (ADM = NO) which is granted to =>
User => ORABLOG (ADM = NO)
User => SYSTEM (ADM = NO)
User => SYS (ADM = YES)
Role => IMP_FULL_DATABASE (ADM = NO) which is granted to =>
Role => DATAPUMP_IMP_FULL_DATABASE (ADM = NO) which is granted to =>
Role => DBA (ADM = NO) which is granted to =>
User => ORABLOG (ADM = NO)
User => SYSTEM (ADM = NO)
User => SYS (ADM = YES)
User => SYS (ADM = YES)
User => GSMADMIN_INTERNAL (ADM = NO)
User => SYS (ADM = YES)
Role => DBA (ADM = NO) which is granted to =>
User => ORABLOG (ADM = NO)
User => SYSTEM (ADM = NO)
User => SYS (ADM = YES)
Role => DV_ACCTMGR (ADM = NO) which is granted to =>
User => C##ACCO (ADM = YES)
User => C##ACCO_BK (ADM = YES)
User => C##ACCO_BK (ADM = YES)
Role => DATAPUMP_IMP_FULL_DATABASE (ADM = NO) which is granted to =>
Role => DBA (ADM = NO) which is granted to =>
User => ORABLOG (ADM = NO)
User => SYSTEM (ADM = NO)
User => SYS (ADM = YES)
User => SYS (ADM = YES)
User => GSMADMIN_INTERNAL (ADM = NO)
Role => EM_EXPRESS_ALL (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
Role => DBA (ADM = NO) which is granted to =>
User => ORABLOG (ADM = NO)
User => SYSTEM (ADM = NO)
User => SYS (ADM = YES)
User => WMSYS (ADM = NO)
PL/SQL procedure successfully completed.
SQL>
Connect as the user COMPILER who does not have ALTER USER granted and change its password:
SQL> connect compiler/compiler@//192.168.56.77:1521/orclpdb.localdomain
ERROR:
ORA-28002: the password will expire within 7 days
Connected.
SQL> alter user compiler identified by compiler;
User altered.
SQL>
Now, lets check the audit trail:
SQL> @aud
EVENT_TIMESTAMP_UTC AUDIT_TY SESSIONID DBUSERNA OBJECT_SCH OBJECT_NAME ACTION_NAME RETURN_CODE SYSTEM_PRIVILEGE SYSTEM_PRIVILEGE_USE ADDITIONAL_INFO UNIFIED_AUDIT_POLICIES
------------------------------ -------- ----------- -------- ---------- ------------------------------ -------------------- ----------- -------------------- -------------------- -------------------- --------------------------------------------------
11-JUL-23 09.34.40.619582 Standard 11970818 SYSTEM LOGOFF BY CLEANUP 0
27-NOV-24 15.01.50.001045 Standard 4002832509 SYS GRANT 0 RESTRICTED SESSION SYSDBA ORA_ACCOUNT_MGMT
27-NOV-24 14.37.13.991751 Standard 522751737 ORABLOG SYS DUAL SELECT 0
27-NOV-24 14.37.14.028856 Standard 522751737 ORABLOG SYSTEM SQLPLUS_PRODUCT_PROFILE SELECT 0
27-NOV-24 14.37.14.030042 Standard 522751737 ORABLOG SYSTEM PRODUCT_PRIVS SELECT 0
27-NOV-24 14.37.14.035820 Standard 522751737 ORABLOG SYSTEM SQLPLUS_PRODUCT_PROFILE SELECT 0
27-NOV-24 14.37.14.036376 Standard 522751737 ORABLOG SYSTEM PRODUCT_PRIVS SELECT 0
27-NOV-24 14.37.14.067515 Standard 522751737 ORABLOG SYS DUAL SELECT 0
27-NOV-24 14.37.28.098112 Standard 522751737 ORABLOG ORABLOG ALTER USER 0 ALTER USER ORA_ACCOUNT_MGMT, ORA_SECURECONFIG
27-NOV-24 14.56.34.087694 Standard 1521097332 COMPILER LOGON 1035 CREATE SESSION ORA_LOGON_FAILURES
27-NOV-24 15.02.13.550409 Standard 2148166546 COMPILER COMPILER ALTER USER 0 ORA_ACCOUNT_MGMT, ORA_SECURECONFIG
EVENT_TIMESTAMP_UTC AUDIT_TY SESSIONID DBUSERNA OBJECT_SCH OBJECT_NAME ACTION_NAME RETURN_CODE SYSTEM_PRIVILEGE SYSTEM_PRIVILEGE_USE ADDITIONAL_INFO UNIFIED_AUDIT_POLICIES
------------------------------ -------- ----------- -------- ---------- ------------------------------ -------------------- ----------- -------------------- -------------------- -------------------- --------------------------------------------------
27-NOV-24 13.13.10.663368 Standard 3261248236 SYS SYS DBMS_AUDIT_MGMT EXECUTE 0 SYSDBA
27-NOV-24 13.46.58.169573 Standard 3261248236 SYS SYS ORA_ACCOUNT_MGMT AUDIT 0 SYSDBA
13 rows selected.
SQL>
As you can see for the user ORABLOG we captured the change of password by that user with the action ALTER USER and the use of the system privilege ALTER USER as ORABLOG has. For the user COMPILER who also changed their own password we see the action ALTER USER but no use of a system privilege as COMPILER does not have ALTER USER. Lets try one more example and connect as SYSTEM and change the REF users password:
SQL> connect system/oracle1@//192.168.56.77:1521/orclpdb.localdomain
Connected.
SQL> alter user ref identified by ref;
User altered.
SQL>
Connect as SYS and check the audit trail:
SQL> @aud
EVENT_TIMESTAMP_UTC AUDIT_TY SESSIONID DBUSERNA OBJECT_SCH OBJECT_NAME ACTION_NAME RETURN_CODE SYSTEM_PRIVILEGE SYSTEM_PRIVILEGE_USE ADDITIONAL_INFO UNIFIED_AUDIT_POLICIES
------------------------------ -------- ----------- -------- ---------- ------------------------------ -------------------- ----------- -------------------- -------------------- -------------------- --------------------------------------------------
11-JUL-23 09.34.40.619582 Standard 11970818 SYSTEM LOGOFF BY CLEANUP 0
27-NOV-24 15.01.50.001045 Standard 4002832509 SYS GRANT 0 RESTRICTED SESSION SYSDBA ORA_ACCOUNT_MGMT
27-NOV-24 14.37.13.991751 Standard 522751737 ORABLOG SYS DUAL SELECT 0
27-NOV-24 14.37.14.028856 Standard 522751737 ORABLOG SYSTEM SQLPLUS_PRODUCT_PROFILE SELECT 0
27-NOV-24 14.37.14.030042 Standard 522751737 ORABLOG SYSTEM PRODUCT_PRIVS SELECT 0
27-NOV-24 14.37.14.035820 Standard 522751737 ORABLOG SYSTEM SQLPLUS_PRODUCT_PROFILE SELECT 0
27-NOV-24 14.37.14.036376 Standard 522751737 ORABLOG SYSTEM PRODUCT_PRIVS SELECT 0
27-NOV-24 14.37.14.067515 Standard 522751737 ORABLOG SYS DUAL SELECT 0
27-NOV-24 14.37.28.098112 Standard 522751737 ORABLOG ORABLOG ALTER USER 0 ALTER USER ORA_ACCOUNT_MGMT, ORA_SECURECONFIG
27-NOV-24 14.56.34.087694 Standard 1521097332 COMPILER LOGON 1035 CREATE SESSION ORA_LOGON_FAILURES
27-NOV-24 15.02.13.550409 Standard 2148166546 COMPILER COMPILER ALTER USER 0 ORA_ACCOUNT_MGMT, ORA_SECURECONFIG
EVENT_TIMESTAMP_UTC AUDIT_TY SESSIONID DBUSERNA OBJECT_SCH OBJECT_NAME ACTION_NAME RETURN_CODE SYSTEM_PRIVILEGE SYSTEM_PRIVILEGE_USE ADDITIONAL_INFO UNIFIED_AUDIT_POLICIES
------------------------------ -------- ----------- -------- ---------- ------------------------------ -------------------- ----------- -------------------- -------------------- -------------------- --------------------------------------------------
27-NOV-24 15.07.27.378014 Standard 2161779837 SYSTEM SYS DUAL SELECT 0
27-NOV-24 15.07.27.386550 Standard 2161779837 SYSTEM SYS DUAL SELECT 0
27-NOV-24 15.07.35.623560 Standard 2161779837 SYSTEM REF ALTER USER 0 ALTER USER ORA_ACCOUNT_MGMT, ORA_SECURECONFIG
27-NOV-24 13.13.10.663368 Standard 3261248236 SYS SYS DBMS_AUDIT_MGMT EXECUTE 0 SYSDBA
27-NOV-24 13.46.58.169573 Standard 3261248236 SYS SYS ORA_ACCOUNT_MGMT AUDIT 0 SYSDBA
16 rows selected.
SQL>
This is the same behaviour as traditional audit. I have done the same tests on traditional audit and I get the same result. This behaviour to me is not correct. The user SYSTEM has the ALTER USER system privilege and changes another users password so ALTER USER the system privilege is used - CORRECT. The user COMPILER changes their own password and does not have the system privilege ALTER USER so it is not used - CORRECT.
The user ORABLOG has the system privilege and changes its own password BUT it can do this without the system privilege - INCORRECT
This is summarised here:
SQL> select event_timestamp_utc,dbusername,object_name,action_name,system_privilege_used
2 from unified_audit_trail
3 where action_name='ALTER USER';
EVENT_TIMESTAMP_UTC DBUSERNA OBJECT_NAME ACTION_NAME SYSTEM_PRIVILEGE_USE
------------------------------ -------- ------------------------------ -------------------- --------------------
27-NOV-24 14.37.28.098112 ORABLOG ORABLOG ALTER USER ALTER USER
27-NOV-24 15.02.13.550409 COMPILER COMPILER ALTER USER
27-NOV-24 15.07.35.623560 SYSTEM REF ALTER USER ALTER USER
SQL>
So, why is the system privilege marked as used when its not necessary to use it to do the action, so why does ORABLOG use it to change its own password when this is a statement not a privilege?
As I said the same behaviour was in traditional auditing for many years.
#oracleace #sym_42 #ukoug #oracle #security #audit #unifiedaudit #policy #passwordchange #password #audittrail
