Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Posts
    19

    Unanswered: RESOURCE Role privileges

    Hello,
    My name is Tal Olier and I serve as a database expert.

    I have found a strange behavior I am not familiar with and would greatly appreciate if you can shed some light on it.

    I use the following scenario on both 9.2.0.6 server and 10.2.0.1 server:

    create user otal_resource identified by otal_resource default tablespace otal;
    grant resource to otal;
    grant CREATE SESSION to otal_resource;

    conn otal_resource/otal_resource

    SQL> create table t1(c1 number);

    Table created.

    Elapsed: 00:00:00.18
    SQL> create view v1 as select * from t1;
    create view v1 as select * from t1
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges

    SQL> create index t1_ix1 on t1(c1);

    Index created.

    SQL>


    I also get:

    select * from dba_sys_PRIVS where grantee like 'RESOURCE' order by privilege
    /* 9.2 and 10.2:
    CREATE CLUSTER
    CREATE INDEXTYPE
    CREATE OPERATOR
    CREATE PROCEDURE
    CREATE SEQUENCE
    CREATE TABLE
    CREATE TRIGGER
    CREATE TYPE

    => no CREATE VIEW (In 9i it belongs to the CONNECT role and in 10g I do not find it anywhere…)
    */

    My questions are:
    1.
    Is that a bug that create view is not included in the RESOURCE role?

    2.
    How is that possible that I am able to create an index without having the CREATE INDEX privilege?

    Regards,


    Tal Olier.
    Tal.olier@gmail.com
    tolier@mercury.com

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This is what I can tell about the issue (which still doesn't mean that this is all true):

    Both CONNECT and RESOURCE roles have lost their functionality and are here just for backwards compatibility, and will be deprecated in future Oracle versions (check this link). As of today, CONNECT has only one privilege: CREATE SESSION.

    Therefore, to be able to grant all necessary (or, should I rather say, old connect and resource) privileges, you'll have to create your own role(s) and grant it/them to newly created user. Those privileges were:

    Connect:
    ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW

    Resource:
    CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE

    As of your CREATE INDEX question, I believe that it is implicitly granted along with the CREATE TABLE privilege because you do need a table to create an index, right? BTW, "CREATE INDEX" privilege doesn't exist.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •