Oracle Database 12c
introduces changes to database auditing. Auditing in database 12c is now policy-based,
and all audit records are integrated into one centralized repository, which is
known as the Unified Audit Trail.
Auditing is administered
by use of audit policies. A single audit policy can group together a number of
audit settings, easing administration.
In a multi tenant
environment, there is one unified audit trail for each container.
Migrating to unified auditing - about
mixed-mode auditing
The pre-12c auditing
framework, which is controlled by the AUDIT_TRAIL parameter, is now deprecated
but supported.
Newly
created/upgraded databases, will continue to write standard audit records to
both the unified audit trail and the traditional audit trails.
The databases will
be in this mode called mix-mode auditing, until at least a single unified audit
policy is created and enabled.
The databases fall
back to mixed-mode auditing if there are no longer any enabled audit policies.
A new audit policy called
ORA_SECURECONFIG which is enabled by
default establishes this behavior.
The new CREATE AUDIT POLICY statement:
To create a unified
audit policy, use the new CREATE AUDIT POLICY statement. As mentioned, the syntax
can cover auditing of different types of database access in a single DDL.
Type of usage
|
Example
|
Dbms component use
|
As in SQl*Loader,
Data Pump
|
Standard
actions/system privilege usge
|
CREATE SESSION,
CREATE TABLE, SELECT/INSERT/UPDATE tables
|
Role use
|
Database access
through a granted role
|
The generation of
auditing action can be made conditional; there is a WHEN clause which accepts
expressions returning a boolnean value. The general form of the statement is
CREATE
AUDIT POLICY <policy-name>
<component-usage-clause>
<privilege-standard-actions-audit-clause>
<role-audit-clause>
WHEN <boolean-expr>
The following
sections discuss a few examples of policy driven auditing using CREATE AUDIT
POLICY statement.
- Privilege audit policy – see example # 1 below
- Standard action audit policy – see example # 2 below
- Role audit policy – see example # 3 below
- Component actions audit policy – see example # 4 below
- Audit policy auditing both privilege + standard action – see example # 5 below
- Conditional auditing using CREATE AUDIT POLICY...WHEN – see example # 6 below
Pre-requisite:
Unified Auditing
should have been enabled for the database home from the OS.
Other topics discussed in this page
Enabling
unified auditing for the Oracle home
About Spillover
audit files
Purging audit
records from the database
Example # 1 - Privilege audit policy example
System privileges
like CREATE SESSION, CREATE TABLE, CREATE PROCEDURE are auditable. The privileges clause of the create audit policy statement enables
auditing of system privilege usage.
For example, if the
user HR user creates tables using the CREATE TABLE privilege, it can be audited
by creating a privileges auditing policy …
SQL>
CREATE AUDIT POLICY audit_syspriv_pol
PRIVILEGES create session, create table,
create procedure;
2
Audit
policy created.
Trigger the auditing.
Executing the
audited action – let’s do a create table in the HRPDB container.
SQL>
show user
USER
is "HR"
SQL>
show con_name
CON_NAME
------------------------------
HRPDB
SQL> create table audit_test
2 as select * from emp;
Table created.
SQL>
Examine the audit records
Log on as a
privileged user like sys to examine the audit records.
In the screen grab
below, we are executing a audit trail flush using dbms_audit_mgmt.flush_unified_audit_trail to ensure that the audit
records which were generated in SGA are written down to the disk, so that the
records can be queried using the UNIFIED_AUDIT_TRAIL view.
SQL> show user
USER is "SYS"
SQL> show con_name
CON_NAME
------------------------------
HRPDB
SQL> execute dbms_audit_mgmt.flush_unified_audit_trail;
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 select dbusername,object_name,action_name
from unified_audit_trail
2* where dbusername='HR' and event_timestamp
> trunc(sysdate)
SQL> /
DBUSERNAME
OBJECT_NAME ACTION_NAME
-------------------- --------------------
----------------------------------------
HR
AUDIT_TEST CREATE TABLE
HR
EMP SELECT
SQL>
Doing a clean up below
using the NOAUDIT statement; disabling first, and then deleting the audit
policy …(it requires privileged access)
SQL>
noaudit policy audit_syspriv_pol;
Noaudit
succeeded.
SQL>
drop audit policy audit_syspriv_pol;
Audit
Policy dropped.
Example # 2 – Standard Action audit policy example
Standard actions are
the actions like select, insert, updated, delete, create, drop. Create audit policy such that any select and
update on the table HR.EMP in the HRPDB database will generate audit records.
Steps are given below.
1. Connect to the
hrpdb container as sysdba and create the audit policy with the CREATE audit
policy…ACTIONS statement. The ACTIONS
clause accepts a actions list as an argument.
CREATE AUDIT POLICY test_objpriv_audits
ACTIONS SELECT on
HR.EMP, UPDATE on HR.emp;
Next, query the audited
table to generate audit records.
SQL>
select * from emp fetch first 10 rows only;
ID NAME
----------
--------------------
101 EMP_1
102 EMP_2
103 EMP_3
104 EMP_4
105 EMP_5
106 EMP_6
107 EMP_7
108 EMP_8
109 EMP_9
110 EMP_10
10
rows selected.
Checking the audit records generated
From the console
connect to the HRPDB container as a privileged user.
[oracle@laboms
~]$ sqlplus sys/oracle@localhost:1521/hrpdb as sysdba
SQL*Plus:
Release 12.1.0.1.0 Production on Sat Jul 5 12:00:12 2014
Copyright
(c) 1982, 2013, Oracle. All rights
reserved.
Connected
to:
Oracle
Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With
the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and
Unified Auditing options
Querying the
UNIFIED_AUDIT_TRAIL view as SYSDBA, an
inspection of the columns DBUSERNAME, OBJECT_NAME, ACTION_NAME, and EVENT_TIMESTAMP
shows that the SELECT has been audited. (query output formatted for readability)
SQL>
set lines 150
column
dbusername format a20
column
object_name format a20
column
action_name format a40
column
audit_type format a20
column
system_privilege_used format a20 heading SYS_PRIV_USED
SQL>
SQL> SQL> SQL>
SQL>
select
dbusername,object_name,action_name from unified_audit_trail
where dbusername='HR' and event_timestamp >
trunc(sysdate)
2
3 /
DBUSERNAME OBJECT_NAME ACTION_NAME
-------------------- --------------------
------------
HR EMP SELECT
HR EMP SELECT
HR LOGOFF
SQL>
Disabling first, and
then deleting the policy…(requires privileged access)
SQL>
NOAUDIT POLICY
2
audit_objpriv_pol;
Noaudit
succeeded.
SQL>
DROP audit policy
2
audit_objpriv_pol;
Audit
Policy dropped.
Example # 3 - Role audit policy example
Role usage can be audited with an audit policy. For example,
suppose there is a role created on the database called hr_admin, which has the
ability to create a db user.
We will grant it to hr_admin, and create an audit policy to
audit usage of this role.
The HR admin will be able to create a database user – and it
will be audited.
Creating the role (hrpdb container) and granting it to the
HR user as SYSDBA
SQL> create role hr_admin;
Role created.
SQL> grant create user to hr_admin;
Grant succeeded.
SQL> grant hr_admin to hr;
Grant succeeded.
Creating audit policy
SQL> create audit policy
2 audit_role_pol
3 roles hr_admin;
Audit policy created.
SQL> audit policy audit_role_pol;
Audit succeeded.
Creating a user as
the HR db user
[oracle@laboms ~]$ sqlplus hr/hr@localhost:1521/hrpdb
SQL*Plus: Release 12.1.0.1.0 Production on Sun Jul 6
08:30:55 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Last Successful login time: Sun Jul 06 2014 08:02:13 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 -
64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real
Application Testing
and Unified Auditing options
SQL> create user user001
2 identified by password123
3 default tablespace ts_hr;
User created.
Querying
the audits, we can see that the CREATE USER USER001 has been audited.
SQL> execute dbms_audit_mgmt.flush_unified_audit_trail;
PL/SQL procedure successfully completed.
SQL> @query_audits.sql;
SP2-0310: unable to open file "query_audits.sql"
SQL> @query_audit.sql;
DBUSERNAME
AUDIT_TYPE ACTION_NAME OBJECT_NAME SYS_PRIV_USED
--------------- --------------- ---------------
-------------- --------------------
HR
Standard CREATE USER USER001 CREATE USER
HR Standard LOGOFF
Example # 4 – Component actions audit policy example
Usage of a database
component like Data Pump, or Sql*Loader direct-path load can be audited by the
COMPONENTS keyword supported by the CREATE AUDIT POLICY statement.
Let us audit the export of
a database by the system user who has the EXP_FULL_DATABASE privilege. To
create an audit policy which will audit component usage, use this syntax.
CREATE AUDIT POLICY audit_dpump_export_pol
ACTIONS COMPONENT=datapump
export;
SQL> show user
USER is
"SYS"
SQL> CREATE AUDIT POLICY audit_dpump_export_pol
ACTIONS COMPONENT=datapump export;
2
Audit policy
created.
SQL> audit policy
audit_dpump_export_pol;
Audit succeeded.
To
test the auditing perform a data pump export of the HRPDB container.
[oracle@laboms ~]$ expdp system/oracle@localhost:1521/hrpdb directory=TMPDIR
Export: Release
12.1.0.1.0 - Production on Sat Jul 5 17:37:45 2014
Copyright (c) 1982,
2013, Oracle and/or its affiliates. All
rights reserved.
Connected to: Oracle
Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the
Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing
options
Starting
"SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@localhost:1521/hrpdb
directory=TMPDIR
Estimate in progress
using BLOCKS method...
Processing object
type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation
using BLOCKS method: 0 KB
Processing object
type SCHEMA_EXPORT/USER
Processing object
type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object
type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object
type SCHEMA_EXPORT/TABLE/TABLE
Processing object
type SCHEMA_EXPORT/TABLE/COMMENT
Processing object
type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object
type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object
type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object
type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object
type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object
type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Master table
"SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully
loaded/unloaded
******************************************************************************
Dump file set for
SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/oradata/tmp/expdat.dmp
Job
"SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at
Sat Jul 5 17:38:41 2014 elapsed 0 00:00:39
Back
in sqlplus, let us examine the audit records as the privileged user. The output
below (edited) shows one audit record created of the export session.
[oracle@laboms
~]$ sqlplus sys/oracle@localhost:1521/hrpdb as sysdba
…
SQL>
set lines 150
column
dbusername format a20
column
object_name format a20
column
action_name format a40
column
audit_type format a20
column
system_privilege_used format a20 heading SYS_PRIV_USED
SQL>
select dbusername,audit_type,action_name,system_privilege_used
from unified_audit_trail
where dbusername='SYSTEM'
and audit_type='Datapump'
and event_timestamp > trunc(sysdate)
2
3 4 5
6 /
DBUSERNAME AUDIT_TYPE ACTION_NAME SYS_PRIV_USED
--------------------
-------------------- ----------------------------------------
--------------------
SYSTEM Datapump EXPORT
SQL>
Example # 5 - Combined privilege + action audit example
One audit policy can
specify multiple types of auditing. For example, if auditing is required for
both privilege usage as well as a standard action, a single CREATE AUDIT POLICY
statement will do it.
For example, to audit
CREATE table and also a DELETE on a table, we can create an audit policy such
as.
SQL> CREATE AUDIT POLICY audit_syspriv_objpriv_pol
Privileges CREATE
table
Actions DELETE on hr.emp;
2 3
Audit policy created.
SQL> audit policy audit_syspriv_objpriv_pol;
Audit succeeded.
SQL>
Connecting as the HR user and creating a table to see if it
gets audit.
SQL> create table audit_trial
2 (
3 emp_id number(9),
4 join_date date,
5 dept_id number(9)
6 );
Table created.
Likewise deleting a record
from the emp table.
SQL> delete from emp
2 where id=200;
1 row deleted.
Lastly, querying the audit
records as SYS
SQL> execute dbms_audit_mgmt.flush_unified_audit_trail;
PL/SQL procedure successfully completed.
SQL> set lines 150
column dbusername format a20
column object_name format a20
column action_name format a40
column audit_type format a20
column system_privilege_used format a20 heading
SYS_PRIV_USED
SQL> @query_audit.sql;
DBUSERNAME
AUDIT_TYPE ACTION_NAME OBJECT_NAME SYS_PRIV_USED
--------------- --------------- ---------------
-------------- --------------------
HR
Standard CREATE TABLE AUDIT_TRIAL CREATE TABLE
HR
Standard DELETE EMP
SQL>
It is seen in the query
result, that there are audit records from the CREATE and DELETE.
Example # 6 - Conditional audit example using WHEN
The auditing can be made
conditional on run-time environment, for example auditing can be made to occur
only when the audited event occurs at a particular time of the day. The CREATE
AUDIT POLICY has syntax to support conditional auditing. To create such a
policy use the WHEN <expression> syntax.
For example, below is an
attempt to define an audit policy which audits selects on the hr.emp table by
the HR user.
SQL> CREATE AUDIT POLICY audit_conditional_pol
ACTIONS SELECT on hr.emp,
UPDATE
on hr.emp
WHEN 'sys_context(''userenv'',''SESSION_USER'')=''HR'''
EVALUATE PER
STATEMENT;
2 3
4 5
Audit policy created.
SQL> audit policy audit_conditional_pol;
Audit succeeded.
Generating audit
activity:
SQL>
show user
USER
is "HR"
SQL>
show con_name
CON_NAME
------------------------------
HRPDB
SQL>
select * from hr.emp fetch first 10 rows only;
ID NAME
----------
--------------------
101 EMP_1
102 EMP_2
103 EMP_3
104 EMP_4
105 EMP_5
106 EMP_6
107 EMP_7
108 EMP_8
109 EMP_9
110 EMP_10
10
rows selected.
Querying the audit
records as the privileged user SYS:
SQL>
@query_audit.sql;
DBUSERNAME
AUDIT_TYPE ACTION_NAME OBJECT_NAME SYS_PRIV_USED
---------------
--------------- --------------- -------------- --------------------
HR Standard SELECT EMP
The WHEN expression cannot
contain complex expressions like references to SYSDATE. For example this policy
tries to audit activity based on system time:
Wrote
file afiedt.buf
1
CREATE AUDIT POLICY audit_conditional_pol
2
ACTIONS SELECT on hr.emp,
3 UPDATE on hr.emp
4 WHEN 'to_char(sysdate,''hh24'') not between
''09'' and ''18'''
5*
EVALUATE PER STATEMENT
SQL>
/
CREATE
AUDIT POLICY audit_conditional_pol
*
ERROR
at line 1:
ORA-46368:
Audit policy does not have a simple rule condition.
Such a requirement can perhaps,
be handled by creating a fine-grained auditing policy using DBMS_FGA.
About enabling unified auditing
To enable unified
auditing for the oracle home, it is necessary first to shut down all instances
and listeners running off that home, and re-link the Oracle binaries with
uniaud_on switch at the command line as shown:
$>
cd $ORACLE_HOME/rdbms/admin/
$ > make –f ins_rdbms.mk uniaud_on
ioracle
Note:
Audit records are
written to the SGA and flushed to disk periodically (every 3 seconds), but less
frequently if there is a lot of database activity. It may be necessary to flush
the memory and force a disk write, to view audit information.
When the database is
available, audit records are written to the in-database audit trail which is
now housed in the SYSAUX tablespace (by default), and owned by the AUDSYS user.
Oracle Database 12c provides a new view called UNIFIED_AUDIT_TRAIL to access
audit records.
About Spillover audit files
Auditing continues
even when the database is not available
or writable. In these cases, the audit records are written to an on-disk
audit trail, to allow auditing of actions by privileged users like backup,
recovery, etc.
The location of this
file is $ORACLE_BASE/audit where audit files will be created such as shown
below.
These files are
known as the spillover audit files. It is possible to load these on-disk audits
into the database using the DBMS_AUDIT_MGMTN package. The procedure name is DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES.
Purging audit records:
Audit records can be
purged altogether using the DBMS_AUDIT_MGMT.clean_audit_trial procedure. The
pl/sql block below cleans all audit records..
begin
dbms_audit_mgmt.clean_audit_trail(
audit_trail_type=>dbms_audit_mgmt.audit_trail_unified,
use_last_arch_timestamp=>false,
container=>dbms_audit_mgmt.container_current);
end;
/
Conclusion
In Oracle Database
12c auditing has evolved to support complex auditing requirements with policy
based auditing mechanism.
No comments:
Post a Comment