I was asked a question in LinkedIn about whether we can enable traditional audit again in 23c with any parameter. I added a new blog - Re-Enable Traditional Audit in 23c - that showed how to do this. When I posted that I was sent a DM question / point on Twitter about the fact that Oracle do not support new audit settings anymore and it could be the case that you rely on traditional audit for a non-updated application that uses it and you use the parameter to enable it BUT maybe it doesn't fire in the database because Oracle are not maintaining traditional audit anymore. How would you know? in simple terms you wouldn't BUT if we have a migrated application that retains its traditional audit settings after the upgrade to 23c which Oracle state in the documentation that these settings will fire and will be honoured BUT update to Unified Audit as soon as you can. How can we rely on these settings firing and how does Oracle know what settings were enabled before the upgrade and honour them and how would it not honour ones you added by using the parameter to set?
I don't think there is a way to tell either case that Oracle would be interested in checking before firing. I think we have to assume that because traditional audit is de-supported Oracle will not fix issues; the traditional audit worked before 23c so it will probably work afterwards BUT you cannot rely on it. You must think about moving your traditional audit / standard audit to unified audit as soon as possible to be supported.
OK, so we have an old database that is migrated to 23c that had standard audit enabled and its firing now in 23c. We have decided that we must migrate to Unified Audit as soon as possible. This means at some point soon we should disable the traditional audit settings in the database. This brings up two points:
- We need to disable the standard audit from firing
- We need to migrate / or control the two audit trails at change over and remove the audit trail in SYS.AUD$ in 23c and then rely on Unified Audit
Let's have a look at 23c Free and see what we were given by Oracle by default. First check if we are in "pure" mode or mixed mode:
SQL> select * from v$option where parameter='Unified Auditing';
PARAMETER
----------------------------------------------------------------
VALUE CON_ID
---------------------------------------------------------------- ----------
Unified Auditing
TRUE 0
SQL>
So, the database is in PURE mode and Unified audit is enabled. From 12c until 23c you could run in MIXED MODE where standard audit would fire but also Unified Audit would fire or PURE mode where Unified Audit would work BUT the traditional audit settings would be ignored. So, in 23c if we have migrated Standard Audit settings the documentation states that they will fire BUT you cannot change them. This means 23c is different to earlier databases as PURE mode is now a sort of MIXED mode in 23c.
What are the standard audit settings:
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_allow_traditional_audit_configurati boolean FALSE
on
audit_file_dest string /opt/oracle/admin/FREE/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB
unified_audit_common_systemlog string
unified_audit_systemlog string
SQL>
So standard audit is enabled to write to the DB, no SYSDBA audit to the file system. Are there any standard audit settings enabled in 23c?
SQL> select count(*) from dba_priv_audit_opts;
COUNT(*)
----------
1
SQL> select count(*) from dba_stmt_audit_opts;
COUNT(*)
----------
1
SQL> select count(*) from dba_obj_audit_opts;
COUNT(*)
----------
19
SQL>
Yes, there are existing traditional audit settings in the PDB of 23c Free. What are they?
SQL> select *
2 from dba_priv_audit_opts
3 order by user_name;
USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
-------------------- -------------------- ------------------------------ --------- ---------
CREATE SESSION BY ACCESS BY ACCESS
SQL>
SQL> select *
2 from dba_stmt_audit_opts
3 order by user_name;
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE
-------------------- -------------------- ------------------------------ --------- ---------
CREATE SESSION BY ACCESS BY ACCESS
SQL>
SQL> select *
2 from dba_obj_audit_opts
3 order by owner;
OWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD EXE CRE REA WRI FBK
-------------------- ------------------------------ -------------------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
DVSYS MAC_POLICY$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS AUDIT_TRAIL$ TABLE A/A A/A A/A A/A A/A A/A -/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS CODE$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS MAC_POLICY_FACTOR$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS FACTOR$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS FACTOR_TYPE$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS COMMAND_RULE$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS FACTOR_LINK$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS ROLE$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS IDENTITY$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS IDENTITY_MAP$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS RULE$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS POLICY_LABEL$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS RULE_SET_RULE$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS RULE_SET$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS REALM_OBJECT$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS REALM_AUTH$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
DVSYS REALM$ TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
LBACSYS OLS$PROPS TABLE A/A A/A A/A A/A A/A A/A A/A -/- A/A -/A A/A -/- -/- -/- -/- -/-
SQL>
So we can play with CREATE SESSION. Also you will see above that the hidden parameter _allow_traditional_audit_configuration is set to FALSE so we cannot enable anymore standard audit without changing it.
Why would Oracle leave these settings in the PDB of 23c Free? - whats the significance; if this were 23c production should we be concerned? as these would be Oracles old settings not ours or part of an old application that was migrated into 23c. In production we would need to understand these and remove them or replace them with Unified Audit equivelent policies.
An interesting aside not directly related to this discussion is that the online docs for error messages shows error ORA-46502 which states:
ORA-46402: _ALLOW_TRADITIONAL_AUDIT_CONFIGURATION initialization parameter cannot be set for this database.
Cause: An attempt is made to set _ALLOW_TRADITIONAL_AUDIT_CONFIGURATION initialization parameter.
Action: Traditional Audit has been desupported. Traditional Audit configuration cannot be enabled for this database.
Params: n/a
Clearly in Part 2 we set this underscore parameter to allow traditional audit settings (rules) to be enabled again. This message reads like this parameter will be removed or disabled soon.
So, we have CREATE SESSION audit in standard audit enabled. Lets see how many records there are in AUD$ and truncate it:
SQL> select count(*) from sys.aud$;
COUNT(*)
----------
17
SQL> truncate table sys.aud$;
Table truncated.
SQL>
Now, let's connect to the database and see if this traditional audit works still:
SQL> connect orablog/orablog@//192.168.56.18:1521/freepdb1
Connected.
SQL> connect sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
Connected.
SQL> select count(*) from sys.aud$;
COUNT(*)
----------
2
SQL>
Get some details of these:
SQL> col timestamp for a30
SQL> col username for a10
SQL> col action_name for a10
SQL> select timestamp,username,action_name from dba_audit_trail;
TIMESTAMP USERNAME ACTION_NAM
------------------------------ ---------- ----------
21-AUG-23 ORABLOG LOGON
21-AUG-23 ORABLOG LOGOFF
SQL>
So, this traditional audit settings that we inherited in 23c does work. We logged in as ORABLOG and this is captured in the AUD$ traditional audit trail. So assuming we are simulating the case where standard audit is migrated into 23c then at some point soon we design and implement Unified Audit policies and we need to remove the standard audit. So we can try:
SQL> sho user
USER is "SYS"
SQL> noaudit create session;
Noaudit succeeded.
SQL> @hidden
_allow_traditional_audit_configuration FALSE FALSE
SQL>
The parameter to add new settings to Traditional Audit is not set and we seem to be able to disable the standard audit still in 23c. Lets check and make sure:
SQL> select * from dba_priv_audit_opts;
no rows selected
SQL> select * from dba_stmt_audit_opts;
no rows selected
SQL>
So, yes it has been removed.
This is good as it means we can migrate to 23c, keep traditional audit firing until we are ready and we are not forced to just disable standard audit by setting the audit_trail parameter and bouncing the database and turning off everything in one go; we can remove old audit step by step without resorting to hidden parameters
Well done Oracle on the noaudit working still
#oracleace #23c #oracle #database #security #traditional #standard #audit #audittrail
