Check your Unified Auditing Policies after upgrading to Oracle 19c

This may become a longer blog post. One of the customers I work with for many years contacted me a few weeks ago. After upgrade to 19c, it looked like as if some Unified Auditing Policies weren’t correct anymore. It started as a tiny issue, but thanks to the customer who analyzed down to the bones, it seems to be bigger than we thought at the beginning. So you may Check your Unified Auditing Policies after upgrading to Oracle 19c as well.

Photo by Felix Luo on Unsplash

Unified Auditing

Actually a lot of my customers use Unified Auditing these days. It is more powerful and less impacting (usually) compared to the old auditing. And since Oracle 12.2.0.1, it has even a working relational structure which allows you to query data. I added some useful links in the “Links” section at the end of this blog post.

Unified Auditing is enabled partially in mixed mode. But in order to unleash it completely, you need to link it into your kernel. It is not linked in by default.

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle

Then you can double-check once you started the database again:

SELECT PARAMETER, VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

PARAMETER           VALUE
------------------  ---------- 
Unified Auditing    TRUE

Database Setup

For a test, I use a 12.2.0.1 non-CDB database with the July 2020 RU applied.

At first, I will link Unified Auditing into my kernels and note down the current status for the standard policies. Afterwards I will add a few policies by myself and upgrade the database to Oracle 19.8.0. Once the upgrade is completed, I will compare before/after and check whether my policies are still in good shape and exactly the same as before.

I’m checking which policies are enabled by default:

SELECT policy_name, enabled_opt, user_name FROM audit_unified_enabled_policies

POLICY_NAME			 ENABLED USER_NAME
-------------------------------- ------- ------------------------------
ORA_SECURECONFIG		 BY	 ALL USERS
ORA_LOGON_FAILURES		 BY	 ALL USERS

By the way, the view changed in Oracle 19c – now the above query would be:

select POLICY_NAME, ENABLED_OPTION  FROM audit_unified_enabled_policies;

There is no USER_NAME column anymore, and the ENABLED_OPT column got renamed to ENABLED_OPTION.

In addition, I check the AUDIT_OPTIONS of these enabled standard policies before upgrade:

SET LINESIZE 200
SET PAGESIZE 300
COLUMN audit_option FORMAT A40
COLUMN condition_eval_opt FORMAT A10
COLUMN audit_condition FORMAT A50
COLUMN policy_name FORMAT A20

SELECT policy_name,
       audit_option,
       condition_eval_opt,
       audit_condition
FROM   audit_unified_policies
WHERE  policy_name in ('ORA_SECURECONFIG','ORA_LOGON_FAILURES')
ORDER BY 1,2;

