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.

Internal Connections Deprecated
Secure Application Roles
Partitioned Fine Grain Access Control
Global Application Context
Fine Grained Auditing (FGA)
Other New Features

Internal Connections Deprecated

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

CONNECT / AS SYSDBA
CONNECT / AS SYSOPER
CONNECT username/password AS SYSDBA
CONNECT username/password AS SYSOPERA

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:

CREATE ROLE tsh_role USING TSH.TSH_Admin;

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

Sys_Context('USERENV','PROXY_USER') = '??'
Sys_Context('USERENV','IP_ADDRESS') = '??'
Sys_Context('USERENV','AUTHENTICATION_DATA') = '??'

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

Dbms_Session.Set_Role('TSH_ROLE');

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:

CREATE CONTEXT tsh USING tsh.init ACCESSED GLOBALLY;

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:

Set_Context
Clear_Context
Set_Identifier
Clear_Identifier

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);
Dbms_Session.Set_Identifier(client_id);
-- eg.
Dbms_Session.Set_Context('tsh', 'id', 'TIM', 'APP_SERVER_DB_USER', 12345);
Dbms_Session.Set_Identifier(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:

Dbms_Session.Clear_Identifier(12345);

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:

Dbms_Fga.Add_Policy(
  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:

Dbms_Fga.Add_Policy(
  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:

Add_Policy
Drop_Policy
Enable_Policy
Disable_Policy

Other New Features

Default Account Security - All default accounts except SYS, SYSTEM, SCOTT, DBSNMP, OUTLN, AURORA$ORB$UNAUTHENTICATED, AURORA$JIS$UTILITY$, OSE$HTTP$ADMIN are locked when the database is created. In future releases more users including SYSTEM will also be locked by default. Oracle expects these accounts to be unlocked using ALTER USER tsh ACCOUNT UNLOCK; only during use, thereby reducing the likelyhood of unauthorised access.
Oracle Label Security (OLS) - Built on the concept of Virtual Private Database (VPD), OLS adds a NUMBER(10) label column to the selected tables. This column is used to signify a security level or label. Rows can only be accessed by users with the appropriate label. The advantage of this method over normal VPD is that it is an "out-of-the-box" solution requiring no code. Label administration is performed via the Oracle Policy Manager, which is also used for VPD and Application Context management.
Single Sign-On - Using a combination of technologies including LDAP, Public Key Encryption and Secure Application Roles, this feature allows a single login to an application server to authorize access to multiple servers and databases. The passwords are configured in the LDAP server and passed between the servers/databases using assorted Encryption methods.

Hope this helps. Regards Tim...

Back to the Top.