Create security policies that are associated with tables or views. (Associating security policies with tables and views, rather than applications or user ids, means that the security system is contained and managed from within the database, rather than in applications.)
Security policies can be created for the different SQL statements (SELECT, INSERT, UPDATE and DELETE). The server invokes the security policy for the table or view that is accessed with the particular SQL statement. This is implemented by Oracle’s transparent rewrite of the query where it appends a WHERE clause that reflects the proper security policy. For example, this SELECT statement:
SELECT * FROM DEPARTMENT_TABLE ;
might be internally rewritten as this statement after applying a security policy for the DEPARTMENT_TABLE when SELECTs are involved:
SELECT * FROM DEPARTMENT_TABLE
WHERE DEPT = ‘ACCOUNTING’;
The GUI to create and manage security policies is called the Oracle Policy Manager. It is part of the Oracle Enterprise Manager (OEM) GUI. With it, you can create and manage security policies, associate them with tables and views, and create and manage application contexts (described below).
- VPDs were introduced in Oracle8i, and 9i greatly extends their usefulness through new features:
- Secure application roles
- Global application contexts
- Partitioned fine-grained access control
The next few sections discuss these new features.
Secure Application Role
8i introduced Secure Application Context, the ability to tailor or limit access to tables and views based on attributes of the user’s session. 9i takes this a step further. Now you can assign a set of security roles for each application, then assign users to the roles. Here are the steps to set this up:
1. Create the role. The role does not refer to a password. Instead it refers to a stored procedure that authenticates whether the user is allowed to use this role.
This example creates a Security Application Role, which is authenticated by the procedure verify_dba_manager:
CREATE ROLE dba_manager IDENTIFIED USING verify_dba_manager;
2. Now write the procedure that authenticates the user for this role. It should contain logic that either verifies the user can be set to this role, or that the user is not eligible to use this role. The Oracle procedure DBMS_SESSION.SET_ROLE is used for this purpose. Here’s an example:
CREATE OR REPLACE PROCEDURE verify_dba_manager
AUTHID CURRENT_USER IS instring VARCHAR2(30);
BEGIN
.... IF (user context is appropriate) ...THEN
DBMS_SESSION.SET_ROLE(‘dba_manager’);
ELSE
RETURN;
END IF;
END;
3. When the application starts, it should enable the role(s) it will use by the
SET_ROLE statement.
Remember that as in 8i, an application can query its context by using the DBMS_SESSION.SYS_CONTEXT package:
SYS_CONTEXT(‘userenv’, ‘attribute’) ;
Global Application Context
When building web applications, for performance reasons you typically use a middle tier with Oracle’s connection pooling. You’ll also want to pool and reuse application contexts – this is referred to as Global Application Context. This is more scalable because applications reuse Global Application Contexts instead of creating individual user sessions. 9i contains a set of procedures to support this feature in its package DBMS_SESSION:
Procedure:
SET_CONTEXT - Sets the global application context
CLEAR_CONTEXT - Clears the context
SET_IDENTIFIER - Associates a pre-established global application context (an existing database connection) with a particular client session
CLEAR_IDENTIFIER - Releases the client session’s access to the global application context
SYS_CONTEXT - Retrieves info about the context associated with the SET_IDENTIFIER that was executed
Here are the steps to using global application contexts:
1. Create a global application context, using the ACCESSED GLOBALLY phrase, to show that it is available for reuse:
CREATE CONTEXT dba_manager USING dba.init ACCESSED GLOBALLY;
2. The middle tier application starts up and establishes database connections. When a user logs in, the application assigns a temporary identifier ID for it. The application returns the ID as a cookie residing on the user’s machine and browser, or maintains it itself.
3. When the application invokes the ‘dba.init’ package in order to initialize the application context, the package issues SET_CONTEXT commands using the temporary ID to set the application’s context.
4. The application gets an existing database connection for this user session by issuing SET_IDENTIFER.
5. The user uses the database application and makes authorized database calls.
6. When done, the client’s access to the global application context is released by CLEAR_IDENTIFIER and CLEAR_CONTEXT.
Partitioned Fine-Grained Access Control
In 9i, you can assign multiple security policies for a table or view. All policies must be satisfied to get data access. In other words, Oracle treats multiple policies as if there is a logical AND relating them.
For better control of the security facility, several Security Policies may be combined into a Security Policy Group. The Group is then associated with an application context, known as the driving application context. Oracle refers to the driving application context to identify and apply the security policy group when users try to reference the data. Oracle applies all security policies in that group to determine if the user can access the data. So security policy groups allow you to partition fine-grained access control based on the driving application context.
In the Oracle Policy Manager GUI tool, there is a folder for Fine-Grained Access Control, and under it, a folder for the Policy Groups. Security Policies are organized under a security policy group (by default called SYS_DEFAULT). Oracle provides the DBMS_RLS package for working with policies, with such procedures as CREATE_POLICY_GROUP, ADD_GROUPED_POLICY, and ADD_POLICY_CONTEXT.
Here’s an example of how to use partitioned fine-grained access control:
Two groups of users have different security needs. Set up a driving application context for each user group (call them USER_1_CONTEXT and USER_2_CONTEXT).
Now set up a security policy group for each (call them USER_1_POLICY_GROUP and USER_2_POLICY_GROUP).
When a user in the first driving application context tries to access data, Oracle applies all security policies in USER_1_POLICY_GROUP and then all in SYS_DEFAULT. When a user in the USER_2_CONTEXT tries to access data, Oracle applies all policies in USER_2_POLICY_GROUP, and then those in SYS_DEFAULT. Only if all policies are satisfied can the user access data.
If you do not set up the driving application context, or if it is NULL, Oracle executes all the policies associated with the table or view. This ensures applications can not circumvent security.
Fine Grained Auditing (FGA)
You can create and manage audit policies using the new Oracle package DBMS_FGA and its procedures ADD_POLICY, DROP_POLICY, DISABLE_POLICY, and ENABLE_POLICY.
When you create an audit policy, it is stored in Oracle’s data dictionary table DBA_AUDIT_POLICIES. When the policy is triggered (through appropriate data access), the audit event data is stored in table DBA_FGA_AUDIT_TRAIL. The information collected includes session id, username, timestamp, policy name, and the SQL query text.
The audit policy is only triggered when the condition you specified in the ADD_POLICY statement is met.
You can also specify an audit column to restrict audits. Only queries meeting the audit condition and referencing the audit column will be audited. Take this example audit policy:
DBMS_FGA.ADD_POLICY (
object_schema => ‘dba_group’,
object_name => ‘dept’,
policy_name => ‘audit_drones’,
audit_condition => ‘emp_status = ‘‘DRONE’ ’ ’,
audit_column => ‘drone_id’ ) ;
Prior to adding the audit_column, any access to the object that meets the audit_condition triggers an audit event. Adding the audit_column, as in the policy definition above, means the audit only occurs if the audit_condition is met and the audit_column is referenced in the user’s query.
You can additionally specify an audit event handler when creating an audit policy. Add lines like these to the sample code above to specify the event handler:
handler_schema => ‘dba’,
handler_module => ‘dba_drone_handler’,
Now, code the audit event handler named DBA_DRONE_HANDLER like any other stored procedure, using the CREATE OR REPLACE PROCEDURE statement.