POLICY_NAME	     AUDIT_OPTION			      CONDITION_ AUDIT_CONDITION
-------------------- ---------------------------------------- ---------- --------------------------------------------------
ORA_LOGON_FAILURES   LOGON				      NONE	 NONE
ORA_SECURECONFIG     ADMINISTER KEY MANAGEMENT		      NONE	 NONE
ORA_SECURECONFIG     ALTER ANY PROCEDURE		      NONE	 NONE
ORA_SECURECONFIG     ALTER ANY SQL TRANSLATION PROFILE	      NONE	 NONE
ORA_SECURECONFIG     ALTER ANY TABLE			      NONE	 NONE
ORA_SECURECONFIG     ALTER DATABASE			      NONE	 NONE
ORA_SECURECONFIG     ALTER DATABASE LINK		      NONE	 NONE
ORA_SECURECONFIG     ALTER PLUGGABLE DATABASE		      NONE	 NONE
ORA_SECURECONFIG     ALTER PROFILE			      NONE	 NONE
ORA_SECURECONFIG     ALTER ROLE 			      NONE	 NONE
ORA_SECURECONFIG     ALTER SYSTEM			      NONE	 NONE
ORA_SECURECONFIG     ALTER USER 			      NONE	 NONE
ORA_SECURECONFIG     AUDIT SYSTEM			      NONE	 NONE
ORA_SECURECONFIG     BECOME USER			      NONE	 NONE
ORA_SECURECONFIG     CREATE ANY JOB			      NONE	 NONE
ORA_SECURECONFIG     CREATE ANY LIBRARY 		      NONE	 NONE
ORA_SECURECONFIG     CREATE ANY PROCEDURE		      NONE	 NONE
ORA_SECURECONFIG     CREATE ANY SQL TRANSLATION PROFILE       NONE	 NONE
ORA_SECURECONFIG     CREATE ANY TABLE			      NONE	 NONE
ORA_SECURECONFIG     CREATE DATABASE LINK		      NONE	 NONE
ORA_SECURECONFIG     CREATE DIRECTORY			      NONE	 NONE
ORA_SECURECONFIG     CREATE EXTERNAL JOB		      NONE	 NONE
ORA_SECURECONFIG     CREATE PLUGGABLE DATABASE		      NONE	 NONE
ORA_SECURECONFIG     CREATE PROFILE			      NONE	 NONE
ORA_SECURECONFIG     CREATE PUBLIC SYNONYM		      NONE	 NONE
ORA_SECURECONFIG     CREATE ROLE			      NONE	 NONE
ORA_SECURECONFIG     CREATE SQL TRANSLATION PROFILE	      NONE	 NONE
ORA_SECURECONFIG     CREATE USER			      NONE	 NONE
ORA_SECURECONFIG     DROP ANY PROCEDURE 		      NONE	 NONE
ORA_SECURECONFIG     DROP ANY SQL TRANSLATION PROFILE	      NONE	 NONE
ORA_SECURECONFIG     DROP ANY TABLE			      NONE	 NONE
ORA_SECURECONFIG     DROP DATABASE LINK 		      NONE	 NONE
ORA_SECURECONFIG     DROP DIRECTORY			      NONE	 NONE
ORA_SECURECONFIG     DROP PLUGGABLE DATABASE		      NONE	 NONE
ORA_SECURECONFIG     DROP PROFILE			      NONE	 NONE
ORA_SECURECONFIG     DROP PUBLIC SYNONYM		      NONE	 NONE
ORA_SECURECONFIG     DROP ROLE				      NONE	 NONE
ORA_SECURECONFIG     DROP USER				      NONE	 NONE
ORA_SECURECONFIG     EXECUTE				      NONE	 NONE
ORA_SECURECONFIG     EXECUTE				      NONE	 NONE
ORA_SECURECONFIG     EXEMPT ACCESS POLICY		      NONE	 NONE
ORA_SECURECONFIG     EXEMPT REDACTION POLICY		      NONE	 NONE
ORA_SECURECONFIG     GRANT ANY OBJECT PRIVILEGE 	      NONE	 NONE
ORA_SECURECONFIG     GRANT ANY PRIVILEGE		      NONE	 NONE
ORA_SECURECONFIG     GRANT ANY ROLE			      NONE	 NONE
ORA_SECURECONFIG     LOGMINING				      NONE	 NONE
ORA_SECURECONFIG     PURGE DBA_RECYCLEBIN		      NONE	 NONE
ORA_SECURECONFIG     SET ROLE				      NONE	 NONE
ORA_SECURECONFIG     TRANSLATE ANY SQL			      NONE	 NONE

49 rows selected.

Additional Policies

For my test case I then create three additional policies and enable them:

CREATE AUDIT POLICY MIKE_DIRECT_LOAD Actions Component = DIRECT_LOAD ALL;
AUDIT POLICY MIKE_DIRECT_LOAD;

CREATE AUDIT POLICY MIKE_DATAPUMP Actions Component = DataPump ALL;
AUDIT POLICY MIKE_DATAPUMP;

CREATE AUDIT POLICY MIKE_PRIV_SELECTION 
 PRIVILEGES DROP ANY TABLE, DROP ANY CONTEXT, DROP ANY INDEX, DROP ANY LIBRARY;
AUDIT POLICY MIKE_PRIV_SELECTION;

This adds 6 rows to the above currently enabled AUDIT_OPTION list. Please take note as well on the AUDIT_OPTION_TYPE as this will be important later as well:

SET LINESIZE 200
COLUMN audit_option FORMAT A20
COLUMN condition_eval_opt FORMAT A10
COLUMN audit_condition FORMAT A50
COLUMN policy_name FORMAT A30
 
SELECT policy_name,
       audit_option,
       audit_option_type,
       condition_eval_opt,
       audit_condition
FROM   audit_unified_policies
WHERE  policy_name like 'MIKE%';

