#!/usr/bin/ksh
# whatdbtableprivs ()
#-----------------------------------------------------------------------#
#    Module Name:  whatdbtableprivs  (c) 2012                            #
#                                                                       #
#    Purpose:      Report on Database TablePrivileges for a Grantee     #
#                                                                       #
#    Maintenance                                                        #
#    Date          Author        Description                            #
#    -----------   ------------  -------------------------------------  #
#    10-May-2012   M.Barone      Module design/creation                 #
#-----------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)

	clear
	echo
	echo  " #-------------------------------------------------------#"
	echo  " # whatdbtableprivs:  Oracle Database Privileges         #"
	echo  " #-------------------------------------------------------#"
	echo

	#----------------------------------------------------------------#
	#  Trap:  Set CNTL-C CNTL-D CNTL-\ Before Password Protection    #
	#----------------------------------------------------------------#
	trap        "stty echo; return"     2
	trap        "stty echo; return"     3
	stty -echo

	read PASSWD?" Please Enter the Oracle [APPS] Passwd: "

	stty echo
	echo
	#----------------------------------------------------------------#
	#  Trap:  Reset CNTL-C CNTL-D CNTL-\ After Password Protection   #
	#----------------------------------------------------------------#
	trap        2
	trap        3

	read SCHNAM?"        Enter the [SchemaName] Example: APPLSYS: "

	clear
	ORANAM=${ORASID}

#-----------------------------------------------------------------------#
#  SQL*Plus ("Here-Document")                                           #
#-----------------------------------------------------------------------#
sqlplus  -s  /nolog  <<-END_FILE

	CONNECT  APPS/$PASSWD

	clear	BREAKS
	clear	BUFFER
	clear	COLUMNS
	clear	COMPUTES
	clear	SCREEN
	clear	SQL
	clear	TIMING

	set	feedback	off
	set	flush		on
	set	heading	on
	set	pause		off
	set	space		1
	set	termout	on
	set	verify		on
	set	linesize	80
	set	pagesize	999

       SPOOL /tmp/whatdbtableprivs_$$.txt

	COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
	COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

	COLUMN	grantee			FORMAT A8  HEADING 'Grantee'
	COLUMN	owner			FORMAT A8  HEADING 'Owner'
	COLUMN	table_name		FORMAT A29 HEADING 'Table|Name'
	COLUMN	grantor			FORMAT A8  HEADING 'Grantor'
	COLUMN	privilege		FORMAT A17 HEADING 'Privilege'
	COLUMN	grantable		FORMAT A5  HEADING 'Grant|able'

       TTITLE  CENTER  'Oracle Database TablePrivileges for Grantee'	-
               LEFT    DATEVAR						-
	       RIGHT   'Page:'         FORMAT 999 SQL.pno	skip 1  -
               CENTER  'whatdbtableprivs -- Sorted by Owner, TableName' -
               LEFT    TIMEVAR                                  skip 2

       SELECT TO_CHAR(sysdate, 'DD-MON-YY')   datevalue,
              TO_CHAR(sysdate, 'HH24:MI:SS')  timevalue,
		grantee, owner, table_name,
		grantor, privilege, grantable
	 FROM 	SYS.DBA_TAB_PRIVS
	WHERE  	grantee  =  UPPER('${SCHNAM}')
	ORDER	BY grantee, owner, table_name, privilege;

EXIT;

END_FILE
print
print "Please see /tmp/whatdbtableprivs_$$.txt"
print
}