#!/usr/bin/ksh
# set -xv
# program: smenu_db_role.sh
# author : Bernard Polarski
#          18 Jun 2009
#
# this script regroup all role/grants utilities

HOST=`hostname`
HOST=`echo $HOST | awk '{ printf ("%-+15.15s",$1)  }'`

# -------------------------------------------------------------------------------------
function do_execute
{
$SETXV
sqlplus -s "$CONNECT_STRING" <<EOF
ttitle skip 2 'MACHINE $HOST - ORACLE_SID : $ORACLE_SID '  right 'Page:' format 999 sql.pno skip 2
column nline newline 
set pagesize 66 linesize 100 
set termout on pause off embedded on verify off heading off
select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||rpad(USER,15)|| '$TITTLE (rol -h : help)' nline
from sys.dual
/
set head on
$BREAK
set linesize 132 pages 65
col roleh   form a30 head '   Role in the Database' 
$SQL
EOF
}
# -------------------------------------------------------------------------------------
function help
{
 cat <<EOF

                    ------------------------------------------
                     ROLES, SYSMTEM GRANTS and OBJECT GRANTS     
                    ------------------------------------------

Role:                rol -l                        : role present in DB
-----                rol -u  [<user>]              : role/user distribution   [ restricted to <role> ]
                     rol -r  [<role>]              : user/role distribution   [ restricted to <user> ]

Object grants:       rol -o   [<usr|role>]         : object / user:role   object grants
--------------

System grants:       rol -s   [<usr>]              : user   / system Privileges
-------------

Grants:              rol -g <role>                 : Grants for role
-------              rol -t <user>                 : Grants hierarchy for user

Misc:                rol -sp                       : Display users with the SYSDBA or SYSOPR privilge
-----                rol -smap                     : List system privilege map

Generated Scripts:  
------------------
                     rol -cr <user>                : user grants script
                     rol -gr <role>                : role grants script
   
EOF
exit
}
# -------------------------------------------------------------------------------------
#                    Main
# -------------------------------------------------------------------------------------

if [ -z "$1" ];then
    help
fi
typeset -u fuser
typeset -u frole
typeset -u fentity

while [ -n "$1" ]
do
  case "$1" in
      -cr ) CHOICE=SCRIPT ; fuser=$2; shift ;;
       -g ) CHOICE=LIST_ROLE_GRANT ; frole=$2 ; shift ;;
      -gr ) CHOICE=ROB_CR ; frole=$2 ; shift ;;
       -l ) CHOICE=LIST_ROLE ;;
       -o ) CHOICE=OBJECT_GRANT ; fentity=$2; shift ;;
       -r ) CHOICE=ROLE_USER ; EXECUTE=YES 
            if [ -n "$2" -a ! "$2" = "-v"  ];then
               frole=$2 ; shift ; 
            fi
            ;;
       -s ) CHOICE=SYSTEM_GRANT_TO_USER 
            if [ -n "$2" -a ! "$2" = "-v" -a ! "$2" = "-u" ];then
               fuser=$2 ; shift ;
            fi ;;
    -smap ) CHOICE=MAP ; EXECUTE=YES ;; 
      -sp ) CHOICE=PF ;;
       -t ) CHOICE=TREE ; fuser=$2 ; shift ;;
       -x ) EXECUTE=YES;;
       -v ) SETXV="set -xv";;
       -u ) if [ -n "$2" -a ! "$2" = "-v" ];then
                  fuser=$2 ; shift 
            fi
            if [ -z "$ACTION" ];then
               CHOICE=USER_ROLE ; EXECUTE=YES
            fi;;
       -h )  help ;;
        * ) echo "Unknown parameter : $1" ; exit ;;
 esac
 shift
done
# ......................................
# 
# ......................................
$SETXV
# ......................................
#  generate script to create role
# ......................................
if [ "$CHOICE" = "ROB_CR" ];then
   
. $SBIN/scripts/passwd.env
. ${GET_PASSWD} $S_USER $ORACLE_SID
if [  "x-$CONNECT_STRING" = "x-" ];then
   echo "could no get a the password of $S_USER"
   exit 0
fi
sqlplus -s "$CONNECT_STRING" <<EOF
   set pagesize 333 linesize 132 heading off pause off embedded off verify off feed off trimspool on
spool cr_${fuser}.sql
select 'create role $frole ; ' from dual;
select
    'grant ' || t.privilege || ' on ' ||t.owner||'.' || t.table_name || ' to ' || '$frole' ||
    decode(GRANTABLE,'YES',' with grant option ;',';')
from
  dba_roles r, dba_tab_privs  t
where
  r.ROLE = t.grantee  and r.ROLE = upper('$frole')
UNION
select  'grant ' || t.privilege || ' to ' || '$frole' || ';'
   from dba_roles r,
        dba_sys_privs t
where
  r.ROLE = t.grantee  and r.ROLE = '$frole' $ADD_AND_GRANTEE
/
spool off
EOF
exit
# ......................................
#  List System Privilege(s) 
# ......................................
elif [ "$CHOICE" = "SYSTEM_GRANT_TO_USER" ];then
    if [ -z "$fuser" ];then
        unset AND_USER
     else
       AND_USER="and username = upper('$fuser')"
       FSQL="
col a1 head 'System grants obtained from Role' for a60
select
  lpad(' ', 4*level) || granted_role a1
from
  (
    select grantee, granted_role
    from
      dba_role_privs
    union 
    (
    select grantee, privilege from dba_sys_privs where grantee <> '$fuser'
   -- union
   --  select grantee, privilege from dba_tab_privs  where grantee <> '$fuser'
    )
  )
start with grantee = '$fuser'
connect by grantee = prior granted_role ;
"
    fi

    TITTLE="List System Grants"
SQL="
col username form a23 head 'Username'             just l
col grantee form a16 head 'Username'             just l
col dts      form a23 head 'Default|Tablespace'   just c
col tts      form a16 head 'Temporary|Tablespace' just c
col privilege      form a18 head 'Privilege' just c
col fprivilege      form a35 head 'Privilege' just c
col grantable      form a4 head 'Grant'          just c
col prof     form a12 head 'Profile'              just c
col owner     form a16 head 'Owner'              just c
col table_name     form a29 head 'Table'         just c
col column_name     form a16 head 'Column'       just c

break on username  on dts on tts on prof

prompt.          Type:    ros <user>           to limit to one user
prompt
prompt Direct System privileges
select
  username,
  default_tablespace    dts,
  temporary_tablespace  tts,
  PRIVILEGE || decode(admin_option,'YES','-A',' ') fprivilege
from
  dba_users,
  dba_sys_privs
where
  dba_users.username = dba_sys_privs.grantee $AND_USER
order by
  1,2,3,4
/
$FSQL
"
# ......................................
# List user/role object privilege
# ......................................
elif [ "$CHOICE" = "OBJECT_GRANT" ];then
    DO_EXECUTE=YES
    TITTLE="List user/role object privilege"
    SQL="set linesize 155 pagesize 66 verify off feed on pause off 
col user_name   form a22 head 'Username/Role'        just c
col object_type   form a16 head 'Type'                 just c
col table_name    form a26 head 'Object|Name'          just c
col column_name    form a26 head 'Column|Name'          just c
col tab_priv    form a18 head 'Object|Privilege'     just c
col grantable   form a9 head 'Grantable'
col privilege     form a18 head 'privilege'
-- set  timing on
prompt Direct Objects privileges given to $fentity:
         col grantee form a23 head 'Granted user' justify left
         col owner  form a16 head 'Object Owner' justify l
         break on object_type on grantee on owner on table_name
         prompt
       -- Both blocks do same thing, but I could not manage to unnest or merge the obj$
       --   Select   o.object_type, 
       --            pp.owner, pp.table_name, pp.column_name, pp.privilege, pp.grantable
       --   from   sys.dba_objects o,
       --         (Select p.grantee, p.owner, p.table_name, null column_name, p.privilege, p.grantable
       --                 from   sys.dba_tab_privs p where p.grantee =  '$fentity'
       --          UNION  
       --          Select p.grantee, p.owner, p.table_name, p.column_name, p.privilege, p.grantable
       --                 from   sys.dba_col_privs p where  p.grantee =  '$fentity'
       --         ) pp
       --   where o.OWNER  = pp.owner and o.object_name = pp.table_name ;

         Select    decode(type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                      11, 'PACKAGE BODY', 12, 'TRIGGER',
                      13, 'TYPE', 14, 'TYPE BODY',
                      19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
                      22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
                      28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
                      32, 'INDEXTYPE', 33, 'OPERATOR',
                      34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
                      40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
                      42, NVL((SELECT distinct 'REWRITE EQUIVALENCE'
                               FROM sum$ s
                               WHERE s.obj#=obj#
                                     and bitand(s.xpflags, 8388608) = 8388608),
                              'MATERIALIZED VIEW'),
                      43, 'DIMENSION',
                      44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
                      48, 'CONSUMER GROUP',
                      51, 'SUBSCRIPTION', 52, 'LOCATION',
                      55, 'XML SCHEMA', 56, 'JAVA DATA',
                      57, 'SECURITY PROFILE', 59, 'RULE',
                      60, 'CAPTURE', 61, 'APPLY',
                      62, 'EVALUATION CONTEXT',
                      66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',
                      72, 'WINDOW GROUP', 74, 'SCHEDULE', 79, 'CHAIN',
                      81, 'FILE GROUP',
                     'UNDEFINED') object_type, 
                  owner, table_name, column_name, privilege, grantable
 from(
     select u.name owner, o.name table_name,null column_name,  tpm.name privilege,
            decode(mod(oa.option$,2), 1, 'YES', 'NO') grantable,
            o.type#,o.obj#
     from sys.objauth$ oa, sys.obj$ o, sys.user$ u, sys.user$ ur, sys.user$ ue,
          table_privilege_map tpm
     where oa.obj# = o.obj#
       and oa.grantor# = ur.user#
       and oa.grantee# = ue.user#
       and oa.col# is null
       and oa.privilege# = tpm.privilege
       and u.user# = o.owner#
       and ue.name='$fentity'
union
     select u.name owner, o.name table_name, c.name column_name, tpm.name privilege,
            decode(mod(oa.option$,2), 1, 'YES', 'NO') grantable,
            o.type#, o.obj#
     from sys.objauth$ oa, sys.obj$ o, sys.user$ u, sys.user$ ur, sys.user$ ue,
          sys.col$ c, table_privilege_map tpm
     where oa.obj# = o.obj#
       and oa.grantor# = ur.user#
       and oa.grantee# = ue.user#
       and oa.obj# = c.obj#
       and oa.col# = c.col#
       and bitand(c.property, 32) = 0 /* not hidden column */
       and oa.col# is not null
       and oa.privilege# = tpm.privilege
       and u.user# = o.owner#
       and ue.name='$fentity'
) order by 3
/

prompt
Prompt Excluded from this list are Object grants from role 'SELECT_CATALOG_ROLE' and 'DBA'
prompt
col a1 head '$fentity''s Object privileges Obtained from Roles' for a80
select
    decode (instr(granted_role,' on '),0, lpad(' ', 4*level) ||granted_role || '  (Role)',
                                            lpad(' ', 4*level) || granted_role
    ) a1
from
  (
    select grantee, granted_role
    from
      dba_role_privs
    union
    select grantee,  rpad(privilege,10) || ' on   ' || owner||'.'||table_name as granted_role
           from dba_tab_privs  where grantee not in ('$fentity','SELECT_CATALOG_ROLE','DBA')
  )
start with grantee = '$fentity'
connect by grantee = prior granted_role
/
"
# ......................................
# List system privilege map 
# ......................................

elif [ "$CHOICE" = "MAP" ];then
   TITTLE="List system privilege map"
   SQL="select * from system_privilege_map;"

# ......................................
# List Role and grants hierarchy for a user
# ......................................
elif [ "$CHOICE" = "TREE" ];then
   TITTLE="List Role and grants hierarchy for a user"
   SQL="col a1 head 'User, his roles and privileges'
select
  lpad(' ', 4*level) || granted_role a1