POLICY_NAME		       AUDIT_OPTION	    AUDIT_OPTION_TYPE  CONDITION_ AUDIT_CONDITION
------------------------------ -------------------- ------------------ ---------- --------------------------------------------------
MIKE_PRIV_SELECTION	       DROP ANY CONTEXT     SYSTEM PRIVILEGE   NONE	  NONE
MIKE_PRIV_SELECTION	       DROP ANY LIBRARY     SYSTEM PRIVILEGE   NONE	  NONE
MIKE_PRIV_SELECTION	       DROP ANY INDEX	    SYSTEM PRIVILEGE   NONE	  NONE
MIKE_PRIV_SELECTION	       DROP ANY TABLE	    SYSTEM PRIVILEGE   NONE	  NONE
MIKE_DIRECT_LOAD	       ALL		    DIRECT_LOAD ACTION NONE	  NONE
MIKE_DATAPUMP		       ALL		    DATAPUMP ACTION    NONE	  NONE

This looks exactly as expected.

Upgrading to Oracle Database 19.8.0

I quickly upgrade my database to Oracle 19.8.0. This takes roughly 20 minutes.

$ java -jar $OH19/rdbms/admin/autoupgrade.jar -config DB12.cfg -mode deploy
AutoUpgrade tool launched with default options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be processed
Type 'help' to list console commands

upg> lsj
+----+-------+---------+---------+-------+--------------+--------+------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME| UPDATED|     MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+------------+
| 100|   DB12|DBUPGRADE|EXECUTING|RUNNING|20/10/20 21:39|21:48:13|21%Upgraded |
+----+-------+---------+---------+-------+--------------+--------+------------+
Total jobs 1

Upgrade completed when this message appears:

upg> Job 100 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished successfully     [1]
Jobs failed                    [0]
Jobs pending                   [0]
------------- JOBS FINISHED SUCCESSFULLY -------------
Job 100 for DB12

Post Upgrade Audit Policy Check

The customer who alerted me found out that some audit policies had been changed to strange settings post upgrade. Hence, I will repeat the checks now in my environment for the policies I created.

First surprise after the upgrade – I receive an error where I don’t find any reason for:

select count(*), action_name, unified_audit_policies
 from cdb_unified_audit_trail
 group by action_name, unified_audit_policies
 order by 1 desc;

select count(*), action_name, unified_audit_policies
*
ERROR at line 1:
ORA-55940: An error occurred during execution of ktliIngestExternData.

The view does exist, the columns are named the same – and MOS does not show any hit. Hmmm … the “solution” of loading the spillover audit file information back into my database with exec DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES; failed as well.

SQL> exec DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES;
BEGIN DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES; END;

*
ERROR at line 1:
ORA-14300: partitioning key maps to a partition outside maximum permitted
number of partitions
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 5815
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 435
ORA-06512: at "AUDSYS.DBMS_AUDIT_MGMT", line 5797
ORA-06512: at line 1

But investigating this issue was not the purpose for my blog post. On Oct 23 you can read the solution here.

So back to the audit policies after upgrade. And here, I see the first unexpected surprise.

As I stored the results of my above query checking the policies in a table, I repeat I simple compare before/after with a MINUS query:

SQL> select * from UA_OPTION minus select * from UA_OPTION_2;

POLICY_NAME	     AUDIT_OPTION			      CONDITION_ AUDIT_CONDITION
-------------------- ---------------------------------------- ---------- --------------------------------------------------
MIKE_DIRECT_LOAD     ALL				      NONE	 NONE

Ouch!

Where is my policy MIKE_DIRECT_LOAD which existed before? It has disappeared after upgrade.

And another check reveals, that this is not the only issue. Even an AUDIT_OPTION_TYPE has been changed for my other policy:

SET LINESIZE 200
COLUMN audit_option FORMAT A20
COLUMN condition_eval_opt FORMAT A10
COLUMN audit_condition FORMAT A50
COLUMN policy_name FORMAT A30
 
SELECT policy_name,
       audit_option,
       audit_option_type,
       condition_eval_opt,
       audit_condition
FROM   audit_unified_policies
WHERE  policy_name like 'MIKE%';

POLICY_NAME	     AUDIT_OPTION	  AUDIT_OPTION_TYPE  CONDITION_ AUDIT_CONDITION
-------------------- -------------------- ------------------ ---------- --------------------------------------------------
MIKE_PRIV_SELECTION  DROP ANY CONTEXT	  SYSTEM PRIVILEGE   NONE	NONE
MIKE_PRIV_SELECTION  DROP ANY LIBRARY	  SYSTEM PRIVILEGE   NONE	NONE
MIKE_PRIV_SELECTION  DROP ANY INDEX	  SYSTEM PRIVILEGE   NONE	NONE
MIKE_PRIV_SELECTION  DROP ANY TABLE	  SYSTEM PRIVILEGE   NONE	NONE
MIKE_DATAPUMP	     ALL		  OLS ACTION	     NONE	NONE

The MIKE_DATAPUMP policy has been changed to “OLS ACTION“. It was “DATAPUMP ACTION” before. Yes, I have Oracle Label Security installed in this database. But this should not change my MIKE_DATAPUMP policy, right?

What is the issue?

My colleagues from the audit team quickly diagnosed the issue as (unpublished) bug 29233186.

A fix should be available soon but is neither included in the October 2020 RU nor is it available via a one-off fix yet.

The bug offers a script to fix the issue. And we tried it out. The script indeed fixes the broken policy in the customer’s and in my case, and even brings back the missing ones.

COLUMN condition_eval_opt FORMAT A10
COLUMN audit_condition FORMAT A50
COLUMN policy_name FORMAT A30

SELECT policy_name,
       audit_option,
       audit_option_type,
       condition_eval_opt,
       audit_condition
FROM   audit_unified_policies
WHERE  policy_name like 'MIKE%';

POLICY_NAME		       AUDIT_OPTION	    AUDIT_OPTION_TYPE  CONDITION_ AUDIT_CONDITION
------------------------------ -------------------- ------------------ ---------- --------------------------------------------------
MIKE_PRIV_SELECTION	       DROP ANY CONTEXT     SYSTEM PRIVILEGE   NONE	  NONE
MIKE_PRIV_SELECTION	       DROP ANY LIBRARY     SYSTEM PRIVILEGE   NONE	  NONE
MIKE_PRIV_SELECTION	       DROP ANY INDEX	    SYSTEM PRIVILEGE   NONE	  NONE
MIKE_PRIV_SELECTION	       DROP ANY TABLE	    SYSTEM PRIVILEGE   NONE	  NONE
MIKE_DIRECT_LOAD	       ALL		    DIRECT_LOAD ACTION NONE	  NONE
MIKE_DATAPUMP		       ALL		    DATAPUMP ACTION    NONE	  NONE

6 rows selected.

Hurray! My policies are both back and in shape!

But … the script has a side effect which is not visible at first sight. But thanks to Christian, the DBA at the customer, the script seems to change the ORA_SECURECONFIG policy as well. And this tiny little change leads to a dramatic increase of audit records in a very short amount of time. Hence, the interim fix needs a fix as well I’d guess.

Here is what happened – and the little addition of ALL as AUDIT_OPTION has a massive impact now. If the ORA_SECURECONFIG policy is on, ALL (aka every action) gets audited in my understanding. I therefore marked it in RED below:

