Shell Script to Capture the User Role, privileges, Object Privileges

 #!/bin/ksh

Schema=AREP

priv()

{

sqlplus -s $ID/$PASS<<EOF

prompt ==========

PROMPT  USER $Schema PRIVILEGS

prompt ==========

SELECT granted_role FROM DBA_ROLE_PRIVS WHERE GRANTEE like upper('%$Schema%');

set line 550

set pages 300

--set serveroutput on size 999999

--undef schema

--accept schema prompt 'Schema :'

prompt dba_role_privs

col GRANTEE for a20

col GRANTED_ROLE for a30

select * from dba_role_privs

where grantee like UPPER('%$Schema%');

prompt dba_tab_privs

set lines 500

col GRANTEE for a50

col OWNER for a20

col PRIVILEGE for a10

col TABLE_NAME for a40

col GRANTOR for a10

select * from dba_tab_privs

where grantee like UPPER('%$Schema%');

prompt dba_sys_privs

col GRANTEE for a19

col PRIVILEGE for a30

select * from dba_sys_privs

where grantee like UPPER('%$Schema%');

PROMPT ROLE GRANTED  TO ROLE

select *

from ROLE_ROLE_PRIVS

where

ROLE IN (select granted_role from DBA_ROLE_PRIVS where grantee like upper('%$Schema%'));

prompt ROLE_TAB_PRIVS

PROMPT

col ROLE for a10

col OWNER a10

col TABLE_NAME for a40

col COLUMN_NAME for a20

col PRIVILEGE for a40

select *

from ROLE_TAB_PRIVS

where

ROLE in(

SELECT granted_role

FROM DBA_ROLE_PRIVS

WHERE GRANTEE like upper('%$Schema%'));

PROMPT ROLE PRIVILEGES

col GRANTEE for a49

col owner for a10

col PRIVILEGE for a30

SELECT *

FROM DBA_TAB_PRIVS

WHERE GRANTEE IN

(SELECT granted_role

FROM DBA_ROLE_PRIVS

WHERE GRANTEE like upper('%$Schema%'));

EOF

}

priv > /tmp/priv_nil1.log

vi /tmp/priv_nil1.log