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