ORA_SECURECONFIG 12.2.0.1	ORA_SECURECONFIG 19.8.0	ORA_SECURECONFIG 19.8.0 after bug fix script
AUDIT_OPTION —————————————- ADMINISTER KEY MANAGEMENT ALTER ANY PROCEDURE ALTER ANY SQL TRANSLATION PROFILE ALTER ANY TABLE ALTER DATABASE ALTER DATABASE LINK ALTER PLUGGABLE DATABASE ALTER PROFILE ALTER ROLE ALTER SYSTEM ALTER USER AUDIT SYSTEM BECOME USER CREATE ANY JOB CREATE ANY LIBRARY CREATE ANY PROCEDURE CREATE ANY SQL TRANSLATION PROFILE CREATE ANY TABLE CREATE DATABASE LINK CREATE DIRECTORY CREATE EXTERNAL JOB CREATE PLUGGABLE DATABASE CREATE PROFILE CREATE PUBLIC SYNONYM CREATE ROLE CREATE SQL TRANSLATION PROFILE CREATE USER DROP ANY PROCEDURE DROP ANY SQL TRANSLATION PROFILE DROP ANY TABLE DROP DATABASE LINK DROP DIRECTORY DROP PLUGGABLE DATABASE DROP PROFILE DROP PUBLIC SYNONYM DROP ROLE DROP USER EXECUTE EXECUTE EXEMPT ACCESS POLICY EXEMPT REDACTION POLICY GRANT ANY OBJECT PRIVILEGE GRANT ANY PRIVILEGE GRANT ANY ROLE LOGMINING PURGE DBA_RECYCLEBIN SET ROLE TRANSLATE ANY SQL	AUDIT_OPTION —————————————- ADMINISTER KEY MANAGEMENT ALTER ANY PROCEDURE ALTER ANY SQL TRANSLATION PROFILE ALTER ANY TABLE ALTER DATABASE ALTER DATABASE DICTIONARY ALTER DATABASE LINK ALTER PLUGGABLE DATABASE ALTER PROFILE ALTER ROLE ALTER SYSTEM ALTER USER AUDIT SYSTEM BECOME USER CREATE ANY JOB CREATE ANY LIBRARY CREATE ANY PROCEDURE CREATE ANY SQL TRANSLATION PROFILE CREATE ANY TABLE CREATE DATABASE LINK CREATE DIRECTORY CREATE EXTERNAL JOB CREATE PLUGGABLE DATABASE CREATE PROFILE CREATE PUBLIC SYNONYM CREATE ROLE CREATE SQL TRANSLATION PROFILE CREATE USER DROP ANY PROCEDURE DROP ANY SQL TRANSLATION PROFILE DROP ANY TABLE DROP DATABASE LINK DROP DIRECTORY DROP PLUGGABLE DATABASE DROP PROFILE DROP PUBLIC SYNONYM DROP ROLE DROP USER EXECUTE EXECUTE EXEMPT ACCESS POLICY EXEMPT REDACTION POLICY GRANT ANY OBJECT PRIVILEGE GRANT ANY PRIVILEGE GRANT ANY ROLE LOGMINING PURGE DBA_RECYCLEBIN SET ROLE TRANSLATE ANY SQL	AUDIT_OPTION —————————————- ADMINISTER KEY MANAGEMENT ALL ALTER ANY PROCEDURE ALTER ANY SQL TRANSLATION PROFILE ALTER ANY TABLE ALTER DATABASE ALTER DATABASE LINK ALTER PLUGGABLE DATABASE ALTER PROFILE ALTER ROLE ALTER SYSTEM ALTER USER AUDIT SYSTEM BECOME USER CREATE ANY JOB CREATE ANY LIBRARY CREATE ANY PROCEDURE CREATE ANY SQL TRANSLATION PROFILE CREATE ANY TABLE CREATE DATABASE LINK CREATE DIRECTORY CREATE EXTERNAL JOB CREATE PLUGGABLE DATABASE CREATE PROFILE CREATE PUBLIC SYNONYM CREATE ROLE CREATE SQL TRANSLATION PROFILE CREATE USER DROP ANY PROCEDURE DROP ANY SQL TRANSLATION PROFILE DROP ANY TABLE DROP DATABASE LINK DROP DIRECTORY DROP PLUGGABLE DATABASE DROP PROFILE DROP PUBLIC SYNONYM DROP ROLE DROP USER EXECUTE EXECUTE EXEMPT ACCESS POLICY EXEMPT REDACTION POLICY GRANT ANY OBJECT PRIVILEGE GRANT ANY PRIVILEGE GRANT ANY ROLE LOGMINING PURGE DBA_RECYCLEBIN SET ROLE TRANSLATE ANY SQL

So, after upgrading to Oracle Database 19c (19.8.0), the default auditing policy ORA_SECURECONFIG has gotten one additional AUDIT_OPTION: ALTER DATABASE DICTIONARY. This sounds ok to me.

But as you can see in the 3rd column, after invoking the supposed-to-fix script, it gets changed to ALL. Which is not so good as my customer recognized within a few days. Am additional whooping 500,000 extra audit records got written within the next few days compared to the system average behavior before. My guess is that the script does not take into account that there may have been new options added to the default policy. And hence It changes the new (correct) AUDIT_OPTION ALTER DATABASE DICTIONARY as well – and to the worst possible value: ALL.

