Oracle Database Audit
This week there was a specific demand to configure and work with auditing in the Oracle database, in this way I will unravel how to use auditing in standard and enterprise environments.
Oracle Standard Database – In this edition we have the option of auditing with low flexibility of actions, but which in general can help your company meeting various demands of security auditing.
Oracle Enterprise Database – In this edition we have the option of auditing with flexible actions to be audited using Fine Grained Auditing (FGA), in this edition we have several possibilities to work with auditing.
1 – As a good practice, we will create a tablespace and move the audit objects to this tablespace, thus preventing the system tablespaces from growing too much and making our maintenance difficult.
1.1 - Check tablespace that the objects used in the audit are located.
SQL> SELECT SEGMENT_NAME,TABLESPACE_NAME FROM dba_segments WHERE SEGMENT_NAME in ('AUD$','FGA$');| SEGMENT_NAME | TABLESPACE_NAME |
|---|---|
| AUD$ | TBS_AUDITORIA |
| FGA$ | SYSTEM |
SQL> select segment_name,bytes/1024/1024 size_in_megabytes from dba_segments where segment_name in ('AUD$','FGA_LOG$');| SEGMENT_NAME | SIZE_IN_MEGABYTES |
|---|---|
| FGA_LOG$ | .0625 |
| AUD$ | .6875 |
1.2 - Creating tablespace TBS_AUDITORIA to use audit objects.
CREATE TABLESPACE "TBS_AUDITORIA" DATAFILE '+DATA' SIZE 1G AUTOEXTEND ON NEXT 1G,
LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;1.3 – Moving audit objects to tablespace TBS_AUDITORIA.
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'TBS_AUDITORIA');
END;
/
-- MOVENDO FGA_LOG$ PARA TABLESPACE TBS_AUDITORIA
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_location_value => 'TBS_AUDITORIA');
END;
/1.4 - Checking the audit objects in the created tablespace TBS_AUDITORIA.
SELECT SEGMENT_NAME,TABLESPACE_NAME,segment_type FROM dba_segments WHERE SEGMENT_NAME in ('AUD$','FGA$');| SEGMENT_NAME | TABLESPACE_NAME | SEGMENT_TYPE |
|---|---|---|
| AUD$ | TBS_AUDITORIA | TABLE |
| FGA$ | SYSTEM | TABLE |
SQL> SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;| TABLE_NAME | TABLESPACE_NAME |
|---|---|
| AUD$ | TBS_AUDITORIA |
| FGA_LOG$ | TBS_AUDITORIA |
SQL> select segment_name,bytes/1024/1024 size_in_megabytes from dba_segments where segment_name in ('AUD$','FGA_LOG$');| SEGMENT_NAME | SIZE_IN_MEGABYTES |
|---|---|
| FGA_LOG$ | .0625 |
| AUD$ | .6875 |
2 – How to enable and use auditing in Oracle database standard edition.
/*AUDIT_TRAIL enables or disables database auditing.
Values: none Disables database auditing.
=======================
os
Enables database auditing and directs all audit records to the operating system’s audit trail.
db
=======================
Enables database auditing and directs all audit records to the database audit trail (the SYS.AUD$ table).
db,extended
=======================
Enables database auditing and directs all audit records to the database audit trail (the SYS.AUD$ table). In addition, populates the SQLBIND and SQLTEXT CLOB columns of the SYS.AUD$ table.
xml
=======================
Enables database auditing and writes all audit records to XML format OS files.
xml,extended
=======================
Enables database auditing and prints all columns of the audit trail, including SqlText and SqlBind values. */
2.1 – Before changing the parameters, we have to keep in mind the values that can be used, how we are going to configure the standard auditing saving the executed sql, having to use the db,extended option.
SQL> show parameter audit;| NAME | TYPE | VALUE |
|---|---|---|
| audit_file_dest | string | /u01/app/oracle/admin/orcl/adu mp |
| dump | ||
| audit_sys_operations | boolean | FALSE |
| audit_syslog_level | string | |
| audit_trail | string | NONE |
SQL> alter system set audit_trail='DB','EXTENDED' scope=spfile;
System altered.SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.SQL> startup open;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area | 417546240 | bytes
Fixed Size | 2253824 | bytes
Variable Size | 180358144 | bytes
Database Buffers | 230686720 | bytes
Redo Buffers | 4247552 | bytes
Database mounted.
Database opened.SQL> show parameter audit;| NAME | TYPE | VALUE |
|---|---|---|
| audit_file_dest | string | /u01/app/oracle/admin/orcl/adu mp |
| dump | ||
| audit_sys_operations | boolean | FALSE |
| audit_syslog_level | string | |
| audit_trail | string | DB, EXTENDED |
2.2 – If you want to enable auditing of SYS events.
-- ALTER SYSTEM SET audit_sys_operations=true SCOPE=spfile;3 – Enabling DDL auditing of CESAR_TEST1 and CESAR_TEST2 users.
SQL>
AUDIT ALL BY CESAR_TEST1 BY ACCESS;
Audit succeeded.
SQL> AUDIT ALL BY CESAR_TEST2 BY ACCESS;
Audit succeeded.4 – Enabling DML and DDL auditing of CESAR_TEST1 and CESAR_TEST2 users.
SQL>
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY CESAR_TEST1 BY ACCESS;
Audit succeeded.
SQL> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY CESAR_TEST2 BY ACCESS;
Audit succeeded.5 – DBA_STMT_AUDIT_OPTS describes current system auditing options across the system and by user.
SQL> SELECT * FROM dba_stmt_audit_opts ORDER BY audit_option;| USER_NAME | PROXY_NAME | AUDIT_OPTION | SUCCESS | FAILURE |
|---|---|---|---|---|
| ALTER ANY PROCEDURE | BY ACCESS | BY ACCESS | ||
| ALTER ANY TABLE | BY ACCESS | BY ACCESS | ||
| ALTER DATABASE | BY ACCESS | BY ACCESS | ||
| ALTER PROFILE | BY ACCESS | BY ACCESS | ||
| ALTER SYSTEM | BY ACCESS | BY ACCESS | ||
| CESAR_TEST1 | ALTER SYSTEM | BY ACCESS | BY ACCESS | |
| CESAR | ALTER SYSTEM | BY ACCESS | BY ACCESS | |
| CESAR_TEST2 | ALTER SYSTEM | BY ACCESS | BY ACCESS | |
| ALTER USER | BY ACCESS | BY ACCESS | ||
| CESAR_TEST2 | CLUSTER | BY ACCESS | BY ACCESS | |
| CESAR | CLUSTER | BY ACCESS | BY ACCESS | |
| CESAR_TEST1 | CLUSTER | BY ACCESS | BY ACCESS | |
| CESAR | CONTEXT | BY ACCESS | BY ACCESS | |
| CESAR_TEST1 | CONTEXT | BY ACCESS | BY ACCESS | |
| CESAR_TEST2 | CONTEXT | BY ACCESS | BY ACCESS | |
| CREATE ANY JOB | BY ACCESS | BY ACCESS | ||
| CREATE ANY LIBRARY | BY ACCESS | BY ACCESS | ||
| CREATE ANY PROCEDURE | BY ACCESS | BY ACCESS | ||
| CREATE ANY TABLE | BY ACCESS | BY ACCESS | ||
| CREATE EXTERNAL JOB | BY ACCESS | BY ACCESS | ||
| CREATE PUBLIC DATABASE LINK | BY ACCESS | BY ACCESS | ||
| CESAR_TEST2 | CREATE SESSION | BY ACCESS | BY ACCESS | |
| CREATE SESSION | BY ACCESS | BY ACCESS | ||
| CESAR | CREATE SESSION | BY ACCESS | BY ACCESS | |
| CESAR_TEST1 | CREATE SESSION | BY ACCESS | BY ACCESS | |
| CREATE USER | BY ACCESS | BY ACCESS | ||
| CESAR_TEST1 | DATABASE LINK | BY ACCESS | BY ACCESS | |
| CESAR | DATABASE LINK | BY ACCESS | BY ACCESS | |
| DATABASE LINK | BY ACCESS | BY ACCESS | ||
| CESAR_TEST2 | DATABASE LINK | BY ACCESS | BY ACCESS | |
| CESAR_TEST2 | DELETE TABLE | BY ACCESS | BY ACCESS | |
| CESAR_TEST1 | DELETE TABLE | BY ACCESS | BY ACCESS | |
| CESAR | DELETE TABLE | BY ACCESS | BY ACCESS | |
| CESAR | DIMENSION | BY ACCESS | BY ACCESS | |
| CESAR_TEST2 | DIMENSION | BY ACCESS | BY ACCESS | |
| CESAR_TEST1 | DIMENSION | BY ACCESS | BY ACCESS | |
| CESAR_TEST1 | DIRECTORY | BY ACCESS | BY ACCESS | |
| CESAR_TEST2 | DIRECTORY | BY ACCESS | BY ACCESS | |
| DIRECTORY | BY ACCESS | BY ACCESS | ||
| CESAR | DIRECTORY | BY ACCESS | BY ACCESS | |
| DROP ANY PROCEDURE | BY ACCESS | BY ACCESS | ||
| DROP ANY TABLE | BY ACCESS | BY ACCESS | ||
| DROP PROFILE | BY ACCESS | BY ACCESS | ||
| DROP USER | BY ACCESS | BY ACCESS | ||
| CESAR | EXECUTE PROCEDURE | BY ACCESS | BY ACCESS | |
| EXEMPT ACCESS POLICY | BY ACCESS | BY ACCESS | ||
| GRANT ANY OBJECT PRIVILEGE | BY ACCESS | BY ACCESS | ||
| GRANT ANY PRIVILEGE | BY ACCESS | BY ACCESS | ||
| GRANT ANY ROLE | BY ACCESS | BY ACCESS | ||
| CESAR | INDEX | BY ACCESS | BY ACCESS | |
| CESAR_TEST1 | INDEX | BY ACCESS | BY ACCESS | |
| CESAR_TEST2 | INDEX | BY ACCESS | BY ACCESS | |
| CESAR_TEST2 | INSERT TABLE | BY ACCESS | BY ACCESS | |
| CESAR | INSERT TABLE | BY ACCESS | BY ACCESS | |
| CESAR_TEST1 | INSERT TABLE | BY ACCESS | BY ACCESS | |
| CESAR | MATERIALIZED VIEW | BY ACCESS | BY ACCESS | |
| CESAR_TEST1 | MATERIALIZED VIEW | BY ACCESS | BY ACCESS | |
| CESAR_TEST2 | MATERIALIZED VIEW | BY ACCESS | BY ACCESS | |
| CESAR_TEST2 | MINING MODEL | BY ACCESS | BY ACCESS | |
| CESAR | MINING MODEL | BY ACCESS | BY ACCESS | |
| CESAR_TEST1 | MINING MODEL | BY ACCESS | BY ACCESS | |
| CESAR | NOT EXISTS | BY ACCESS | BY ACCESS | |
| CESAR_TEST2 | NOT EXISTS | BY ACCESS | BY ACCESS | |
| CESAR_TEST1 | NOT EXISTS | BY ACCESS | BY ACCESS | |
| CESAR_TEST1 | PROCEDURE | BY ACCESS | BY ACCESS | |
| CESAR | PROCEDURE | BY ACCESS | BY ACCESS | |
| CESAR_TEST2 | PROCEDURE | BY ACCESS | BY ACCESS | |
| CESAR | PROFILE | BY ACCESS | BY ACCESS | |
| PROFILE | BY ACCESS | BY ACCESS | ||
| CESAR_TEST1 | PROFILE | BY ACCESS | BY ACCESS | |
| CESAR_TEST2 | PROFILE | BY ACCESS | BY ACCESS | |
| CESAR | PUBLIC DATABASE LINK | BY ACCESS | BY ACCESS | |
| CESAR_TEST1 | PUBLIC DATABASE LINK | BY ACCESS | BY ACCESS | |
| CESAR_TEST2 | PUBLIC DATABASE LINK | BY ACCESS | BY ACCESS | |
| CESAR_TEST1 | PUBLIC SYNONYM | BY ACCESS | BY ACCESS | |
| PUBLIC SYNONYM | BY ACCESS | BY ACCESS | ||
| CESAR_TEST2 | PUBLIC SYNONYM | BY ACCESS | BY ACCESS | |
| CESAR | PUBLIC SYNONYM | BY ACCESS | BY ACCESS | |
| ROLE | BY ACCESS | BY ACCESS | ||
| CESAR | ROLE | BY ACCESS | BY ACCESS | |
| CESAR_TEST1 | ROLE | BY ACCESS | BY ACCESS | |
| CESAR_TEST2 | ROLE | BY ACCESS | BY ACCESS | |
| CESAR_TEST1 | ROLLBACK SEGMENT | BY ACCESS | BY ACCESS | |
| CESAR_TEST2 | ROLLBACK SEGMENT | BY ACCESS | BY ACCESS | |
| CESAR | ROLLBACK SEGMENT | BY ACCESS | BY ACCESS | |
| CESAR_TEST1 | SELECT TABLE | BY ACCESS | BY ACCESS | |
| CESAR_TEST2 | SELECT TABLE | BY ACCESS | BY ACCESS | |
| CESAR | SELECT TABLE | BY ACCESS | BY ACCESS | |
| CESAR_TEST1 | SEQUENCE | BY ACCESS | BY ACCESS | |
| CESAR | SEQUENCE | BY ACCESS | BY ACCESS | |
| CESAR_TEST2 | SEQUENCE | BY ACCESS | BY ACCESS | |
| CESAR | SYNONYM | BY ACCESS | BY ACCESS | |
| CESAR_TEST2 | SYNONYM | BY ACCESS | BY ACCESS | |
| CESAR_TEST1 | SYNONYM | BY ACCESS | BY ACCESS | |
| CESAR_TEST2 | SYSTEM AUDIT | BY ACCESS | BY ACCESS | |
| CESAR_TEST1 | SYSTEM AUDIT | BY ACCESS | BY ACCESS | |
| SYSTEM AUDIT | BY ACCESS | BY ACCESS | ||
| CESAR | SYSTEM AUDIT | BY ACCESS | BY ACCESS | |
| CESAR | SYSTEM GRANT | BY ACCESS | BY ACCESS | |
| CESAR_TEST2 | SYSTEM GRANT | BY ACCESS | BY ACCESS | |
| SYSTEM GRANT | BY ACCESS | BY ACCESS | ||
| CESAR_TEST1 | SYSTEM GRANT | BY ACCESS | BY ACCESS | |
| CESAR_TEST1 | TABLE | BY ACCESS | BY ACCESS | |
| CESAR | TABLE | BY ACCESS | BY ACCESS | |
| CESAR_TEST2 | TABLE | BY ACCESS | BY ACCESS | |
| CESAR_TEST1 | TABLESPACE | BY ACCESS | BY ACCESS | |
| CESAR | TABLESPACE | BY ACCESS | BY ACCESS | |
| CESAR_TEST2 | TABLESPACE | BY ACCESS | BY ACCESS | |
| CESAR_TEST2 | TRIGGER | BY ACCESS | BY ACCESS | |
| CESAR | TRIGGER | BY ACCESS | BY ACCESS | |
| CESAR_TEST1 | TRIGGER | BY ACCESS | BY ACCESS | |
| CESAR_TEST2 | TYPE | BY ACCESS | BY ACCESS | |
| CESAR | TYPE | BY ACCESS | BY ACCESS | |
| CESAR_TEST1 | TYPE | BY ACCESS | BY ACCESS | |
| CESAR | UPDATE TABLE | BY ACCESS | BY ACCESS | |
| CESAR_TEST1 | UPDATE TABLE | BY ACCESS | BY ACCESS | |
| CESAR_TEST2 | UPDATE TABLE | BY ACCESS | BY ACCESS | |
| CESAR_TEST1 | USER | BY ACCESS | BY ACCESS | |
| CESAR_TEST2 | USER | BY ACCESS | BY ACCESS | |
| CESAR | USER | BY ACCESS | BY ACCESS | |
| CESAR | VIEW | BY ACCESS | BY ACCESS | |
| CESAR_TEST2 | VIEW | BY ACCESS | BY ACCESS | |
| CESAR_TEST1 | VIEW | BY ACCESS | BY ACCESS |
123 rows selected.
6 – Verifying information auditing the operations of the environment.
SELECT
TO_CHAR (EXTENDED_TIMESTAMP,'DD-MM-RRRR HH24:MI:SS') DATA,
USERNAME, OS_USERNAME, USERHOST,TERMINAL,
OWNER,ACTION_NAME,OBJ_NAME,SQL_TEXT
FROM
DBA_AUDIT_TRAIL
WHERE USERNAME in ('CESAR_TEST1','CESAR_TEST2')
AND ACTION_NAME NOT LIKE '%SELECT%'
ORDER BY EXTENDED_TIMESTAMP DESC;
Disabling auditing.
-- Disabling by user.
NOAUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY CESAR_TEST1 BY ACCESS;
NOAUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY CESAR_TEST2 BY ACCESS;
-- Drop created FGA rule.
SELECT 'EXEC DBMS_FGA.DROP_POLICY('''||OBJECT_SCHEMA||''','''||OBJECT_NAME||''','''||POLICY_NAME||''');'
FROM DBA_AUDIT_POLICIES;
-- Disabling at the database level.
show parameter audit;
alter system set audit_trail=none SCOPE=spfile;
shutdown immediate;
startup open;
show parameter audit;