A Addressing The CONNECT Role Change

Database Upgrade

Upgrading your existing Oracle database to Oracle Database 10gR2 automatically changes the CONNECT role to have only the CREATE SESSION privilege. Most applications are not affected because the applications objects already exist: no new tables, views, sequences, synonyms, clusters, or database links need be created.

Applications that create tables, views, sequences, synonyms, clusters, or database links, or that use the ALTER SESSION command dynamically, may fail due to insufficient privileges.

Account Provisioning

If your application or DBA grants the CONNECT role as part of the account provisioning process, then no privileges beyond CREATE SESSION are included. Any additional privilege must be granted either directly or through another role.

This issue can be addressed by creating a new customized database role.

Installation of Applications Using New Databases

New databases created using the Oracle Database 10gR2 Utility (DBCA), or using database creation templates generated from DBCA, define the CONNECT role with only the CREATE SESSION privilege. Installing an application to use such a new database may fail if the database schema used for the application is granted privileges solely through the CONNECT role.

General Users

The new CONNECT supplies only the CREATE SESSION privilege. Therefore users who connect to the database to use an application are not affected, because the CONNECT role still has the CREATE SESSION privilege.

However, appropriate privileges will not be present for a certain set of users if they are provisioned solely with CONNECT. These are users who create tables, views, sequences, synonyms, clusters, or database links, or use the ALTER SESSION command. The privileges they need are no longer provided with the CONNECT role. To authorize the additional privileges needed, the database administrator must create and apply additional roles for the appropriate privileges, or grant them directly to the users who need them.

Note that the ALTER SESSION privilege is required for setting events. Very few database users should require the alter session privilege.

• SQL> ALTER SESSION SET EVENTS ........
  

The alter session privilege is not required for other alter session commands.

• SQL> ALTER SESSION SET NLS_TERRITORY = FRANCE;
  

Application Developers

Similarly, application developers provisioned solely with CONNECT will not have appropriate privileges to create tables, views, sequences, synonyms, clusters, or database links, nor to use the alter session command. The database administrator must either create and apply additional roles for the appropriate privileges, or grant them directly to the application developers who need them.

Client Server Applications

Most traditional client/server applications using dedicated user accounts will not be affected by this change. However, applications that create private synonyms or temporary tables using dynamic SQL in the user schema during account provisioning or run time operations will be affected. They will require additional roles or directly grants to acquire the system privileges appropiate to their activities.

Approach 1 - Create a new database role

The privileges removed from the CONNECT role can be easily managed by creating a new database role.

First, connect to the upgraded Oracle database and create a new database role. The following example uses a role called my_app_developer:

• SQL> CREATE ROLE my_app_developer;
  SQL> GRANT CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE SYNONYM, CREATE CLUSTER, CREATE DATABASE LINK, ALTER SESSION TO my_app_developer;
  SQL>
  

Second, determine which users or database roles have the CONNECT role and grant the new role to these users or roles.

• SQL> SELECT user$.name, admin_option, default_role
       FROM user$, sysauth$, dba_role_privs
       WHERE privilege# = 
       (SELECT user# from user$ WHERE name = 'CONNECT')
       AND user$.user# = grantee#
       AND grantee = user$.name
       AND granted_role = 'CONNECT';
  
  NAME                           ADMIN_OPTI DEF
  ------------------------------ ---------- ---
  R1                             YES        YES
  R2                             NO         YES
  
  SQL> GRANT my_app_developer TO R1 WITH ADMIN OPTION;
  SQL> GRANT my_app_developer TO R2;
  
  

You can determine the privileges that users require by using Oracle Auditing. The audit information can then be analyzed and used to create additional database roles with finer granularity.

Privileges not used can then be revoked for specific users. Note that prior to auditing, the database initialization parameter AUDIT_TRAIL must be initialized and the database restarted.

• SQL> AUDIT CREATE TABLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE DATABASE LINK, CREATE CLUSTER, CREATE VIEW, ALTER SESSION;
  

Database privilege usage can now be monitored periodically.

• SQL> SELECT userid, name FROM aud$, system_privilege_map 
  WHERE - priv$used = privilege;
  USERID                         NAME
  ------------------------------ ----------------
  ACME                           CREATE TABLE
  ACME                           CREATE SEQUENCE
  ACME                           CREATE TABLE
  ACME                           ALTER SESSION
  APPS                           CREATE TABLE
  APPS                           CREATE TABLE
  APPS                           CREATE TABLE
  APPS                           CREATE TABLE
  8 rows selected.
  SQL> 
  

Approach 2 - Restore CONNECT privileges

Starting with 10g Release 2 (10.2), Oracle provides a script called rstrconn.sql located in the $ORACLE_HOME/rdbms/admin directory. After a database upgrade or new database creation, this script can be used to grant back the privileges removed from the CONNECT role in Oracle Database 10g Release 2 (10.2).

If this approach is used, then privileges that are not used should be revoked from users who do not need them. To identify such privileges and users, the database must be restarted with the database initialization parameter AUDIT_TRAIL initialized, for example, AUDIT_TRAIL=DB. Oracle Database auditing should then be turned on to monitor what privileges are used as follows:

• SQL> AUDIT CREATE TABLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE DATABASE LINK, CREATE CLUSTER, CREATE VIEW, ALTER SESSION;
  

Database privilege usage can also be monitored periodically.

• SQL> SELECT userid, name FROM aud$, system_privilege_map WHERE - priv$used = privilege;
  USERID                         NAME
  ------------------------------ ----------------
  ACME                           CREATE TABLE
  ACME                           CREATE SEQUENCE
  ACME                           CREATE TABLE
  ACME                           ALTER SESSION
  APPS                           CREATE TABLE
  APPS                           CREATE TABLE
  APPS                           CREATE TABLE
  APPS                           CREATE TABLE
  8 rows selected.
  SQL> 
  

Approach 3 - Conduct least privilege analysis

Oracle partners and application providers should use this approach to deliver more secure products to the Oracle customer base. The principle of least privilege mitigates risk by limiting privileges to the minimum set required to perform a given function.

For each class of users that the analysis shows need the same set of privileges, create a role with exactly those privileges. Remove all other privileges from those users, and assign that role to those users. As needs change, additional privileges can be granted either directly or through these new roles, or new roles can be created to meet new needs. At any given time, however, there is a greater assurance that inappropriate privileges have been limited, thereby reducing the risk of inadvertent or malicious harm.