Oracle Security Shared and Non-Shared Policies 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.
 

---

Shared and Non-Shared Policies

The policy types are further subdivided by the way they are added to a group of objects. While defining policies to a group of objects, often it becomes necessary to define the same policy function on all of them. For instance, in our example database, the tables CLAIMS and CLAIM_LINES may have restrictions based on the column CLAIM_ID, i.e. both the tables have been applied predicate

where claim_id in (<list of claim ids>)

Since the predicate applied is the same, we can define the same policy on both the tables and their values are determined from the predicate function. If this is possible, then we can define the policy as a shared one, i.e. shared between multiple objects. Shared policies can be defined for both Static and Context Sensitive ones. They are defined by setting the parameter policy_type to either shared_static or shared_context_sensitive. The following code segment creates a shared context sensitive policy.

begin
dbms_rls.add_policy (
    object_schema    => 'CLAIM_SCHEMA',
    object_name      => 'CLAIMS',
    policy_name      => 'CLAIM_VIEW_POLICY',
    function_schema  => 'CLAIM_SCHEMA',
    policy_function  => 'GET_AUTH_PROVIDERS',
    statement_types  =>
        'INSERT, UPDATE, DELETE, SELECT',
    update_check     => TRUE,
    enable           => TRUE,
    policy_type      =>
           dbms_rls.shared_context_sensitive
);
end;
/

Since dynamic policies, by definition, are always executed, there is no equivalent shared dynamic policy. In summary, there are five new types of policy types in Oracle 10g.

* dynamic

* shared_context_sensitive

* context_sensitive

* shared_static

* static

Relevant Columns

Remember, in Oracle 9i and below, if a policy has been defined on a table, the policy is always applied, regardless of which columns are accessed. Sometimes it may be necessary to relax the policy depending upon the type of data accessed. For instance, when a user selects Social Security Numbers (SSN) from the claims table, it should be restricted to only the authorized claims, not all. However, if a user finds out how many rows are there in the claims table, the restriction may be relaxed to report the count on all rows, without restriction. In Oracle 9i and below this is not possible; either the policy is not applied or if applied then the policy predicate is applied for each query.

Oracle 10g allows selective restriction on the rows, based on which columns are accessed in the query, quite similar to the audit columns in Fine Grained Auditing. The relevant columns are indicated in the add_policy procedure by a new parameter sec_relevant_cols. In our earlier example, if we wanted to apply the predicate only to the columns SSN and CLAIM_AMOUNT, then we would have defined the policy as

begin
dbms_rls.add_policy (
    object_schema     => 'CLAIM_SCHEMA',
    object_name       => 'CLAIMS',
    policy_name       => 'CLAIM_VIEW_POLICY',
    function_schema   => 'CLAIM_SCHEMA',
    policy_function   => 'GET_AUTH_PROVIDERS',
    statement_types   =>
        'INSERT, UPDATE, DELETE, SELECT',
    update_check      => TRUE,
    enable            => TRUE,
    policy_type       => dbms_rls.dynamic,
    sec_relevant_cols => ‘SSN, CLAIM_AMOUNT’
);
end;
/

Note the relevant columns are defined inside single quotes separated by commas.

Examples

Imagine the user making several queries on the table. Here are the various situations and the effect on the policy being applied and the rows returned.

* select * from claims;

Restricted. Only the authorized rows are returned.

The query selected all the columns, including the relevant columns SSN and CLAIM_AMOUNT; hence the restrictions were applied.

* select count(*) from claims;

Not Restricted. The actual count of the records in the table is returned. Since the user did not specifically select any of the relevant columns, the restrictions were not placed.

* select claim_id from claims;

Not Restricted. Since the query has no reference to SSN or CLAIM_AMOUNT, which are the relevant columns, all the records will be returned.

* select claim_id from claims where SSN = ‘123456789’;

Restricted. Although the query did not specify the columns SSN or CLAIM_AMOUNT, the predicate has the SSN column; hence the restrictions are placed.

Auditing Enhancements

Oracle 10g supports a new type of auditing known as Extended Database Auditing. If specified, the regular AUDIT features also capture the exact SQL statements, even the bind variables. The option is specified at the instance level in the initialization file as

audit_trail = db_extended

The audit trails now provide a lot more information. The aud$ table has been expanded with several additional columns. Table 13.1 shows the additional columns of aud$ and their meaning.

COLUMN NAME	DESCRIPTION
NTIMESTAMP#	Oracle 9i introduced a new timestamp datatype, which can theoretically store to a dimension of a millisecond. This column stores the extended timestamp in that format.
PROXY$SID	The SID of the session, if the user is connected using a proxy authorization. Typically users authenticated through external mechanisms such as directory are connected through proxy users.
USER$GUID	If the user is authenticated via a means other than database authentication, e.g. through a directory, then this column shows the global user ID of the user. These types of users are known as Enterprise Users.
INSTANCE#	In Real Application Clusters, this column stores the instance number to which the user was connected when the audit trail was triggered.
PROCESS#	The operating system process identifier of the user’s process is stored here.
TRAN_ID	The transaction id, if it is a transaction that triggered the audit event, is stored here. This has the format u.s.q where “u” is the rollback (or UNDO) segment number, “s” is the slot number and “q” is the sequence number in that slot.
AUDITID	Audit Identifier
SCN	The System Change Number where this particular audit entry occurred. This is very useful in reconstructing the exact replica of the database at the time of the audit trail creation using the flashback database feature.
DBID	The Database Identifier.
SQLTEXT	The exact SQL text entered by the user is captured in this column. This column is populated only if extended auditing is enabled.
SQLBIND	If the SQL text contains some bind variables, the variables and their values are stored in this column.

Table 13.1 Additional Columns of AUD$

These new columns are also visible in the view dba_audit_trail, although in a much more user-friendly manner. Here are the additional columns of the view, described in brief in Table 13.2.

COLUMN NAME	DESCRIPTION
EXTENDED_TIMESTAMP	The Extended Timestamp.
PROXY_SESSIONID	The SID of the proxy user session.
GLOBAL_UID	The global user ID of the enterprise user.
INSTANCE_NUMBER	The instance number if using RAC.
OS_PROCESS	The OS Process Id.
TRANSACTIONID	The transaction id.
SCN	The System Change Number.
SQL_BIND	The bind variables and the value they are set to.
SQL_TEXT	The SQL statement that triggered the audit.

Table 13.2 Additional Columns of dba_audit_trail

Using Extended Audit Functionality, several types of events can be audited, without any additional coding.

 

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.