#!/bin/ksh
# *******************************************************************************
#
# Filename: Extract_Users_Permissions.sh
# ———
#
# Description: This script uses ddlgen to extract all the users prior to a refresh.
# ———— These users can then easily be re-added afterwards.
#
# ———–
#
#
# *****************************************************************************/

# Source variables
SERVER=$1
DATABASE=$2
USERNAME=sa
PASSWORD=`/sybdba/sybutils/dba/utils/*******.ksh ${SERVER} ${USERNAME}`
INTERFACES=”/home/sybase/interfaces”
COLLECTION_DATE=`date +”%Y%m%d_%H%M%S”`
BASEDIR=/tmp/USER_EXTRACT
TMPDIR=”${BASEDIR}/tmp”
OUTPUTDIR=”${BASEDIR}/output”
ALIASFILE=”${SERVER}_${DATABASE}_${COLLECTION_DATE}_alias_list.tmp”
LOGFILE=”${SERVER}_${DATABASE}_${COLLECTION_DATE}_logfile.out”
ISQL=”/home/sybdba/ase_16.04-pl02/OCS-16_0/bin/isql -X”

# Check Usage
if [ $# != 2 ]
then
echo “Usage : $0 ”
exit 0
fi

# Check directories exist etc
if [ ! -d “${BASEDIR}” ]; then
mkdir ${BASEDIR}
fi
if [ ! -d “${TMPDIR}” ]; then
mkdir ${TMPDIR}
fi
if [ ! -d “${OUTPUTDIR}” ]; then
mkdir ${OUTPUTDIR}
fi
if [ -f “${OUTPUTDIR}/${SERVER}_${DATABASE}” ]; then
rm -f ${OUTPUTDIR}/${SERVER}_${DATABASE}
fi

# Generate sp_drop and adduser statements
echo “———————————————————-” >> ${OUTPUTDIR}/${SERVER}_${DATABASE}
echo “– USERS SECTION” >> ${OUTPUTDIR}/${SERVER}_${DATABASE}
echo “———————————————————-” >> ${OUTPUTDIR}/${SERVER}_${DATABASE}
${ISQL} -U${USERNAME} -P${PASSWORD} -w999 -S${SERVER} -D${DATABASE} -I${INTERFACES} </dev/null
set flushmessage on
set nocount on
go
select “sp_dropuser ” + u.name + CHAR(10) + “GO”
from sysusers u, sysusers g
where u.gid *= g.uid
and u.uid between 4 and 16382
go
select “sp_adduser ” + u.name + “, ” + u.name + “, ” + g.name + CHAR(10) + “GO”
from sysusers u, sysusers g
where u.gid *= g.uid
and u.uid between 4 and 16382
go
END

# Extract the aliases
${ISQL} -U${USERNAME} -P${PASSWORD} -w999 -S${SERVER} -D${DATABASE} -I${INTERFACES} </dev/null
set flushmessage on
set nocount on
go
select l.name from master..syslogins l, sysalternates a
where a.suid=l.suid
and a.altsuid=1
go
END2

# Run sql to extract User permissions
echo ” ”
echo “Extracting user permissions”
echo “———————————————————-” >> ${OUTPUTDIR}/${SERVER}_${DATABASE}
echo “USERS PERMISSIONS” >> ${OUTPUTDIR}/${SERVER}_${DATABASE}
echo “” >> ${OUTPUTDIR}/${SERVER}_${DATABASE}
${ISQL} -U${USERNAME} -P${PASSWORD} -w999 -S${SERVER} -D${DATABASE} -I${INTERFACES} </dev/null
set flushmessage on
set nocount on
go
select case when a.protecttype =1 then “GRANT” end, ” “,
case when a.action=151 then “references”
when a.action=193 then “select”
when a.action=195 then “insert”
when a.action=196 then “delete”
when a.action=197 then “update”
when a.action=224 then “execute”
when a.action=282 then “delete statistics”
when a.action=320 then “truncate table”
when a.action=326 then “update statistics”
when a.action=368 then “Transfer Table”
end, ” on “, convert(varchar(50),object_name(a.id)), ” to”, user_name(a.uid) + CHAR(10) + ‘GO’
from sysprotects a, sysobjects b, sysusers c
where a.id=b.id
and a.uid=c.uid
and b.type in (‘U’, ‘P’, ‘V’)
and c.uid between 4 and 16382
and c.suid >= 0
order by 3
go
END3

# Add in any Aliases
echo ” ”
echo ” ” >> ${OUTPUTDIR}/${SERVER}_${DATABASE}
echo “———————————————————-” >> ${OUTPUTDIR}/${SERVER}_${DATABASE}
echo “– ALIASES SECTION” >> ${OUTPUTDIR}/${SERVER}_${DATABASE}
echo “———————————————————-” >> ${OUTPUTDIR}/${SERVER}_${DATABASE}
for alias_user in `cat ${TMPDIR}/${ALIASFILE}`
do
echo “Creating sp_addalias for Alias ${alias_user}”
echo “sp_addalias ${alias_user}, dbo” >> ${OUTPUTDIR}/${SERVER}_${DATABASE}
echo “go” >> ${OUTPUTDIR}/${SERVER}_${DATABASE}
echo ” ”
echo ” ”
done

#
#
# NOW RESTORE THE DATABASE
#
# Recreate the users and aliases by running in the ddlgen extracts from above
echo “Recreate the users and aliases by running the following and supplying the password:”
echo “isql -X -U${USERNAME} -w999 -S${SERVER} -D${DATABASE} -I${INTERFACES} -i${OUTPUTDIR}/${SERVER}_${DATABASE}”
echo ” “