A Addressing The CONNECT Role Change

The CONNECT role was introduced with Oracle Database version 7, which added new and robust support for database roles. The CONNECT role is used in sample code, applications, documentation, and technical papers. The CONNECT role was established with the following privileges :

Privileges Originally Associated	with the CONNECT Role
Alter Session	Create Session
Create Cluster	Create Synonym
Create Database Link	Create Table
Create Sequence	Create View

However, beginning in Oracle Database 10g Release 2 (10.2), the CONNECT role has only the CREATE SESSION privilege, all other privileges are removed.

Although the CONNECT role has frequently been used when provisioning new accounts in the Oracle database, simply connecting to the database does not require all those privileges. Making this change enables new and existing database customers to enforce good security practices more easily.

Each user should have only those privileges appropriate to the tasks she needs to do, an idea termed the principle of least privilege. Least privilege mitigates risk by limiting privileges, so that it remains easy to do what is needed while concurrently reducing the ability to do inappropriate things, either inadvertently or maliciously.

This Appendix discusses the effects of changed CONNECT privileges in the following sections:

How Applications Are Affected
How Users Are Affected
Approaches to Addressing the CONNECT Role Change

A.1 How Applications Are Affected

The effects of the changes to the CONNECT role can be seen in database upgrades, account provisioning, and installation of applications using new databases.

A.1.1 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.

A.1.2 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.

A.1.3 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.

A.2 How Users Are Affected

The change to the CONNECT role affects three classes of users differently: general users, application developers, and client/server applications.

A.2.1 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;

A.2.2 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.

A.2.3 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.

A.3 Approaches to Addressing the CONNECT Role Change

Three approaches are recommended for addressing the impact of this change.

A.3.1 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>

A.3.2 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>

A.3.2.1 New View Showing CONNECT Grantees

A new view enables administrators who decide to continue using the old CONNECT role with its many privileges to see quickly which users have CONNECT.

The new view, DBA_CONNECT_ROLE_GRANTEES, has the following columns:

Table A-1 Columns and Contents for DBA_CONNECT_ROLE_GRANTEES

Column Name	Contents
Grantee	User granted the `CONNECT` role
Path_of_connect_role_grant	Role (or nested roles) by which the user is granted `CONNECT`
Admin_opt	`VARCHAR2(3)`, `YES` if user has `ADMIN OPTION` on `CONNECT`, else `NO`

A.3.3 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.