Pages

Sunday, October 11, 2015

Oracle Audit Options

===========================
General
===========================
There are four types of Audit:
1. System Audit - for Administrative Logins.
2. Standard Audit - AKA AUDIT_TRAIL
3. Fine Grain Auditing

Where Are Standard Audit Activities Recorded?
Option 1. - in data dictionary table, AKA Database Audit Trail
Option 2. - in operating system files, AKA Operating System Audit Trail. 


===========================
Audit Tables
===========================
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL

===========================
Audit Parameters
===========================
AUDIT_TRAIL
AUDIT_SYS_OPERATIONS
AUDIT_FILE_DEST 

SHOW PARAMETER AUDIT

NAME                   TYPE        VALUE
---------------------- ----------- ------------------------------
audit_file_dest        string      C:\ORACLE\PRODUCT\10.2.0\ADMIN\DB10G\ADUMP
audit_sys_operations   boolean     FALSE
audit_trail            string      NONE


AUDIT_SYS_OPERATIONS
Enable and disable SYS auditing - i.e. enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. 
All AUDIT_SYS_OPERATIONS audit records are written to the OS audit trail.

AUDIT_FILE_DEST
Control the destination for OS audit files.
By default it points to $ORACLE_BASE/admin/$ORACLE_SID/adump/

OS Audit is generated by:
A. The mandatory auditing specified by the AUDIT_SYS_OPERATIONS parameter.
B. The optional auditing enabled by AUDIT_TRAIL when the os, xml and xml,extended options are used


AUDIT_TRAIL

AUDIT_TRAIL  can have these values:
{
none | os | db | db,extended | xml | xml,extended }

NONE - No Auditing.

DB -   Enables Auditing and directs audit records to SYS.AUD$

DB,EXTENDED - Same as DB, plus populates SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table

OS - Enables Auditing and directs audit records to an operating system file.       
     AUDIT_FILE_DEST control the directory for these audit files.
     Default value is $ORACLE_BASE/admin/$ORACLE_SID/adump/.

XML - Same as OS, only writes to the operating system audit record file in XML format.

XML,EXTENDED - Same as DB,EXTENDED only to OS file.

Default value:
When creating the database via CLI it is NONE.
But when creating the Database via Database Configuration Assistant (DBCA) the default value is
DB.

Changing AUDIT_TRAIL value
Changing AUDIT_TRAIL value by example.

After change, need to restart Instance, so the change would take effect.
SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
System altered.
SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.
Total System Global Area  289406976 bytes
Fixed Size                  1248600 bytes
Variable Size              71303848 bytes
Database Buffers          213909504 bytes
Redo Buffers                2945024 bytes
Database mounted.

Database opened.

===========================
How to setup Audit
===========================
CONNECT sys/password AS SYSDBA

AUDIT ALL BY some_user BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY some_user BY ACCESS;
AUDIT EXECUTE PROCEDURE BY some_user BY ACCESS;

AUDIT DELETE ANY TABLE BY ACCESS WHENEVER NOT SUCCESSFUL;


===========================
login as sysdba
===========================
Logins as sysdba are audited always, even if AUDIT_TRAIL  is set to NONE.

The login would generate a single file under $audit_file_dest.

For example:

root@my_server:/software/oracle/admin/igt/adump>% less igt_ora_29998_20181127060021262058143795.aud 
Audit file /software/oracle/admin/igt/adump/igt_ora_29998_20181127060021262058143795.aud
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
ORACLE_HOME = /software/oracle/112
System name:    Linux
Node name:      esp-tel-1-dbu-1
Release:        2.6.32-431.el6.x86_64
Version:        #1 SMP Sun Nov 10 22:19:54 EST 2013
Machine:        x86_64
Instance name: igt
Redo thread mounted by this instance: 1
Oracle process number: 667
Unix process pid: 29998, image: oracle@esp-tel-1-dbu-1 (TNS V1-V3)

Tue Nov 27 06:00:21 2018 +00:00
LENGTH : '155'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[0] ''
STATUS:[1] '0'

DBID:[10] '1066039690'


===========================
View Audit Trail
===========================
The audit trail is stored in the SYS.AUD$ table. 
Its contents can be viewed directly or via the following views:
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS

