Pete Finnigan's Oracle Security Weblog

I talked last week about an issue where I wanted to pre-create a unified audit policy with no rules so that I could create a number of policies for a customer in advance and then we could add the actions/rules later.

I got an error ORA-46373 that if there were no rules on the unified policy then this is not allowed. Fine, we have a simple solution to create a dummy role and make sure its not granted to anyone and then create the policy with a ROLES rule for that dummy role. We can do this again now before we discuss the new point.

Create a dummy role and create the policy and also revoke the role from SYS so its not granted to anyone.

SQL> sho user
USER is "SYS"
SQL> create role pfclatk;

Role created.

SQL> revoke pfclatk from sys;

Revoke succeeded.

SQL> create audit policy pete1 roles pfclatk;

Audit policy created.

SQL>

Now, this solved my issue BUT the customer wanted to name his policies POL1.1.1 or similar. So he created a policy and said my fix was wrong:

SQL> create audit policy pete1.1 roles pfclatk;
create audit policy pete1.1 roles pfclatk
*
ERROR at line 1:
ORA-46373: Audit policy 'PETE1' must have at least one audit option.


SQL>

Hmmm, that does not add up. We have a valid ROLES clause so the error should not be ORA-46373. The issue is obvious as the name of the unified policy cannot contain a "." (DOT) unless the whole thing is encased in quotes. We can try that now:

SQL> create audit policy "pete1.1" roles pfclatk;

Audit policy created.

SQL>

That obviously works BUT I don't like to create objects encased in quotes. What if we try and create a unified audit policy in another schema? the syntax diagram for 19c does not state that this is allowed. Lets try anyway:

SQL> create audit policy atk.pete1 roles pfclatk;
create audit policy atk.pete1 roles pfclatk
*
ERROR at line 1:
ORA-46373: Audit policy 'ATK' must have at least one audit option.


SQL>

No, the same error BUT i expected that as the syntax does not state its possible and the dictionary does not include OWNER:

SQL> desc audit_unified_policies
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 POLICY_NAME                                        VARCHAR2(128)
 AUDIT_CONDITION                                    VARCHAR2(4000)
 CONDITION_EVAL_OPT                                 VARCHAR2(9)
 AUDIT_OPTION                                       VARCHAR2(128)
 AUDIT_OPTION_TYPE                                  VARCHAR2(18)
 OBJECT_SCHEMA                                      VARCHAR2(128)
 OBJECT_NAME                                        VARCHAR2(128)
 OBJECT_TYPE                                        VARCHAR2(23)
 COMMON                                             VARCHAR2(3)
 INHERITED                                          VARCHAR2(3)
 AUDIT_ONLY_TOPLEVEL                                VARCHAR2(3)

SQL>

If we do the same with a procedure for instance; call the procedure pete1.1 then we get:

SQL> create or replace procedure pete1.1
  2  is
  3  begin
  4  null;
  5  end;
  6  /

Warning: Procedure created with compilation errors.

SQL> sho err
No errors.
SQL>
SQL> desc pete1.1
ERROR:
ORA-24372: invalid object for describe


SQL> select procedure_name from dba_procedures where procedure_name like 'PETE%';

no rows selected

SQL>

So, a similar issue; the database said we created the procedure pete1.1 with errors BUT there were no errors and the procedure does not exist. We can create the procedure encased in double quotes:

SQL> create or replace procedure "pete1.1"
  2  is
  3  begin
  4  null;
  5  end;
  6  /

Procedure created.

SQL>

What if we create a table:

SQL> create table pete1.1 (col1 number);
create table pete1.1 (col1 number)
                  *
ERROR at line 1:
ORA-00922: missing or invalid option


SQL>

This is slightly more descriptive as the ORA-0922 points at the "." (DOT) and we can create the table with double quotes:

SQL> create table "pete1.1" (col1 number);

Table created.

SQL>

The issue we see in the unified audit policy shows the wrong error in my opinion as the error ORA-46373 shows that we don't have an ACTION, ROLES or PRIVILEGES and generated ORA-46373 but in fact the issue is we used an invalid character in the policy name. Similar occurs with the wrong character in a procedure name but no error number directly shown to the user/dba/developer and no warnings. Similarly with a table, we get closer to the actual place of error with ORA-0922 but still it doesn't state the real error, i.e. it doesn't say "you cant use '.' (DOT) in the name".

Be careful with Oracle errors, sometimes they don't tell you the real problem

#oracleace #sym_42 #oracle #security #audit #trail #unified #unifiedaudit

