dba_tab_privs references on custom roles
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: dba_tab_privs references on custom roles

Hybrid View

  1. #1
    Join Date
    Sep 2010
    Posts
    2

    dba_tab_privs references on custom roles

    Hi,

    I'm wondering why the REFERENCES privilege for a user defined role does not appear in the DBA_TAB_PRIVS table?

    example:

    create role mycustomrole identified by mycustomrole;
    grant references on theowner.thetable to mycustomrole;
    grant alter on theowner.thetable to mycustomrole;
    grant mycustomrole to myuser1;
    grant references on theowner.thetable to myuser2;

    select owner,table_name,privilege,grantee from dba_tab_privs where owner='THEOWNER';

    Code:
    OWNER      TABLE_NAME PRIVILEGE  GRANTEE
    ---------- ---------- ---------- ------------
    THEOWNER   THETABLE   REFERENCES MYUSER2
    THEOWNER   THETABLE   ALTER      MYCUSTOMROLE
    Note mycustomrole does not show up.

    Am I looking in the wrong place?

    FYI, I am running Oracle 10g, if that matters.

    Thanks
    Last edited by da808wiz; 09-21-2010 at 09:39 PM.

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    have you checked in ROLE_TAB_PRIVS view?

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  3. #3
    Join Date
    Jul 2006
    Posts
    195
    FYI

    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
    AIX


    SQL> grant references on xxx.junk to mycustomrole;
    grant references on xxx.junk to mycustomrole
    *
    ERROR at line 1:
    ORA-01931: cannot grant REFERENCES to a role


    SQL> !oerr ora 01931
    01931, 00000, "cannot grant %s to a role"
    // *Cause: UNLIMITED TABLESPACE, REFERENCES, INDEX, SYSDBA or SYSOPER
    // privilege cannot be granted to a role.
    // *Action: Grant privilege directly to the user.

  4. #4
    Join Date
    Sep 2010
    Posts
    2

    Question Thanks for everyone's input

    http://www.google.com/url?url=http:/...R0Ec1AKekdLPyg

    Looks like it is not possible to assign INDEX or REFERENCES to a role.

    The weird part about it is when I issue the same commands that BeefStu does, I get "grant succeeded."

    When I searched for it in the various role tables, I couldn't find it. If it's not possible, it would be better if I received an error message. I'm running 32-bit 10.2 if that makes any difference.

    Gz

Tags for this Thread

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