Wednesday, June 22, 2011

CONNECT Role


Think carefully before granting CONNECT as it has changed over the years.

In Oracle 7: The Complete Reference by George Koch, it says:
 
Versions of ORACLE before ORACLE7 had somewhat more limited privileges for the connect role.
 
It also says that the CONNECT role had the following system privileges in Oracle 7: ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE and CREATE VIEW.
 
In their Release 8.0 SQL Reference, Oracle say:

The CONNECT, RESOURCE and DBA roles are provided for compatibility with previous versions of Oracle. You should not rely on these roles; rather, Oracle recommends that you to (sic) design your own roles for database security. These roles may not be created automatically by future versions of Oracle.

Between Oracle 7 and Oracle 9, CONNECT did not change.

As mentioned above, CONNECT is a role, not a privilege:
  
SQL> select role from dba_roles
  2  where role = 'CONNECT'
  3  /
 
ROLE
------------------------------
CONNECT
 
SQL>

It had no roles granted to it in Oracle 9 and, as far as I can ascertain, it had none in other versions either:

SQL> select granted_role from dba_role_privs
  2  where grantee = 'CONNECT'
  3  /
 
no rows selected
 
SQL>

It had the following system privileges granted to it in Oracle 9:

SQL> select privilege from dba_sys_privs
  2  where grantee = 'CONNECT'
  3  /
 
PRIVILEGE
----------------------------------------
CREATE VIEW
CREATE TABLE
ALTER SESSION
CREATE CLUSTER
CREATE SESSION
CREATE SYNONYM
CREATE SEQUENCE
CREATE DATABASE LINK
 
8 rows selected.
 
SQL>
 
By Oracle 10 , it had changed and only had one privilege granted to it:

SQL> select privilege from dba_sys_privs
  2  where grantee = 'CONNECT'
  3  /
 
PRIVILEGE
----------------------------------------
CREATE SESSION
 
SQL>

So, what are the issues with granting CONNECT?

If you grant it in Oracle 9, you will have a problem when you upgrade, as users with CONNECT will lose 7 of the 8 privileges it provided.

If you grant it in Oracle 10 or above, you will get nothing more than the CREATE SESSION privilege. You may as well grant that instead, to avoid any confusion from people who are unaware of the change to CONNECT between Oracle 9 and Oracle 10.

No comments: