Auditing in Oracle Database: In this article we will discuss about Auditing in Oracle Database. Here we use our Oracle 19c database. Auditing is a default feature in Oracle Database to monitoring and recording of configured database actions, from both database users and nondatabase users. Below are the initialization parameters that can be displayed and describe its behaviour by using “show parameter audit” command.
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/PROD/adu
mp
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB
unified_audit_common_systemlog string
unified_audit_sga_queue_size integer 1048576
unified_audit_systemlog string
SQL>Auditing is enabled by default in Oracle 19c database. But we can enabled or disabled by setting the AUDIT_TRAIL static parameter, which has the following allowed values.
AUDIT_TRAIL = { NONE | OS | DB | DB,EXTENDED | XML | XML,EXTENDED }The AUDIT_TRAIL parameter values define database auditing status. You can take any of the following values:
- NONE :- Auditing is disabled.
- OS :- Auditing is enabled, with all audit records directed to the operating system’s audit trail.
- DB :- Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
- DB,EXTENDED :- As DB, but the SQL_BIND and SQL_TEXT columns are also populated.
- XML :- Auditing is enabled, with all audit records stored as XML format OS files.
- XML,EXTENDED :- As XML, but the SQL_BIND and SQL_TEXT columns are also populated.
Move AUD$ Table to Another Tablespace:
How to check the tablespace for AUD$ Table and move AUD$ Table to Another Tablespace:
SQL> set lines 400 pages 400
SQL> col owner for a10
SQL> col segment_name for a10
SQL> col tablespace_name for a15
SQL> select owner, segment_name, segment_type, tablespace_name, bytes/1024/1024 as MB from dba_segments where segment_name in ('AUD$','FGA_LOG$');
OWNER SEGMENT_NA SEGMENT_TYPE TABLESPACE_NAME MB
---------- ---------- ------------------ --------------- ----------
SYS AUD$ TABLE SYSTEM .0625
SYS FGA_LOG$ TABLE SYSTEM .0625
SQL>
SQL> BEGIN
2 DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
3 audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
4 audit_trail_location_value => 'AUDIT_DATA');
5 END;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL> select owner, segment_name, segment_type, tablespace_name, bytes/1024/1024 as MB from dba_segments where segment_name in ('AUD$','FGA_LOG$');
OWNER SEGMENT_NA SEGMENT_TYPE TABLESPACE_NAME MB
---------- ---------- ------------------ --------------- ----------
SYS AUD$ TABLE AUDIT_DATA .0625
SYS FGA_LOG$ TABLE AUDIT_DATA .0625
SQL>
Audit Options:
Firstly, create a new user name TEST and grant priviliges.
SQL> create user TEST identified by test;
User created.
SQL> grant connect to TEST;
Grant succeeded.
SQL> grant create table, create procedure to TEST;
Grant succeeded.
SQL>Afterthat, we audit all operations by the TEST user.
SQL> audit all by TEST by access;
Audit succeeded.
SQL> audit select table, update table, insert table, delete table by TEST by access;
Audit succeeded.
SQL> audit execute procedure by TEST by access;
Audit succeeded.
SQL>Now, we perform some operations in TEST user that will be audited.
SQL> conn TEST
Enter password:
Connected.
SQL>
SQL> create table employees (id varchar2(10), name varchar(50), location char(20), salary varchar2(10));
Table created.
SQL>
SQL> insert into employees values (1001,'prasanta','delhi',10000);
1 row created.
SQL> insert into employees values (1002,'anil','noida',12000);
1 row created.
SQL> insert into employees values (1003,'akhilesh','mumbai',15000);
1 row created.
SQL> select * from employees;
ID NAME LOCATION SALARY
---------- -------------------------------------------------- -------------------- ----------
1001 prasanta delhi 10000
1002 anil noida 12000
1003 akhilesh mumbai 15000
SQL>
SQL> update employees set name='shubham' where id=1002;
1 row updated.
SQL>
SQL> select * from employees;
ID NAME LOCATION SALARY
---------- -------------------------------------------------- -------------------- ----------
1001 prasanta delhi 10000
1002 shubham noida 12000
1003 akhilesh mumbai 15000
SQL> delete from employees where name='shubham';
1 row deleted.
SQL> select * from employees;
ID NAME LOCATION SALARY
---------- -------------------------------------------------- -------------------- ----------
1001 prasanta delhi 10000
1003 akhilesh mumbai 15000
SQL> drop table employees;
Table dropped.
SQL>View Audit Trail:
The audit trail is stored in the table SYS.AUD$. Its contents can be viewed by using following views.
SQL> conn / as sysdba
Connected.
SQL>
SQL> select view_name from dba_views where view_name like 'DBA%AUDIT%' order by view_name;
VIEW_NAME
--------------------------------------------------
DBA_AUDIT_EXISTS
DBA_AUDIT_MGMT_CLEANUP_JOBS
DBA_AUDIT_MGMT_CLEAN_EVENTS
DBA_AUDIT_MGMT_CONFIG_PARAMS
DBA_AUDIT_MGMT_LAST_ARCH_TS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_DV_PATCH_ADMIN_AUDIT
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_OLS_AUDIT_OPTIONS
DBA_PRIV_AUDIT_OPTS
DBA_SA_AUDIT_OPTIONS
DBA_STMT_AUDIT_OPTS
DBA_XS_AUDIT_POLICY_OPTIONS
DBA_XS_AUDIT_TRAIL
DBA_XS_ENABLED_AUDIT_POLICIES
22 rows selected.
SQL>
The three main views are shown below.
DBA_AUDIT_TRAIL - Standard auditing only (from AUD$).
DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$).
DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.
The most basic view of the database audit trail is provided by the DBA_AUDIT_TRAIL view, which contains a wide variety of information. The following query displays the some of the information from the database audit trail.
SQL> col username format a10
SQL> col owner format a10
SQL> col obj_name format a10
SQL> col extended_timestamp format a40
SQL> select username, extended_timestamp, owner, obj_name, action_name from dba_audit_trail where owner='TEST' order by extended_timestamp;
USERNAME EXTENDED_TIMESTAMP OWNER OBJ_NAME ACTION_NAME
---------- ---------------------------------------- ---------- ---------- ----------------------------
TEST 05-FEB-24 01.07.16.905791 AM +05:30 TEST EMPLOYEES CREATE TABLE
TEST 05-FEB-24 01.09.47.490437 AM +05:30 TEST EMPLOYEES INSERT
TEST 05-FEB-24 01.10.17.342274 AM +05:30 TEST EMPLOYEES DROP TABLE
TEST 05-FEB-24 01.13.39.197256 AM +05:30 TEST EMPLOYEES CREATE TABLE
TEST 05-FEB-24 01.13.54.614288 AM +05:30 TEST EMPLOYEES INSERT
TEST 05-FEB-24 01.16.03.815828 AM +05:30 TEST EMPLOYEES INSERT
TEST 05-FEB-24 01.16.12.491950 AM +05:30 TEST EMPLOYEES INSERT
TEST 05-FEB-24 01.17.32.140431 AM +05:30 TEST EMPLOYEES SELECT
TEST 05-FEB-24 01.19.02.985968 AM +05:30 TEST EMPLOYEES UPDATE
TEST 05-FEB-24 01.19.08.038888 AM +05:30 TEST EMPLOYEES SELECT
TEST 05-FEB-24 01.19.49.859736 AM +05:30 TEST EMPLOYEES DELETE
TEST 05-FEB-24 01.19.54.663807 AM +05:30 TEST EMPLOYEES SELECT
TEST 05-FEB-24 01.20.14.339060 AM +05:30 TEST EMPLOYEES DROP TABLE
13 rows selected.
SQL>
How to enable or disable Unified Auditing in Oracle
I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com. Keep learning… Have a great day!!!
Email :- orakldba@gmail.com
Please comment here for your any query related to above content. You can write to mail me through my Email.
LinkedIn :- https://linkedin.com/in/prasantakirtaniya
Twitter :- https://twitter.com/prasantadba
Facebook Page :- https://www.facebook.com/orakldba
Website :- https://orakldba.com
ACE Profile :- https://ace.oracle.com/apex/ace/profile/prasantakirtaniya