Shell Script: To provide Synonyms and Grants

Using the following shell script we can provide a synonyms and Grants from Core schema to another schema.

For example, to provide synonyms and grants from Dev_Core_Schema to Dev_Batch_Schema, we need to pass 2 command line arguments to this script as follows.

==> sh synonym_grant_schmea.sh Dev_Core_Schema Dev_Batch_Schema

-> Command Line Argument 1 - Dev_Core_Schema

-> Command Line Argument 2 – Dev_Batch_Schema

Script Content:

#!/bin/bash

#Program Name:synonym_grant_schmea.sh

#Purpose:Provide Synonym and Grants to Schmeas

export CORE=$1

export GRANTEE=$2

clean_event()

{

rm synonym_grant_schemas.sql

}

provide_syn_grant_all()

{

sqlplus -S / as sysdba <<EOF

whenever sqlerror exit sql.sqlcode;

set define off

set head off

set line 500

set pagesize 2000

spool synonym_grant_schemas.sql append

--CORE TO $GRANTEE

---------------------

SELECT 'CREATE OR REPLACE SYNONYM ' || '$GRANTEE' || '.' || OBJECT_NAME || ' FOR $CORE.' || OBJECT_NAME || ';' FROM DBA_OBJECTS WHERE OBJECT_TYPE IN ('TABLE','VIEW','SEQUENCE','PACKAGE','PACKAGE BODY','TYPE','PROCEDURE','FUNCTION','TRIGGER') AND OWNER ='$CORE';

SELECT 'GRANT SELECT,INSERT,UPDATE,DELETE ON $CORE.' || OBJECT_NAME || ' TO ' || '$GRANTEE' || ';' FROM DBA_OBJECTS  WHERE OBJECT_TYPE IN ('TABLE','VIEW') AND OWNER ='$CORE';

SELECT 'GRANT EXECUTE,DEBUG ON $CORE.' || OBJECT_NAME || ' TO ' || '$GRANTEE' || ';' FROM DBA_OBJECTS WHERE OBJECT_TYPE IN ('PACKAGE','PACKAGE BODY','TYPE','PROCEDURE','FUNCTION') AND OWNER ='$CORE';

exit;

EOF

}

execute()

{

sqlplus "/ as sysdba" <<EOF

whenever sqlerror exit sql.sqlcode;

@/Opt/Scripts/Synonym_Grant/synonym_grant_schemas.sql

exit;

EOF

}

help()

{

echo "Usage:$0 <<core>> <<grantee>> "

echo "Example:$0 CORE_SCHEMA OTHER_SCHEMA"

}

#Main Function starts here

if [ $# -eq 2 ]

then

clean_event

provide_syn_grant_all

else

echo "$0 : You must apply correct no of argument"

help

fi

I hope that this help someone !!