I have a requirement to pre-create unified audit policies and then add the ACTIONS, PRIVILEGES, ROLES etc after they have been created based on some stored audit rules for a customer. We tried to create a policy with no actions, roles, privileges and we get:

SQL> create audit policy pete1;
create audit policy pete1
*
ERROR at line 1:
ORA-46373: Audit policy 'PETE1' must have at least one audit option.


SQL>

Hmm, it would be nice to be able to be able to pre-create each policy before we start adding the rules but Oracle does not allow this. What if we create a policy with a rule and then remove that rule?

SQL> create audit policy pete1 privileges create session;

Audit policy created.

SQL> alter audit policy pete1 drop privileges create session;
alter audit policy pete1 drop privileges create session
*
ERROR at line 1:
ORA-46373: Audit policy 'PETE1' must have at least one audit option.


SQL>

Nope, we cannot just add a dummy rule and then remove it. We could add a dummy rule and before enabling the policy after we add all the real rules remove the dummy. What I mean by dummy here is a rule that I do not actually need. Lets drop our policy:

SQL> drop audit policy pete1;

Audit Policy dropped.

SQL>

Can we add a rule for an object that does not exist?

SQL> create audit policy pete1 actions select on orablog.dummy;
create audit policy pete1 actions select on orablog.dummy
                                                    *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>

Nope, cannot do that either. The database obviously checks that the object that we wish to audit exists or is visible to the policy.

So, it seems that we have two options:

• Add an audit action, privilege or role to out unified audit policy that we do not actually need and then add the real audit options later and remove the dummy before the policy is finally enabled


• Pre-Add some dummy object and make sure it is not used and then add that as an audit option so that if there were no other audit options added the enabling of the policy would not fail.

We will use the second option BUT a third option would ne to delay the creation of the policy until we need to add the first rule to it so creating the policy with a real rule. Whilst I would probably prefer that option that management is more complex so we will go with option 2 by creating a role and then revoke it from everyone and then add that to the policy so that the policy would work with no real rules and should not generate audit as the role is not granted to anyone.

SQL> sho user
USER is "SYS"
SQL> create role pfclatk;

Role created.

SQL> revoke pfclatk from sys;

Revoke succeeded.

SQL> create audit policy pete1 roles pfclatk;

Audit policy created.

SQL> audit policy pete1;

Audit succeeded.

SQL>

So, we can remove the dummy audit before we issue the "audit policy" command or leave it there and make sure our dummy role PFCLATK is not granted to anyone. We can clean up:

SQL> noaudit policy pete1;

Noaudit succeeded.

SQL> drop audit policy pete1;

Audit Policy dropped.

SQL>

So, this is an option to allow me to pre-create unified audit policies and to allow me to create those policies without any "real" audit options and to not generate or cause ORA-46373.

#oracle_ace #sym_42 #oracle #unified #audit #auditing #forensics #audittrail

This is the second talk that I did at the recent UKOUG conference at the East Side rooms in Birmingham. This talk discusses how you should respond if you think that your database has been breached or if you unfortunately know that you really have been breached.

The main purpose of this short blog post is to introduce the talk and to present a link to the MS Powerpoint slides.

We started by discussing the fact that data is now the new gold rush like the Yukon and Klondike in the 1890s onwards. Data is growing faster than anything and we manage data in Oracle databases and that data is now valuable so becomes a target for theft or damage.

Data hacking and breaches are now an every day occurrence and almost every person now knows what a data breach is. We cover laws like GDPR and the hefty fines that can happen if personal data is stolen. These elements confirm why we must protect Oracle databases and why the data is an easy target now for thieves.

The next part I covered is what do hackers do; steal data (read it), change data (update it) and destroy data (delete it). Also we cover how attackers do this, what do they exploit in your systems and processes to gain access to your data. We also discuss who is the attacker, internal, external, third parties, public? Finally we looked at the methods used to attack the data. These are essentially the open doors that you have left open to allow them in; this could be through bugs in your applications, bad configuration, bad processes and more.

Next we laid out the complete breach handling process and all the steps before focusing in on the breach response team, the leader and how to handle an incoming breach. We finished with a brief discussion on writing a report and how to fix the security and the breached system. Finally we looked at what you should be doing now to prevent a breach and also to add things to make breach response and analysis easier.

Here is the MS PPT slides:
What should you do if your Oracle database is hacked? - link here. Please have a read and be prepared for a breahc before it happens or take action to prevent a breach.

#oracleace #sym_42 #oracle #forensics #databreach #liveresponse #datasecurity

I presented recently at the UKOUG tech conference 2024 in Birmingham, UK at the Eastside rooms. This was a good conference and I had two talks there. This blog is about the first of those and includes a link to the MS Power Point slides as well.

This talk focused on what can go wrong with the data you hold and process in your Oracle database and how it can be vulnerable to attack or rather how the data held and processed can be vulnerable to attack. We also covered some background on data breaches and attack types and we position the task of securing data; we are securing data and not securing Oracle. We briefly covered cloud and of course Oracles security options.

The rest of the talk was to cover the top 10 security things to look at in the Oracle database to secure your data. The spoiler is that there is not a simple list of ten things that you can do in ten minutes that makes your data and database more secure. Its easy to see why this is the case. Oracle is a generic engine that allows you to process any data model and create any applications. So its not possible to have a generic security that just works. The security is part of the development of your data model, tables and code; its your job.

The power point slides for my talk Top 10 Things to Security Review in an Oracle Database are linked here. Please have a look.

#oracleace #sym_42 #oracle #security #databreach #hacking #datasecurity #data #database #ukoug #ukoug2024

I get asked a question often "Is your database more secure if its moved to the cloud?" and it does not matter which cloud we are talking about; could be Oracle OCI or Amazon or...

This is an interesting question from a lot of people over many years. A lot of people / customers are taken in by the association often of the word cloud and security and assume that there is some magical way that an Oracle database or database in general becomes more secure simply by relocating it to the cloud.

Lets be fair the cloud infrastructures now are mature and the interfaces to design and layout and specify security amongst other things are good and the whole infrastructure is probably better than your own in-house IT and servers and local management BUT that is not the whole picture

One big problem is that often customers do not know their data and therefore database is insecure already when hosted in-house and believe that the additional features of the cloud make it more secure.

If you have a database where you process customer data and the applications are littered with security issues such as SQL Injection bugs then does moving to the cloud fox these? - of course not. If you have half your staff accessing the database directly with development tools and reporting tools and more and the people share well known single accounts with rights to access any data then moving this to the cloud does not make the data more secure because half the staff access the data directly and it is therefore insecure. If the application design has made it so all data and code is in one schema then moving to the cloud does not fox this lack of privilege granularity. If the application design has allowed permissions on data and other rights to be the opposite of least privilege; in other words anyone connected can do anything to the data; then moving this design to the cloud does not secure the data. Or, if your DBAs use SYSDBA or oracle or DBA day in day out; then they can access and change anything and moving this to the cloud does not improve the security of the data.


Oracle security is about more than the location of the database; it's about the applications design including permissions and grants and access and the code and the security of the users, the data and the database settings itself. The security of the data is not the Cloud settings and cloud design; it is the database and application and data design.

If you think about this, it is obvious and we must tackle the securing of the data in the database primarily, the users and database settings; this leads to the obvious conclusion that if a database is insecure on-premise it is still insecure in a cloud or vice-versa; if a database is secure on premise then its secure in the cloud.

The cloud security matters but it does not magically make your data secure. In cloud we must also consider what model we are buying; who is in charge of the cloud and services and who is responsible for what? if we allow a third party to manage the database for instance; it is the same as allowing a third party to manage the database on premise or at their location; there is a risk that others outside can access your data directly or indirectly.

Let us be clear, this is not about cloud specifically but about the security of data at all levels of the design and deployment.

An insecure database cannot be magically be made secure by putting it in any cloud; only you can do that with good design, management and processes

#oracleace #sym_42 #oracle #database #security #cloud #data #breach #cloudsecurity

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

The last time I taught an in-person training class around Oracle security was almost 5 years ago. I was in The Hague for a class in February 2020 and then the lockdown hit us for Covid in March 2020 and this situation of work from home and no travel went on for some time. I started to move classes to on-line only via webex and I have taught quite a few classes online but its not the same as in-person classes.

I have spoken at conferences in person since the lockdowns BUT its time to move the training back to the classroom with people sat in front of me.
Online is still live but you don't get the same fact-to-face interaction or chats in the breaks or before we start or at lunch time.

I have planned a unique 3 day Oracle Security even to be held from January 21st to January 23rd 2025 here at our offices in York, UK. The class is a new agenda and is up to date for all relevant versions of Oracle up to 23.

We are going to cover cradle to grave of understanding why data in an Oracle database can be insecure and we are going to walk through the whole process of securing Oracle across the organisation.

There is a lot more details on the Oracle Security in 2025 page and also details of how to register.

This class will be limited in places to make it more personal to allow more people to interact and discuss and ask questions. To book your place then please see the Oracle security class details and agenda and I hope to see you there!!

#oracleace #sym_42 #oracle #security #training #class #york #2025 #book