grant resource gives unlimited tablespace privilege
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: grant resource gives unlimited tablespace privilege

Hybrid View

  1. #1
    Join Date
    Dec 2001
    Posts
    141
    Hi all !
    - When I look in the dba_sys_privs for the RESOURCE system privileges, I have :
    CREATE CLUSTER
    CREATE INDEXTYPE
    CREATE OPERATOR
    CREATE PROCEDURE
    CREATE SEQUENCE
    CREATE TABLE
    CREATE TRIGGER
    CREATE TYPE
    - When I grant RESOURCE to a user and look in the dba_sys_privs for this user I have :
    UNLIMITED TABLESPACE

    Where does this privilege comes from ? It is not included in the RESOURCE role, so how can it be possible ?

    Thanks a lot in advance ...

  2. #2
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    I think UNLIMITED TABLESPACE is more of quota to enable object creation than a privilge .
    though it shows in Privilege columns.
    siva prakash
    DBA

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    No, UNLIMITED TABLESPACE is perfectly normal system privilege.

    How it is granted with RESOURCE role, although it is not part of a RESOURCE role? Obviously there is some kind of "hidden trigger" built in oracle that grants/revokes it as an explicit privilege to/from user whenever he is granted/revoked RESOURCE role.

    The whole process is not externalized, you can't see it how it is implemented anywhere - that's why I said it must be a "hidden trigger".
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Dec 2001
    Posts
    141
    I found out that this behavoir is referenced as a bug for Oracle :

    The system privilege 'UNLIMITED TABLESPACE' cannot be granted to a role.
    However, the predefined 'RESOURCE' role contains the 'UNLIMITED TABLESPACE'
    privilege.
    If you grant the 'RESOURCE' role to any other role, the 'UNLIMITED TABLESPACE'
    privilege will not be transfered to this other role.
    For example, if you grant the'RESOURCE' role to another role, i.e., the
    accounting role, the user enabling the accounting role will lose the
    'UNLIMITED TABLESPACE' privilege, and the user will get an error when trying
    to create an object.
    This was filed as bug 172360, which is closed as 'not a bug'.
    This is a good reason to not use the resource role.

Posting Permissions

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


Click Here to Expand Forum to Full Width