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:
The effects of the changes to the CONNECT role can be seen in database upgrades, account provisioning, and installation of applications using new databases.
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.
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.
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.
The change to the CONNECT role affects three classes of users differently: general users, application developers, and client/server applications.
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;
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.
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.
Three approaches are recommended for addressing the impact of this change.
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>
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>
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.