Oracle role Info

@roleinfo DBA
@roleinfo SCOTT





-- -------------------
-- http://www.allenhayden.com/cgi/getdoc.pl?file=ascr74.htm from this site
-- roleup.sql
--
-- Creates a graphical list of all roles
-- that a certain role/user has been assigned.
--
-- Modifications:
-- 08-Sep-98 SEC Created
--
-- Usage:
-- @roleup {ROLE/USER_NAME}
--
-- Notes:
-- Must have select on DBA views, and create table privilege.
--
-- --------------------

prompt Examining the data dictionary. Please wait....

set heading off echo off verify off term off feedback off

whenever sqlerror stop 1;

-- temp table - essentially a reverse of dba_sys_privs, to
-- allow CONNECT BY operations, working from the bottom up.
create table user_dba_role_privs
(granted_role varchar2(30),
grantee varchar2(30))
storage (initial 100k next 200k);

insert into user_dba_role_privs
select grantee, granted_role
from dba_role_privs;

set heading on term on feedback on

column role_level format a50 heading "Role Level"

define role_parameter=&1;
prompt Roles for user/role &role_parameter : ;

SELECT LPAD(' ',2*(LEVEL-1)) || grantee role_level
FROM user_dba_role_privs
START WITH granted_role = upper('&role_parameter' )
CONNECT BY PRIOR grantee = granted_role
/

set heading off echo off verify off term off feedback off
drop table user_dba_role_privs;
set heading on term on feedback on


-- -------------------
--
-- roledown.sql
--
-- Creates a graphical list of all roles/users
-- under a certain role.
--
-- Modifications:
-- 08-Sep-98 SEC Created
--
-- Usage:
-- @roledown {ROLE_NAME}
--
-- Notes:
-- Must have select on DBA views, and create table privilege.
--
-- --------------------

prompt Examining the data dictionary. Please wait....

set heading off echo off verify off term off feedback off

whenever sqlerror stop 1;

-- temp table - essentially a copy of dba_sys_privs,
-- to allow CONNECT BY operations, from top down.
create table user_dba_role_privs
storage (initial 100k next 200k)
as select * from dba_role_privs
where rownum <1;

insert into user_dba_role_privs
select * from dba_role_privs;

def role_parameter=&1;
column role_level format a50 heading "Role Level"

prompt Roles/users under role &role_parameter : ;

set heading on term on feedback on

SELECT LPAD(' ',2*(LEVEL-1)) || grantee role_level
FROM user_dba_role_privs
START WITH granted_role = upper('&role_parameter')
CONNECT BY PRIOR grantee = granted_role
/

set heading off term off feedback off
drop table user_dba_role_privs;
set heading on term on feedback on

-- -------------------
--
-- usp.sql (User System Privileges)
--
-- Creates list of all privileges assigned to a
-- user, whether granted directly, or through a role.
--
-- Modifications:
-- 08-Sep-98 SEC Created
--
-- Usage:
-- @usp {ROLE/USER_NAME}
--
-- Notes:
-- Must have select on DBA views, and create table privilege.
--
-- --------------------

prompt Examining the data dictionary. Please wait....


set heading off echo off verify off term off feedback off

whenever sqlerror stop 1;

create table user_dba_role_privs
(granted_role varchar2(30),
grantee varchar2(30))
storage (initial 100k next 200k);

insert into user_dba_role_privs
select grantee, granted_role
from dba_role_privs;

create table temp_dba_role_privs
(granted_role varchar2(30))
storage (initial 100k next 200k);

column role_level format a30

define role_parameter=&1;
prompt Roles for user/role &role_parameter : ;

insert into temp_dba_role_privs
SELECT grantee
FROM user_dba_role_privs
START WITH granted_role = upper('&role_parameter' )
CONNECT BY PRIOR grantee = granted_role
/

set heading on term on feedback on

select distinct privilege , 'Thru role '|| granted_role "Access Route"
from temp_dba_role_privs t , dba_sys_privs d
where t.granted_role = d.grantee
union
select privilege , 'Direct'
from dba_sys_privs
where grantee = upper('&role_parameter')
order by 1
/

set heading off echo off verify off term off feedback off
drop table user_dba_role_privs;
drop table temp_dba_role_privs;


set heading on term on feedback on