#!/bin/ksh
#***********************************************************************
#*                                                                     *
#*             Copyright (c) Ultra Electronics Limited 2007            *
#*                         All rights reserved                         *
#*                                                                     *
#***********************************************************************
#* 
#* Filename:           grant_schema.ksh
#* Subsystem Name:     Database build
#* Originating Author: Gary Roberts
#* Design Reference:   UAS/FALCP/CA/2743
#*
#***********************************************************************
#*
#* Description:
#*     This file is used to grant select, update, insert and delete 
#*     permissions on all of a schema's tables to another schema.
#*
#* Usage:
#*     grant_schema.ksh <grantor schema> <grantor password> <grantee schema>
#*
#* Options:
#*     All three arguments are mandatory.
#*
#***********************************************************************
#* Program Identification Array
#*
#* "%Z%%M% VERSION %I% %E% %U%"
#***********************************************************************
#* Modification History
#*
#* Rel Date/Time      Proj/Id   CA Ref/Details Of Change
#* --- -------------- --------  --------------------------------------
#**********************************************************************/


#
# Ensure that all three arguments have been passed . otherwise echo usage.
#
if [ $# -ne 3 ]
then
    echo "Usage: $0 <grantor schema> <grantor schema password> <grantee schema>"
    exit 1
fi

#
# Log into SQL*PLUS and create a tempfile of grant commands by selecting
# all of the user tables.
#
sqlplus -s $1/$2 <<EOF
BEGIN
    FOR rec IN (SELECT table_name FROM user_tables)
    LOOP
        EXECUTE IMMEDIATE 'GRANT INSERT,UPDATE,DELETE,SELECT ON ' || rec.table_name  || ' TO $3 WITH GRANT OPTION';
    END LOOP;

END;
/
EOF
