dba_tab_privs references on custom roles
I'm wondering why the REFERENCES privilege for a user defined role does not appear in the DBA_TAB_PRIVS table?
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';
Note mycustomrole does not show up.
OWNER TABLE_NAME PRIVILEGE GRANTEE
---------- ---------- ---------- ------------
THEOWNER THETABLE REFERENCES MYUSER2
THEOWNER THETABLE ALTER MYCUSTOMROLE
Am I looking in the wrong place?
FYI, I am running Oracle 10g, if that matters.
Last edited by da808wiz; 09-21-2010 at 09:39 PM.
have you checked in ROLE_TAB_PRIVS view?
Try hard to get what you like OR you will be forced to like what you get.
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
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.
Thanks for everyone's input
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.
Tags for this Thread
Click Here to Expand Forum to Full Width