Oracle Security Shortcuts and Their Auditing Actions Oracle Security Tips by Burleson Consulting

This is an excerpt from the bestselling book "Oracle Privacy Security Auditing", a complete Oracle security reference with working Oracle security scripts.
 

---

Oracle Shortcuts

Sometimes you may want to audit a group of options on all objects of a specific type. For instance, you want to audit CREATE, DROP and TRUNCATE on all tables by user JUDY whenever they are not successful. You could issue:

audit create table by judy whenever not successful;
audit drop table by judy whenever not successful;
audit truncate table by judy whenever not successful;

Or you could just issue:

audit table by judy whenever not successful;

This will audit the statements CREATE, DROP and TRUNCATE only, not other statements such as INSERT, DELTE, ALTER, etc. Therefore it is important to remember which of the operations are audited and which are not. The shortcuts and their actions have been given in table [lbv2]8.3 below. The general syntax is:

AUDIT  <shortcut> ……

The available shortcuts have been described in Table 8.3 below.

IF THE SHORTCUT IS	THEN THE AUDITING OPTIONS ARE SET FOR
CLUSTER	CREATE CLUSTER
AUDIT CLUSTER	DROP CLUSTER
TRUNCATE CLUSTER	CONTEXT
CREATE CONTEXT	DROP CONTEXT
DATABASE LINK	CREATE DATABASE LINK
DROP DATABASE LINK	DIMENSION
CREATE DIMENSION	ALTER DIMENSION
DROP DIMENSION	DIRECTORY
CREATE DIRECTORY	DROP DIRECTORY
INDEX	CREATE INDEX
ALTER INDEX	DROP INDEX
ANALYZE ANY	NOT EXISTS

This is a special case of shortcut. If this is set, any SQL statement failing due to the non-existence of an object triggers and audit record.

PROCEDURE

CREATE FUNCTION

CREATE LIBRARY

CREATE PACKAGE

CREATE PACKAGE BODY

CREATE PROCEDURE

DROP FUNCTION

DROP LIBRARY

DROP PACKAGE

DROP PROCEDURE

JAVA STORED PROCEDURES

PROFILE

CREATE PROFILE

ALTER PROFILE

DROP PROFILE

PUBLIC DATABASE LINK

CREATE PUBLIC DATABASE LINK

DROP PUBLIC DATABASE LINK

PUBLIC SYNONYM

CREATE PUBLIC SYNONYM

DROP PUBLIC SYNONYM

ROLE

CREATE ROLE

ALTER ROLE

DROP ROLE

SET ROLE

ROLLBACK SEGMENT

CREATE ROLLBACK SEGMENT

ALTER ROLLBACK SEGMENT

DROP ROLLBACK SEGMENT

SEQUENCE

CREATE SEQUENCE

DROP SEQUENCE

SESSION

The connections and disconnections by users are audited.

SYNONYM

CREATE SYNONYM

DROP SYNONYM

SYSTEM AUDIT

This is also a special case of auditing. When a user issues the AUDIT command itself, or NOAUDIT, this setting triggers an audit record to be generated.

SYSTEM GRANT

Whenever the user grants a system privilege to another user or itself, this setting triggers an audit trail.

TABLE

CREATE TABLE

DROP TABLE

TRUNCATE TABLE

TABLESPACE

CREATE TABLESPACE

ALTER TABLESPACE

DROP TABLESPACE

TRIGGER

CREATE TRIGGER

ALTER TRIGGER

Important: Only the statements that enable or disable a trigger by the ALTER TRIGGER command are logged, not altering the trigger body.

DROP TRIGGER
ALTER TABLE

Only the trigger related statements in ALTER TABLE, such as adding or dropping triggers are audited.

TYPE

CREATE TYPE

CREATE TYPE BODY

ALTER TYPE

DROP TYPE

DROP TYPE BODY

USER

CREATE USER

ALTER USER

DROP USER

VIEW

CREATE VIEW

DROP VIEW

Table 8.3 Shortcuts and their Auditing Actions

Oracle also provides another type of shortcut to audit some commonly used system privileges. They are referred to as DBA, RESOURCE and CONNECT to record groups of privileges.

* Note: The words CONNECT, RESOURCE, and DBA should not be confused with the database roles of the same names. These are just shortcuts to a group of privileges.

The following example shows how the shortcuts are used:

audit connect;

This will enable auditing for CREATE SESSION, since CONNECT is a shortcut for it. The shortcut RESOURCE enables auditing on the following:

* ALTER SYSTEM

* TABLE

* CLUSTER

