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
|
If you like Oracle tuning, see the
book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning
tips and scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |