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: oracle, dba, database, database administrator, rdbms, database security, security
We had a strange issue after upgrading to 10GR2. Though create procedure privilege is not part of the connect role. Our procedures with dynamic DDL stopped working with ORA-1031. We had to explicitly grant create procedure to th user. Create any procedure did not work either. Any clues why ?
Uma, that is a strange issue. ORA-1031 seems to be a username/password related error. Here’s what Oracle has to say about the error:
Error: ORA 1031
Text: insufficient privileges
——————————————————————————-
Cause: An attempt was made to change the current username or password without
the appropriate privilege. This error also occurs if attempting to
UPDATE a table with only SELECT privileges, if attempting to CONNECT
INTERNAL, or if attempting to install a database without the necessary
operating system privileges.
Action: Ask the database administrator to perform the operation or grant the
required privileges.
What does the dynamic SQL do? Anything with changing usernames or passwords? I am not sure why granting create procedure would have solved the issue, but I would guess there was something else going on here.
Uma, if you follow
http://forums.oracle.com/forums/message.jspa?messageID=1271340
and read posted: Apr 15, 2006 6:14 PM and Apr 15, 2006 9:44 PM
you’ll find similar problem and decision. Hope it would help you if it is not too late 😉
the resource rol also includes unlimited tablespace privilege (10g).
A clean Oracle 10gR2 (linux) role connect only has create session privs.
However applications can add sys privs to the role.
The moral of the story is only give people the system priv create session or create your own connect role if you want a secure system.
select grantee,privilege,admin_option from dba_sys_privs where grantee=’CONNECT’;
GRANTEE PRIVILEGE ADM
—————————— —————————————- —
CONNECT CREATE SESSION NO