#! /bin/bash

###############################################################################
# Install instructions:
#  * Start a command terminal as the user 'sybase' in each machine you want to install at.
#  * NOTE: A "Continue?" Question will be asked before every step where you can choose y/n
#          In case of a "failure" somewhere, this makes it easier to "re-execute" the script
###############################################################################

ASE_SRV_NAME=${1:-${DSQUERY}} ## Use first input parameter, if no parameter then use DSQUERY, if no DSQUERY, it will be blank
ASE_SRV_PASSWD=${2:-${ASE_SRV_PASSWD:-$(awk -F: '{if ($1=="sa") print $2}' ~/.passwd.enc | openssl enc -aes-128-cbc -a -d -salt -pass pass:${USER})}}

##
## Get Sybase env
##
if [ -f /opt/sybase/0/SYBASE.sh ]
then
	. /opt/sybase/0/SYBASE.sh

elif [ -f /opt/sybase/DS/SYBASE.sh ]
then
	. /opt/sybase/DS/SYBASE.sh
else
	echo ""
	echo "======================================="
	echo " WARNING: The Sybase environment file '/opt/sybase/0/SYBASE.sh/SYBASE.sh' doesn't exists."
	echo "---------------------------------------"
	echo " Continuing with current environment settings, and hope that it works..."
fi


##
## Check input params
##
if [ -z "${ASE_SRV_NAME}" ]
then
	echo ""
	echo " ### Missing ASE Server name..."
	echo " ###  * You can pass it as the FIRST parameter to the script."
	echo " ###  * Or set the environment variable DSQUERY"
	echo ""
	exit 1
fi
if [ -z "${ASE_SRV_PASSWD}" ]
then
	echo ""
	echo " ### Missing ASE Password..."
	echo " ###  * You can pass it as the SECOND parameter to the script."
	echo " ###  * Or set the environment variable ASE_SRV_PASSWD"
	echo " ###  * Or suse the file: ${HOME}/.passwd.enc"
	echo ""
	exit 1
fi


#--------------------------------
# Function: confirmQuestion
#--------------------------------
function confirmQuestion() 
{
	## The default value is only used when you do NOT have STDIN available
	local lQuestion=${1:-Continue?}
	local lDefaultAnswer=${2:-Y}
	local isValid=0
	
	## Check the default value for Y or N
	case ${lDefaultAnswer} in
		[yY]) isValid=1 ;;
		[nN]) isValid=1 ;;
		*)
			echo "ERROR: confirmQuestion(): non valid default answer '${lDefaultAnswer}' was supplied.  EXITING..."
			exit 1
			;;
	esac 

	## Compose y/n default
	local lDefaultQuestion=""
	case ${lDefaultAnswer} in
		[yY]) lDefaultQuestion="[Y/n]" ;;
		[nN]) lDefaultQuestion="[y/N]" ;;
	esac 

	## if STDIN is open
	if [ -t 0 ]
	then
		while true
		do
			read -r -n 1 -p "${lQuestion} ${lDefaultQuestion}: " REPLY
			if [ -z "${REPLY}" ]
			then
				REPLY=${lDefaultAnswer}
			fi
			
			case ${REPLY} in
				[yY]) echo ; return 0 ;;
				[nN]) echo ; return 1 ;;
				*) printf " \033[31m %s \n\033[0m" "invalid input"
			esac 
		done
	else
		echo "Standard input isn't open, just CONTINUING... The question was answered as '${lDefaultAnswer}'."
		case ${lDefaultAnswer} in
			[yY]) echo ; return 0 ;;
			[nN]) echo ; return 1 ;;
		esac 
	fi
}

echo ""
echo "======================================================================="
echo " * Check if we can login to ASE '${ASE_SRV_NAME}'."
echo "======================================================================="
isql -X -Usa -P${ASE_SRV_PASSWD} -S${ASE_SRV_NAME} -w999 <<-EOB

	set nocount on
	go

	select @@servername as AT_SERVERNAME, getdate() as NOW, db_name() as DBNAME, suser_name() as LOGIN_NAME
	go

	print ' -- INFO: successful login'
	go

EOB

if [ $? -ne 0 ]
then
	echo " ### Somting went wrong. Exiting ..."
	exit 1
fi



##################################################################
##################################################################
echo ""
echo "======================================================================="
echo " * Create login 'audit_user' and permissions for the login."
echo " * NOTE: Check the password here..."
echo "======================================================================="
confirmQuestion "Continue?" "Y"
if [ $? -eq 0 ] ## ---- Yes ----
then
	isql -X -Usa -P${ASE_SRV_PASSWD} -S${ASE_SRV_NAME} -w999 <<-EOB

		set nocount on
		go

		print ' -- Creating login ''audit_user''.'
		go
		CREATE LOGIN audit_user        WITH PASSWORD fA3MtHHNaTp2v2X3Hanz8Lbt    SUID 6  -- password generated at: https://passwordsgenerator.net/
		go

		-- Give 'mon_role' to login 'audit_user' so we can access monSysSQLText & monSysStatement tables
		print ' -- granting ''mon_role'' to ''audit_user''.'
		go
		grant role mon_role to audit_user 
		go

		-- Give access to 'sysloginroles' to login 'audit_user' so we can get role names for logins
		use master
		go

		if not exists (select * from sysusers where name = 'audit_user')
		begin
			print ' -- Adding user ''audit_user'' in database ''master''.'
			exec sp_adduser audit_user 
		end
		go

		print ' -- grating select on ''sysloginroles'' to user ''audit_user''.'
		go
		grant select on sysloginroles to audit_user
		go

	EOB
else ## ---- No ----
	echo " >>> Skipped this section."
fi



##################################################################
##################################################################
echo ""
echo "======================================================================="
echo " * Check/Set ASE 'Monitoring' Configuration"
echo "======================================================================="
confirmQuestion "Continue?" "Y"
if [ $? -eq 0 ] ## ---- Yes ----
then
	isql -X -Usa -P${ASE_SRV_PASSWD} -S${ASE_SRV_NAME} -w999 <<-EOB

		set nocount on
		go

		------------------------------------------------------------------
		declare @cfgName varchar(30) set @cfgName = 'enable monitoring'
		------------------------------------------------------------------
		declare @minVal  int         set @minVal  = 1
		declare @setVal  int         set @setVal  = 1
		declare @curVal  int         select @curVal = value from syscurconfigs where config = (select config from sysconfigures where comment = @cfgName)
		if (@curVal >= @minVal)
		begin
			print ' -- INFO: ASE Monitor Config ''%1!'' is OK. (minVal=%2!, curVal=%3!)', @cfgName, @minVal, @curVal
		end
		else
		begin
			print ' ----->>>> FIXING: ASE Monitor Config ''%1!'' will be set to %5! (minVal=%2!, curVal=%3!, setVal=%4!)', @cfgName, @minVal, @curVal, @setVal, @setVal
			exec sp_configure @cfgName, @setVal
		end
		go

		------------------------------------------------------------------
		declare @cfgName varchar(30) set @cfgName = 'sql text pipe active'
		------------------------------------------------------------------
		declare @minVal  int         set @minVal  = 1
		declare @setVal  int         set @setVal  = 1
		declare @curVal  int         select @curVal = value from syscurconfigs where config = (select config from sysconfigures where comment = @cfgName)
		if (@curVal >= @minVal)
		begin
			print ' -- INFO: ASE Monitor Config ''%1!'' is OK. (minVal=%2!, curVal=%3!)', @cfgName, @minVal, @curVal
		end
		else
		begin
			print ' ----->>>> FIXING: ASE Monitor Config ''%1!'' will be set to %5! (minVal=%2!, curVal=%3!, setVal=%4!)', @cfgName, @minVal, @curVal, @setVal, @setVal
			exec sp_configure @cfgName, @setVal
		end
		go

		------------------------------------------------------------------
		declare @cfgName varchar(30) set @cfgName = 'sql text pipe max messages'
		------------------------------------------------------------------
		declare @minVal  int         set @minVal  = 1000
		declare @setVal  int         set @setVal  = 1000
		declare @curVal  int         select @curVal = value from syscurconfigs where config = (select config from sysconfigures where comment = @cfgName)
		if (@curVal >= @minVal)
		begin
			print ' -- INFO: ASE Monitor Config ''%1!'' is OK. (minVal=%2!, curVal=%3!)', @cfgName, @minVal, @curVal
		end
		else
		begin
			print ' ----->>>> FIXING: ASE Monitor Config ''%1!'' will be set to %5! (minVal=%2!, curVal=%3!, setVal=%4!)', @cfgName, @minVal, @curVal, @setVal, @setVal
			exec sp_configure @cfgName, @setVal
		end
		go

		------------------------------------------------------------------
		declare @cfgName varchar(30) set @cfgName = 'statement pipe active'
		------------------------------------------------------------------
		declare @minVal  int         set @minVal  = 1
		declare @setVal  int         set @setVal  = 1
		declare @curVal  int         select @curVal = value from syscurconfigs where config = (select config from sysconfigures where comment = @cfgName)
		if (@curVal >= @minVal)
		begin
			print ' -- INFO: ASE Monitor Config ''%1!'' is OK. (minVal=%2!, curVal=%3!)', @cfgName, @minVal, @curVal
		end
		else
		begin
			print ' ----->>>> FIXING: ASE Monitor Config ''%1!'' will be set to %5! (minVal=%2!, curVal=%3!, setVal=%4!)', @cfgName, @minVal, @curVal, @setVal, @setVal
			exec sp_configure @cfgName, @setVal
		end
		go

		------------------------------------------------------------------
		declare @cfgName varchar(30) set @cfgName = 'statement pipe max messages'
		------------------------------------------------------------------
		declare @minVal  int         set @minVal  = 10000
		declare @setVal  int         set @setVal  = 10000
		declare @curVal  int         select @curVal = value from syscurconfigs where config = (select config from sysconfigures where comment = @cfgName)
		if (@curVal >= @minVal)
		begin
			print ' -- INFO: ASE Monitor Config ''%1!'' is OK. (minVal=%2!, curVal=%3!)', @cfgName, @minVal, @curVal
		end
		else
		begin
			print ' ----->>>> FIXING: ASE Monitor Config ''%1!'' will be set to %5! (minVal=%2!, curVal=%3!, setVal=%4!)', @cfgName, @minVal, @curVal, @setVal, @setVal
			exec sp_configure @cfgName, @setVal
		end
		go

		------------------------------------------------------------------
		declare @cfgName varchar(30) set @cfgName = 'statement statistics active'
		------------------------------------------------------------------
		declare @minVal  int         set @minVal  = 1
		declare @setVal  int         set @setVal  = 1
		declare @curVal  int         select @curVal = value from syscurconfigs where config = (select config from sysconfigures where comment = @cfgName)
		if (@curVal >= @minVal)
		begin
			print ' -- INFO: ASE Monitor Config ''%1!'' is OK. (minVal=%2!, curVal=%3!)', @cfgName, @minVal, @curVal
		end
		else
		begin
			print ' ----->>>> FIXING: ASE Monitor Config ''%1!'' will be set to %5! (minVal=%2!, curVal=%3!, setVal=%4!)', @cfgName, @minVal, @curVal, @setVal, @setVal
			exec sp_configure @cfgName, @setVal
		end
		go

		------------------------------------------------------------------
		declare @cfgName varchar(30) set @cfgName = 'per object statistics active'
		------------------------------------------------------------------
		declare @minVal  int         set @minVal  = 1
		declare @setVal  int         set @setVal  = 1
		declare @curVal  int         select @curVal = value from syscurconfigs where config = (select config from sysconfigures where comment = @cfgName)
		if (@curVal >= @minVal)
		begin
			print ' -- INFO: ASE Monitor Config ''%1!'' is OK. (minVal=%2!, curVal=%3!)', @cfgName, @minVal, @curVal
		end
		else
		begin
			print ' ----->>>> FIXING: ASE Monitor Config ''%1!'' will be set to %5! (minVal=%2!, curVal=%3!, setVal=%4!)', @cfgName, @minVal, @curVal, @setVal, @setVal
			exec sp_configure @cfgName, @setVal
		end
		go

	EOB
