-------------------------------------------------------------------------------
-- CONFIGURE some parts of the auditing subsystem
-------------------------------------------------------------------------------
-- All other parts like:
--     * creating database devices
--     * creating the database
--     * database options
-- are done in the 'install_ase.sh' script
-------------------------------------------------------------------------------
-- NOTE: You still have to configure *WHAT* you want to capture in the audit train
-- Look at: 'sp_audit' in the Sybase/SAP manuals
-------------------------------------------------------------------------------
-- Below is a few example of what may be of interest to audit
--
-- sp_audit 'security', 'all',       'all', 'on' -- "anything" that has with security is logged, which is a bunch of things: 
--                                                  -- Audit table access and changes; 
--                                                  -- server start/stop; 
--                                                  -- create/drop/alter login/login-profile/role; 
--                                                  -- online database; 
--                                                  -- set role/user; 
--                                                  -- sp_configure; 
--                                                  -- sp_passwordpolicy; 
--                                                  -- sp_ssladmin; 
--                                                  -- x number of builtin functions(authmech, show_cached_plan_in_xml, 
--                                                  --                               show_cached_text, show_cached_text_long, 
--                                                  --                               show_condensed_text, show_plan, 
--                                                  --                               valid_user, workload_metric)
-- sp_audit 'all',      'sa_role',   'all', 'on' -- everything the "sa_role" is doing
-- sp_audit 'all',      'sso_role',  'all', 'on' -- everything the "sso_role" is doing
-- sp_audit 'all',      'oper_role', 'all', 'on' -- everything the "oper_role" is doing
-- sp_audit 'login',    'all',       'all', 'on' -- All logins to ASE (both Success and Failed)
-- sp_audit 'logout',   'all',       'all', 'on' -- All Logouts from ASE
-- sp_audit 'dbcc',     'all',       'all', 'on' -- Any DBCC Commands executed by "anyone"
-------------------------------------------------------------------------------
go

exec sp_audit 'security', 'all',       'all', 'on' -- "anything" that has with security is logged, which is a bunch of things: 
--exec sp_audit 'all',      'sa_role',   'all', 'on' -- everything the "sa_role" is doing   -- note: This will take A LOT OF resources
exec sp_audit 'all',      'sso_role',  'all', 'on' -- everything the "sso_role" is doing  -- note: This will take A LOT OF resources
exec sp_audit 'all',      'oper_role', 'all', 'on' -- everything the "oper_role" is doing -- note: This will take A LOT OF resources
--exec sp_audit 'cmdtext',  'sa',        'all', 'on' -- cmd text for 'sa' if that's not already in 'all' for 'sa_role'...
exec sp_audit 'login',    'all',       'all', 'on' -- All logins to ASE (both Success and Failed)
exec sp_audit 'logout',   'all',       'all', 'on' -- All Logouts from ASE
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