The main ones are:
DBA_AUDIT_TRAILStandard auditing
DBA_AUDIT_TRAIL -> Synonym to SYS.DBA_AUDIT_TRAIL view
SYS.DBA_AUDIT_TRAIL view => SELECT ... from SYS.AUD$

DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$).
DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.



===========================
FGA - Fine Grain Auditing
===========================
Fine grained auditing extends Oracle standard auditing capabilities by allowing the user to audit actions based on user-defined predicates. 
It is independent of the AUDIT_TRAIL parameter.
All audit records are stored in the FGA_LOG$ table (DBA_FGA_AUDIT_TRAIL) , rather than the AUD$(DBA_AUDIT_TRAIL) table.


DBMS_FGA Package
FGA is set via DBMS_FGA package.
DBMS_FGA package contains the following procedures:
ADD_POLICY
DROP_POLICY
ENABLE_POLICY
DISABLE_POLICY

Example A.
BEGIN
  DBMS_FGA.add_policy(
    object_schema   => 'SOME_USER',
    object_name     => 'SOME_USER',
    policy_name     => 'SALARY_CHK_AUDIT',
    audit_condition => 'SAL > 50000',
    audit_column    => 'SAL',
    statement_types => 'SELECT,INSERT,UPDATE,DELETE');
END;
/

Example B.
BEGIN
  DBMS_FGA.add_policy(
    object_schema   => 'SOME_USER',
    object_name     => 'SOME_USER',
    policy_name     => 'SALARY_CHK_AUDIT',
    audit_condition => 'SAL > 50000',
    audit_column    => 'SAL',
    handler_schema  => 'AUDIT_TEST',
    handler_module  => 'FIRE_CLERK', --Audit logic goes here
    enable          => TRUE);
END;
/

===========================
FGA_LOG$ Table
===========================
SYS.FGA_LOG$ table is never cleaned up!

Even when backing up data, and deleting old entries, the table storage space continues to grow, as with any other table with frequent INSERT and DELETE statements.


Since the owner is SYS, the table is stored in SYSTEM Tablespace.
This can result in table growing up, until it uses all of the SYSTEM Tablespace!!!

The solution is to manually execute
TRUNCATE TABLE 
FGA_LOG$ after backing up the Audit Trail data.


===========================
SYS.AUDIT_ACTIONS Table
===========================
SYS.AUDIT_ACTIONS describes audit trail action type codes. 
These values also appear in V$SESSION.command column.
This table maps action type numbers to action type names.

SELECT * FROM SYS.AUDIT_ACTIONS;
    ACTION NAME
