8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 9i » Here

Security Enhancements In Oracle9i

There are a number of security enhancements in Oracle9i. Here I will focus on those directly related to the "Oracle9i Database: New Features For Administrators" OCP exam.

Related articles.

Internal Connections Deprecated

The CONNECT INTERNAL syntax has finally been deprecated. Connecting as a priviledged user is now only possible using the following methods.

Remeber also, Server Manager (SVRGMRL) has been deprecated so all administration must be done using SQLPlus.

Secure Application Roles

Secure application roles are a mechanism for checking that a user is entitled to have a role granted on them at runtime. Previous versions of Oracle allowed the role to have a password, but secure application roles have their validation done using packages assigned to them during creation.


Amongst other things the security package can use the following session information to decide if the role should be granted.

If the role is valid for this access it can be set using the following.


Partitioned Fine Grain Access Control

In Oracle8i only a single security policy could be assigned to a table. This means that if two independent applications referred to a single table, the security policy assigned to that table would have to be developed to cope with all possibilities for both applications. In Oracle9i multiple security policies can be assigned to a single table, using an application context to decide between them. This is implemented using policy groups.

First policy groups are defined for each application. Then security policies are assigned to these policy groups. Then an application/driving context is defined which sets the policy group for the current login so when tables are accessed, only policies from the selected policy group are executed.

Security policies associated with the predefined policy group SYS_DEFAULT are always executed along with the application specific policies, making them global policies. All Oracle8i security policies are automatically assigned to this policy group.

Global Application Context

When virtual private database (VPD) and connection pooling are used in conjunction, one or more application contexts can be shared between connections, improving performance as per-user context setup is avoided.

The global context is defined using the following syntax.


Once created, this context is stored in the SGA, reducing initialization time for other connections. The Dbms_Session package has been updated to include routines to assign and clear contexts and identifiers to facilitate this process. The new procedures are listed below.

When the application server starts up it makes several connections to the same database user. When the a user logs into the application server he is allocated to one of the connections and given a temporary client id. The application server then sets his secific context along with his current session identifier.

DBMS_SESSION.set_context(namespace, attribute, value, username, client_id);
-- eg.
DBMS_SESSION.set_context('tsh', 'id', 'TIM', 'APP_SERVER_DB_USER', 12345);

This way a single database user can use differing contexts depending on the validation done by the application server. Once the user disconnects the application can issue the following.


Fine Grained Auditing (FGA)

Fine grained auditing extends Oracle existing auditing capabilities by allowing the user to audit actions based on user-defined predicates. Assuming that finance clerks are allowed to look at the salaries of all employees except those with salaries greater than £50,000, the following audit may be assigned to the employees table using the Dbms_FGA package.

  object_schema   => 'SCOTT',
  object_name     => 'EMPLOYEES',
  policy_name     => 'SALARY_CHK_AUDIT',
  audit_condition => 'SALARY > 50000',
  audit_column    => 'SALARY');

From now on, if salaries greater than £50,000 are queried the action will be logged in the FGA_LOG$ table.

If extra processing is required based on an FGA event this can be accomplished by defining a database procedure and associating this to the audit event.

  object_schema   => 'SCOTT',
  object_name     => 'EMPLOYEES',
  policy_name     => 'SALARY_CHK_AUDIT',
  audit_condition => 'SALARY > 50000',
  audit_column    => 'SALARY',
  handler_schema  => 'TSH',
  handler_module  => 'FIRE_CLERK',
  enable          => TRUE);

The DBMS_FGA package contains the following procedures.

Other New Features

For more information see:

Hope this helps. Regards Tim...

Back to the Top.