DBA_ROLE_PRIVS view tips

Oracle Tips by Burleson Consulting

DBA_ROLE_PRIVS

<< Return to the index

Oracle 11g makes the following comments about the DBA_ROLE_PRIVS table: "Roles granted to users and roles"

Related notes on DBA_ROLE_PRIVS:

Column description of the DBA_ROLE_PRIVS view:

GRANTEE

Description of DBA_ROLE_PRIVS.GRANTEE: "Grantee Name, User or Role receiving the grant"

GRANTED_ROLE

Description of DBA_ROLE_PRIVS.GRANTED_ROLE: "Granted role name"

ADMIN_OPTION

Description of DBA_ROLE_PRIVS.ADMIN_OPTION: "Grant was with the ADMIN option"

DEFAULT_ROLE

Description of DBA_ROLE_PRIVS.DEFAULT_ROLE: "Role is designated as a DEFAULT ROLE for the user"

DBA_ROLE_PRIVS View Source

Oracle 11g's data dictionary defines the DBA_ROLE_PRIVS view using the following source query:

select /*+ ordered */ decode(sa.grantee#, 1, 'PUBLIC', u1.name), u2.name,
decode(min(option$), 1, 'YES', 'NO'),
decode(min(u1.defrole), 0, 'NO', 1, 'YES',
2, decode(min(ud.role#),null,'NO','YES'),
3, decode(min(ud.role#),null,'YES','NO'), 'NO')
from sysauth$ sa, user$ u1, user$ u2, defrole$ ud
where sa.grantee#=ud.user#(+)
and sa.privilege#=ud.role#(+) and u1.user#=sa.grantee#
and u2.user#=sa.privilege#
group by decode(sa.grantee#,1,'PUBLIC',u1.name),u2.name

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.
You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books