else ## ---- No ----
	echo " >>> Skipped this section."
fi



##################################################################
##################################################################
echo ""
echo "======================================================================="
echo " * Create database 'sybsecurity', and set database options."
echo "======================================================================="
confirmQuestion "Continue?" "Y"
if [ $? -eq 0 ] ## ---- Yes ----
then
	isql -X -Usa -P${ASE_SRV_PASSWD} -S${ASE_SRV_NAME} -w999 --retserverror <<-EOB

		set nocount on
		go

		print ' -- INFO: Creating db devices ''sybsecurity_data_1'', with size 4GB.'
		print ' -- INFO: Creating db devices ''sybsecurity_log_1'' , with size 1GB.'
		go

		-- Create data and log device to hold the database
		disk init name = 'sybsecurity_data_1', physname = '/sybdev/mx3/data/${ASE_SRV_NAME}.sybsecurity_data_1.dev', size = '4096m', skip_alloc=false, directio=true, dsync=false
		go
		disk init name = 'sybsecurity_log_1',  physname = '/sybdev/mx3/log/${ASE_SRV_NAME}.sybsecurity_log_1.dev',  size = '1024m', skip_alloc=false, directio=true, dsync=false
		go


		print ' -- INFO: Creating database ''sybsecurity'', with database option ''trunc log on chkpt''.'
		go
		 
		-- Create the database
		create database sybsecurity 
					 on sybsecurity_data_1 = 4096 
				 log on sybsecurity_log_1  = 1024
		go
		sp_dboption sybsecurity, 'trunc log on chkpt', true
		go
		sp_helpdb sybsecurity
		go

	EOB

	if [ $? -ne 0 ]
	then
		echo " ### Somting went wrong. Exiting ..."
		exit 1
	fi
else ## ---- No ----
	echo " >>> Skipped this section."
fi



##################################################################
##################################################################
echo ""
echo "======================================================================="
echo " * Run Sybase provided script '${SYBASE}/${SYBASE_ASE}/scripts/installsecurity' "
echo " * Then restart ASE "
echo "======================================================================="
confirmQuestion "Continue?" "Y"
if [ $? -eq 0 ] ## ---- Yes ----
then
	## -- apply the install script from the OS
	isql -X -Usa -P${ASE_SRV_PASSWD} -S${ASE_SRV_NAME} -w999 -i ${SYBASE}/${SYBASE_ASE}/scripts/installsecurity --retserverror

	if [ $? -ne 0 ]
	then
		echo " ### Somting went wrong. Exiting ..."
		exit 1
	fi

	#### OS: RESTART ASE (subshell to move back to current working directory after execution)
	(
		cd /opt/sybase/home
		./stopsyb.sh
		./startsyb.sh
	)
else ## ---- No ----
	echo " >>> Skipped this section."
fi



##################################################################
##################################################################
echo ""
echo "======================================================================="
echo " * Enable auditing... sp_configure 'auditing', 1"
echo "======================================================================="
confirmQuestion "Continue?" "Y"
if [ $? -eq 0 ] ## ---- Yes ----
then
## <<<<--- Start copy from below row
	isql -X -Usa -P${ASE_SRV_PASSWD} -S${ASE_SRV_NAME} -w999 --retserverror <<-EOB

		set nocount on
		go

		-- Configure auditing
		exec sp_configure 'auditing', 1 -- NOTE: if you get errors here, you will probably have to reboot ASE
		go

	EOB

	if [ $? -ne 0 ]
	then
		echo " ### Somting went wrong. Exiting ..."
		exit 1
	fi
else ## ---- No ----
	echo " >>> Skipped this section."
fi



