Oracle Database Security

Oracle Tips by Burleson Consulting

This is an excerpt from "Oracle 10g New Features for Administrators" by Ahmed Baraka.

In Oracle 10.2 auditing records can be output in XML. XML files are known to be portable and readable. Furthermore, they can easily be parsed by any XML parser to extract useful information from them.

ALTER SYSTEM SET audit_trail = 'XML' SCOPE = SPFILE

The previous setting does not record the SQL statement issued by the session. To do that you should enable the extended XML auditing option by issuing the following command:

ALTER SYSTEM SET audit_trail = 'XML, extended'
SCOPE = SPFILE

Audit trail files are written to directory specified by AUDIT_DUMP_DEST parameter

Defaults to $ORACLE_BASE/admin/<SID>/adump

Note: You can still view contents of the auditing XML files using SQL commands by querying the view V$XML_AUDIT_TRAIL. The only difference between data displayed in the view and in XML files is the column EXTENDED_TIMESTAMP which has the UTC-based format in the XML files whereas it is displayed on the local time zone in the view.

VPD and Auditing Enhancements

VPD policies apply to tables, views, and synonyms. You can apply VPD policies to SELECT, INSERT, DELETE, UPDATE, and any INDEX statements.

Column-Level VPD

A column-level VPD policy applies only to tables and views and not to synonyms. You may apply a policy function to queries as well as DML statements. When you use column-level VPD, you have a choice of two types of behavior by the policy:

o Default behavior will restrict the number of rows returned by any query that contains the security relevant columns.

o Column-masking behavior, on the other hand, will return all the rows, but show null values for the security-relevant columns in those rows.

Creating a Column-Level Policy

DBMS_RLS.ADD_POLICY (OBJECT_SCHEMA=>'scott',
OBJECT_NAME=>'emp',
POLICY_NAME=>'test_policy',
FUNCTION_SCHEMA=>'test_schema',
POLICY_FUNCTION=>'test_function�,
STATEMENT_TYPE=�insert,update�
SEC_RELEVANT_COLS=>'salary,commission')

Note: You can implement column-masking behavior by using the SEC_RELEVANT_COLS_OPT =>DBMS_RLS.ALL_ROWS parameter.

Note: The default of STATEMENT_TYPE is to apply to all the types except INDEX.

A function policy can be created as in the following:

CREATE OR REPLACE FUNCTION test_function
(objowner IN VARCHAR2, objname IN VARCHAR2)
RETURN VARCHAR2 AS
con VARCHAR2(200);
BEGIN
con := 'deptno = 5';
RETURN (con);
END test_function;

Note: You can grant the privilege GRANT EXEMPT ACCESS POLICY to a user so that he or she may bypass a security policy.

New Policy Types

Dynamic

By default, Oracle VPD policy functions are dynamic in nature. That is, Oracle will execute the security policy statement each time a DML statement refers to it and this leads to high resources consumption.

Static Policies

The database executes a static policy function just once, and caches the predicate resulting from the policy evaluation in the SGA. It then applies this predicate to all queries accessing the protected objects.

Static policy can be defined in DBMS_RLS.ADD_POLICY by passing the following parameter POLICY_TYPE =>DBMS_RLS.STATIC

If you want to allow the sharing of the same static policy function over different database objects, you can set the POLICY_TYPE parameter to the following value:

POLICY_TYPE => DBMS_RLS.SHARED_STATIC

Context-Sensitive Policies

These policies will change based on any session context changes.

Context-sensitive VPD policies are particularly useful in a web-based application with connection pooling, where a session needs to change its behavior depending on the CLIENT_IDENTIFIER of the user using the session at any given moment.

Context-Sensitive policies can be defined in DBMS_RLS.ADD_POLICY by passing the following parameter POLICY_TYPE =>DBMS_RLS.CONTEXT_SENSITIVE

If you want to allow the sharing of the same context sensitive policy function over different database objects:

POLICY_TYPE =>DBMS_RLS.SHARED_CONTEXT_SENSITIVE