#!/bin/ksh
tput clear
######################################################
#To Dispalye the Users Privilges.
#####################################
echo '**********************************************************'
echo
echo "Enter the Date to Display User Created in the Database Format :<DD-MON-YY>:=\c"
read dat
echo
echo '***************************************************************'
sqlplus -s $ID/$PASS <<EOF
set lines 100
set pages 0
col username for a30
set feedback off
select username,created from dba_users
where to_char(created,'DD-MON-YY') > to_date('$dat','DD-MON-YY');
EOF
user1()
{
sqlplus -s $ID/$PASS <<EOF
set pages 0
set heading off
set feedback off
select username from dba_users
where to_char(created,'DD-MON-YY') > to_date('$dat','DD-MON-YY');
EOF
}
user1 > /tmp/user1.log
echo
echo '***********************************************************************'
list=/tmp/user1.log
echo "\t\tLIST OF USER ROLE ,PRIVELEGES, TABLE ASSIGN"
for i in `cat $list`
do
sqlplus -s $ID/$PASS <<EOF
set lines 200
col GRANTEE for a40
set heading off
set feedback off
SELECT GRANTEE, 'ROLE TYPE-->', GRANTED_ROLE PRIV
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = upper('$i')
UNION
SELECT GRANTEE, 'SYSTEM PRIV-->' TYPE, PRIVILEGE PRIV
FROM DBA_SYS_PRIVS
WHERE GRANTEE = upper('$i');
EOF
done
for b in `cat $list`
do
sqlplus -s $ID/$PASS <<EOF
set heading off
set feedback off
set lines 200
col GRANTEE for a30
col privilege for a30
col table_name for a30
col TABLENAME for a40
col OWNER for a50
select 'TABLE' type, grantee grantee, privilege priv, table_name tablename, '-->' colnm, owner owner
from dba_tab_privs
where grantee='$b';
EOF
done
