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