##################################################################
##################################################################
echo ""
echo "======================================================================="
echo " * Configure some BASIC Audit Configurations:"
echo "   - suspend audit when device full"
echo "   - audit queue size"
echo "   - etc..."
echo "======================================================================="
confirmQuestion "Continue?" "Y"
if [ $? -eq 0 ] ## ---- Yes ----
then
	isql -X -Usa -P${ASE_SRV_PASSWD} -S${ASE_SRV_NAME} -w999 <<-EOB

		set nocount on
		go

		print ' -- INFO: configuring specififc auditing options...'
		go

		/*
		** suspend audit when device full determines what Adaptive Server does when an audit device becomes completely full.
		** Note: If you have two or more audit tables, each on a separate device other than the master device, and you have a threshold procedure for each audit table segment, the audit devices should never become full. Only if a threshold procedure is not functioning properly does the “full” condition occur.
		** Choose one of these values:
		**   0 – truncates the next audit table and starts using it as the current audit table when the current audit table becomes full. If you set suspend audit when device full to 0, you ensure that the audit process is never suspended. However, you incur the risk that older audit records are lost if they have not been archived.
		**   1 – suspends the audit process and all user processes that cause an auditable event. To resume normal operation, the system security officer must log in and set up an empty table as the current audit table. During this period, the system security officer is exempt from normal auditing. If the system security officer’s actions would generate audit records under normal operation, Adaptive Server sends an error message and information about the event to the error log.
		*/
		sp_configure 'suspend audit when device full', 0   ---- DEFAULT=1    set to 0
		go

		/*
		** The in-memory audit queue holds audit records generated by user processes until the records can be processed and written to the audit trail. To change the size of an audit queue, a system security officer can use audit queue size. When you configure the queue suze, there is a trade-off between performance and risk. If the queue is too large, records can remain in it for some time. As long as records are in the queue, they are at risk of being lost if the system fails. However, if the queue is too small, it can repeatedly become full, which affects overall system performance; user processes that generate audit records sleep if the audit queue is full.
		** Following are some guidelines for determining how big your audit queue should be. You must also take into account the amount of auditing to be performed at your site.
		** - The memory requirement for a single audit record is 424 bytes; however, a record can be as small as 22 bytes when it is written to a data page.
		** - The maximum number of audit records that can be lost in a system failure is the size of the audit queue (in records), plus 20. After records leave the audit queue, they remain on a buffer page until they are written to the current audit table on the disk. The pages are flushed to disk every 20 records, less if the audit process is not constantly busy.
		** - In the system audit tables, the extrainfo field and fields containing names are of variable length, so audit records that contain complete name information are generally larger.
		** The number of audit records that can fit on a page varies from 4 to as many as 80 or more. The memory requirement for the default audit queue size of 100 is approximately 42K.
		*/
		sp_configure 'audit queue size', 512               ---- DEFAULT=100    set to 512 or larger
		go

		/*
		** current audit table establishes the table where Adaptive Server writes audit rows. A system security officer can change the current audit table, using:
		**   sp_configure "current audit table", n [, "with truncate"]
		** where n is an integer that determines the new current audit table, as follows:
		**   - 1 means sysaudits_01, 2 means sysaudits_02, and so forth, up to 8.
		**   - 0 tells Adaptive Server to set the current audit table to the next table. For example, if your installation has three audit tables, sysaudits_01, sysaudits_02, and sysaudits_03, Adaptive Server sets the current audit table to:
		**       - 2 if the current audit table is sysaudits_01
		**       - 3 if the current audit table is sysaudits_02
		**       - 1 if the current audit table is sysaudits_03
		** "with truncate" specifies that Adaptive Server should truncate the new table if it is not already empty. sp_configure fails if this option is not specified and the table is not empty.
		** Note: If Adaptive Server truncates the current audit table, and you have not archived the data, the table’s audit records are lost. Be sure that the audit data is archived before using the with truncate option.
		** To execute sp_configure to change the current audit table, you must have the sso_role active. You can write a threshold procedure to change the current audit table automatically.
		*/
		sp_configure 'current audit table'                 ---- DEFAULT=1    set this to the *current* audit trail table: sybsecurity.dbo.sysaudits_01
		go

	EOB
else ## ---- No ----
	echo " >>> Skipped this section."
fi



