#!/usr/bin/ksh
#Script: grant_query_only.sh
#Author: goopand
#Platform: AIX only
#Version: 1.0
Show_Usage()
{
echo ""
echo "Usage:"
echo " grant_query_only.sh <db_name> <usr_name>"
echo "Purpose:"
echo " grant query privilege on database <db_name> to user <usr_name>"
echo ""
}
Script_Init()
{
#login user name
cur_usr_id=`id -un`
#current date time
cur_time=`date "+%Y-%m-%d %H:%M:%S"`
}
argsCorrectFlag=0
funArgsCorrect()
{
db_match_num=`db2 list active databases | grep -i "Database name" | awk -F"=" '{print $2}' | awk '{print $1}' | grep -i ${db_name} | wc -l | awk '{print $1}'`
usr_match_num=`cat /etc/passwd | grep -i ${usr_name} | wc -l | awk '{print $1}'`
if [ ${db_match_num} -ge 1 ] && [ ${usr_match_num} -ge 1 ]; then
argsCorrectFlag=1
else
argsCorrectFlag=0
if [ ${db_match_num} -eq 0 ]; then
echo "error: <db_name> '${db_name}' does not exist !"
fi
if [ ${usr_match_num} -eq 0 ]; then
echo "error: <usr_name> '${usr_name}' does not exist !"
fi
fi
}
if [ $# -ne 2 ]; then
Show_Usage
exit 1
else
db_name=$1
usr_name=$2
funArgsCorrect
if [ ${argsCorrectFlag} -ne 1 ]; then
Show_Usage
exit 1
else
Script_Init
fi
fi
grantQuery()
{
db2 connect to ${db_name}
table_list_file=/tmp/${db_name}_all_tables.txt
#grant connect,bindadd privileges,etc.
echo "[${cur_time}] grant basic connect,bindadd privileges ..."
db2 "grant connect,bindadd on database to user ${usr_name}"
db2 "grant usage on workload SYSDEFAULTUSERWORKLOAD to user ${usr_name}"
db2 "grant execute on package NULLID.SYSSH200 to user ${usr_name}"
echo ""
sleep 1
#grant select privilege on tables
echo "[${cur_time}] grant select privilege on tables ..."
sleep 1
db2 -x "select lower(ltrim(rtrim(tabschema)))||'.'||lower(ltrim(rtrim(tabname))) from syscat.tables order by 1 with ur" > ${table_list_file}
while read tb_name
do
echo "[${cur_time}] grant select on table ${tb_name} to user ${usr_name} ..."
db2 "grant select on table ${tb_name} to user ${usr_name}"
done < ${table_list_file}
if [ -f ${table_list_file} ]; then
rm -f ${table_list_file}
fi
db2 connect reset
}
#run grantQuery()
grantQuery
#End of script