#! /bin/bash
###############################################################################
# Install instructions: (1 ASE reboot needs to be done, at step 3)
#  * Copy everything in the text file to a new file. (just so you have the original source text for next install)
#  * Replace the below <PLACEHOLDERS> with real values
# 	<SIP_USER_NUM>
# 	<ASE_SRV_NAME>
# 	<ASE_SRV_PASSWD>
#  * Start a command terminal as the user 'sybase' in each machine you want to install at.
#  * Then copy "isql...<<EOB" until "EOB" text in each section... 
#  * Paste it at the command terminal 
#    make sure SQL Commands executes with SUCCESS
#    note: step 3 and step 6: install a SQL File using "isql" (so there is only 1 row to copy)
###############################################################################
ASE_SRV_PASSWD=$(awk -F: '{if ($1=="sa") print $2}' ~/.passwd.enc | openssl enc -aes-128-cbc -a -d -salt -pass pass:${USER})
ASE_SRV_NAME=SYB_SEK_MXGT57
SIP_USER_NUM='57'


##################################################################
# step 0: Test that it works and you are logged in to the correct ASE
##################################################################
## <<<<--- Start copy from below row
isql -X -Usa -P$ASE_SRV_PASSWD -S$ASE_SRV_NAME -w999 <<EOB

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

EOB
## <<<<--- Until the above row



##################################################################
## 1: Create extra SEK logins with SUID: 5,6,7
## mimics what we are doing in: sql_templates_SEK/aseCreateLoginsScript.sql
##################################################################

## <<<<--- Start copy from below row
isql -X -Usa -P$ASE_SRV_PASSWD -S$ASE_SRV_NAME -w999 <<EOB

--CREATE LOGIN SIP<SIP_USER_NUM> WITH PASSWORD QwertY123     SUID 5
--go
CREATE LOGIN audit_user        WITH PASSWORD audit_user    SUID 6
go
--CREATE LOGIN ReadUserMX        WITH PASSWORD QwertY123     SUID 7
--go

EOB
## <<<<--- Until the above row




##################################################################
# 2: Create DB. OBS !! avmarkera
##################################################################

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

-- 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', 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',  size = '1024m', skip_alloc=false, directio=true, dsync=false
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
## <<<<--- Until the above row

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



##################################################################
# step 3: Sybase install script (dome FROM OS, NOTE: Needs reboot)
##################################################################

## -- apply the install script from the OS
## lafo Ta bort kommentarer
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 
fi

#### OS: RESTART ASE
cd /opt/sybase/home
./stopsyb.sh
./startsyb.sh




##################################################################
# step 4: Configure Auditing -- 
##################################################################

## <<<<--- Start copy from below row
isql -X -Usa -P$ASE_SRV_PASSWD -S$ASE_SRV_NAME -w999 --retserverror <<EOB

-- 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 
fi





##################################################################
# step 5: Some basic configuration (probably also done in aseConfigAuditScript.sql at step 6)
#         also: appply 'config_history' to auditing (not done in step 6)
##################################################################

## <<<<--- Start copy from below row
isql -X -Usa -P$ASE_SRV_PASSWD -S$ASE_SRV_NAME -w999 <<EOB

/*
** 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


-- 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
## <<<<--- Until the above row





##################################################################
# step 6: install SEK specifics -- aseConfigAuditScript.sql
##################################################################

## 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





##################################################################
# step 7: install SEK Post Specifics -- aseConfigAuditScriptPostInstall.sql (I extracted the script into the below)
##################################################################

## <<<<--- Start copy from below row
isql -X -Usa -P$ASE_SRV_PASSWD -S$ASE_SRV_NAME -w999 <<EOB

use sybsecurity
go

------------------------------------------
-- audit_user
------------------------------------------
exec sp_adduser audit_user
go
grant exec   on audit_collector       to audit_user
-- grant select on audit_collector_proxy to audit_user
go

------------------------------------------
-- SIP## user
------------------------------------------
--exec sp_adduser "SIP$SIP_USER_NUM"
go
--grant exec   on audit_collector       to "SIP$SIP_USER_NUM"
--grant select on audit_collector_proxy to "SIP$SIP_USER_NUM"
go

-- Check what we are currently auditing
sp_displayaudit 
go

EOB
## <<<<--- Until the above row

## Config security
isql -X -Usa -P$ASE_SRV_PASSWD -S$ASE_SRV_NAME -w999 -i /opt/sybase/home/scripts/sybinst/sql_templates_SEK/audit_collector.sql
isql -X -Usa -P$ASE_SRV_PASSWD -S$ASE_SRV_NAME -w999 -i /opt/sybase/home/scripts/sybinst/sql_templates_SEK/audit_collector_threshold_action.sql