* TABLESPACE

* ROLLBACK SEGMENT

* TYPE

* SYNONYM

* VIEW

* SEQUENCE

* DATABASE LINK

* PROCEDURE

So when you use AUDIT RESOURCE, the auditing is enabled for all objects mentioned above.

The third shortcut, DBA does the same as enabling auditing on the following:

* SYSTEM AUDIT

* USER

* PUBLIC SYNONYM

* PUBLIC DATABASE LINK

* ROLE

* SYSTEM GRANT

These three shortcuts can be used to enable auditing quickly. It may not be practical to audit everything these shortcuts offer since some may be left out. If necessary, one convenient way may be to enable auditing by the shortcut and then selectively disable auditing. For instance, if all the audit options in the shortcut RESOURCE are necessary, except VIEW, they can all be enabled by AUDIT RESOURCE and then the undesired one can be disabled by the NOAUDIT VIEW command.

This shortcut concept may not be particularly useful and can be prone to mistakes. Remember from the above discussion that a shortcut TABLE refers to only CREATE TABLE, DROP TABLE and TRUNCATE TABLE. This shortcut does not include other important operations such as ALTER TABLE, even though the user may expect it to. It is perhaps best to avoid shortcuts at least to avoid confusion, if nothing else.

Object Auditing

This type of auditing is based on specific objects and the actions made on them, not general statements, or using privileges. An example may make it clearer:

audit select on claim_schema.claims by access;

This will enable audit trails when any user selects from the table CLAIMS. Note the important difference here – there is no user specified. Just the object is specified and the frequency of triggering – by session or by access. So any user who selects from this table will trigger auditing.

In general, the audit command takes the following form:

AUDIT
<statement_list>|ALL
[ON <objectname>|DEFAULT]
[BY SESSION|ACCESS]
[WHENEVER [NOT] SUCCESSFUL]

Where:

statement_list are the allowable statements that can be audited. The list is different in every version; a recent list for each version may be found in the data dictionary view stmt_audit_option_map. Table 8.1 has a list of such statements. If more than one statement is given, they are separated by comma.

If all statements are required to be audited on an object, the keyword ALL can be used.

objectname is the name of the object on which the actions are to be audited. A list of object names can be given also, separated by commas. The objects can also be specified in the <owner>.<objectname> format

Examples

To enable audit on all types of privileges on the object, use the clause ALL PRIVILEGES in the statement. An example is given below for illustration.

audit all privileges;

The use of ON DEFAULT specifies that those auditing options be turned on for any objects of that type created after this statement is given. For instance, if we want to enable select auditing on all tables created, we would issue:

audit select on default by access;

The setting of auditing on default objects can be seen from the data dictionary view all_def_audit_opts, as shown below:

select * from all_def_audit_opts
/

ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE
--- --- --- --- --- --- --- --- --- --- --- --- ---
-/- -/- -/- -/- -/- -/- -/- -/- -/- A/A -/- -/- -/-

Note how the SEL column shows by access on both successful and unsuccessful attempts.

Checking for Unsuccessful Actions

If the action is not successful, and the action is audited for non-success, then an audit record is generated. The view dba_audit_exists shows those records. For instance, assume we set up auditing for CREATE TABLE by any user for both success and non-success. We would issue the following two statements consecutively.

connect claim_schema/claim_schema
create table mytab1 (col1 number)
/
create table mytab1 (col1 number)
/

Note how the attempt was made to create the same table again, and it will fail with the following message:

ORA-00955: name is already used by an existing object

The success and failure of these two statements will be logged in the audit table. The failure of the statement can be seen from records in the dba_audit_exists as follows:

select ACTION_NAME, returncode
from dba_audit_exists
where owner = 'CLAIM_SCHEMA'
and obj_name = 'MYTAB1';

The result comes back as:

ACTION_NAME                 RETURNCODE
--------------------------- ----------
CREATE TABLE                       955

The only record here is the failure of the second statement. The RETURNCODE field is the important since it records the exact error condition that caused this failure. In this case, it is 955, which corresponds to the ORA-00955 thrown earlier.

It is important to note that not all types of errors are reported in this view. For instance ORA-955 is reported, but “ORA-1950 no privileges on tablespace XXX” is not reported. Reported audit records are in the dba_audit_trail and dba_audit_object views.

This may cause some confusion while searching for unsuccessful operations in the database. It may be worthwhile to query only the view dba_audit_trail for everything, including unsuccessful operations.

 

This is an excerpt from the book "Oracle Privacy Security Auditing". You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle security and auditing scripts.