-- For SIP
use sybsystemprocs
go
-- Audit any change at procedure level for login/user/alias (since we don't use: sp_audit 'all', 'XXX_role')
-- exec sp_audit 'exec_procedure', 'all', 'sybsystemprocs.dbo.sp_addlogin'   , 'on' 
-- exec sp_audit 'exec_procedure', 'all', 'sybsystemprocs.dbo.sp_droplogin'  , 'on' 
-- exec sp_audit 'exec_procedure', 'all', 'sybsystemprocs.dbo.sp_modifylogin', 'on' 
-- exec sp_audit 'exec_procedure', 'all', 'sybsystemprocs.dbo.sp_locklogin'  , 'on' 
go
-- exec sp_audit 'exec_procedure', 'all', 'sybsystemprocs.dbo.sp_adduser'    , 'on' 
-- exec sp_audit 'exec_procedure', 'all', 'sybsystemprocs.dbo.sp_dropuser'   , 'on' 
-- exec sp_audit 'exec_procedure', 'all', 'sybsystemprocs.dbo.sp_addalias'   , 'on' 
-- exec sp_audit 'exec_procedure', 'all', 'sybsystemprocs.dbo.sp_dropalias'  , 'on' 
go
-- exec sp_audit 'exec_procedure', 'all', 'sybsystemprocs.dbo.sp_addgroup'   , 'on' 
-- exec sp_audit 'exec_procedure', 'all', 'sybsystemprocs.dbo.sp_changegroup', 'on' 
-- exec sp_audit 'exec_procedure', 'all', 'sybsystemprocs.dbo.sp_dropgroup'  , 'on' 
go

-- For SIP
use master
go
-- Audit any change at table level for login/user/alias 
-- exec sp_audit 'insert', 'all', 'master.dbo.syslogins', 'on'
-- exec sp_audit 'update', 'all', 'master.dbo.syslogins', 'on'
-- exec sp_audit 'delete', 'all', 'master.dbo.syslogins', 'on'
go
-- exec sp_audit 'insert', 'all', 'master.dbo.sysusers', 'on'
-- exec sp_audit 'update', 'all', 'master.dbo.sysusers', 'on'
-- exec sp_audit 'delete', 'all', 'master.dbo.sysusers', 'on'
go
-- exec sp_audit 'insert', 'all', 'master.dbo.sysalternates', 'on'
-- exec sp_audit 'update', 'all', 'master.dbo.sysalternates', 'on'
-- exec sp_audit 'delete', 'all', 'master.dbo.sysalternates', 'on'
go

-- exec sp_audit 'insert', 'all', 'master.dbo.syssrvroles', 'on'
-- exec sp_audit 'update', 'all', 'master.dbo.syssrvroles', 'on'
-- exec sp_audit 'delete', 'all', 'master.dbo.syssrvroles', 'on'
go
-- exec sp_audit 'insert', 'all', 'master.dbo.sysloginroles', 'on'
-- exec sp_audit 'update', 'all', 'master.dbo.sysloginroles', 'on'
-- exec sp_audit 'delete', 'all', 'master.dbo.sysloginroles', 'on'
go

---------------------------------------
-- lopp all databases and add auditing to the following tables: sysusers, sysalternates
-- NOTE: This is done in file: aseConfigAuditScriptPostInstall.sql 
--       which is executed "near-end" of installation (after all load scripts etc has been executed)
--       At this stage the user databases are NOT YET created
---------------------------------------
--DECLARE dbname_curs cursor for 
--	SELECT name
--	FROM master.dbo.sysdatabases 
--	WHERE name not like 'syb%'
--	  AND name not like '%tempdb%'
--	  AND name not in('master', 'model', 'tempdb')
--	ORDER BY dbid
--FOR READ ONLY 
--go
--set nocount on
--go
--declare @c_dbname varchar(30) 
--declare @tabname  varchar(30) 
--declare @onoff    varchar(30) 
--
--OPEN dbname_curs 
--FETCH dbname_curs into @c_dbname
-- 
--while (@@sqlstatus = 0)  
--begin 
--	set @onoff = 'on'
--
--	set @tabname = 'sysusers'
--	print '>>> Setting auditing ''%1!'', for table ''%2!'' in database ''%3!''.', @onoff, @tabname, @c_dbname
--	exec('exec ' + @c_dbname + '.dbo.sp_audit ''insert'', ''all'', ''' + @c_dbname + '.dbo.' + @tabname + ''', ''' + @onoff + '''')
--	exec('exec ' + @c_dbname + '.dbo.sp_audit ''update'', ''all'', ''' + @c_dbname + '.dbo.' + @tabname + ''', ''' + @onoff + '''')
--	exec('exec ' + @c_dbname + '.dbo.sp_audit ''delete'', ''all'', ''' + @c_dbname + '.dbo.' + @tabname + ''', ''' + @onoff + '''')
--
--	set @tabname = 'sysalternates'
--	print '>>> Setting auditing ''%1!'', for table ''%2!'' in database ''%3!''.', @onoff, @tabname, @c_dbname
--	exec('exec ' + @c_dbname + '.dbo.sp_audit ''insert'', ''all'', ''' + @c_dbname + '.dbo.' + @tabname + ''', ''' + @onoff + '''')
--	exec('exec ' + @c_dbname + '.dbo.sp_audit ''update'', ''all'', ''' + @c_dbname + '.dbo.' + @tabname + ''', ''' + @onoff + '''')
--	exec('exec ' + @c_dbname + '.dbo.sp_audit ''delete'', ''all'', ''' + @c_dbname + '.dbo.' + @tabname + ''', ''' + @onoff + '''')
--
--	FETCH dbname_curs into @c_dbname
--end 
--CLOSE dbname_curs 
--DEALLOCATE cursor dbname_curs 
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.
**
** NOTE: This do NOT solve the issue if the data/log gets full...
**       We probably need to add some threshold action that truncates "everything" if the db starts to be full
**       And possibly for a LCT (Last Chance Threshold) is reached; disable auditing...
*/
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