So please stay tuned – and if you see similar issues in your environments, you may need to open an SR to get this checked by Oracle Support.

Summary

As far as I understand, a necessary change to self-defined Unified Auditing Policies is missing in the upgrade when you go to Oracle Database 19c. I guess, this applies not only to Oracle 19.8.0 but to every Oracle 19c database environment. I haven’t checked this with 18c yet. But it may be there, too. This has been identified as a known issue, bug 29233186.

You may please check your own environment in case you enabled self-defined policies – and ideally compare them to the state “before upgrade” as we did in this particular case. Please get in touch with Oracle Support in case you find similar fuzziness in your database(s).

Additional Testing

I did check also the other standard Oracle policies as I was curious about the changes from 12.2.0.1 to 19c, with and without the script:

ORA_ACCOUNT_MGMT remains unchanged
ORA_CIS_RECOMMENDATIONS gets these additional AUDIT_OPTIONS but is not affected by the script execution
- ALTER FUNCTION
- ALTER PACKAGE
- ALTER PACKAGE BODY
- ALTER SYNONYM
- ALTER SYSTEM
- ALTER TRIGGER
- CREATE FUNCTION
- CREATE PACKAGE
- CREATE PACKAGE BODY
- CREATE TRIGGER
- DROP FUNCTION
- DROP PACKAGE
- DROP PACKAGE BODY
- DROP TRIGGER
- In addition, CREATE ANY LIBRARY and CREATE ANY TRIGGER get removed
ORA_DATABASE_PARAMETER remains unchanged
ORA_LOGON_FAILURES remains unchanged
ORA_RAS_POLICY_MGMT sees plenty of changes and is affected by the execution of the script. These options get newly added:
- ALL
- ALTER INMEMORY JOIN GROUP
- CREATE NAMESPACE ATTRIBUTE
- DROP INMEMORY JOIN GROUP
- GET NAMESPACE ATTRIBUTE
- SET NAMESPACE ATTRIBUTE
- SET USER VERIFIER
- In addition, ADD GLOBAL CALLBACK, DELETE GLOBAL CALLBACK, ENABLE GLOBAL CALLBACK, GRANT SYSTEM PRIVILEGE, REVOKE SYSTEM PRIVILEGE and SET USER PROFILE get removed.
- Very important: The script’s execution changes all this back to the status of 12.2.0.1.
ORA_RAS_SESSION_MGMT sees also plenty of changes and is affected by the execution of the script, too. These options get newly added:
- ADD GLOBAL CALLBACK
- DELETE GLOBAL CALLBACK
- DISABLE DATA SECURITY
- ENABLE GLOBAL CALLBACK
- In addition, CREATE NAMESPACE ATTRIBUTE, DELETE NAMESPACE ATTRIBUTE, GET NAMESPACE ATTRIBUTE and SET NAMESPACE ATTRIBUTE get removed and added to the ORA_RAS_POLICY_MGMT policy as you can see above.
- Very important again: The script’s execution changes all this back to the status of 12.2.0.1.

Hence, I can’t tell you whether the policies are correct after upgrade and get incorrectly changed back by the script. Or if the script does the right thing changing the policies back to the 12.2.0.1. state. Again, as mentioned above, if you need to find out further details, please check back with Oracle Support. I just would like to point out that you may please double-check if the script has been given to you via an SR.

Further Information and Links

–Mike

Tags: 19c Action Auditing AutoUpgrade Check Database Migration oracle 19c Oracle Database 19c Policies Unified Auditing upgrade

Joseph Amalraj says:

October 26, 2020 at 19:49

Hi Mike,

Thanks for your wonderful posts.

Is it possible to run autoupgrade in nohup mode?

Joseph

Reply
- Mike.Dietrich says:
  
  October 27, 2020 at 18:59
  
  See here:
  nohup java -jar $OH19/rdbms/admin/autoupgrade.jar -config DB12.cfg -mode deploy -noconsole &
  
  Then use this technique to monitor the progress:
  https://mikedietrichde.com/2020/07/16/autoupgrade-refresh-status-information-automatically/
  
  Cheers,
  Mike
  
  Reply
  - Joseph Amalraj says:
    
    October 27, 2020 at 19:07
    
    That was very useful. Thanks a lot, Mike.
    
    Reply