---------- ------------------------------
         0 UNKNOWN
         1 CREATE TABLE
         2 INSERT
         3 SELECT
         4 CREATE CLUSTER
         5 ALTER CLUSTER
         6 UPDATE
         7 DELETE
         8 DROP CLUSTER
         9 CREATE INDEX
        10 DROP INDEX
        11 ALTER INDEX
        12 DROP TABLE
        13 CREATE SEQUENCE
        14 ALTER SEQUENCE
        15 ALTER TABLE
        16 DROP SEQUENCE
        17 GRANT OBJECT
        18 REVOKE OBJECT
        19 CREATE SYNONYM
        20 DROP SYNONYM
        21 CREATE VIEW
        22 DROP VIEW
        23 VALIDATE INDEX
        24 CREATE PROCEDURE
        25 ALTER PROCEDURE
        26 LOCK
        27 NO-OP
        28 RENAME
        29 COMMENT
        30 AUDIT OBJECT
        31 NOAUDIT OBJECT
        32 CREATE DATABASE LINK
        33 DROP DATABASE LINK
        34 CREATE DATABASE
        35 ALTER DATABASE
        36 CREATE ROLLBACK SEG
        37 ALTER ROLLBACK SEG
        38 DROP ROLLBACK SEG
        39 CREATE TABLESPACE
        40 ALTER TABLESPACE
        41 DROP TABLESPACE
        42 ALTER SESSION
        43 ALTER USER
        44 COMMIT
        45 ROLLBACK
        46 SAVEPOINT
        47 PL/SQL EXECUTE
        48 SET TRANSACTION
        49 ALTER SYSTEM
        50 EXPLAIN
        51 CREATE USER
        52 CREATE ROLE
        53 DROP USER
        54 DROP ROLE
        55 SET ROLE
        56 CREATE SCHEMA
        57 CREATE CONTROL FILE
        59 CREATE TRIGGER
        60 ALTER TRIGGER
        61 DROP TRIGGER
        62 ANALYZE TABLE
        63 ANALYZE INDEX
        64 ANALYZE CLUSTER
        65 CREATE PROFILE
        66 DROP PROFILE
        67 ALTER PROFILE
        68 DROP PROCEDURE
        70 ALTER RESOURCE COST
        71 CREATE MATERIALIZED VIEW LOG
        72 ALTER MATERIALIZED VIEW LOG
        73 DROP MATERIALIZED VIEW LOG
        74 CREATE MATERIALIZED VIEW
        75 ALTER MATERIALIZED VIEW
        76 DROP MATERIALIZED VIEW
        77 CREATE TYPE
        78 DROP TYPE
        79 ALTER ROLE
        80 ALTER TYPE
        81 CREATE TYPE BODY
        82 ALTER TYPE BODY
        83 DROP TYPE BODY
        84 DROP LIBRARY
        85 TRUNCATE TABLE
        86 TRUNCATE CLUSTER
        88 ALTER VIEW
        91 CREATE FUNCTION
        92 ALTER FUNCTION
        93 DROP FUNCTION
        94 CREATE PACKAGE
        95 ALTER PACKAGE
        96 DROP PACKAGE
        97 CREATE PACKAGE BODY
        98 ALTER PACKAGE BODY
        99 DROP PACKAGE BODY
       100 LOGON
       101 LOGOFF
       102 LOGOFF BY CLEANUP
       103 SESSION REC
       104 SYSTEM AUDIT
       105 SYSTEM NOAUDIT
       106 AUDIT DEFAULT
       107 NOAUDIT DEFAULT
       108 SYSTEM GRANT
       109 SYSTEM REVOKE
       110 CREATE PUBLIC SYNONYM
       111 DROP PUBLIC SYNONYM
       112 CREATE PUBLIC DATABASE LINK
       113 DROP PUBLIC DATABASE LINK
       114 GRANT ROLE
       115 REVOKE ROLE
       116 EXECUTE PROCEDURE
       117 USER COMMENT
       118 ENABLE TRIGGER
       119 DISABLE TRIGGER
       120 ENABLE ALL TRIGGERS
       121 DISABLE ALL TRIGGERS
       122 NETWORK ERROR
       123 EXECUTE TYPE
       128 FLASHBACK
       129 CREATE SESSION
       130 ALTER MINING MODEL
       131 SELECT MINING MODEL
       133 CREATE MINING MODEL
       134 ALTER PUBLIC SYNONYM
       135 DIRECTORY EXECUTE
       136 SQL*LOADER DIRECT PATH LOAD
       137 DATAPUMP DIRECT PATH UNLOAD
       157 CREATE DIRECTORY
       158 DROP DIRECTORY
       159 CREATE LIBRARY
       160 CREATE JAVA
       161 ALTER JAVA
       162 DROP JAVA
       163 CREATE OPERATOR
       164 CREATE INDEXTYPE
       165 DROP INDEXTYPE
       166 ALTER INDEXTYPE
       167 DROP OPERATOR
       168 ASSOCIATE STATISTICS
       169 DISASSOCIATE STATISTICS
       170 CALL METHOD
       171 CREATE SUMMARY
       172 ALTER SUMMARY
       173 DROP SUMMARY
       174 CREATE DIMENSION
       175 ALTER DIMENSION
       176 DROP DIMENSION
       177 CREATE CONTEXT
       178 DROP CONTEXT
       179 ALTER OUTLINE
       180 CREATE OUTLINE
       181 DROP OUTLINE
       182 UPDATE INDEXES
       183 ALTER OPERATOR
       192 ALTER SYNONYM
       197 PURGE USER_RECYCLEBIN
       198 PURGE DBA_RECYCLEBIN
       199 PURGE TABLESPACE
       200 PURGE TABLE
       201 PURGE INDEX
       202 UNDROP OBJECT
       204 FLASHBACK DATABASE
       205 FLASHBACK TABLE
       206 CREATE RESTORE POINT
       207 DROP RESTORE POINT
       208 PROXY AUTHENTICATION ONLY
       209 DECLARE REWRITE EQUIVALENCE
       210 ALTER REWRITE EQUIVALENCE
       211 DROP REWRITE EQUIVALENCE
       212 CREATE EDITION
       213 ALTER EDITION
       214 DROP EDITION
       215 DROP ASSEMBLY
       216 CREATE ASSEMBLY
       217 ALTER ASSEMBLY
       218 CREATE FLASHBACK ARCHIVE
       219 ALTER FLASHBACK ARCHIVE
       220 DROP FLASHBACK ARCHIVE
       225 ALTER DATABASE LINK
       305 ALTER PUBLIC DATABASE LINK


181 rows selected


===========================
Unified Auditing and 
AUDIT_TRAIL
===========================
Regular Oracle Auditing

Audit pre Oracle 12
Controlled by parameter AUDIT_TRAIL
SELECT value FROM V$PARAMETER WHERE name = 'audit_trail';
NONE/DB/OS/...

Unified Auditing
New configuration starting from Oracle 12

SELECT VALUE FROM V$OPTION WHERE parameter='Unified Auditing';
TRUE/FALSE

Fine Grained Auditing
SELECT VALUE FROM V$OPTION WHERE parameter='Fine-grained Auditing';
TRUE/FALSE

If 
'Unified Auditing' is set to TRUE - then values in AUDIT_TRAIL are ignored.
If 'Unified Auditing' is set to FALSE - then audit is per regular AUDIT_TRAIL setup.

===========================
About Unified Auditing

===========================
About Unified Auditing
Unified Auditing is a new auditing facility in Oracle Database 12c Release 1 (12.1).

The unified audit trail, resides in a read-only table in the AUDSYS schema in the SYSAUX tablespace.

It makes this information available in an uniform format in the UNIFIED_AUDIT_TRAIL data dictionary view.

It enables you to capture audit records from a variety of sources.

When the database is writable, audit records are written to the unified audit trail. 

When the database is not writable, then audit records are written to new format operating system files in $ORACLE_BASE/audit/$ORACLE_SID directory.


===========================
See what is being audited
===========================
SELECT POLICY_NAME, ENABLED from DBA_AUDIT_POLICIES;


SELECT audit_option, success, failure 
FROM DBA_STMT_AUDIT_OPTS;

AUDIT_OPTION                  SUCCESS    FAILURE
----------------------------- ---------- ---------
ALTER ANY TABLE               BY ACCESS  BY ACCESS
CREATE ANY TABLE              BY ACCESS  BY ACCESS
DROP ANY TABLE                BY ACCESS  BY ACCESS
CREATE ANY PROCEDURE          BY ACCESS  BY ACCESS
DROP ANY PROCEDURE            BY ACCESS  BY ACCESS
ALTER ANY PROCEDURE           BY ACCESS  BY ACCESS
GRANT ANY PRIVILEGE           BY ACCESS  BY ACCESS
GRANT ANY OBJECT PRIVILEGE    BY ACCESS  BY ACCESS
GRANT ANY ROLE                BY ACCESS  BY ACCESS
SYSTEM AUDIT                  BY ACCESS  BY ACCESS
CREATE EXTERNAL JOB           BY ACCESS  BY ACCESS
CREATE ANY JOB                BY ACCESS  BY ACCESS
CREATE ANY LIBRARY            BY ACCESS  BY ACCESS
CREATE PUBLIC DATABASE LINK   BY ACCESS  BY ACCESS
EXEMPT ACCESS POLICY          BY ACCESS  BY ACCESS
ALTER USER                    BY ACCESS  BY ACCESS
CREATE USER                   BY ACCESS  BY ACCESS
ROLE                          BY ACCESS  BY ACCESS
CREATE SESSION                BY ACCESS  BY ACCESS


===========================
Reference
===========================

No comments:

Post a Comment