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.