from
  (
  /* THE USERS */
    select
      null     grantee,
      username granted_role
    from
      dba_users
    where
      username = upper('$fuser')
  /* THE ROLES TO ROLES RELATIONS */
  union
    select
      grantee,
      granted_role
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */
  union (
    select grantee, privilege from dba_sys_privs
    union
    select grantee, privilege from dba_tab_privs )
  )
start with grantee is null
connect by grantee = prior granted_role
/
"
echo "$SQL"
# ......................................
# Privs for one role
# ......................................
elif [ "$CHOICE" = "LIST_ROLE_GRANT" ];then
   TITTLE="Grant for role $frole"
  SQL=" col rp head 'role and privileges (-A when admin option is also granted)'
select lpad(' ', 4*level) || granted_role|| decode(admin_option,'YES','-A','') rp
    from (
       select grantee, granted_role, admin_option
              from
                 dba_role_privs where GRANTEE = '$frole'
              union
    select grantee, privilege , admin_option
    from
      dba_sys_privs  )
start with grantee = '$frole'
connect by grantee = prior granted_role
/
"

# ......................................
# Display users with the SYSDBA or SYSOPR privilge
# ......................................
elif [ "$CHOICE" = "PF" ];then
   TITTLE="Display users with the SYSDBA or SYSOPR privilge"
   SQL=" rem COL Violations                FORMAT A170      HEADING 'Violations'
select * from V_\$PWFILE_USERS;
"

# ......................................
#  List role distribution among users
# ......................................
# ......................................
elif [ "$CHOICE" = "ROLE_USER" ];then
  # this query consider only direct role, not sub-hierchical exploration done 
  if [ -n "$frole" ];then
       TITTLE="List user with role $frole"
       AND_ROLE=" and granted_role = '$frole' "
  fi
  TITTLE="List roles / user distribution"
  SQL="set linesize 80 pagesize 0
col role     form a30 head 'Role (admin,grant)'   just c
col username form a22 head 'Username'             just c

break  on role
select
  granted_role role ,
  username ||
  decode(admin_option,'YES','-A',' ') ||
  decode(granted_role,'YES','-G',' ') username
from
  dba_users,
  dba_role_privs
where
    dba_users.username = dba_role_privs.grantee  $AND_ROLE
and username not in ('PUBLIC')
order by
  1,2
/
"
# ......................................
# list existing role in DB 
# ......................................
elif [ "$CHOICE" = "LIST_ROLE" ];then
     TITTLE='List Role(s) in Database'
     SQL="prompt Use rol -g <role> to list grants of role
prompt
select '    ' ||ROLE roleh from dba_roles  order by 1;"
# ......................................
# 
# ......................................
elif [ "$CHOICE" = "USER_ROLE" ];then
  if [ -n "$fuser" ];then
     AND_USER=" and username = '$fuser' "
  else
     AND_USER=" and username not in ('PUBLIC')"
  fi
  TITTLE='List User  / ROLE distribution (rol -h : help)'
  SQL="
set linesize 132 pagesize 0
col username form a15 head 'Username'             just c
col dts      form a12 head 'Default|Tablespace'   just c
col tts      form a12 head 'Temporary|Tablespace' just c
col prof     form a14 head 'Profile'              just c
col role     form a27 head 'Role (admin,grant)'   just c
col default_role     form a7 head 'Default|Role'   just c
col ACCOUNT_STATUS     form a17 head 'Account|Status'   just c

break  on username on dts on tts  on prof

select
  username,
  default_tablespace    dts,
  temporary_tablespace  tts,
  profile prof,
  granted_role ||
  decode(admin_option,'YES','-A',' ') ||
  decode(granted_role,'YES','-G',' ') role , default_role, ACCOUNT_STATUS
from
  dba_users,
  dba_role_privs
where 
  dba_users.username = dba_role_privs.grantee  $AND_USER
order by
  1,2,3,4
/
"
# ......................................
# Generate a user creation script 
# ......................................
elif [ "$CHOICE" = "SCRIPT" ];then
. $SBIN/scripts/passwd.env
. ${GET_PASSWD} $S_USER $ORACLE_SID
if [  "x-$CONNECT_STRING" = "x-" ];then
   echo "could no get a the password of $S_USER"
   exit 0
