#! /bin/sh
#
# $DBA/creops
#
# Creates an account of OPS$username (where username is your current username)
# in the database whose SID is specified as the $1 parameter.
#
# Copies security from the SYSTEM account to the new OPS$username account.
#
# Restrictions:
#
#    1) Requires the following files to exist:
#	   $DBA/copyusg.sql
#          $DBA/copyusis.sql
#	   $DBA/copyusl.sql
#	   $DBA/copyustg.sql
#	   $DBA/copyuser.sql
#	   $TOOLS/define
#	   $TOOLS/fixcase
#	   $DBA/valid_db
#
#    2) Requires that the PW, and PWSYS environment variables exist.
#       The PW    variable must contain the password to the SYSTEM account.
#       The PWSYS variable must contain the password to the SYS account.
#
# Parameters:	$1 = Oracle SID of the database to be accessed
#
# <<<<< Modification History >>>>>
# 06/19/96	Brian Lomasky	Change ops\$oracle to $newname
# 06/17/96	Brian Lomasky	Use logname instead of whoami for SunOS
#				  compatibility
#
if [ $# -eq 0 ]; then
	echo "creops:  Error - No Oracle SID specified"
	echo "Usage: creops <SID-to-create-the-new-account-in>"
	exit 2
fi
if [ "$1" = "" ]; then
	echo "creops:  Error - No Oracle SID specified"
	echo "Usage: creops <SID-to-create-the-new-account-in>"
	exit 2
fi
if [ "$PW" = "" ]; then
	echo "creops:  Error - No PW environment variable found"
	exit 2
fi
if [ "$PWSYS" = "" ]; then
	echo "creops:  Error - No PWSYS environment variable found"
	exit 2
fi
uname=`logname`
newname="OPS\$$uname"
newname=`echo $newname | tr '[a-z]' '[A-Z]'`
uname="SYSTEM"
echo "Creating new account of $newname..."
#
# Verify the Oracle SID and set the correct environment variables
#
mysid=`fixcase $1`
valid_db $mysid
if [ $? -ne 0 ]; then exit 2
fi
SID=$mysid
. define
#
# Get a list of users in the database -- If the first entered username is
# not in the list, display an error.  If the second entered username is in
# the list, display an error
#
sqlplus -s system/$PW @$DBA/copyusis.sql
#
if [ ! -r copyusis.tmp ]
then
	echo "Error:  No copyusis.tmp file exists...\007"
	exit 2
fi
if [ -r copyuser_match.tmp ]
then
	rm copyuser_match.tmp
fi
cat copyusis.tmp | while read LINE
do
	if [ "$LINE" = "$uname" ]
	then
		touch copyuser_match.tmp
	fi
done
if [ -r copyuser_match.tmp ]
then
	rm copyuser_match.tmp
else
	echo ""
	echo "Userid $uname does not exist in the database.\007"
	echo "Please verify the userid and try again."
	echo ""
	echo "The following is a list of all users in the $mysid database:"
	cat copyusis.tmp
	echo ""
	rm copyusis.tmp
	exit 1
fi
cat copyusis.tmp | while read LINE
do
	if [ "$LINE" = "$newname" ]
	then
		touch copyuser_match.tmp
	fi
done
if [ -r copyuser_match.tmp ]
then
	rm copyuser_match.tmp
	echo ""
	echo "Userid $newname is already in the database.\007"
	echo "Please verify the userid and try again."
	echo ""
	echo "The following is a list of all users in the $mysid database:"
	cat copyusis.tmp
	echo ""
	echo "If you want to delete the old $newname user, log into"\
		"SYSTEM and type:"
	echo "     drop user $newname cascade;"
	rm copyusis.tmp
	exit 1
fi
rm copyusis.tmp
#
# Create scripts to copy the user and any system privileges
#
sqlplus -s system/$PW @$DBA/copyuser.sql $uname $newname
#
# Execute the script to copy the user and any system privileges
#
sqlplus -s system/$PW @copyuser.tmp
rm copyuser.tmp
#
# Default roles must be set correctly
# (When the roles are re-granted one by one, only the last role granted
#  becomes the default -- fix it)
#
sqlplus -s system/$PW @$DBA/copyusl.sql $uname $newname
#
if [ ! -r copyusl_$uname.tmp ]
then
	echo "Error:  No copyusl_$uname.tmp file exists...\007"
	exit 2
fi
#
# Process list of default roles, if any exist
#
if [ -s copyusl_$uname.tmp ]
then
	# Loop for every line in the output file from copyusl.sql
	echo "ALTER USER $newname" > reset_default_roles_$newname.sql
	echo "DEFAULT ROLE ALL EXCEPT" >> reset_default_roles_$newname.sql
	cat copyusl_$uname.tmp | while read LINE
	do
		if [ -r copyuser_match.tmp ]
		then
			echo "," $LINE >> reset_default_roles_$newname.sql
		else
			touch copyuser_match.tmp
			echo $LINE >> reset_default_roles_$newname.sql
		fi
	done
	if [ -r copyuser_match.tmp ]
	then
		rm copyuser_match.tmp
	fi
	echo ""
	echo "Defining non-default roles for the new user by executing:"
	cat reset_default_roles_$newname.sql
	echo ""
	sqlplus -s system/$PW @reset_default_roles_$newname.sql
	rm reset_default_roles_$newname.sql
else
	echo "Note:  No non-default roles found for $uname"
fi
rm copyusl_$uname.tmp
#
# Create scripts to grant access to the table objects the user has
#
echo "Creating table access scripts for $newname..."
sqlplus -s system/$PW @$DBA/copyustg.sql $uname $newname
#
# Since the grants to the table objects must be made only by the object owner,
# or an account which has grant option on the object, we will run a script to
# grant all privileges on the objects to SYSTEM (so that SYSTEM can then grant
# the desired privileges to the username being created).  This script is run as
# SYS, since that username owns the objects.
#
if [ -r copyuser_tab_SYSTEM.tmp ]
then
	if [ -s copyuser_tab_SYSTEM.tmp ]
	then
		echo "Granting SYSTEM access to tables..."
		sqlplus -s SYS/$PWSYS @copyuser_tab_SYSTEM.tmp
	fi
	rm copyuser_tab_SYSTEM.tmp
fi
#
# Run the script (as SYSTEM) to grant the desired privileges to the username
# being created.
#
if [ -r copyustg.tmp ]
then
	if [ -s copyustg.tmp ]
	then
		echo "Granting table object access to $newname..."
		sqlplus -s system/$PW @copyustg.tmp
	fi
	rm copyustg.tmp
fi
#
# Create scripts to grant access to the table columns the user has
#
echo "Creating column access scripts for $newname..."
sqlplus -s system/$PW @$DBA/copyusg.sql $uname $newname
#
# Since the grants to the table columns must be made only by the object owner,
# or an account which has grant option on the object, we will run a script to
# grant all privileges on the objects to SYSTEM (so that SYSTEM can then grant
# the desired privileges to the username being created).  This script is run as
# SYS, since that username owns the objects.
#
if [ -r copyuser_col_SYSTEM.tmp ]
then
	if [ -s copyuser_col_SYSTEM.tmp ]
	then
		echo "Granting SYSTEM access to columns..."
		sqlplus -s SYS/$PWSYS @copyuser_col_SYSTEM.tmp
	fi
	rm copyuser_col_SYSTEM.tmp
fi
#
# Run the script (as SYSTEM) to grant the desired privileges to the username
# being created.
#
if [ -r copyusg.tmp ]
then
	if [ -s copyusg.tmp ]
	then
		echo "Granting column access to $newname..."
		sqlplus -s system/$PW @copyusg.tmp
	fi
	rm copyusg.tmp
fi
#
# Define synonyms for DBA views for this newly-created DBA account
#
if [ -r copyuser_define_syns.tmp ]
then
	rm copyuser_define_syns.tmp
fi
#
# Extract all of the "CREATE SYNONYM" lines from catdbsyn.sql, append a ; at
# the end of each line, and write it out to a temp file to be executed.
# (If catdbsyn.sql is executed as it is, you'll get a whole bunch of errors
# from the "drop synonym" lines that are trying to drop synonyms that do not
# exist).
#
cat $ORACLE_HOME/rdbms/admin/catdbsyn.sql | awk ' {
	while (getline > 0) {
		if (match($0, "create synonym")) {
			printf("%s;\n",$0)
		}
	}
} END {print "exit"}' > copyuser_define_syns.tmp
echo "Defining synonyms for DBA views for this newly-created DBA account..."
sqlplus -s / @copyuser_define_syns.tmp
rm copyuser_define_syns.tmp
echo "Done..."