Configuring Oracle Database for Auditing.

To enable Unified Auditing in Oracle Database 12 and 18 on Oracle Linux I did this:

export ORACLE_SID=ORCLCDB
cd $ORACLE_HOME/bin
./sqlplus sys as sysdba
SHUTDOWN IMMEDIATE
EXIT
./lsnrctl stop
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle
cd $ORACLE_HOME/bin
./lsnrctl start
./sqlplus sys as sysdba
STARTUP

After that the following query

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

returned TRUE. And the following query shows the list of enabled audit options:

col AUDIT_OPTION format A30
SELECT up.AUDIT_OPTION, uep.SUCCESS, uep.FAILURE from AUDIT_UNIFIED_ENABLED_POLICIES uep, AUDIT_UNIFIED_POLICIES up 
where uep.USER_NAME = 'ALL USERS' and uep.ENABLED_OPT='BY USER' and uep.POLICY_NAME = up.POLICY_NAME and up.AUDIT_OPTION_TYPE = 'STANDARD ACTION'

or in Oracle 19c (see Oracle 19c Audit changes):

col AUDIT_OPTION format A30
SELECT up.AUDIT_OPTION, uep.SUCCESS, uep.FAILURE from AUDIT_UNIFIED_ENABLED_POLICIES uep, AUDIT_UNIFIED_POLICIES up 
where uep.ENTITY_NAME = 'ALL USERS' and uep.ENABLED_OPTION='BY USER' and uep.POLICY_NAME = up.POLICY_NAME and up.AUDIT_OPTION_TYPE = 'STANDARD ACTION'
AUDIT_OPTION                   SUC FAI
------------------------------ --- ---
LOGON                          NO  YES
CREATE DATABASE LINK           YES YES
DROP DATABASE LINK             YES YES
ALTER USER                     YES YES
CREATE ROLE                    YES YES
DROP ROLE                      YES YES
SET ROLE                       YES YES
CREATE PROFILE                 YES YES
DROP PROFILE                   YES YES
ALTER PROFILE                  YES YES
ALTER ROLE                     YES YES

AUDIT_OPTION                   SUC FAI
------------------------------ --- ---
CREATE DIRECTORY               YES YES
DROP DIRECTORY                 YES YES
ALTER DATABASE LINK            YES YES
CREATE PLUGGABLE DATABASE      YES YES
ALTER PLUGGABLE DATABASE       YES YES
DROP PLUGGABLE DATABASE        YES YES
CREATE TABLE                   YES YES
DROP TABLE                     YES YES
ALTER TABLE                    YES YES
CREATE VIEW                    YES YES
DROP VIEW                      YES YES

AUDIT_OPTION                   SUC FAI
------------------------------ --- ---
CREATE PROCEDURE               YES YES
ALTER PROCEDURE                YES YES
RENAME                         YES YES
ALTER DATABASE                 YES YES
ALTER USER                     YES YES
ALTER SYSTEM                   YES YES
CREATE USER                    YES YES
CREATE ROLE                    YES YES
DROP USER                      YES YES
DROP ROLE                      YES YES
SET ROLE                       YES YES

AUDIT_OPTION                   SUC FAI
------------------------------ --- ---
CREATE TRIGGER                 YES YES
ALTER TRIGGER                  YES YES
DROP TRIGGER                   YES YES
CREATE PROFILE                 YES YES
DROP PROFILE                   YES YES
ALTER PROFILE                  YES YES
DROP PROCEDURE                 YES YES
CREATE MATERIALIZED VIEW       YES YES
DROP MATERIALIZED VIEW         YES YES
ALTER ROLE                     YES YES
TRUNCATE TABLE                 YES YES

AUDIT_OPTION                   SUC FAI
------------------------------ --- ---
CREATE FUNCTION                YES YES
ALTER FUNCTION                 YES YES
DROP FUNCTION                  YES YES
CREATE PACKAGE                 YES YES
ALTER PACKAGE                  YES YES
DROP PACKAGE                   YES YES
CREATE PACKAGE BODY            YES YES
ALTER PACKAGE BODY             YES YES
DROP PACKAGE BODY              YES YES
CREATE DIRECTORY               YES YES
DROP DIRECTORY                 YES YES

AUDIT_OPTION                   SUC FAI
------------------------------ --- ---
CREATE JAVA                    YES YES
ALTER JAVA                     YES YES
DROP JAVA                      YES YES
PURGE TABLE                    YES YES
CREATE FLASHBACK ARCHIVE       YES YES
ALTER FLASHBACK ARCHIVE        YES YES
DROP FLASHBACK ARCHIVE         YES YES
CREATE PLUGGABLE DATABASE      YES YES
ALTER PLUGGABLE DATABASE       YES YES
DROP PLUGGABLE DATABASE        YES YES
CREATE AUDIT POLICY            YES YES

AUDIT_OPTION                   SUC FAI
------------------------------ --- ---
ALTER AUDIT POLICY             YES YES
DROP AUDIT POLICY              YES YES
GRANT                          YES YES
REVOKE                         YES YES
AUDIT                          YES YES
NOAUDIT                        YES YES
LOGON                          YES YES
LOGOFF                         YES YES

74 rows selected. 

The query to show enabled audit policies:

SELECT uep.POLICY_NAME from AUDIT_UNIFIED_ENABLED_POLICIES uep;

Disabling Unified Auditing:

make -f ins_rdbms.mk uniaud_off ioracle

To determine specific policies the following can be added to the query, for example:

... and up.AUDIT_OPTION in ('ALL', 'GRANT', 'REVOKE')

Also I enabled old auditing with the following commands:

export ORACLE_SID=ORCLCDB
cd $ORACLE_HOME/bin
./sqlplus sys as sysdba
ALTER SYSTEM SET audit_sys_operations=true SCOPE=spfile;
ALTER SYSTEM SET audit_trail=DB SCOPE=SPFILE;
commit;
SHUTDOWN IMMEDIATE
STARTUP

Checking if old audit has been enabled:

SELECT VALUE FROM V$PARAMETER WHERE NAME='audit_trail';
show parameter audit
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /opt/oracle/admin/ORCLCDB/adum
                                                 p
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string
audit_trail                          string      DB
unified_audit_sga_queue_size         integer     1048576
unified_audit_systemlog              string

Switching old auditing off:

ALTER SYSTEM SET audit_sys_operations=false SCOPE=spfile;
ALTER SYSTEM SET audit_trail=NONE SCOPE=SPFILE;

If audit_trail is set to XML or XML,EXTENDED the audit records are written in XML format to $ORACLE_BASE/admin/$ORACLE_SID/adump directory where $ORACLE_SID is “ORCLCDB”.

Links:

3 Responses to Configuring Oracle Database for Auditing.

  1. dmitriano says:

    Oracle Database Sample Schemas: https://github.com/oracle/db-sample-schemas

  2. dmitriano says:

    Old audit:

    AUDIT ALTER SYSTEM,SYSTEM AUDIT,SESSION,TABLE,USER, …
    AUDIT ALTER DATABASE, FLASHBACK ARCHIVE ADMINISTER;
    AUDIT SYSTEM GRANT, SESSION, TABLE, PROCEDURE BY ;

  3. guest says:

    also it can be:
    ALTER SYSTEM SET audit_trail=DB,Extended SCOPE=SPFILE;

Leave a Reply

Your email address will not be published. Required fields are marked *