If you use still or used standard auditing in the Oracle database then you should have come across an issue. If you have multiple teams enabling audit one team can destroy another teams audit settings. Here is a couple of examples:

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

No one wants to be hacked or their data stolen or leaked or their database be breached but it can happen. If you become the latest victim of a data hack and your Oracle database is compromised then what do you do?

There are 3 high level elements in responding to a data breach or hack of an Oracle database; within these high level areas there are many smaller steps and hoops to jump through so I am not going to list everything here but will cover the basic parts. The three steps are:

Manage the breach: Properly assess if a breach was reported and most likely occurred and then manage it via a dedicated process and ideally team.

Perform live response: this means gathering the transient data evidence from the database and other relevant systems before it is lost due to time or other movements of the data. Then also gather the less transient evidence from the database and other relevant systems.

Perform forensic analysis: This is to try and establish the why, how, when and who of the attack in a time line and the data checksummed to prove the validity of the data as it was extracted from the database.

As I said a few minutes ago there are many sub-steps to each of these main parts of the breach response plan. In the first step you should have a mechanism that allows potential breaches to be reported internally or from external sources to one central point. This can be as simple as an email address. The report of a potential breach should be funnelled to one person/team so that its centrally managed. There should be careful management of the breach by a single person (This person can be more than one and assigned to each breach response) so that the breach is managed carefully and consistently to a pre-defined plan. The breach response team is in charge from this point not the business. Ensure a consistent message is given out to media or other sources by one person - I do not mean lie, I just mean control what is said so the message is not ad-hoc and is consistent. The team should confirm a breach has occurred and then perform the live response and forensic analysis.

PFCLForensics version 2024 is our product / tool that can be used to help manage and complete a breach response. I am going to show a brief overview here of some of the main features and functions of the tool and we are not looking at a specific attack but showing the features of the tool and how they operate.

On opening the software create a new project and you are given a check list to tick off my one person as the breach response progresses:

PFCLForensics checklist for breach management

This allows management of the whole process; the list can be saved to the project and the list can be edited and changed as desired as the work progresses.

The next step is to connect PFCLForensics to the database to do the live response and gather the transient. The user needed to connect should have CREATE SESSION and SELECT ANY DICTIONARY. If a breach has occurred then it makes sense to not change the database to add users or grant privileges so either use an existing suitable user.

After we connect, choose the menu options to collect the transient data for an Oracle database and then the static data for an Oracle database:

The above view shows the source data collected from the database for transient data (that data that changes easily and quickly) and static data (data that could change but is less likely to). As you can see in the view we can also adjust and align all time stamps so that all data when chosen to be part of the investigation is correlated with all other data. This could be because some data in the database is held as UTC or GMT and some with the local timezone; and further some data from a web server or PC may have further time differences maybe because the PC is not aligned with NTP. We can adjust all times as viewed and correlated with this feature.

The next step for the forensic analyst needs to do is review the raw data; this can be done clicking any row in the "List of Data Sources" and this raw data then shows in the "Source Data" grid at the bottom of the screen.

PFCLForensics - select source data to the time line

The view above shows the user selecting source data that maybe part of the attack and clicking the little green button to send this data to the timeline.

If data is deemed interesting but maybe not part of the actual breach then instead it can be sent to the supporting timeline. As we add data to the main timeline we can view drillable graphs of the events and also see all of the chosen data in a grid correlated across data points such as user name or timestamp so that all of the gathered data can be seen in the same time frame and also data context

PFCLForensics - multiple pieces of evidence gathered

Additional data can be added via the Import File menu option and added to the investigation. We include some standard data sources such as Apache access logs or Apache error logs BUT any data can be included. The user can simply create a new import type pattern by defining column types and then importing the data using the new type.

The analysis should now progress to the point where the attack start point and end point are known and the data gathered is between these two points. The validate button can be used at any point to re-validate that the input data used has not been tampered with. When enough evidence is available to answer the questions who, when, why, how then you can create a report. The product includes a built in word processor and a template that can be used or you can write your own report. All data in grids can be selected and copied directly to the report and there is a screen shot facility to allow screen shots of graphs for instance.

The goal is to understand in advance how you may manage and respond to a potential data breach or attack of an Oracle database under your protection. PFCLForensics allows this to be done more easily and to be managed.

It is really worth while understanding the process and tasks in advance of an actual breach. You do not want to be told to handle a breach and also learn how to do it in advance.

Practice in advance and also define and create your processes and teams

You might think, I don't care as it has not happened to me but you should care. It is really worth knowing in advance what to do and how to act and respond it a real breach or even a potential breach. Talk to us to see more of PFCLForensics and see a live demo arranged for you over webex or to purchase a license

#oracleace #sym_42 #oracle #forensics #liveresponse #databreach #security #forensicanalysis #forensics #hacking #datasecurity