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