fi
sqlplus -s "$CONNECT_STRING" <<EOF
set verify on feedback on termout on linesize 200 verify off feedback off pagesize 0
set trimspool on
set head off
spool cr_${fuser}.sql
SELECT 'CREATE USER $fuser IDENTIFIED by values ' ||''''||PASSWORD ||''' DEFAULT TABLESPACE  '
  || default_tablespace || '  TEMPORARY TABLESPACE ' || temporary_tablespace ||';'
  FROM sys.dba_users
 WHERE username = '$fuser'
/
SELECT    'ALTER USER $fuser QUOTA '
       || DECODE (max_bytes, -1, 'Unlimited', max_bytes)
       || ' ON '
       || tablespace_name
       || ';'
  FROM sys.dba_ts_quotas
 WHERE username = '$fuser'
/

SELECT 'GRANT ' || privilege || ' TO $fuser ' || admin_option
  FROM (
   SELECT LOWER(grantee) grantee, LOWER(granted_role) privilege,
          DECODE(admin_option,'YES',' WITH ADMIN OPTION;',';') admin_option
     FROM sys.dba_role_privs
     WHERE grantee != 'SYS'
   union
   SELECT LOWER(grantee) grantee, LOWER(granted_role) privilege,
          DECODE(admin_option,'YES',' WITH ADMIN OPTION;',';') admin_option
     FROM sys.dba_role_privs
     WHERE grantee != 'SYS'
   union
   SELECT LOWER(grantee) grantee, LOWER(privilege) privilege,
          DECODE(admin_option,'YES',' WITH ADMIN OPTION;',';') admin_option
     FROM dba_sys_privs s
     WHERE grantee != 'SYS'
   union
   SELECT LOWER(grantee) grantee,
          case privilege
               when 'READ'  then
                     LOWER(privilege) || ' ON DIRECTORY ' || lower(owner) ||'.'||LOWER(table_name)
               when 'WRITE'  then
                     LOWER(privilege) || ' ON DIRECTORY ' || lower(owner) ||'.'||LOWER(table_name)
               else
                     LOWER(privilege) || ' ON ' || lower(owner) ||'.'||LOWER(table_name)
               end privilege,
                DECODE(grantable,'YES', ' WITH ADMIN OPTION;',';') admin_option
           FROM dba_tab_privs t
           WHERE grantee != 'SYS'
             and t.privilege !='EXECUTE'
   union
   SELECT LOWER(grantee) grantee, LOWER(privilege) || ' ON ' ||
         lower(owner) ||'.'||LOWER(table_name) privilege, DECODE(grantable,'YES',
                                               ' WITH ADMIN OPTION;',';') admin_option
     FROM dba_tab_privs t
     WHERE grantee != 'SYS'
       and t.privilege ='EXECUTE'
   union
   SELECT LOWER(owner) grantee, 'ALL ON ' || LOWER(owner) ||'.'||
         LOWER(table_name) privilege, ';' admin_option
     FROM all_tables
    WHERE owner = lower('$fuser')
   ORDER BY 1
       )
  WHERE grantee = LOWER('$fuser');

SELECT DISTINCT 'CREATE SYNONYM '|| LOWER('$fuser') || '.' || LOWER(SYNONYM_NAME) ||
       ' FOR ' || LOWER(TABLE_OWNER) || '.' || LOWER(TABLE_NAME) || ';'
  FROM SYS.DBA_SYNONYMS
 WHERE owner = upper('$fuser')
 ORDER BY 1
/
SELECT  'CREATE DATABASE LINK ' || l.name ||
   ' CONNECT TO ' || LOWER(l.userid) || ' IDENTIFIED BY ' || LOWER(l.password) ||
   DECODE(l.host,NULL, NULL, ' USING '''||l.host) || ''';'
  FROM     sys.link$ l, sys.user$ u
 WHERE    l.owner# = u.user#
   AND    u.name = UPPER('$fuser')
 ORDER BY l.name
/
spool off

EOF

fi

. $SBIN/scripts/passwd.env
. ${GET_PASSWD} $S_USER $ORACLE_SID
if [  "x-$CONNECT_STRING" = "x-" ];then
   echo "could no get a the password of $S_USER"
   exit 0
fi
do_execute