##################################################################
##################################################################
echo ""
echo "======================================================================="
echo " * install SEK specifics -- sp_audit '...options...'"
echo "======================================================================="
confirmQuestion "Continue?" "Y"
if [ $? -eq 0 ] ## ---- Yes ----
then
	## At the OS:
	## Edit /opt/sybase/home/scripts/sybinst/sql_templates_SEK/aseConfigAuditScript.sql first !!
	## isql -X -Usa -P${ASE_SRV_PASSWD} -S${ASE_SRV_NAME} -w999 -i /opt/sybase/home/scripts/sybinst/sql_templates_SEK/aseConfigAuditScript.sql
	## isql -X -Usa -P${ASE_SRV_PASSWD} -S${ASE_SRV_NAME} -w999 -i /opt/sybase/home/scripts/sybinst/sql_templates_SEK/aseConfigAuditScriptPostInstall.sql

	isql -X -Usa -P${ASE_SRV_PASSWD} -S${ASE_SRV_NAME} -w999 <<-EOB

		set nocount on
		go

		print ' -- INFO: auditing options... tailored for SEK...'
		go

		exec sp_audit 'security', 'all',       'all', 'on' -- "anything" that has with security is logged, which is a bunch of things: 
		go
		--exec sp_audit 'all',    'sa_role',   'all', 'on' -- everything the "sa_role" is doing   -- note: This will take A LOT OF resources (NOT ENABLED )
		go
		exec sp_audit 'all',      'sso_role',  'all', 'on' -- everything the "sso_role" is doing  -- note: This will take A LOT OF resources (can probably be disabled)
		go
		exec sp_audit 'all',      'oper_role', 'all', 'on' -- everything the "oper_role" is doing -- note: This will take A LOT OF resources (can probably be disabled)
		go
		--exec sp_audit 'cmdtext','sa',        'all', 'on' -- cmd text for 'sa' if that's not already in 'all' for 'sa_role'... (NOT ENABLED )
		go
		exec sp_audit 'login',    'all',       'all', 'on' -- All logins to ASE (both Success and Failed)
		go
		exec sp_audit 'logout',   'all',       'all', 'on' -- All Logouts from ASE
		go
		exec sp_audit 'dbcc',     'all',       'all', 'on' -- Any DBCC Commands executed by "anyone"
		go

		-- Login Admin
		exec sp_audit 'login_admin', 'all', 'all', 'on'
		go

		-- Configure auditing to save config changes
		exec sp_audit 'config_history', 'all', 'all', 'on' 
		go
		 
		-- Create a view so we can read the config changes
		exec sybsecurity.dbo.sp_confighistory 'create_view' 
		go

	EOB
else ## ---- No ----
	echo " >>> Skipped this section."
fi



##################################################################
##################################################################
echo ""
echo "======================================================================="
echo " * install SEK Specific extentions info 'sybsecurity' -- audit_collector.sql"
echo "======================================================================="
confirmQuestion "Continue?" "Y"
if [ $? -eq 0 ] ## ---- Yes ----
then
	isql -X -Usa -P${ASE_SRV_PASSWD} -S${ASE_SRV_NAME} -w999 -i /opt/sek_ase_auditor/0/sql/audit_collector.sql
else ## ---- No ----
	echo " >>> Skipped this section."
fi



##################################################################
##################################################################
echo ""
echo "======================================================================="
echo " * install SEK Specific extentions info 'sybsecurity' -- audit_collector_threshold_action.sql"
echo "======================================================================="
confirmQuestion "Continue?" "Y"
if [ $? -eq 0 ] ## ---- Yes ----
then
	isql -X -Usa -P${ASE_SRV_PASSWD} -S${ASE_SRV_NAME} -w999 -i /opt/sek_ase_auditor/0/sql/audit_collector_threshold_action.sql
else ## ---- No ----
	echo " >>> Skipped this section."
fi



##################################################################
##################################################################
echo ""
echo "======================================================================="
echo " * Finally display auditing settings"
echo "======================================================================="
confirmQuestion "Continue?" "Y"
if [ $? -eq 0 ] ## ---- Yes ----
then
	isql -X -Usa -P${ASE_SRV_PASSWD} -S${ASE_SRV_NAME} -w999 <<-EOB

		set nocount on
		go

		-- Check what we are currently auditing
		sp_configure 'auditing'
		go

		-- Check what we are currently auditing
		sp_displayaudit 
		go

	EOB
else ## ---- No ----
	echo " >>> Skipped this section."
fi