Reyaz says:

February 1, 2021 at 11:43

Hi Mike,

I want to create an policy which audits all the database objects for the statements:

DDL Statements: CREATE, ALTER, DROP,REPLACE,TRUNCATE

DML Statements: INSERT,UPDATE,DELETE

Do I have to specify this for each database object sperately , for example:

CREATE TABLE. CREATE TRIGGER, CREATE ANY PROCEDURE ? Or is there a more simple way to do this ?

Hope you can point me in the right direction.

Regards,

Reyaz

Reply
- Mike.Dietrich says:
  
  February 7, 2021 at 21:53
  
  Hi Reyaz,
  
  not exactly sure. Have you check the Unified Auditing documentation?
  
  Cheers,
  Mike
  
  Reply
Martin Decker says:

September 21, 2021 at 07:16

Today, patch 31562484 AUDIT OPTION CHANGED TO “ALTER DATABASE DICTIONARY” FROM “ALL” AFTER UPGRADE FROM 12C TO 19C. was released for 19.12.0 on Linux x86-64.

Reply
- Mike.Dietrich says:
  
  September 26, 2021 at 21:17
  
  Thanks Martin!
  
  Cheers,
  Mike
  
  Reply
Basu Navindgi says:

April 3, 2023 at 09:56

Hi Mike,

We have 19c database, and unified_auditing is enabled and I am seeing currently that for the LOGON actions, only the login failures are being captured. What do I do to capture the successful logins? this could be very useful to see who all have logged in successfully using the OS user name and Machine for auditing purpose.

Best Regards
Basu

Reply
- Mike.Dietrich says:
  
  May 16, 2023 at 15:24
  
  Hi Basu,
  
  I won’t recommend monitoring successful logons since you can easily get a huge amount of data.
  If you want that, you can change the standard policy to monitor successful logons as well.
  
  Cheers,
  Mike
  
  Reply
Dieter Henig says:

April 19, 2023 at 15:56

Dear Mike,

again i read you blog and I really like your pragmatic and helpful way to explain things.
Now I have a question about unified auditing in 21c.
As the traditional auditing is deprecated, I was astonished to find again plenty of aud-files in $ORACLE_BASE/admin/$SID/adump (and the subdirs for the PDBs).

First I checked the DB parameter ‘audit_trail’, but is set to ‘NONE’
next I thought that I would need to run “make -f ins_rdbms.mk uniaud_on ioracle” as in Oracle 19c
but it responded with: make: *** No rule to make target ‘/opt/oracle/product/21c/dbhomeXE/rdbms/lib/kzaiang.o’, needed by ‘uniaud_on’. Stop.”

Could you explain why we still get adump filled by aud-files while table AUD$UNIFIED gets populated as expected?

best regards
Dieter

Reply
- Mike.Dietrich says:
  
  May 7, 2023 at 22:43
  
  Hi Dieter,
  
  this may be an 21c anomaly. Can you check this with Support please?
  I think there were issues with so called “overspill” files if I remember correctly.
  
  Cheers,
  Mike
  
  Reply

Check your Unified Auditing Policies after upgrading to Oracle 19c

Unified Auditing

Database Setup

Additional Policies

Upgrading to Oracle Database 19.8.0

Post Upgrade Audit Policy Check

What is the issue?

Summary

Additional Testing

Further Information and Links

Related

You may also like...

11 Responses

Leave a Reply Cancel reply

Archives

Categories

Upgrade @YouTube

RSS Feed for Upgrade your Database – NOW!

Subscribe via Email

Follow me on Twitter

Recent Posts

Unified Auditing

Database Setup

Additional Policies

Upgrading to Oracle Database 19.8.0

Post Upgrade Audit Policy Check

What is the issue?

Summary

Additional Testing

Further Information and Links

Related

You may also like...

Database Migration from non-CDB to PDB – Plug in, upgrade, convert

“Mr. Upgrade” on DOAG.TV

Migrating Flashback Data Archive (FDA) tables

11 Responses

Leave a Reply Cancel reply

Archives

Categories

Upgrade @YouTube

RSS Feed for Upgrade your Database – NOW!

Subscribe via Email

Follow me on Twitter

Recent Posts