You don't have privileges to see the role.

role_role_privs

Quote:

You don't have privileges to see the role.

hmmm...not quite...cos i log in as sysdba. So should be able to see?

Quote:

role_role_privs

so you are saying check the 'related' role that might carry the privileges?

hmmm...not quite...cos i log in as sysdba.

Quote:

hmmm...not quite...cos i log in as sysdba.

And you are WRONG.
Don't you remember what I said:

- Never ever use SYS (or SYSDBA) but for maintenance purpose (startup, shutdown, backup, recover)
- SYS/SYSDBA is special
- SYS/SYSDBA is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS/SYSDBA" and you'll see the immediate answer)
- SYS/SYSDBA does not act like any other user
- When you use SYS/SYSDBA Oracle deactivates some code path and activates others
- Whatever you do with SYS/SYSDBA will neither validate nor invalidate the same thing with any other user.

NEVER EVER use SYS/SYSDBA for anything that can be done by another user.
Use SYS/SYSDBA ONLY for something that can't be done by someone else.

People don't care about this until they encounter something they can't understand or they screw up their database.
Too bad!
I say if one day you'll come with such problem don't count on me to help you. You'll deserve what you'll get.

Regards
Michel

SQL> conn tom/tom
Connected.
SQL>
SQL>
SQL>
SQL>
SQL> create role pp_query;

Role created.


SQL> grant select on hr.regions to pp_query;

Grant succeeded.

SQL> select privilege, role from role_tab_privs
  2  where role='PP_QUERY';

PRIVILEGE                                ROLE
---------------------------------------- ------------------------------
SELECT                                   PP_QUERY



SQL> disco
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0
ction
SQL>
SQL>
SQL>
SQL> conn sys as sysdba
Enter password:
Connected.
SQL>
SQL>
SQL> select privilege, role from role_tab_privs
  2  where role='PP_QUERY';

no rows selected


SQL> desc dba_role_privs
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------------

 GRANTEE                                            VARCHAR2(30)
 GRANTED_ROLE                              NOT NULL VARCHAR2(30)
 ADMIN_OPTION                                       VARCHAR2(3)
 DEFAULT_ROLE                                       VARCHAR2(3)

SQL> select grantee, granted_role from dba_role_privs
  2  where grantee='TOM';

GRANTEE                        GRANTED_ROLE
------------------------------ ------------------------------
TOM                            DBA
TOM                            PP_QUERY

Will literally take this advise seriously!

SQL> conn sys as sysdba
Enter password:
Connected.

It seems you don't.

I realise even another fellow username with DBA role cannot see the result from the role_tab_privs.... hmmm ....

hanner wrote on Thu, 13 October 2011 08:51

I realise even another fellow username with DBA role cannot see the result from the role_tab_privs.... hmmm ....

Hmmmm... maybe you should read the definition of a view before using it.
Database Reference

Regards
Michel

ROLE_TAB_PRIVSROLE_TAB_PRIVS describes table privileges granted to roles. Information is provided only about roles to which the user has access.

Column Datatype NULL Description
ROLE VARCHAR2(30) NOT NULL Name of the role
OWNER VARCHAR2(30) NOT NULL Owner of the object
TABLE_NAME VARCHAR2(30) NOT NULL Name of the object
COLUMN_NAME VARCHAR2(30) Name of the column, if applicable
PRIVILEGE VARCHAR2(40) NOT NULL Object privilege granted to the role
GRANTABLE VARCHAR2(3) YES if the role was granted with ADMIN OPTION; otherwise NO

Intriguing. If you look at te source code for the view (it is in $OH/rdbms/admin/cdsec.sql) you can probably work out what is going on:

create or replace view ROLE_TAB_PRIVS
    (ROLE, OWNER, TABLE_NAME, COLUMN_NAME, PRIVILEGE, GRANTABLE)
as
select u1.name,u2.name,o.name,col$.name,tpm.name,
       decode(max(mod(oa.option$,2)), 1, 'YES', 'NO')
from  sys.user$ u1,sys.user$ u2,sys.table_privilege_map tpm,
      sys.objauth$ oa,sys."_CURRENT_EDITION_OBJ" o,sys.col$
where grantee# in
   (select distinct(privilege#)
    from sys.sysauth$ sa
    where privilege# > 0
    connect by prior sa.privilege# = sa.grantee#
    start with grantee#=userenv('SCHEMAID') or grantee#=1 or grantee# in
      (select kzdosrol from x$kzdos))
   and u1.user#=oa.grantee# and oa.privilege#=tpm.privilege
   and oa.obj#=o.obj# and oa.obj#=col$.obj#(+) and oa.col#=col$.col#(+)
   and u2.user#=o.owner#
  and (col$.property IS NULL OR bitand(col$.property, 32) = 0 )
group by u1.name,u2.name,o.name,col$.name,tpm.name
/

wow..... i have differculties understanding what is going on in this code.

Quote:

wow..... i have differculties understanding what is going on in this code.

This is why you have to read the documentation which says:
Information is provided only about roles to which the user has access.

Regards
Michel

When you create a role and do not grant it to anyone then noone has "access" to it and so no one can see its privileges with this view.
This does NOT mean that no one can see the privileges, this just means this is not the correct view to see them.

Regards
Michel

this is not the correct view

i was thinking in the line that those who have the dba role should have the 'view all' super user mode to check it out. Hopefully you know what i mean.

They've got access to the DBA views which gives that.

If not, how am i suppose to find the role owner?

Quote:

They've got access to the DBA views which gives that.

who is that they....?

i was thinking in the line that those who have the dba role should have the 'view all' super user mode to check it out. Hopefully you know what i mean.

how to find what privileges pp_query have, by not logging in as DBA_A then?