Home » RDBMS Server » Server Administration » find out what have been assign to a role
find out what have been assign to a role [message #526656] Wed, 12 October 2011 05:11 Go to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
to find what kind of privileges are assign to a role, you can,

(1)
use: role_tab_privs
- to find any object privileges that might be assign to it.

(2)

use: role_sys_privs
- to find any system privileges that might be assign to it.

So can i say it is either a system p / object p?

Cos i am doing some investigation on some created role in the database that is create with 'EPP_query' role and when i use the view above, no rows were shown.






Re: find out what have been assign to a role [message #526658 is a reply to message #526656] Wed, 12 October 2011 05:32 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
There's also role_role_privs that shows roles granted to other roles.

2 possibilities:
1) You don't have privileges to see the role.
2) All the roles privs come from it being assigned another role (look in role_role_priv)
Re: find out what have been assign to a role [message #526696 is a reply to message #526658] Wed, 12 October 2011 09:39 Go to previous messageGo to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
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?
Re: find out what have been assign to a role [message #526698 is a reply to message #526696] Wed, 12 October 2011 09:42 Go to previous messageGo to next message
BlackSwan
Messages: 21941
Registered: January 2009
Senior Member
http://www.petefinnigan.com/tools.htm
Re: find out what have been assign to a role [message #526707 is a reply to message #526698] Wed, 12 October 2011 09:58 Go to previous messageGo to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
Thanks, blackSwan, will explore with it....very good one. ... .
Re: find out what have been assign to a role [message #526708 is a reply to message #526696] Wed, 12 October 2011 09:58 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
hanner wrote on Wed, 12 October 2011 15:39
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?

I did say two possibilities.

hanner wrote on Wed, 12 October 2011 15:39

Quote:

role_role_privs


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

Yes.
Re: find out what have been assign to a role [message #526711 is a reply to message #526696] Wed, 12 October 2011 10:19 Go to previous messageGo to next message
Michel Cadot
Messages: 57607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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
Re: find out what have been assign to a role [message #526780 is a reply to message #526711] Thu, 13 October 2011 00:52 Go to previous messageGo to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
Michel Cadot wrote on Wed, 12 October 2011 23:19
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


Will literally take this advise seriously!

Not too sure about this, i am still figuring things out on my 'playing db':

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



what i observe is that when i create the pp_query role and grant select priviliege to pp_query role in TOM(DBA), i can see the result query on the role_tab_privs.

But when i log in as sys, and begin to query the role_tab_privs, i was not able see any output.

SO not so sure whether is it becos the invalidation due to the different log in.

[Updated on: Thu, 13 October 2011 01:02]

Report message to a moderator

Re: find out what have been assign to a role [message #526785 is a reply to message #526780] Thu, 13 October 2011 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 57607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Will literally take this advise seriously!


SQL> conn sys as sysdba
Enter password:
Connected.


It seems you don't.

Regards
Michel
Re: find out what have been assign to a role [message #526800 is a reply to message #526785] Thu, 13 October 2011 01:46 Go to previous messageGo to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
Quote:

It seems you don't.


I am still trying out...will not practice it. Just want to know why when log in differently will not able to see the result from the role_tab_privs.
Re: find out what have been assign to a role [message #526801 is a reply to message #526800] Thu, 13 October 2011 01:51 Go to previous messageGo to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
I realise even another fellow username with DBA role cannot see the result from the role_tab_privs.... hmmm ....
Re: find out what have been assign to a role [message #526804 is a reply to message #526801] Thu, 13 October 2011 02:11 Go to previous messageGo to next message
John Watson
Messages: 4081
Registered: January 2010
Location: Global Village
Senior Member
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
/
Re: find out what have been assign to a role [message #526806 is a reply to message #526801] Thu, 13 October 2011 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 57607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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

Re: find out what have been assign to a role [message #526816 is a reply to message #526806] Thu, 13 October 2011 03:23 Go to previous messageGo to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
Michel Cadot wrote on Thu, 13 October 2011 15:19
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



Quote:
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


Yah i am looking at what privilege was given to the role.

[Updated on: Thu, 13 October 2011 03:37] by Moderator

Report message to a moderator

Re: find out what have been assign to a role [message #526817 is a reply to message #526804] Thu, 13 October 2011 03:30 Go to previous messageGo to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
John Watson wrote on Thu, 13 October 2011 15:11
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.

Re: find out what have been assign to a role [message #526818 is a reply to message #526817] Thu, 13 October 2011 03:38 Go to previous messageGo to next message
Michel Cadot
Messages: 57607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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
Re: find out what have been assign to a role [message #526819 is a reply to message #526818] Thu, 13 October 2011 03:55 Go to previous messageGo to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
Michel Cadot wrote on Thu, 13 October 2011 16:38
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


you mean, only to the one that create the role then can ownself see the priviledges assign to the role.

So dba_A create role A,

dba_B cannnot see what privileges are assigned to role A?

Is what the reference means and what the code means?
Re: find out what have been assign to a role [message #526824 is a reply to message #526819] Thu, 13 October 2011 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 57607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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
Re: find out what have been assign to a role [message #526827 is a reply to message #526824] Thu, 13 October 2011 04:31 Go to previous messageGo to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
Michel Cadot wrote on Thu, 13 October 2011 17:14
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


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.

DBA_A and DBA_B with the same dba role status have no problem finding out what that particular role with what sort of privileges... .

In another understanding is, is there a one - level up above the dba role having the capability to view EVERYTHING.

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

Quote:
this is not the correct view


???

[Updated on: Thu, 13 October 2011 04:44]

Report message to a moderator

Re: find out what have been assign to a role [message #526828 is a reply to message #526827] Thu, 13 October 2011 04:35 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
hanner wrote on Thu, 13 October 2011 10:31

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.
Re: find out what have been assign to a role [message #526831 is a reply to message #526828] Thu, 13 October 2011 04:48 Go to previous messageGo to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
Quote:
They've got access to the DBA views which gives that.


who is that they....?

how to find what privileges pp_query have, by not logging in as DBA_A then?
Re: find out what have been assign to a role [message #526834 is a reply to message #526831] Thu, 13 October 2011 05:00 Go to previous messageGo to next message
hanner
Messages: 90
Registered: August 2011
Location: at HOME
Member
SQL>  select grantee, table_name, privilege from dba_tab_privs
  2   where table_name='REGIONS'
  3   and owner='HR';

GRANTEE              TABLE_NAME           PRIVILEGE
-------------------- -------------------- --------------------
PP_QUERY             REGIONS              SELECT

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 granted_role='PP_QUERY';

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



so far this is my finding. This is done not logging in as TOM (DBA)

The finding is interesting cos i always thought that grantee should reflect a username. But in this case it was a role name.

???....

[Updated on: Thu, 13 October 2011 05:15]

Report message to a moderator

Re: find out what have been assign to a role [message #526837 is a reply to message #526827] Thu, 13 October 2011 05:09 Go to previous messageGo to next message
Michel Cadot
Messages: 57607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If not, how am i suppose to find the role owner?

There is NO role owner.

Please READ the documentation we pointed you to.

Regards
Michel
Re: find out what have been assign to a role [message #526839 is a reply to message #526831] Thu, 13 October 2011 05:17 Go to previous message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
hanner wrote on Thu, 13 October 2011 10:48
Quote:
They've got access to the DBA views which gives that.


who is that they....?

Huh?
The answer is in your text that I quoted:
hanner wrote on Thu, 13 October 2011 10:31

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.

Them!



hanner wrote on Thu, 13 October 2011 10:48

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


Either you log in as a DBA and query dba_tab_privs and dba_sys_privs, or you log in as someone who has access to the role and query the role views.
Previous Topic: iSQL*Plus login for DBA
Next Topic: how to split a default subpartition
Goto Forum:
  


Current Time: Thu Apr 17 19:34:56 CDT 2014

Total time taken to generate the page: 0.13243 seconds