ROLES Query Examples

ROLES  Query Examples 

set lines 200 pages 1000 ver off

col grantee for a30

col role for a16

col type for a10

col pv for a80 hea 'PRIVILEGE OR ROLE'

bre on role on type skip 1

define usercheck ='<user_name>'

select grantee, 'ROL' type, granted_role pv

from dba_role_privs where grantee = '&usercheck' union

select grantee, 'PRV' type, privilege pv

from dba_sys_privs where grantee = '&usercheck' union

select grantee, 'OBJ' type,

max(decode(privilege,'WRITE','WRITE,'))||max(decode(privilege,'READ','READ'))||

max(decode(privilege,'EXECUTE','EXECUTE'))||max(decode(privilege,'SELECT','SELECT'))||

max(decode(privilege,'DELETE',',DELETE'))||max(decode(privilege,'UPDATE',',UPDATE'))||

max(decode(privilege,'INSERT',',INSERT'))||' ON '||object_type||'  "'||a.owner||'.'||table_name||'"' pv

from dba_tab_privs a, dba_objects b

where a.owner=b.owner and a.table_name = b.object_name and a.grantee='&usercheck'

group by a.owner,table_name,object_type,grantee union

select username grantee, '---' type, 'empty user ---' pv from dba_users

where not username in (select distinct grantee from dba_role_privs) and

not username in (select distinct grantee from dba_sys_privs) and

not username in (select distinct grantee from dba_tab_privs) and username like  '%&usercheck%'

group by username

order by grantee, type, pv;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SPDPAMF') from dual;

set heading off;

set echo off;

set pagesize 0;      

set long 99999;      

set linesize 32767;  

set trimspool on;   

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SUDAWA2') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SPDPAME') from dual;

col GRANTEE for a40

col OWNER for a30

col TABLE_NAME for a30

col GRANTOR for a30

col PRIVILEGE for a30

set lines 200

SELECT * FROM DBA_TAB_PRIVS  WHERE GRANTEE IN (SELECT granted_role FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'E593469');

SQL> SET lines 100

COL privilege FOR a50

SELECT grantee, granted_role, default_role

FROM dba_role_privs

WHERE granted_role IN ('DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE')

ORDER BY 1,2; 

GRANTEE                                  GRANTED_ROLE                                                                                                                     DEF

---------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---

DATAPUMP_EXP_FULL_DATABASE               EXP_FULL_DATABASE                                                                                                                YES

DATAPUMP_IMP_FULL_DATABASE               EXP_FULL_DATABASE                                                                                                                YES

DATAPUMP_IMP_FULL_DATABASE               IMP_FULL_DATABASE                                                                                                                YES

DBA                                      EXP_FULL_DATABASE                                                                                                                YES

DBA                                      IMP_FULL_DATABASE                                                                                                                YES

DBA_ROLE                                 DBA                                                                                                                              YES

DBMAINT                                  DBA                                                                                                                              YES

EDP_SUPPORT_UPDATE_ROLE                  EXP_FULL_DATABASE                                                                                                                YES

EDP_SUPPORT_UPDATE_ROLE                  IMP_FULL_DATABASE                                                                                                                YES

IOC_ROLE                                 EXP_FULL_DATABASE                                                                                                                YES

IOC_ROLE                                 IMP_FULL_DATABASE                                                                                                                YES

GRANTEE                                  GRANTED_ROLE                                                                                                                     DEF

---------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---

SYS                                      DBA                                                                                                                              YES

SYS                                      EXP_FULL_DATABASE                                                                                                                YES

SYS                                      IMP_FULL_DATABASE                                                                                                                YES

SYSTEM                                   DBA          

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','LINCPAM_DMM_UPDATE_ROLE') from dual;

SELECT dbms_metadata.get_ddl('USER','SCOTT') FROM dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL(

  2  'SYSTEM_GRANT','RESOURCE') from dual;

  

  select granted_role,admin_option,default_role from dba_role_privs where grantee='20';

  

  select * from dba_role_privs where grantee='20';

  

  select * from dba_sys_privs where grantee='20';

  

  select * from USER_ROLE_PRIVS where USERNAME=82';

select * from USER_TAB_PRIVS where Grantee = 82';

select * from USER_SYS_PRIVS where USERNAME = 'E

2';

Generate on source. 

select 'create role '||role||';' from dba_roles;

2. Than generate a ddl from the dump for grants only

include=grant

sqlfile=grant.sql