BASH Script: privs_update.sh

<- Scripts

Overview

Oracle does not recommend giving users DBA or equivalent privledges. Instead they recommend GRANTing table privs in other schema explicitly. This script automates the process. It grants a (target) user access to all tables in another (source) schema.

Options

• Can routinely run from cron.
• Can have multiple versions:
  • privs_update.HR.sh
  • privs_update.MyApp.sh ...

Import Note

Change below [ @ ] in your actual script taking spaces out. It would not properly format for posting here without the spaces. Could not escape them etc.

#!/bin/bash
# Purpose:      Update Privs
# Version:      2020.05.18
# Dependencies: inc_system.sh
#
# Background:   Oracle does not recommend giving users DBA or equiv privs.
#               Instead they recommend GRANTing table privs in other schema explicitly.
#               This script automates this process.

######################################
# Preamble: 2018.10.01               #
######################################
set -a; DIR_SCRIPTS="/u01/app/scripts"; source $DIR_SCRIPTS/inc_system.sh
sFullName=$(basename "$0"); me=${sFullName%.*}
sSLog="$DIR_LOGS/$me.sess.log"; sHLog="$DIR_LOGS/$me.hist.log"; > $sSLog
if [[ $MAINT_WINDOW -eq 1 ]]; then printf "Maintenance Window Detected - Exiting\n"; exit; fi

# Init Script Actions\Functions\Vars
source $DIR_LIB/inc_sqlplus.sh

# User Vars
usrSourceSchema="BI"
usrTargetSchema="ETL"

######################################
# Start                              #
######################################
clear; log "$sSLog" "$sFullName Started"; linesep
SysVars_show

# Create Array with All Tables in usrSourceSchema
sSQL="SELECT table_name FROM dba_tables WHERE owner='$usrSourceSchema' ORDER BY table_name;"
aTables=$(xsql "$sSQL");

# Update Privs for Each Table in usrTargetSchema
for tbl in ${aTables[ @ ]}; do
  sSQL_Grant="GRANT ALL ON $usrSourceSchema.$tbl TO $usrTargetSchema;"
  echo $sSQL_Grant
  CMD=$(xsql "$sSQL_Grant");
done

######################################
# End                                #
######################################
log "$sSLog" "$sFullName Ended [Elapse Time: $(elapse)]";
echo $(linesep "=") >> $sHLog; cat $sSLog >> $sHLog;
tail -32768 $sHLog > $sHLog.tmp; mv $sHLog.tmp $sHLog

Change per your requirements.

Output Snippet

 GRANT ALL ON BI.DIMDATE TO ETL;
 GRANT ALL ON BI.EXPRESSGOALS TO ETL;
 ...
 GRANT ALL ON BI.SECOND_MTG_IN_PROCESSING TO ETL;
 GRANT ALL ON BI.ZIPCITYCOUNTY TO ETL;