So what exactly is in those CONNECT and RESOURCE Oracle roles? The ship with every Oracle database and many apps require they be granted. I did a little digging and found the following:

In Oracle 10gR2 things are fairly sane:
CONNECT role has only CREATE SESSION
RESOURCE has CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER and CREATE TYPE

In Oracle 9iR2 things get a little scary:
CONNECT has ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE and CREATE VIEW. Rather a scary lot for a role called ‘connect’
RESOURCE has CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER and CREATE TYPE

The admin option would allow the users to grant the privlelge to another user. Thankfully neither of these roles have the admin option in the versions of Oracle I checked.

To find these privileges you can query the DBA_SYS_PRIVS table with a query like this:

select grantee, privilege, admin_option from dba_sys_privs where grantee='CONNECT';

Of course an oracle role could also have table or column privileges granted to it, so to be thorough you should also check for entries in DBA_TAB_PRIVS and DBA_COL_PRIVS.

NOTE: You should always check these privileges in your database before granting roles as a script, application or previous DBA may have granted or revoked additional roles.

Technorati tags: , , , , , ,