As your application and database grow, more and more users get connected to the database, and it becomes difficult to manage all the privileges that individual user has granted. So, there is a feature provided by the oracle called AUDITING.
Users with DBA privileges can do a lot of things
with the database, it is important to make sure that users with DBA privileges
should not harm the database by any means, in order to do it, the security team
or database admin can enable different levels of auditing.
Before enabling any auditing, you need to
consider the fact that, this is put some extra effort into the database, so try
to push it on a minimal basis.
Auditing SYSDBA activity
Based on the parameter AUDIT_SYS_OPERATIONS value, SYSDBA
activity is tracked at the operating system level audit trail file. If AUDIT_SYS_OPERATIONS is set to TRUE
then every statement executed by the user connected as “as sysdba” or “as
sysopr” is audited. Location of audit
trail file can be set and monitored by parameter AUDIT_FILE_DEST
Database Auditing
Database auditing is controlled using the parameter
AUDIT_TRAIL parameter, there
are different values associated with it.
NONE (FALSE) – database auditing
is disabled
OS – auditing will be recorded at the OS level audit trail and location is controlled by audit_file_dest
DB- auditing will be recorded at database
table SYS.AUD$
DB_EXTENDED – saves at
database level but includes the SQL statements with bind variables
XML- auditing is done at OS level, formatted with
XML tag
XML_EXTENDED- formatted as XML
tags, includes SQL statements with bind variables
Database auditing can be configured by
AUDIT commands,
For example
Audit succeeded.
SQL> audit select any table by session;
Audit succeeded.
SQL>
By default, auditing will generate one row
for each auditing violation.
BY SESSION- one record for
each session does not matter how many times it violates (DEFAULT)
BY ACCESS- one record for
every violation.
Auditing can be enabled on specific objects
as well, for example
SQL> audit select on SYS.DBA_USERS whenever successful;
Audit succeeded.
SQL>
This statement generates a record for
every successful insertion for the table SYS.DBA_USERS
WHENEVER SUCCESSFUL – records only
when the insertion is successful
WHENEVER NOT SUCCESSFUL – records only
when the insertion is failed
By default- both conditions are recorded.
When AUDIT_TRAIL is set as OS or
XML, one can check audit records at OS level audit trail file and when AUDIT_TRAIL
is set as DB or a similar one, you can fetch records using SYS.AUD$ or you can
use the DBA_AUDIT_TRAIL view. There are
around 50 columns available to view in the DBA_AUDIT_TRAIL view.
There are a couple of subset views of DBA_AUDIT_TRAIL and can be used to narrow down the results
DBA_AUDIT_OBJECT,
DBA_AUDIT_STATEMENT
DBA_AUDIT_SESSION
Auditing WITH triggers
Auditing enabled using the audit command will only have a single record for each statement, but it will not have the exact statement used to insert the record. Sometimes you may need to watch out for statements executed,
Database triggers will help to capture the
statement based on the condition defined. Assume, there is an update trigger defined
on the table, you try to update the table, it will simply generate an audit
record and put the row in another table defined in trigger.
Fine-Grained Auditing (FGA)
Till now, we have discussed auditing at
database level or table level, what if you want to capture auditing for only
specific rows in table or views, FGA can help you to achieve this.
FGA can be configured through package DBMS_FGA and add FGA audit
policy, need to use ADD_POLICY procedure.
To view records, you need to use the DBA_FGA_AUDIT_TRAIL view, generally
FGA auditing can be enabled on rows on which data is critical such as salary or
budget or revenue.
DBMS_FGA has a lot of procedures and can
be used to add, drop, enable policy, disable policy.
SQL> desc dbms_FGA
PROCEDURE ADD_POLICY
PROCEDURE DISABLE_POLICY
PROCEDURE DROP_POLICY
PROCEDURE ENABLE_POLICY
This is all about the database auditing
theory part.
