The auditing mechanism for Oracle is extremely flexible.
Oracle stores information that is relevant to auditing in its data dictionary.
Every time a user attempts anything in the database where
audit is enabled the Oracle kernel checks to see if an audit record should be
created or updated (in the case or a session record) and generates the record
in a table owned by the SYS user called AUD$. This table is, by default,
located in the SYSTEM tablespace. This itself can cause problems with potential
denial of service attacks. If the SYSTEM tablespace fills up, the database will
hang.
init parameters
Until Oracle 10g, auditing is disabled by default, but can
be enabled by setting the AUDIT_TRAIL static parameter in the init.ora file.
From Oracle 11g, auditing is enabled for some system level
privileges.
SQL> show parameter audit
NAME TYPE VALUE
---------------------- ------------ ----------
audit_file_dest string ?/rdbms/audit
audit_sys_operations boolean FALSE
audit_syslog_level string NONE
audit_trail string DB
transaction_auditing boolean TRUE
AUDIT_TRAIL can have the following values.
AUDIT_TRAIL={NONE or FALSE| OS| DB or TRUE| DB_EXTENDED| XML
|XML_EXTENDED}
Download Pdf Multiples Base Table Cause Problems Change
Number Parameter
The following list provides a description of each value:
NONE or FALSE -> Auditing is disabled. Default until
Oracle 10g.
DB or TRUE -> Auditing is enabled, with all audit records
stored in the database audit trial (AUD$). Default from Oracle 11g.
DB_EXTENDED –> Same 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 –> Same as XML, but the SQL_BIND and
SQL_TEXT columns are also populated.
OS -> Auditing is enabled, with all audit records
directed to the operating system's file specified by AUDIT_FILE_DEST.
Note: In Oracle 10g Release 1, DB_EXTENDED was used in place
of "DB,EXTENDED". The XML options were brought in Oracle 10g Release
2.
The AUDIT_FILE_DEST parameter specifies the OS directory
used for the audit trail when the OS, XML and XML_EXTENDED options are used. It
is also the location for all mandatory auditing specified by the
AUDIT_SYS_OPERATIONS parameter.
The AUDIT_SYS_OPERATIONS static parameter enables or
disables the auditing of operations issued by users connecting with SYSDBA or
SYSOPER privileges, including the SYS user. All audit records are written to
the OS audit trail.
Run the $ORACLE_HOME/rdbms/admin/cataudit.sql script while
connected as SYS (no need to run this, if you ran catalog.sql at the time of
database creation).
Start Auditing
Syntax of audit command:
audit {statement_option|privilege_option} [by user] [by
{session|access}] [whenever {successful|not successful}]
Sql Statements Table All System The Users Download Pdf
Multiples Base Table
Only the statement_option or privilege_option part is
mandatory. The other clauses are optional and enabling them allows audit be
more specific.
There are three levels that can be audited:
Statement level
Auditing will be done at statement level.
Statements that can be audited are found in
STMT_AUDIT_OPTION_MAP.
SQL> audit table by scott;
Audit records can be found in DBA_STMT_AUDIT_OPTS.
SQL> select * from DBA_STMT_AUDIT_OPTS;
Object level
Auditing will be done at object level.
These objects can be audited: tables, views, sequences,
packages, stored procedures and stored functions.
SQL> audit insert, update, delete on scott.emp by hr;
Audit records can be found in DBA_OBJ_AUDIT_OPTS.
SQL> select * from DBA_OBJ_AUDIT_OPTS;
Privilege level
Auditing will be done at privilege level.
All system privileges that are found in SYSTEM_PRIVILEGE_MAP
can be audited.
SQL> audit create tablespace, alter tablespace by all;
Specify ALL PRIVILEGES to audit all system privileges.
Audit records can be found in DBA_PRIV_AUDIT_OPTS.
SQL> select * from DBA_PRIV_AUDIT_OPTS;
Audit options
BY SESSION
Specify BY SESSION if you want Oracle to write a single
record for all SQL statements of the same type issued and operations of the
same type executed on the same schema objects in the same session.
Oracle database can write to an operating system audit file
but cannot read it to detect whether an entry has already been written for a
particular operation. Therefore, if you are using an operating system file for
the audit trail (that is, the AUDIT_TRAIL initialization parameter is set to
OS), then the database may write multiple records to the audit trail file even
if you specify BY SESSION.
SQL> audit create, alter, drop on currency by xe by
session;
SQL> audit alter materialized view by session;
BY ACCESS
Specify BY ACCESS if you want Oracle database to write one record
for each audited statement and operation.
If you specify statement options or system privileges that
audit data definition language (DDL) statements, then the database
automatically audits by access regardless of whether you specify the BY SESSION
clause or BY ACCESS clause.
For statement options and system privileges that audit SQL
statements other than DDL, you can specify either BY SESSION or BY ACCESS. BY
SESSION is the default.
SQL> audit update on health by access;
SQL> audit alter sequence by tester by access;
WHENEVER [NOT] SUCCESSFUL
Specify WHENEVER SUCCESSFUL to audit only SQL statements and
operations that succeed.
Specify WHENEVER NOT SUCCESSFUL to audit only SQL statements
and operations that fail or result in errors.
If you omit this clause, then Oracle Database performs the
audit regardless of success or failure.
SQL> audit insert, update, delete on hr.emp by hr by
session whenever not successful;
SQL> audit materialized view by pingme by access whenever
successful;
Examples
Auditing for every SQL statement related to roles (create,
alter, drop or set a role).
SQL> AUDIT ROLE;
Auditing for every statement that reads files from database
directory
SQL> AUDIT READ ON DIRECTORY ext_dir;
Auditing for every statement that performs any operation on
the sequence
SQL> AUDIT ALL ON hr.emp_seq;
View Audit Trail
The audit trail is stored in the base table SYS.AUD$.
It's contents can be viewed in the following views:
· DBA_AUDIT_TRAIL
· DBA_OBJ_AUDIT_OPTS
· DBA_PRIV_AUDIT_OPTS
· DBA_STMT_AUDIT_OPTS
· DBA_AUDIT_EXISTS
· DBA_AUDIT_OBJECT
· DBA_AUDIT_SESSION
· DBA_AUDIT_STATEMENT
· AUDIT_ACTIONS
· DBA_AUDIT_POLICIES
· DBA_AUDIT_POLICY_COLUMNS
· DBA_COMMON_AUDIT_TRAIL
· DBA_FGA_AUDIT_TRAIL (FGA_LOG$)
· DBA_REPAUDIT_ATTRIBUTE
· DBA_REPAUDIT_COLUMN
The audit trail contains lots of data, but the following are
most likely to be of interest:
Username - Oracle
Username.
Terminal - Machine
that the user performed the action from.
Timestamp - When the
action occurred.
Object Owner - The
owner of the object that was interacted with.
Object Name - name of
the object that was interacted with.
Action Name - The
action that occurred against the object (INSERT, UPDATE, DELETE, SELECT,
EXECUTE)
Fine Grained Auditing (FGA), introduced in Oracle9i, allowed
recording of row-level changes along with SCN numbers to reconstruct the old
data, but they work for select statements only, not for DML such as update,
insert, and delete.
From Oracle 10g, FGA supports DML statements in addition to
selects.
Several fields have been added to both the standard and
fine-grained audit trails:
EXTENDED_TIMESTAMP - A more precise value than the existing
TIMESTAMP column.
PROXY_SESSIONID - Proxy session serial number when an
enterprise user is logging in via the proxy method.
GLOBAL_UID - Global Universal Identifier for an enterprise
user.
INSTANCE_NUMBER - The INSTANCE_NUMBER value from the
actioning instance.
OS_PROCESS - Operating system process id for the oracle
process.
TRANSACTIONID - Transaction identifier for the audited transaction.
This column can be used to join to the XID column on the
FLASHBACK_TRANSACTION_QUERY view.
SCN - System change number of the query. This column can be
used in flashback queries.
SQL_BIND - The values of any bind variables if any.
SQL_TEXT - The SQL statement that initiated the audit
action.
The SQL_BIND and SQL_TEXT columns are only populated when
the AUDIT_TRAIL=DB_EXTENDED or AUDIT_TRAIL=XML_EXTENDED initialization
parameter is set.
Maintenance
The audit trail must be deleted/archived on a regular basis
to prevent the SYS.AUD$ table growing to an unacceptable size.
Only users who have been granted specific access to SYS.AUD$
can access the table to select, alter or delete from it. This is usually just
the user SYS or any user who has permissions. There are two specific roles that
allow access to SYS.AUD$ for select and delete, these are DELETE_CATALOG_ROLE
and SELECT_CATALOG_ROLE. These roles should not be granted to general users.
Auditing modifications of the data in the audit trail itself
can be achieved as follows
SQL> AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;
To delete all audit records from the audit trail:
SQL> DELETE FROM sys.aud$;
From Oracle 11g R2, we can change audit table's (SYS.AUD$
and SYS.FGA_LOG$) tablespace and we can periodically delete the audit trail
records using DBMS_AUDIT_MGMT package.
Disabling Auditing
The NOAUDIT statement turns off the various audit options of
Oracle. Use it to reset statement, privilege and object audit options. A
NOAUDIT statement that sets statement and privilege audit options can include
the BY USER option to specify a list of users to limit the scope of the
statement and privilege audit options.
SQL> NOAUDIT;
SQL> NOAUDIT session;
SQL> NOAUDIT session BY scott, hr;
SQL> NOAUDIT DELETE ON emp;
SQL> NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE,
EXECUTE PROCEDURE;
SQL> NOAUDIT ALL;
SQL> NOAUDIT ALL PRIVILEGES;
SQL> NOAUDIT ALL ON DEFAULT;
