Unified Auditing in Oracle 18c

Oracle database Unified Audit Trail was introduced in Oracle 12cR1 , as a mechanism to unify different oracle database audits (based on different features) under one view. As you may know “mixed mode” auditing is enabled by default starting with 12cR1 release. One of the limitations when switching from “standard auditing” to “unified auditing” in both Oracle 12cR1 and 12cR2 is you can’t push audits to syslog anymore. This has changed in Oracle 18c, you can push audits to SYSLOG in Unix/Linux OS and to windows event log.

A new init parameter has been introduced “unified_audit_systemlog”

https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/UNIFIED_AUDIT_SYSTEMLOG.html#GUID-C4E65709-0FE3-4945-8F39-7B0867E2494A

Picture1

In window OS I have set the parameter as TRUE as shown below:

Picture2

For simulation through RMAN I have executed command to take controlfile backup then check the windows event log:

 

Picture3.png

Another new feature in 18c , is the ability to export and import unified audit trail !

Command to export:

expdp system/XXXXXXXX full=y directory=DUMP_DIR logfile=exp_unified18c_log.log dumpfile=exp_unified18c.dmp INCLUDE=AUDIT_TRAILS

Picture4

 

 

 

 

 

 

 

 

 

USE GRANT READ instead of GRANT SELECT in Oracle 12c

Normally when we want to grant an oracle database account access to read records form certain tables, we use the SQL command (GRANT SELECT), however this is found to be not the best security practice. And, new security feature has been introduced in Oracle 12c which is GRANT READ.

To illustrate more,

I have created a dummy account named “dummy_test” with the following basic privileges:

dummy account

And created a dummy table with random values called “DUMMY_RECORDS”, and executed the below SQL statement to grant the user access to read records from the table:

SQL> grant select on DUMMY_RECORDS to dummy_test ;

Now….the interesting part is the following…..i will be able to exclusively LOCK the table !!!

either by executing the following:

SQL> lock table DUMMY_RECORDS in exclusive mode;

OR

SQL> select * from DUMMY_RECORDS for update;

exclusive lock.jpg

Now, let us revoke (GRANT SELECT) and use (GRANT READ) on the table

grant read 1

grant read 2

as shown above, after logging with the account we were not able to exclusively  lock the table and ORA-01031 was thrown.

Important Remarks:

  • this security feature is only available in 12c release.

 

  • some applications could frequently use (select* from table for update) frequently so you need to test the consequences of using the GRANT READ permission.

 

  • the purpose of this security feature is that it will prevent the hacker who stole the credentials of the account to lock the table which will block transactions and impact the running the application ! (denial of service)