/*
15.5/EBF 17340 SMP/P/x86_64/Enterprise Linux/ase155/2391/64-bit/OPT/Mon Nov  9 14:15:35 2009

Confidential property of Sybase, Inc.
Copyright 1987, 2009
Sybase, Inc.  All rights reserved.
Unpublished rights reserved under U.S. copyright laws.

This software contains confidential and trade secret information of Sybase,
Inc.   Use,  duplication or disclosure of the software and documentation by
the  U.S.  Government  is  subject  to  restrictions set forth in a license
agreement  between  the  Government  and  Sybase,  Inc.  or  other  written
agreement  specifying  the  Government's rights to use the software and any
applicable FAR provisions, for example, FAR 52.227-19.
Sybase, Inc. One Sybase Drive, Dublin, CA 94568, USA
*/

declare @retval int
exec @retval = sp_version 'installsecurity', NULL, '15.5/EBF 17340 SMP/P/x86_64/Enterprise Linux/ase155/2391/64-bit/OPT/Mon Nov  9 14:15:35 2009', 'start'
if (@retval != 0) select syb_quit()
go

declare @script_versnum int
select @script_versnum = 15500
if (@@version_number < @script_versnum)
begin
	print "'installsecurity' is being run on an older ASE installation. ASE version '%1!', install scripts version '%2!'.",
		@@version_number, @script_versnum
	select syb_quit()
end
go

declare @do_quit int
select @do_quit = 0
if (check_db_upgrade('master') = 0)
begin
	print "'installsecurity' cannot continue, because 'master' database is not upgraded to correct version."
	select @do_quit = 1
end
if (check_db_upgrade('sybsystemprocs') = 0)
begin
	print "'installsecurity' cannot continue, because 'sybsystemprocs' database is not upgraded to correct version."
	select @do_quit = 1
end
if (check_db_upgrade('sybsecurity') = 0)
begin
	print "'installsecurity' cannot continue, because 'sybsecurity' database is not upgraded to correct version."
	select @do_quit = 1
end
if (@do_quit = 1)
	select syb_quit()
go

use master
go
if exists (select *
	from sysobjects
		where name = 'sp_configure')
begin
	execute sp_configure "allow updates", 1
end
if exists (select *
        from sysdatabases
                where name = 'sybsystemprocs')
begin
        use sybsystemprocs
end
else
        use master
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_auditobject')
begin
	print 'Dropping sp_auditobject'
	drop procedure sp_auditobject
end
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_auditdatabase')
begin
	print 'Dropping sp_auditdatabase'
	drop procedure sp_auditdatabase
end
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_auditinstall')
begin
	print 'Dropping sp_auditinstall'
	drop procedure sp_auditinstall
end
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_auditlogin')
begin
	print 'Dropping sp_auditlogin'
	drop procedure sp_auditlogin
end
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_auditoption')
begin
	print 'Dropping sp_auditoption'
	drop procedure sp_auditoption
end
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_auditsproc')
begin
	print 'Dropping sp_auditsproc'
	drop procedure sp_auditsproc
end
go
exec sp_drop_object 'sp_addauditrecord', 'procedure'
go

exec sp_drop_object 'sp_audit', 'procedure'
go

exec sp_drop_object 'sp_aux_getsptaud', 'procedure'
go

if ((select db_name()) = "master") 
	dump tran master with truncate_only
else
    if ((select db_name()) = "sybsystemprocs") 
	dump tran sybsystemprocs with truncate_only
    else
        if ((select db_name()) = "dbccdb") 
	    dump tran dbccdb with truncate_only
        else
            if ((select db_name()) = "dbccalt") 
	        dump tran dbccalt with truncate_only
go

exec sp_drop_object 'sp_aux_displaylogaud', 'procedure'
go

exec sp_drop_object 'sp_aux_displayobjaud', 'procedure'
go

exec sp_drop_object 'sp_aux_displayoptaud', 'procedure'
go

exec sp_drop_object 'sp_aux_displaysprocaud', 'procedure'
go

exec sp_drop_object 'sp_aux_displaydbaud', 'procedure'
go

if ((select db_name()) = "master") 
	dump tran master with truncate_only
else
    if ((select db_name()) = "sybsystemprocs") 
	dump tran sybsystemprocs with truncate_only
    else
        if ((select db_name()) = "dbccdb") 
	    dump tran dbccdb with truncate_only
        else
            if ((select db_name()) = "dbccalt") 
	        dump tran dbccalt with truncate_only
go

exec sp_drop_object 'sp_displayaudit', 'procedure'
go

exec sp_drop_object 'sp_addaudittable', 'procedure'
go

use master
go
/* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */

/*
** A_AUDITOPTIONS
**
** This script creates and populates the sysauditoptions table in the
** sybsecurity database.  It will be read into the installsecurity script,
** and thus it returns to the master DB context at the end.
**
** History:
**	08/24/92 (uno)	created
**
** Messages for "sp_a_auditoptions"
**
** 17142, "You need to be able to set curwrite label to data_high. This script will continue in spite of failure to set curwrite. Please rerun after setting your labels correctly."
** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
*/

set nocount on
go

use sybsecurity
go

if (db_name() != "sybsecurity")
begin
	/*
	** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
	*/
	raiserror 19529, "sybsecurity"
	select syb_quit()
end
go



if not exists (select * from sysobjects where name = "sysaudits_01")
begin
	declare @audit_disk varchar(250)

	/* get the name of the device on which sybsecurity is created */
	select @audit_disk = b.name from master..sysusages a, 
		master..sysdevices b where a.lstart = 0 and
		a.dbid = db_id ("sybsecurity") and 
		a.vdevno = b.vdevno and
		(b.status & 2) != 0

	execute sp_addsegment aud_seg_01, sybsecurity, @audit_disk
end
go


if not exists (select * from sysobjects where name = "sysaudits_01")
begin
	create table sysaudits_01 (
		event           smallint not null,
		eventmod        smallint not null,

		spid            smallint not null,

		eventtime       datetime not null,
		sequence        smallint not null,
		suid            int not null,
		dbid            smallint null,
		objid           int null,
		xactid          binary(6) null,
		loginname       varchar(30) null,
		dbname          varchar(30) null,
		objname         varchar(255) null,
		objowner        varchar(30) null,

		extrainfo       varchar(255) null,

		nodeid		tinyint null

		) lock allpages on aud_seg_01

end
go
 

grant select on sysaudits_01 to sso_role
go

if not exists (select * from sysobjects where name = "sysauditoptions")
begin
	create table sysauditoptions
	(	num		smallint not null,
		val		smallint not null,
		minval		smallint not null,
		maxval		smallint not null,
		name		varchar(30),
		sval		varchar(30),
		comment		varchar(255)
	) lock allpages
end
go

if not exists (select 1 from sysauditoptions where name = "login")
	insert sysauditoptions values (3, 0, 0, 3, "login", "off", 
		"Login auditing")

if not exists (select 1 from sysauditoptions where name = "logout")
	insert sysauditoptions values (4, 0, 0, 3, "logout", "off", 
		"Logout auditing")

if not exists (select 1 from sysauditoptions where name = "login_locked")
	insert sysauditoptions values (5, 0, 0, 3, "login_locked", "off", 
		"Login locked auditing")

if not exists (select 1 from sysauditoptions where name = "rpc")
	insert sysauditoptions values (6, 0, 0, 3, "rpc", "off", 
		"RPC connection audting")

if not exists (select 1 from sysauditoptions where name = "errors")
	insert sysauditoptions values (13, 0, 0, 3, "errors", "off", 
		"error auditing")

if not exists (select 1 from sysauditoptions where name = "adhoc")
	insert sysauditoptions values (14, 0, 0, 3, "adhoc", "off",
		"ad hoc auditing")

if not exists (select 1 from sysauditoptions where name = "sso_role")
	insert sysauditoptions values (22, 0, 0, 3, "sso_role", "off",
		"SSO role all-actions auditing")

if not exists (select 1 from sysauditoptions where name = "oper_role")
	insert sysauditoptions values (23, 0, 0, 3, "oper_role", "off",
		"OPER role all-actions auditing")

if not exists (select 1 from sysauditoptions where name = "sa_role")
	insert sysauditoptions values (24, 0, 0, 3, "sa_role", "off",
		"SA role all-actions auditing")

if not exists (select 1 from sysauditoptions where name = "sybase_ts_role")
	insert sysauditoptions values (25, 0, 0, 3, "sybase_ts_role", "off",
		"TS role all-actions auditing")

if not exists (select 1 from sysauditoptions where name = "navigator_role")
	insert sysauditoptions values (26, 0, 0, 3, "navigator_role", "off",
		"NAV role all-actions auditing")

if not exists (select 1 from sysauditoptions where name = "replication_role")
	insert sysauditoptions values (27, 0, 0, 3, "replication_role", "off",
		"REP role all-actions auditing")

if not exists (select 1 from sysauditoptions where name = "security")
	insert sysauditoptions values (28, 0, 0, 3, "security", "off",
		"security relevant auditing")

if not exists (select 1 from sysauditoptions where name = "disk")
	insert sysauditoptions values (29, 0, 0, 3, "disk", "off",
		"disk command auditing")

if not exists (select 1 from sysauditoptions where name = "dbcc")
	insert sysauditoptions values (31, 0, 0, 3, "dbcc", "off",
		"dbcc command auditing")

if not exists (select 1 from sysauditoptions where name = "dtm_tm_role")
	insert sysauditoptions values (32, 0, 0, 3, "dtm_tm_role", "off",
		"DTM_TM role all-actions auditing")

if not exists (select 1 from sysauditoptions where name = "ha_role")
	insert sysauditoptions values (33, 0, 0, 3, "ha_role", "off",
        	"HA role all-actions auditing")

if not exists (select 1 from sysauditoptions where name = "quiesce")
	insert sysauditoptions values (34, 0, 0, 3, "quiesce", "off",
		"quiesce command auditing")

if not exists (select 1 from sysauditoptions where name = "mount")
	insert sysauditoptions values (35, 0, 0, 3, "mount", "off",
		"mount command auditing")

if not exists (select 1 from sysauditoptions where name = "unmount")
	insert sysauditoptions values (36, 0, 0, 3, "unmount", "off",
		"unmount command auditing")

if not exists (select 1 from sysauditoptions where name = "mon_role")
	insert sysauditoptions values (37, 0, 0, 3, "mon_role", "off",
        	"MON role all-actions auditing")

if not exists (select 1 from sysauditoptions where name = "messaging_role")
	insert sysauditoptions values (38, 0, 0, 3, "messaging_role", "off",
        	"Messaging role all-actions auditing")

if not exists (select 1 from sysauditoptions where name = "js_admin_role")
	insert sysauditoptions values (39, 0, 0, 3, "js_admin_role", "off",
        	"JS_ADMIN role all-actions auditing")

if not exists (select 1 from sysauditoptions where name = "js_client_role")
	insert sysauditoptions values (40, 0, 0, 3, "js_client_role", "off",
        	"JS_CLIENT role all-actions auditing")

if not exists (select 1 from sysauditoptions where name = "js_user_role")
	insert sysauditoptions values (41, 0, 0, 3, "js_user_role", "off",
        	"JS_USER role all-actions auditing")

if not exists (select 1 from sysauditoptions where name = "webservices_role")
	insert sysauditoptions values (42, 0, 0, 3, "webservices_role", "off",
        	"WEBSERVICES role all-actions auditing")

if not exists (select 1 from sysauditoptions where name = "keycustodian_role")
	insert sysauditoptions values (43, 0, 0, 3, "keycustodian_role", "off",
        	"KEYCUSTODIAN role all-actions auditing")

if not exists (select 1 from sysauditoptions where name = "password")
	insert sysauditoptions values (44, 0, 0, 3, "password", "off",
        	"password related auditing")
		
if not exists (select 1 from sysauditoptions where name = "cluster")
	insert sysauditoptions values (45, 0, 0, 3, "cluster", "off",
        	"cluster admin command auditing")

if not exists (select 1 from sysauditoptions where name = "errorlog")		
	insert sysauditoptions values (46, 0, 0, 3, "errorlog", "off",
		"errorlog related auditing")



grant select on sysauditoptions to sso_role
go



use sybsystemprocs
go

if (db_name() != "sybsystemprocs")
begin
	/*
	** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
	*/
	raiserror 19529, "sybsystemprocs"
	select syb_quit()
end
go


set nocount off
go
dump transaction master with truncate_only
go
dump transaction sybsystemprocs with truncate_only
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_addauditrecord')
begin
	drop procedure sp_addauditrecord
end
go
print "Installing sp_addauditrecord"
go

/* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
/*	5.0	1.0	08/24/92	sproc/src/addauditrecord */

/*
**  ADDAUDITRECORD
**
**  This purpose of this procedure is to send an "ad-hoc" audit record to
**  sybsecurity..sysaudits.  An "ad-hoc" record is really just a structured
**  comment about anything, usually related to auditing but not necessarily so.
**  This procedure simply calls the ad_hoc_audit() builtin and receives
**  a return code indicating whether the record actually was sent to the
**  audit trail.  You may be wondering why this proc even exists, because it
**  seems as though the user can simply call ad_hoc_audit().  Well, that
**  builtin will check to see that the caller is the "sp_addauditrecord"
**  procedure in master.  This provides a means of DAC-protecting execute
**  permissions, whereas builtins do not provide this checking.
**
**  Parameters:
**	@text		message text to send
**	@db_name	database name to send
**	@obj_name	object name to send
**	@owner_name	owner name to send
**	@dbid		dbid to send
**	@objid		object id to send
**	@objseclab	object sensitivity lable ( B1 only )
**
**  Returns: 
**	 1  ad_hoc_audit() was never called (because auditing wasn't enabled)
**	 0  the audit record was sent to sysaudits
**	-1  an error occurred before calling ad_hoc_audit()
**
**  Side Effects:
**	May write a record to sysaudits
**
**  History:
**	08/24/92 (uno)	written
*/
create procedure sp_addauditrecord
@text varchar(255) = NULL,	/* message text */
@db_name varchar(255) = NULL,	/* database name */
@obj_name varchar(255) = NULL,	/* object name */
@owner_name varchar(255) = NULL,/* owner name */
@dbid int = NULL,		/* database id */
@objid int = NULL		/* object id */

as
declare	@returncode int		/* return from builtin */
declare	@eventnum int		/* event number for ad_hoc_auditing */
/* event type for ad_hoc_auditing which is always not aplicable i.e 0 */
declare	@mod int		

select	@eventnum = 1		
select	@mod = 0		

if ((@dbid is not NULL) and (@db_name is not NULL))
begin
	if (@dbid != db_id(@db_name))
	begin
		/*
		** 18968, "The database '%1!' does not have a database id of '%2!'."
		*/
		raiserror 18968, @db_name, @dbid
		return (1)
	end 
end

if ((@objid is not NULL) and (@obj_name is not NULL))
begin
	if (@objid != object_id(@obj_name))
	begin
		/*
		** 18969, "The object '%1!' does not have an object id of '%2!'."
		*/
		raiserror 18969, @obj_name, @objid
		return (1)
	end 
end


		
select @returncode =
	ad_hoc_audit (@eventnum, @mod, @text, @db_name, @obj_name, @owner_name, 
			@dbid, @objid

			)
return (@returncode)
go
exec sp_procxmode 'sp_addauditrecord', 'anymode'
go
grant execute on sp_addauditrecord to sso_role
go
dump transaction master with truncate_only
go
dump transaction sybsystemprocs with truncate_only
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_audit')
begin
	drop procedure sp_audit
end
go
print "Installing sp_audit"
go

/* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
/*	11.0	6.0	08/02/95	sproc/src/audit */

/*
**	Messages for sp_audit
**
** 18221, "Setting must be 'off', 'on', 'pass', 'fail'"
** 18222, "Object name must be 'all'"
** 18223, "User auditing for '%1!' is '%2!'"
** 18224, "'%1!' is not a valid login name or role name"
** 18225, "Login name must be 'all'"
** 18241, "'%1!' is not a valid database name"
** 18226, "'%1!' is not a valid login name"
** 18227, "'%1!' is not in the current database"
** 18228, "'%1!' is not a valid object name"
** 18229, "'%1!' is not a valid audit option"
** 18230, "Role auditing for '%1!' is '%2!'"
** 18231, "Server wide auditing '%1!' is '%2!'"
** 18232, "Default auditing '%1!' for '%2!' in database '%3!' is '%4!'"
** 18233, "Database specific auditing '%1!' in database '%2!' is '%3!'"
** 18234, "User specific auditing '%1!' for user '%2!' is '%3!'"
** 18235, "Object specific auditing '%1!' for object '%2!' is '%3!'"
** 18236, "Error updating the audit flags.  This is a system error.
**	   Contact an SA-role user."
** 18237, "Error with spt_values table. This is a system error. Contact 
**	   an SA-role user."
** 18926, "Audit process successfully restarted."
** 18927, "Invalid auditing configuration parameters."
** 18928, "Audit process is already being restarted."
** 18929, "Audit process has not terminated."
** 18930, "Cannot create the audit process. See error log for details."
** 18931, "Unknown error '%1!' occurred when trying to 
** 	   restart the audit process."
** 17260, "Can't run %1! from within a transaction."
** 17977, "'%1!' does not exist."
** 17981, "Error updating the audit flags in the system catalogs.  This is a
**	   system error.  Contact an SA-role user."
** 17982, "Audit option has been changed and has taken effect immediately."
** 17983, "Audit option has been changed and will take effect after a reboot."
*/

create procedure sp_audit
@option varchar(30) = NULL,		/* audit option name */
@login_name varchar(255) = NULL,		/* login name or role name */
@object_name varchar(767) = NULL,	/* object name or database name */
@setting varchar(30) = NULL		/* audit option setting */
as

declare @tmpstr varchar(767)		/* temporary string */
declare @msg varchar(1024)		/* for sp_getmessage */
declare @auditflags int			/* current audit option settings */
declare @updatecatalog int		/* system catalog to access */
declare @sysauditoptions int		/* sysauditoptions catalog */
declare @syslogins int			/* syslogins catalog */
declare @sysdatabases int		/* sysdatabases catalog */
declare @sysobjects int			/* sysobjects catalog in current database */
declare @settingflags int		/* bit settings converted from @setting */
declare @returncode int			/* return code from built-in audit_option() */
declare @optnum int			/* option number parameter for audit_option() */
declare @ao_dbref int			/* arg for audit_option to update database auditing */
declare @ao_dbref2 int			/* arg for audit_option to update new database audit options */
declare @ao_def_tab int			/* arg for audit_option to update default tables */
declare @ao_def_view int		/* arg for audit_option to update default views */
declare @ao_def_sproc int		/* arg for audit_option to update default procs/trig */
declare @ao_object int			/* arg for audit_option to update objects */
declare @ao_lgopts int			/* arg for audit_option to update user logins */
declare @ao_restart int			/* arg for audit_option to re-start audit process */
declare @spt_mask int			/* bit offset for audit option */
declare @spt_type char			/* major character for spt_values table */
declare @spt_setting char		/* minor character for valid settings */
declare @spt_user char			/* minor character for user specific auditing */
declare @spt_db char			/* minor character for database specific auditing */
declare @spt_obj char			/* minor character for object specific auditing */
declare @max_s_audit int		/* max pass auditable option */
declare @max_f_audit int		/* max fail auditable option */
declare @pass_mask int                  /* bit mask for pass audit option */
declare @fail_mask int                  /* bit mask for pass audit option */

/*
** Initialize values for spt_values table and system catalogs.
*/
select @spt_type = 'Z'
select @spt_setting = 's'
select @spt_user = 'u'
select @spt_db = 'd'
select @spt_obj = 'o'
select @sysauditoptions = 1
select @syslogins = 2
select @sysdatabases = 3
select @sysobjects = 4
select @ao_dbref = 200
select @ao_def_tab = 201
select @ao_def_view = 202
select @ao_def_sproc = 203
select @ao_object = 204
select @ao_lgopts = 205
select @ao_restart = 206
select @ao_dbref2 = 207
select @max_s_audit=hextoint("0x40000000")
select @max_f_audit=hextoint("0x80000000")

/*
**  If we're in a transaction, disallow this since it might make recovery
**  impossible.
*/
if (@@trancount > 0)
begin
	/* 17260, "Can't run %1 from within a transaction." */
	raiserror 17260, "sp_audit"
	return (1)
end
else
begin
	set transaction isolation level 1
	set chained off
end

/* check if user has sso role, proc_role will also do auditing
** if required. proc_role will also print error message if required.
*/
if (proc_role("sso_role") = 0)
	return (1)

set nocount on

/*
** Convert audit option to lower case with no leading/trailing blanks.
*/
if (@option is not NULL)
	select @option = lower(ltrim(rtrim(@option)))

/*
** Validate the parameters. The audit option, login_name and object_name
** are required fields for all but the 'restart' option. If less than
** 4 arguments have been specified, then print syntax with values for
** each argument. 
*/
if (@option = "restart")
begin
	if (@login_name is not NULL)
	begin
		print "Syntax: sp_audit restart"
		return (1)
	end
end
else
begin
	if (@object_name is NULL)
	begin
		print "Syntax: sp_audit option, login_name, object_name [, setting]"
		print "  option = {adhoc|all|alter|bcp|bind|cmdtext|create|dbaccess|dbcc|delete|disk|"
		print "            drop|dump|encryption_key|errors|exec_procedure|exec_trigger|func_obj_access|"
		print "            func_dbaccess|grant|insert|install|remove|load|"
		print "            login|login_locked|logout|reference|restart|revoke|rpc|security|select|cluster|"

		print "            setuser|table_access|truncate|unbind|update|view_access|quiesce|mount|unmount|"
		print "            password|errorlog|transfer_table}"
		print "  login_name = {all|<server_login_name>|<server_role_name>}"
		print "  object_name = {all|default{table|view|procedure|trigger}|<object_name>}"
		print "  setting = [off|on|pass|fail]"
		return (1)
	end

	/*
	** Convert login name only if it is "all".
	*/
	select @tmpstr = lower(ltrim(rtrim(@login_name)))
	if (@tmpstr = "all")
		select @login_name = @tmpstr

	/*
	** Convert object name only if it is "all", or "default %".
	*/
	select @tmpstr = lower(ltrim(rtrim(@object_name)))
	if (@tmpstr in ("all", "default table", "default view", 
			"default procedure", "default trigger"))
		select @object_name = @tmpstr

	/*
	** Convert setting to lower case with no leading/trailing blanks.
	*/
	if (@setting is not NULL)
		select @setting = lower(ltrim(rtrim(@setting)))

	/*
	** The setting must be valid
	*/
	if (@setting not in (NULL, "off", "on", "pass", "fail"))
	begin
		/* 18221, "Setting must be 'off', 'on', 'pass', 'fail'" */
		raiserror 18221
		return (1)
	end
end

/*
** Process auditing all actions of a user or role.
*/
if (@option = "all")
begin
	/*
	** Object name must be "all"
	*/
	if (@object_name != "all")
	begin
		/* 18222, "Object name must be 'all'" */
		raiserror 18222
		return (1)
	end
	/*
	** The login name must either be a valid loginname or a role name. If not
	** then return error message, otherwise set @updatecatalog to the appropriate
	** system catalog.
	*/
	if (suser_id(@login_name) is not NULL)
		select @updatecatalog = @syslogins
	else if (select count(*) from master..syssrvroles where name = @login_name) = 1
		select @updatecatalog = @sysauditoptions
	else
	begin
		/* 18224, "'%1!' is not a valid login name or role name" */
		raiserror 18224, @login_name
		return (1)
	end
end
/*
** Process server wide audit options.
*/
else if (@option in ("login", "logout", "login_locked", "adhoc", "disk", "errors", "rpc", "security", "dbcc", "quiesce", "mount", "unmount", "password", "cluster", "errorlog"

))
begin
	/*
	** Object name must be "all"
	*/
	if (@object_name != "all")
	begin
		/* 18222, "Object name must be 'all'" */
		raiserror 18222
		return (1)
	end
	/*
	** Login name must be "all"
	*/
	if (@login_name != "all")
	begin
		/* 18225, "Login name must be 'all'" */
		raiserror 18225
		return (1)
	end
	/*
	** For errors and adhoc server wide audit options, the only reasonable settings
	** are NULL, "off", "on". If the user specifies "pass" or "fail", we will set it
	** to "on".
	*/
	if (@option in ("errors", "adhoc", "login_locked")) and (@setting in ("pass", "fail"))
		select @setting = "on"
	select @updatecatalog = @sysauditoptions
end
/*
** Process database specific audit options.
*/
else if (@option in ("alter", "bcp", "bind", "create", "dbaccess", "drop",
	 "dump", "encryption_key",
	"func_dbaccess", "grant", "install", "remove", "load", 
	 "revoke", "setuser", "truncate", "unbind"))
begin
	/*
	** Login name must be "all"
	*/
	if (@login_name != "all")
	begin
		/* 18225, "Login name must be 'all'" */
		raiserror 18225
		return (1)
	end
	/*
	** Database name must exist.
	*/
	if (db_id(@object_name) is NULL)
	begin
		/* 18241, "'%1!' is not a valid database name" */
		raiserror 18241, @object_name
		return (1)
	end
	select @updatecatalog = @sysdatabases
end
/*
** Process user specific audit options.
*/
else if (@option in ("table_access", "view_access", "cmdtext"))
begin
	/*
	** Object name must be "all"
	*/
	if (@object_name != "all")
	begin
		/* 18222, "Object name must be 'all'" */
		raiserror 18222
		return (1)
	end
	/*
	** Login name must be a valid server user name.
	*/
	if (suser_id(@login_name) is NULL)
	begin
		/* 18226, "'%1!' is not a valid login name" */
		raiserror 18226, @login_name
		return (1)
	end
	/*
	** For cmdtext audit option, the only reasonable settings
	** are NULL, "off", "on". If the user specifies "pass" or "fail", 
	** we will set it to "on".
	*/
	if ((@option = "cmdtext") and (@setting in ("pass", "fail")))
		select @setting = "on"

	select @updatecatalog = @syslogins
end
/*
** Process object specific audit options.
*/
else if (@option in ("delete", "func_obj_access", "insert", "select", "update",
	 "exec_procedure", "exec_trigger", "reference","transfer_table"

	))
begin
	/*
	** Login name must be "all"
	*/
	if (@login_name != "all")
	begin
		/* 18225, "Login name must be 'all'" */
		raiserror 18225
		return (1)
	end
	if (@object_name like "%.%.%") and
        	(substring(@object_name, 1, charindex(".", @object_name) - 1) != db_name())
	begin
		/* 18227, "'%1!' is not in the current database" */
		raiserror 18227, @object_name
		return (1)
	end
	/*
	** Process table and view objects.
	*/
	if (@option in ("delete", "insert", "select", "update" 
		,"transfer_table"))
--	))
	begin
		/*
		** For default tables and views, set @updatacatalog to sysdatabases. Otherwise
		** find the specified table/view object in the current database. If found, set
		** @updatecatalog to sysobjects. If not found, then return error.
		*/
		if (@object_name in ("default table", "default view"))
			select @updatecatalog = @sysdatabases
		else if ((select count(*) from sysobjects where id = object_id(@object_name) and
			 type in ('S', 'U', 'V')) = 1)
			select @updatecatalog = @sysobjects
		else
		begin
			/* 18228, "'%1!' is not a valid object name" */
			raiserror 18228, @object_name
			return (1)
		end
	end
	else if (@option = "reference")
	begin
		/*
		** For default tables, set @updatacatalog to sysdatabases. Otherwise
		** find the specified table in the current database. If found, set
		** @updatecatalog to sysobjects. If not found, then return error.
		*/
		if (@object_name = "default table")
			select @updatecatalog = @sysdatabases
		else if ((select count(*) from sysobjects where id = object_id(@object_name) and
			 type in ('S', 'U')) = 1)
			select @updatecatalog = @sysobjects
		else
		begin
			/* 18228, "'%1!' is not a valid object name" */
			raiserror 18228, @object_name
			return (1)
		end
	end
	/*
	** Process procedure objects.
	*/
	else if (@option like "%exec_procedure")
	begin
		/*
		** For default procedures, set @updatecatalog to sysdatabases. Otherwise
		** find the specified procedure in the current database. If found, set
		** @updatecatalog to sysobjects. If not found, then return error.
		*/
		if (@object_name = "default procedure")
			select @updatecatalog = @sysdatabases
		else if ((select count (*) from sysobjects where id = object_id(@object_name) and
			 type in ('P', 'XP')) = 1)
			select @updatecatalog = @sysobjects
		else
		begin
			/* 18228, "'%1!' is not a valid object name" */
			raiserror 18228, @object_name
			return (1)
		end
	end
	/*
	** Process trigger objects.
	*/
	else if (@option like "%exec_trigger")
	begin
		/*
		** For default triggers, set @updatecatalog to sysdatabases. Otherwise
		** find the specified trigger in the current database. If found, set
		** @updatecatalog to sysobjects. If not found, then return error.
		*/
		if (@object_name = "default trigger")
			select @updatecatalog = @sysdatabases
		else if ((select count (*) from sysobjects where id = object_id(@object_name) and
			 (type = 'TR' or type = 'IT')) = 1)
			select @updatecatalog = @sysobjects
		else
		begin
			/* 18228, "'%1!' is not a valid object name" */
			raiserror 18228, @object_name
			return (1)
		end
	end
	/*
	** process access to any objects through builtin
	*/
	else if (@option = "func_obj_access")
	begin
		if ((select count (*) from sysobjects where id 
				= object_id(@object_name)) = 1)
			select @updatecatalog = @sysobjects
		else
		begin
			/* 18228, "'%1!' is not a valid object name" */
			raiserror 18228, @object_name
			return (1)
		end
	end

end
else if (@option = "restart")
begin
	/*
	** The 'restart' option is unlike most of the other auditing
	** options. Rather than turning a particular auditing option on
	** or off, it is used to re-start the audit process after it has
	** been abnormally terminated. 
	** 
	** Call the audit_option() builtin to re-start the audit process.
	*/
	select @returncode = audit_option(@ao_restart, 0, 0, 0)
	if (@returncode = 1)
	begin
		/* 18926, "Audit process successfully restarted." */
		exec sp_getmessage 18926, @msg output
		print @msg, @option, @tmpstr
		return (0)
	end
	else if (@returncode = -1)
		/* 18927, "Invalid auditing configuration parameters." */
		raiserror 18927
	else if (@returncode = -2)
		/* 18928, "Audit process is already being restarted." */
		raiserror 18928
	else if (@returncode = -3)
		/* 18929, "Audit process has not terminated." */
		raiserror 18929
	else if (@returncode = -4)
		/*
		** 18930, "Cannot create the audit process. See error
		** log for details."
		*/
		raiserror 18930
	else
		/*
		** 18931, "Unknown error '%1!' occurred when trying to 
		** restart the audit process."
		*/
		raiserror 18931, @returncode
	return (1)
end
/*
** Invalid audit option.
*/
else
begin
	/* 18229, "'%1!' is not a valid audit option" */
	raiserror 18229, @option
	return (1)
end

/*
** If setting an audit option, then convert the @setting parameter (varchar) to
** an integer. This will be used to set the appropriate bits in the audit flag.
*/
if (@setting is not NULL)
begin
	select @settingflags = number from master.dbo.spt_values
		where type = @spt_type+@spt_setting and name = @setting
	/*
	** Internal error if expected row in spt_values not found.
	*/
	if (@settingflags is NULL)
	begin
		/* 18237, "Error with spt_values table. This is a system error. Contact an SA-role user." */
		raiserror 18237
		return (1)
	end

end

/*
** Process access to sysauditoptions system catalog.
*/
if (@updatecatalog = @sysauditoptions)
begin
	/*
	** For auditing all actions of a role, set the @option to the role name,
	** otherwise the @option would already contain the audit option name.
	*/
	declare @role_aud int	/* 1 = auditing roles, 0 = server wide auditing */
	if (@option = "all")
	begin
		select @option = @login_name
		select @role_aud = 1
	end
	else
		select @role_aud = 0
	select @auditflags = val, @tmpstr = sval from sybsecurity.dbo.sysauditoptions
		where name = @option
	/*
	** Display the current setting role auditing or server wide auditing.
	*/
	if (@setting is NULL)
	begin
		if (@role_aud = 1)
		begin
			/* 18230, "Role auditing for '%1!' is '%2!'" */
			exec sp_getmessage 18230, @msg output
			print @msg, @option, @tmpstr
		end
		else
		begin
			/* 18231, "Server wide auditing '%1!' is '%2!'" */
			exec sp_getmessage 18231, @msg output
			print @msg, @option, @tmpstr
		end
		return (0)
	end
	/*
	** If the setting is "off", then clear the audit option bits corresponding to the specified
	** audit option, otherwise set the audit option bits.
	*/
	if (@setting = "off")
		select @auditflags = 0
	else
	begin
		select @auditflags = (@settingflags | @auditflags)
		if (@auditflags = 3)
			select @setting = "on"
	end
	/*
	** Update the current setting for the specified audit option. If an update error
	** occurred, then rollback the transaction.
	*/
	begin transaction
	update sybsecurity.dbo.sysauditoptions set val = @auditflags, sval = @setting
		where name = @option
	if (@@rowcount != 1)
	begin
		/*
		** 17981, "Error updating the audit flags in the system catalogs.  This
		**	   is a system error.  Contact an SA-role user."
		*/
		rollback transaction
		raiserror 17981
		return (1)
	end
	/*
	** Update audit option in server memory, save the return code.
	*/
	select @optnum = num from sybsecurity.dbo.sysauditoptions
		where name = @option
	select @returncode = audit_option(@optnum, @auditflags, 0, 0)
end
/*
** Process access to sysdatabases system catalog.
*/
else if (@updatecatalog = @sysdatabases)
begin
	/*
	** Retrieve the current audit flag from the appropriate audit field in
	** sysdatabases. For default auditing, retrieve from the current database.
	** For non-default auditing, retrieve from the specified database.
	*/
	if (@object_name = "default table")
		select @auditflags = deftabaud from master.dbo.sysdatabases
			where dbid = db_id()
	else if (@object_name = "default view")
		select @auditflags = defvwaud from master.dbo.sysdatabases
			where dbid = db_id()
	else if (@object_name in ("default procedure", "default trigger"))
		select @auditflags = defpraud from master.dbo.sysdatabases
			where dbid = db_id()
	else if (@option = "encryption_key") /* new db-wide audit options. */
		select @auditflags = deftabaud from master.dbo.sysdatabases
			where dbid = db_id(@object_name)
	else
		select @auditflags = audflags from master.dbo.sysdatabases
			where dbid = db_id(@object_name)
	/*
	** Set the @auditflags to 0 (if previously NULL), so that we can do bitwise AND
	** correctly. At server installation, the audflags in sysdatabases is NULL.
	*/
	if (@auditflags is NULL)
		select @auditflags = 0
	/*
	** Get the bit offset from spt_values table.
	*/
	if (@object_name in ("default table", "default view", "default procedure", "default trigger"))
		select @spt_mask = number from master.dbo.spt_values
			where name = @option and type = @spt_type+@spt_obj
	else
		select @spt_mask = number from master.dbo.spt_values
			where name = @option and type = @spt_type+@spt_db

	/*
	** Assign the pass and failed masks. To avoid arithmetic overflow, 
	** the highest option is hard coded.
	*/
	if (@spt_mask >= @max_s_audit)
		select @pass_mask = @max_s_audit, @fail_mask = @max_f_audit
	else
		select @pass_mask = @spt_mask, @fail_mask = @spt_mask * 2

	/*
	** If we are displaying the current setting, then mask off all bits except the bits
	** corresponding to the specified audit option, shift the audit option bits right by
	** doing a division operation (SQL does not have right shift operation), then mask off
	** all bits except the rightmost 2 bits to get the actual audit option bits. The resultant
	** integer is used as an index into spt_values table to retrieve the setting string.
	*/
	if (@setting is NULL)
	begin
		select @setting = name from master.dbo.spt_values
			where type = @spt_type+@spt_setting and number = ((@auditflags / @spt_mask) & 3)
		if (@object_name like "default %")
		begin
			/* 18232, "Default auditing '%1!' for '%2!' in database '%3!' is '%4!'" */
			select @tmpstr = db_name()
			exec sp_getmessage 18232, @msg output
			print @msg, @option, @object_name, @tmpstr, @setting
		end
		else
		begin
			/* 18233, "Database specific auditing '%1!' in database '%2!' is '%3!'" */
			exec sp_getmessage 18233, @msg output
			print @msg, @option, @object_name, @setting
		end
		return (0)
	end
	/*
	** If the setting is "off", then clear the audit option bits corresponding to the specified
	** audit option by masking out the 2 bits (pass, fail) indicated by the mask and leaving
	** the remaining bits unchanged. If the setting is not "off", then OR in the setting (pass,
	** fail, on) into the audit option bits of the auditflags.
	*/
	if (@setting = 'off')
		select @auditflags = ~(@pass_mask | @fail_mask) & @auditflags   
	else if (@setting = 'on')
		select @auditflags = @pass_mask | @fail_mask | @auditflags
	else if (@setting = 'pass')
		select @auditflags = @pass_mask | @auditflags
	else if (@setting = 'fail')
		select @auditflags = @fail_mask | @auditflags

	/*
	** Update the current setting for the specified audit option. If an update error
	** occurred, then rollback the transaction. For each field accessed, save the option
	** needed as an argument to the built-in audit_option().
	*/
	begin transaction
	if (@object_name = "default table")
	begin
		update master.dbo.sysdatabases set deftabaud = @auditflags
			where dbid = db_id()
		select @optnum = @ao_def_tab
	end
	else if (@object_name = "default view")
	begin
		update master.dbo.sysdatabases set defvwaud = @auditflags
			where dbid = db_id()
		select @optnum = @ao_def_view
	end
	else if (@object_name in ("default procedure", "default trigger"))
	begin
		update master.dbo.sysdatabases set defpraud = @auditflags
			where dbid = db_id()
		select @optnum = @ao_def_sproc
	end
	else if (@option = "encryption_key")
	begin
		/*
		** Additional database options stored in default table.
		*/
		update master.dbo.sysdatabases set deftabaud = @auditflags
			where dbid = db_id(@object_name)
		select @optnum = @ao_dbref2
	end
	else
	begin
		update master.dbo.sysdatabases set audflags = @auditflags
			where dbid = db_id(@object_name)
		select @optnum = @ao_dbref
	end
	if (@@rowcount != 1)
	begin
		/*
		** 17981, "Error updating the audit flags in the system catalogs.  This
		**	   is a system error.  Contact an SA-role user."
		*/
		rollback transaction
		raiserror 17981
		return (1)
	end
	/*
	** Update audit option in server memory, save the return code.
	*/
	if (@object_name in ("default table", "default view", "default procedure", "default trigger"))
		select @returncode = audit_option(@optnum, @auditflags, 0, db_id())
	else
		select @returncode = audit_option(@optnum, @auditflags, 0, db_id(@object_name))
end
/*
** Process user specific audit options.
*/
else if (@updatecatalog = @syslogins)
begin
	/*
	** Retrieve the current audit flag from the row corresponding to the user name
	** in syslogins system catalog.
	*/
	select @auditflags = audflags from master.dbo.syslogins
		where suid = suser_id(@login_name)
	/*
	** Set the @auditflags to 0 (if previously NULL), so that we can do bitwise AND
	** correctly. At server installation, the audflags in syslogins is NULL.
	*/
	if (@auditflags is NULL)
		select @auditflags = 0
	/*
	** Get the bit offset from spt_values table.
	*/
	select @spt_mask = number from master.dbo.spt_values
		where name = @option and type = @spt_type+@spt_user

	/*
	** Assign the pass and failed masks. To avoid arithmetic overflow, 
	** the highest option is hard coded.
	*/
	if (@spt_mask >= @max_s_audit)
		select @pass_mask = @max_s_audit, @fail_mask = @max_f_audit
	else
		select @pass_mask = @spt_mask, @fail_mask = @spt_mask * 2

	/*
	** If we are displaying the current setting, then mask off all bits except the bits
	** corresponding to the specified audit option, shift the audit option bits right by
	** doing a division operation (SQL does not have right shift operation), then mask off
	** all bits except the rightmost 2 bits to get the actual audit option bits. The resultant
	** integer is used as an index into spt_values table to retrieve the setting string.
	*/
	if (@setting is NULL)
	begin
		select @setting = name from master.dbo.spt_values
			where type = @spt_type+@spt_setting and number = ((@auditflags / @spt_mask) & 3)
		/* 18234, "User specific auditing '%1!' for user '%2!' is '%3!'" */
		exec sp_getmessage 18234, @msg output
		print @msg, @option, @login_name, @setting
		return (0)
	end
	/*
	** If the setting is "off", then clear the audit option bits corresponding to the specified
	** audit option by masking out the 2 bits (pass, fail) indicated by the mask and leaving
	** the remaining bits unchanged. If the setting is not "off", then OR in the setting (pass,
	** fail, on) into the audit option bits of the auditflags.
	*/
	if (@setting = 'off')
		select @auditflags = ~(@pass_mask | @fail_mask) & @auditflags   
	else if (@setting = 'on')
		select @auditflags = @pass_mask | @fail_mask | @auditflags
	else if (@setting = 'pass')
		select @auditflags = @pass_mask | @auditflags
	else if (@setting = 'fail')
		select @auditflags = @fail_mask | @auditflags

	/*
	** Update the current setting for the specified audit option. If an update error
	** occurred, then rollback the transaction.
	*/
	begin transaction
	update master.dbo.syslogins set audflags = @auditflags
		where suid = suser_id(@login_name)
	if (@@rowcount != 1)
	begin
		/*
		** 17981, "Error updating the audit flags in the system catalogs.  This
		**	   is a system error.  Contact an SA-role user."
		*/
		rollback transaction
		raiserror 17981
		return (1)
	end
	/*
	** Update audit option in server memory, save the return code.
	*/
	select @returncode = audit_option(@ao_lgopts, @auditflags, suser_id(@login_name), 0)
end
/*
** Process object specific audit options.
*/
else if (@updatecatalog = @sysobjects)
begin
	/*
	** Retrieve the current audit flag from the row corresponding to the specified
	** object in sysobjects system catalog.
	*/
	select @auditflags = audflags from sysobjects
		where id = object_id(@object_name)
	/*
	** Set the @auditflags to 0 (if previously NULL), so that we can do bitwise AND
	** correctly. At server installation, the audflags in syslogins is NULL.
	*/
	if (@auditflags is NULL)
		select @auditflags = 0
	/*
	** Get the bit offset from spt_values table.
	*/
	select @spt_mask = number from master.dbo.spt_values
		where name = @option and type = @spt_type+@spt_obj

	/*
	** Assign the pass and failed masks. To avoid arithmetic overflow, 
	** the highest option is hard coded.
	*/
	if (@spt_mask >= @max_s_audit)
		select @pass_mask = @max_s_audit, @fail_mask = @max_f_audit
	else
		select @pass_mask = @spt_mask, @fail_mask = @spt_mask * 2

	/*
	** If we are displaying the current setting, then mask off all bits except the bits
	** corresponding to the specified audit option, shift the audit option bits right by
	** doing a division operation (SQL does not have right shift operation), then mask off
	** all bits except the rightmost 2 bits to get the actual audit option bits. The resultant
	** integer is used as an index into spt_values table to retrieve the setting string.
	*/
	if (@setting is NULL)
	begin
		select @setting = name from master.dbo.spt_values
			where type = @spt_type+@spt_setting and number = ((@auditflags / @spt_mask) & 3)
		/* 18235, "Object specific auditing '%1!' for object '%2!' is '%3!'" */
		exec sp_getmessage 18235, @msg output
		print @msg, @option, @object_name, @setting
		return (0)
	end
	/*
	** If the setting is "off", then clear the audit option bits corresponding to the specified
	** audit option by masking out the 2 bits (pass, fail) indicated by the mask and leaving
	** the remaining bits unchanged. If the setting is not "off", then OR in the setting (pass,
	** fail, on) into the audit option bits of the auditflags.
	*/
	if (@setting = 'off')
		select @auditflags = ~(@pass_mask | @fail_mask) & @auditflags   
	else if (@setting = 'on')
		select @auditflags = @pass_mask | @fail_mask | @auditflags
	else if (@setting = 'pass')
		select @auditflags = @pass_mask | @auditflags
	else if (@setting = 'fail')
		select @auditflags = @fail_mask | @auditflags

	/*
	** Update the current setting for the specified audit option. If an update error
	** occurred, then rollback the transaction.
	*/
	begin transaction
	update sysobjects set audflags = @auditflags
		where id = object_id(@object_name)
	if (@@rowcount != 1)
	begin
		/*
		** 17981, "Error updating the audit flags in the system catalogs.  This
		**	   is a system error.  Contact an SA-role user."
		*/
		rollback transaction
		raiserror 17981
		return (1)
	end
	/*
	** Update audit option in server memory, save the return code.
	*/
	select @returncode = audit_option(@ao_object, @auditflags, object_id(@object_name), db_id())
end

/*
** Check the return code from call to built-in audit_option(). If successful, then
** commit the transaction, else rollback the transaction.
*/
if (@returncode = 1) or (@returncode = 0)
	commit transaction
else
	rollback transaction
/*
** For returncode = 1, inform user that audit option has taken effect immediately.
*/
if (@returncode = 1)
begin
	/*
	** 17982 "Audit option has been changed and has taken effect immediately."
	*/
        exec sp_getmessage 17982, @msg output
	print @msg
        return (0)
end
/*
** For return code - 0, inform user that audit option will take effect after reboot.
*/
else if (@returncode = 0)
begin
	/*
	** 17983 "Audit option has been changed and will take effect after a reboot."
	*/
        exec sp_getmessage 17983, @msg output
	print @msg
        return (0)
end
/*
** Error with updating audit options in server memory.
*/
else
begin
	/* 18236, "Error updating the audit flags. This is a system error. */
	/*         Contact an SA-role user." */
	raiserror 18236
	return (1)
end
go
exec sp_procxmode 'sp_audit', 'anymode'
go
grant execute on sp_audit to sso_role
go
dump transaction master with truncate_only
go
dump transaction sybsystemprocs with truncate_only
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_aux_getsptaud')
begin
	drop procedure sp_aux_getsptaud
end
go
print "Installing sp_aux_getsptaud"
go


/*
** Auxilliary stored procedure for getting an audit setting 
**
** Input Parameters:
**	input_id 		dbid | suid | objid, depending on flag_type
**	flag_type 		which audit flag and spt type to examine
**	cmdstr 			name of audited command name 
**	setting - return	audit option setting string from spt_values 
** 
** Output parameters:
**	none
** Returns:
**	0		everything went ok
**	1		general error
*/

create procedure sp_aux_getsptaud
@input_id int = NULL,		/* dbid | suid | objid */
@flag_type varchar(10),		/* which audit flag to get */ 
@cmdstr varchar(90),		/* command name */
@setting_str varchar(30) output	/* audit option setting string */
as

declare @setting_num int	/* for calculating numeric value of setting */
declare @succ_mask int		/* bit offset for success audit option */
declare @fail_mask int		/* bit offset for failure audit option */
declare @succ int		
declare @fail int		
declare @auditflags int
declare @spt_type varchar(2)	/* type of command; for a safety check */
declare @spt_setting varchar(2)	
declare @spt_obj varchar(2)	
declare @spt_db varchar(2)	
declare @spt_user varchar(2)	
declare @max_s_audit int                /* max pass auditable option */
declare @max_f_audit int                /* max fail auditable option */

select @succ = 0
select @fail = 0
select @spt_setting = "Zs"
select @spt_obj = "Zo"
select @spt_db = "Zd"
select @spt_user = "Zu"
select @max_s_audit=hextoint("0x40000000")
select @max_f_audit=hextoint("0x80000000")

/* 
** check if user has sso role, proc_role will also do auditing
** if required. proc_role will also print error message if required.
*/

if (proc_role("sso_role") = 0)
	return (1)

/*
** get audit flags 
*/

if (@flag_type = "defpraud")
begin
	select @auditflags = defpraud from master.dbo.sysdatabases 
		where dbid = @input_id
	select @spt_type = @spt_obj
end
else if (@flag_type = "audflags")
begin
	select @auditflags = audflags from sysobjects 
		where id =  @input_id
	select @spt_type = @spt_obj
end
else if (@flag_type = "laudflags")
begin
	select @auditflags = audflags from master.dbo.syslogins 
		where suid =  @input_id
	select @spt_type = @spt_user
end
else if (@flag_type = "deftabaud")
begin
	select @auditflags = deftabaud from master.dbo.sysdatabases 
		where dbid = @input_id
	select @spt_type = @spt_obj
end
else if (@flag_type = "defvwaud")
begin
	select @auditflags = defvwaud from master.dbo.sysdatabases 
		where dbid = @input_id
	select @spt_type = @spt_obj
end
else if (@flag_type = "daudflags")
begin
	select @auditflags = audflags from master.dbo.sysdatabases 
		where dbid = @input_id
	select @spt_type = @spt_db
end
else if (@flag_type = "daudflags2")
begin
	select @auditflags = deftabaud from master.dbo.sysdatabases 
		where dbid = @input_id
	select @spt_type = @spt_db
end


else 
begin
	/* 
	** 18557,  "The parameter '%1!' is invalid. Enter a valid parameter, 
	** and run sp_aux_getsptaud again."
	*/ 
	raiserror 18557, @flag_type
	return (1)
end

/* 
** get the success bit number for the command the user requested
*/
select @succ_mask = number from master.dbo.spt_values
	where name = @cmdstr and type = @spt_type

if @succ_mask is null 
begin
	/* 
	** 18557,  "The parameter '%1!' is invalid. Enter a valid parameter, 
	** and run sp_aux_getsptaud again."
	*/ 
	raiserror 18557, @cmdstr
	return (1)
end
/*
** get failure bit, the next one to the left 
*/

if (@succ_mask < @max_s_audit)
	select @fail_mask = (2 * @succ_mask)
else
	select @fail_mask = @max_f_audit

/*
** find out if success or failure are being audited
*/

if ((@succ_mask & @auditflags) != 0)
	select @succ = 1
if ((@fail_mask & @auditflags) != 0)
	select @fail = 2

/*
** combine succ and fail: range = 0..3
*/

select @setting_num = @succ + @fail

/*
** return the string value for the setting from spt_values
*/

select @setting_str = name from master.dbo.spt_values
	where number = @setting_num and type = @spt_setting

return (0)
go
exec sp_procxmode 'sp_aux_getsptaud', 'anymode'
go
grant execute on sp_aux_getsptaud to sso_role
go
dump transaction master with truncate_only
go
dump transaction sybsystemprocs with truncate_only
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_aux_displaylogaud')
begin
	drop procedure sp_aux_displaylogaud
end
go
print "Installing sp_aux_displaylogaud"
go


/*
** Auxilliary stored procedure to display login auditing options 
**
** If the loginame parameter is NULL, the settings for all logins are 
** displayed
**
** Input Parameters:
**	loginame		login name name (NULL if none specified)
** 
** Output parameters:
**	none
** Returns:
**	0		everything went ok
**	1		general error
*/

create procedure sp_aux_displaylogaud
@loginame varchar(30) = NULL	/* login name of the login */
as

declare @all_logins int		/* flag for all logins */
declare @suid int		
declare @msg varchar(1024)
declare @optstr varchar(90)
declare @setting varchar(30)    /* audit option setting */
declare @flag_type varchar(10) 
declare @spt_user varchar(2)	

select @spt_user = "Zu"
select @all_logins = 0

/* 
** check if user has sso role, proc_role will also do auditing
** if required. proc_role will also print error message if required.
*/

if (proc_role("sso_role") = 0)
	return (1)

/*  Don't display rowcount.  */
set nocount on

/*
** If no login name provided, we'll get the auditing options for all
** logins currently in master.dbo.syslogins that have auditing enabled.
** So, we'll have to set up a loop and step through syslogins 1 row at
** a time in alphabetical order by name.  Start by getting the lowest
** alphabetical name.
*/
if @loginame is NULL
begin
	/*
	** First make sure there's at least one login with auditing
	** enabled.  If not, just print a message and return.
	*/
	if (select count(name) from master.dbo.syslogins
	    where audflags > 0) = 0
	begin
		/* 17991, "No logins currently have auditing enabled. */
		exec sp_getmessage 17991, @msg output
		print @msg
		return (0)
	end

	select @all_logins = 1
        declare ln_cursor cursor for select name from master.dbo.syslogins
                where audflags > 0
        open ln_cursor
        fetch ln_cursor into @loginame	
end
else
if (suser_id(@loginame) is NULL)
begin
	/* 17977, '%1!' does not exist." */
	raiserror 17977, @loginame
	return (1)
end

/*
** create temp table for output
*/
 
create table #output (lname varchar(31), OPname varchar(14), setting varchar(4))

/*
** select which audit flag to look at 
*/

select @flag_type = "laudflags"

/*
** set up cursor for looking at all user options
*/
declare opt_cursor cursor for select name from master.dbo.spt_values
        where type = @spt_user

/*
** Begin the loop.  Break if a login name was provided, or we've run
** out of logins with auditing enabled.
*/
while (1 = 1)
begin
	select @suid = suser_id(@loginame)

        /*
        ** set up option cursor
        */
        open opt_cursor
        fetch opt_cursor into @optstr
 
        /*
        ** get setting for each audit option 
        */
        while (@@sqlstatus != 2)
        begin
                exec sp_aux_getsptaud @suid, @flag_type, @optstr,
                        @setting output
                if @setting != "off"
			insert #output values (@loginame, @optstr, @setting) 
                fetch opt_cursor into @optstr
        end
        close opt_cursor
 
        /*
        ** get next login name
        */
        if @all_logins = 1
        begin
                fetch ln_cursor into @loginame
                if @@sqlstatus = 2
                        break
        end
        else /* @all_logins = 0 */
                break
end

/*
** print output table
*/
 
select "Login Name" = lname, "Audit Option" = OPname,
	"Value" = setting
	from #output
	order by lname

return (0)
go
exec sp_procxmode 'sp_aux_displaylogaud', 'anymode'
go
grant execute on sp_aux_displaylogaud to sso_role
go
dump transaction master with truncate_only
go
dump transaction sybsystemprocs with truncate_only
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_aux_displayobjaud')
begin
	drop procedure sp_aux_displayobjaud
end
go
print "Installing sp_aux_displayobjaud"
go


/*
** Auxilliary stored procedure for displaying object audit settings. 
** This is a multipurpose procedure.  If arg1 is "default" this proc is
** used to report on the default table/view settings in all DBs or in a
** specific DB designated by name.  If arg1 is not "default", this proc
** is used to report on audit settings for all tables/views in the
** current database if name is empty or of a specific sproc/trigger if
** name is not empty.
**
** Input Parameters:
**	arg1 		table or view name, or "default" 
**	name 		dbname - ignored if arg1 is not "default" 
** 
** Output parameters:
**	none
** Returns:
**	0		everything went ok
**	1		general error
*/

create procedure sp_aux_displayobjaud
@arg1 varchar(1023) = NULL,	/* <table/view name> | default */
@name varchar(255) = NULL	/* <dbname> ignored if arg1 is not "default" */

as
declare @objname varchar(1023)		/* target object */
declare @dbname varchar(255)		/* dbname for default auditing */
declare @dbid int
declare @objid int		/* object id of table or view    */
declare @i int
declare @type char		/* 'S'=systable; 'U'=user table; 'V'=view  */
declare @owner varchar(30)	/* object owner's name */
declare @default_table_view int	/* 0=no default; 1=default table/view */
declare @all int		/* flag for displaying all db's or obj's */
declare @tmpstr varchar(1023)	/* for various string usage */
declare @msg varchar(1024)
declare @optstr varchar(90)
declare @setting varchar(30)    /* audit option setting */
declare @defvwstr varchar(30)
declare @deftabstr varchar(30)
declare @flag_type varchar(10) 
declare @userdbname varchar(30)
declare @spt_obj varchar(2) 	

declare	@q_dbname varchar(30)	/* user's db-qualification of object name */  
declare @site  varchar(30) 
declare @tabname varchar(255) 
declare @retcode int

/*
** Initialize values 
*/
select @spt_obj = "Zo"
select @deftabstr = "DEFAULT TABLE"
select @defvwstr = "DEFAULT VIEW"

/* 
** check if user has sso role, proc_role will also do auditing
** if required. proc_role will also print error message if required.
*/

if (proc_role("sso_role") = 0)
	return (1)

/* Don't display rowcount. */
set nocount on

/* Set up the local variables based on what we find in the arglist. */

select @tmpstr = lower(ltrim(rtrim(@arg1)))
select @dbname = @name
if @tmpstr = "default"
begin
	select @default_table_view = 1
	select @tmpstr = "default table/view"
end
else /* @tmpstr is not "default" */
begin
	select @default_table_view = 0
	/*
	** Load the @objname variable and load the @owner variable
	*/
	select @objname = @arg1
	select @owner = NULL
	if @objname is not NULL
	begin
		exec @retcode = sp_namecrack @objname, @site output, 
					     @q_dbname output, @owner output, 
					     @tabname output
		/*
		** if you qualified the name, it needs to be this db.
		*/
		if ((@q_dbname is not null) and (@q_dbname != db_name()) or
			(@site is not null) and (@site != @@servername))
		begin
			/* 18227, "'%1!' is not in the current database" */
			raiserror 18227, @objname
			return (1)
		end

		select @objid = object_id(@objname)
 
		/*
		** an object of this type must exist 
		*/
		if (@objid is NULL) or
		   ((select count(*) from sysobjects 
			where (id = @objid and 
			type in ('U', 'V', 'S'))) = 0)
		begin
			/* 17461, "Object does not exist in this database."  */
			raiserror 17461
			return (1)
		end
 
		/*
		** Construct objname so the output is uniform. 
		** For input of form (owner.name), this is redundant, but it
		** covers all of the other combinations.
		*/
		if (@owner is NULL)
		begin
			select @owner = user_name(uid) from sysobjects
				where id = @objid 
		end
		select @objname = @owner + '.' + @tabname

	end /* if @objname is not NULL */
end /* else @tmpstr is not like "default%" */

/*
** create temp table for output
** Note that we use this table for outputting defaults as well as the
** non-default option settings; if defaults, then oname is "DEFAULT"
*/
/* oname could be site.db.owner.objname, the max length is 30*3+255 +3 */ 
create table #output (oname varchar(348), OPname varchar(14), 
		setting varchar(4), db varchar(30))

/*
** The rest of this proc will be divided into the two cases of whether this
** is "default <object>" processing or regular object processing.  Basically,
** the main difference is that default processing will operate on master.dbo.
** sysdatabases, while regular object processing will operate on curdb.dbo.
** sysobjects.  We'll start with the default object processing case.
**
**	{ "default table" | "default view" }, <dbname>,
*/
if @default_table_view =1
begin
	/*
	** If no arg provided after the "default <object>", display the names
	** and default auditing options of all databases that have an "active"
	** default setting.
	**
	** If the @dbname was provided, display the default auditing status
	** for that database (whether it's "active" or not)
	*/
	select @all = 0

	if @dbname is NULL
	begin
		/*
		** First make sure there's at least one database with
		** an active default. If not, just print a message and
		** return.
		*/
		if ((select count(name) from master.dbo.sysdatabases
		     where ((deftabaud != 0) or (defvwaud > 0))) = 0)
		begin
			/*
			** 17998, "No databases currently have %1!
			**	   auditing enabled.
			*/
			exec sp_getmessage 17998, @msg output
			print @msg, @tmpstr
			return (0)
		end
		select @all = 1
	        /*
	        ** set up the cursor to look through sysdatabases
	        */
	        declare db_cursor cursor for select name 
			from master.dbo.sysdatabases
			where ((deftabaud != 0) or (defvwaud > 0))
	        open db_cursor
	        fetch db_cursor into @dbname
	end
	else
	if (db_id(@dbname) is NULL)
	begin
		/* 17977, "'%1!' does not exist." */
		raiserror 17977, @dbname
		return (1)
	end

	/*
	** set up cursor for looking at all object options
	*/
	declare opt_cursor cursor for select name from master.dbo.spt_values
        	where (type = @spt_obj and name != "func_obj_access"
		       and name not like "%exec%")

	/*
	** Begin the loop.  Break if a dbname name was provided, or
	** we've run out of databases with active defaults.
	*/
	while (1 = 1)
	begin
		select @dbid = db_id(@dbname) 

	        /*
	        ** set up option cursor
	        */
	        open opt_cursor
	        fetch opt_cursor into @optstr
	 
	        /*
	        ** get setting for each audit option
	        */
	        while (@@sqlstatus != 2)
	        begin
	        	/*
	        	** first get table defaults
	        	*/
			select @flag_type = "deftabaud"
	                exec sp_aux_getsptaud @dbid, @flag_type, @optstr,
	                        @setting output
	                if @setting != "off"
				insert #output values (@deftabstr, @optstr, 
						       @setting, @dbname)
	        	/*
	        	** then get view defaults
	        	*/
			select @flag_type = "defvwaud"
	                exec sp_aux_getsptaud @dbid, @flag_type, @optstr,
	                        @setting output
	                if @setting != "off"
				insert #output values (@defvwstr, @optstr, 
						       @setting, @dbname)
	
	                fetch opt_cursor into @optstr
	        end
	        close opt_cursor

	        /*
	        ** get next dbname
	        */
	        if @all = 1
	        begin
	                fetch db_cursor into @dbname
	                if @@sqlstatus = 2
	                        break
	        end
	        else /* @all = 0 */
	                break

	end	/* while (1 = 1) */
end	/* if @default_table_view = 1 */
else
/*
** For regular, non-default object auditing...
** Check for and process 'table' and 'view' auditing
** <tablename | viewname>
*/
begin
	select @all = 0

	select @flag_type = "audflags"

	/*
	** If no @objname provided, we'll get the auditing options for
	** all tables and views currently in curdb.dbo.sysobjects that
	** have some auditing enabled on them.  So, we'll have to set
	** up a loop and step through sysobjects 1 row at a time 
	**
	** If @objname was provided, display its current auditing
	** status.
	*/
	if @objname is NULL
	begin
		/*
		** If there aren't any tables/views that have auditing
		** enabled, just print a message and return.
		*/
		if (select count(name) from sysobjects
		    where type in ('S','U','V') and audflags > 0) = 0
		begin
			/*
			** 18000, "No objects currently have auditing
			**	   enabled.
			*/
			exec sp_getmessage 18000, @msg output
			print @msg
			return (0)
		end

        	/*
        	** set up the cursor to look through sysobjects
        	*/
        	declare obj_cursor cursor for select id from sysobjects
                	where audflags > 0 and type in ('S', 'U', 'V')
        	open obj_cursor
        	fetch obj_cursor into @objid

		select @objname = user_name(uid) + '.' + name from
			sysobjects where id = @objid
		select @all = 1
	end
	/*
	** get the name of the current database for the output
	*/
	select @userdbname = db_name()

	/*
	** set up cursor for looking at all options
	*/
	declare opt_cursor cursor for select name from master.dbo.spt_values
        	where (type = @spt_obj and name not like "%exec%")

	/*
	** Begin the loop.  Break if an objname was provided, or we've
	** run out of objects with auditing enabled.
	*/
	while (1 = 1)
	begin
	        /*
	        ** get first option from cursor
	        */
	        open opt_cursor
	        fetch opt_cursor into @optstr
	 
	        /*
	        ** get setting for each audit option
	        */
	        while (@@sqlstatus != 2)
	        begin
	                exec sp_aux_getsptaud @objid, @flag_type, @optstr,
	                        @setting output
	                if @setting != "off"
	                        insert #output values (@objname, @optstr, 
						       @setting, @userdbname)
	                fetch opt_cursor into @optstr
	        end
	        close opt_cursor
	
	        /*
	        ** get next object id
	        */
	        if @all = 1
	        begin
	                fetch obj_cursor into @objid
	                if @@sqlstatus = 2
	                        break

			select @objname = user_name(uid) + '.' + name from
				sysobjects where id = @objid
	        end
	        else /* @all = 0 */
	                break

	end	/* while (1 = 1) */
end

if (select count(*) from #output) = 0
begin
	/*
	** 17998, "No databases currently have %1!
	**	   auditing enabled.
	*/
	exec sp_getmessage 17998, @msg output
	print @msg, @tmpstr
	return (0)
end
/*
** print output table
*/
 
exec sp_autoformat @fulltabname = #output,
	@selectlist = "'Table/View' = oname, 'Audit Option' = OPname, 'Value' = setting, 'Database' = db",
	@orderby = "order by oname"
 
/* We're done reporting, so good bye. */
return (0)

go
exec sp_procxmode 'sp_aux_displayobjaud', 'anymode'
go
grant execute on sp_aux_displayobjaud to sso_role
go
dump transaction master with truncate_only
go
dump transaction sybsystemprocs with truncate_only
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_aux_displayoptaud')
begin
	drop procedure sp_aux_displayoptaud
end
go
print "Installing sp_aux_displayoptaud"
go

/*
** Auxilliary stored procedure for displaying global audit settings
**
** If the opkeywd parameter is NULL, the settings for all global options are
** displayed
**
** Input Parameters:
**	opkeywd		option name (NULL if none specified)
** 
** Output parameters:
**	none
** Returns:
**	0		everything went ok
**	1		general error
*/

create procedure sp_aux_displayoptaud
@opkeywd  varchar(30)  = NULL		/* audit option name  */

as
declare @optcnt		int		/* number of matching options */
declare @msg		varchar(1024)	/* for sp_getmessage */

/* check if user has sso role, proc_role will also do auditing
** if required. proc_role will also print error message if required.
*/

if (proc_role("sso_role") = 0)
	return (1)

/* Don't display rowcount. */
set nocount on

/* Convert the args to lower case with no leading/trailing blanks. */
if @opkeywd is not NULL
	select @opkeywd = lower(ltrim(rtrim(@opkeywd)))

/*
** If no arguments, then display all the current settings.
*/
if @opkeywd is NULL 
begin
	/*
	** Exclude sybase_ts commands (number 25) from the output,
	** since the whole notion of sybase_ts is undocumented.
	*/
	select "Option Name" = name, "Value" = sval 
		from sybsecurity.dbo.sysauditoptions
		where num != 25 
		order by name
	return (0)
end

/*  Check if it is a valid unique name...  */
set nocount on  /* Do not display number of rows affected */

select @optcnt = count(*) from sybsecurity.dbo.sysauditoptions
	where name like "%" + @opkeywd + "%"

/*  If the name is not valid, then display all options.  */
if @optcnt = 0 
begin
	/* 18003, "Audit option '%1!' does not exist. Valid options are:" */
	raiserror 18003, @opkeywd
	select name from sybsecurity.dbo.sysauditoptions where num != 25
	return (1)
end

/* If option string is ambiguous, display the ambiguous ones. */
else if @optcnt > 1
begin
	/* 18004, "Audit option %1! is ambiguous.  Ambiguous options are:" */
	raiserror 18004, @opkeywd
	select name from sybsecurity.dbo.sysauditoptions 
		where name like "%" + @opkeywd + "%" and num != 25
	return (1)
end 
else /* @optcnt = 1 */ 

	/*  the name is given, display only it's setting. */
	select "Audit Option" = name, "Value" = sval 
		from sybsecurity.dbo.sysauditoptions
		where name like "%" + @opkeywd + "%"
return (0)

go
exec sp_procxmode 'sp_aux_displayoptaud', 'anymode'
go
grant execute on sp_aux_displayoptaud to sso_role
go
dump transaction master with truncate_only
go
dump transaction sybsystemprocs with truncate_only
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_aux_displaysprocaud')
begin
	drop procedure sp_aux_displaysprocaud
end
go
print "Installing sp_aux_displaysprocaud"
go

/*
** Auxilliary stored procedure for displaying stored procedure audit settings.
** This is a multipurpose procedure.  If arg1 is "default*" this proc is
** used to report on the default sproc/trigger settings in all DBs or in a
** specific DB designated by name.  If arg1 is not "default*", this proc
** is used to report on audit settings for all sproc/triggers in the
** current database if name is empty or of a specific sproc/trigger if
** name is not empty.
**
** If the database parameter is NULL, the settings for all databases are 
** displayed
**
** Input Parameters:
**	arg1 		table or view name, or "default" 
**	name 		dbname - ignored if arg1 is not "default" 
** 
** Output parameters:
**	none
** Returns:
**	0		everything went ok
**	1		general error
*/


create procedure sp_aux_displaysprocaud
@arg1 varchar(1023) = NULL,	/* <sproc/trigger name> | default */
@name varchar(255) = NULL	/* <dbname> ignored if arg1 is not "default" */

as
declare @default int		/* "default" flag */
declare @dbname varchar(255)
declare @userdbname varchar(30)
declare @owner varchar(30)
declare @procname varchar(1023)
declare @objid int
declare @dbid int
declare @all int		/* indicates that dbname/sproc_name was not 
				** given, so we are reporting on all db's
				** or sproc/triggers
				*/
declare @i int
declare @tmpstr varchar(1023)	/* owner.object and other uses */
declare @msg varchar(1024)	/* for sp_getmessage */
declare @optstr varchar(90)	
declare @troptstr varchar(90)	
declare @setting varchar(30)	/* audit option setting */
declare @ptype varchar(2) 
declare @deftstr varchar(30)	
declare @flag_type varchar(10)
declare @spt_obj varchar(2)
declare @q_dbname varchar(30)   /* user's db-qualification of object name */
declare @site  varchar(30)
declare @q_pname varchar(255)	/* user's qualified object name */
declare @retcode int
 
/*
** Initialize values
*/
select @spt_obj = "Zo"
select @deftstr = "DEFAULT"

/* check if user has sso role, proc_role will also do auditing
** if required. proc_role will also print error message if required.
*/

if (proc_role("sso_role") = 0)
	return (1)

/* Don't display rowcount. */
set nocount on

/* Load the args into variables.  Trim blanks for args 1 and 3. */
select @tmpstr = lower(ltrim(rtrim(@arg1)))
select @dbname = @name

/* Determine the argument semantics based on the first argument */
if (@tmpstr = "default")
begin
	select @default = 1
end
else
begin
	select @default = 0
	/*
	** Load the @procname and @owner variables
	*/
	select @procname = @arg1
	select @owner = NULL
	if (@procname is not NULL)
	begin
		exec @retcode = sp_namecrack @procname, @site output,
					     @q_dbname output, @owner output,
					     @q_pname output
		/*
		** if you qualified the name, it needs to be this site and db.
		*/
		if ((@q_dbname is not null) and (@q_dbname != db_name()) or
			(@site is not null) and (@site != @@servername))
		begin
			/* 18227, "'%1!' is not in the current database" */
			raiserror 18227, @procname
			return (1)
		end
 
		select @objid = object_id(@procname)

                /*
                ** an object of this type must exist 
                */
		if (@objid is NULL) or
		   ((select count(*) from sysobjects 
			where (id = @objid and
			type in  ('P', 'TR', 'IT', 'XP'))) = 0) 
		begin
			/* 17461, "Object does not exist in this database."  */
			raiserror 17461
			return (1)
		end
 
                /*
                ** Construct procname so the output is uniform.
                ** For input of form (owner.name), this is redundant, but it
                ** covers all of the other combinations.
                */
                if (@owner is NULL)
                begin
                        select @owner = user_name(uid) from sysobjects
                                where id = @objid
                end
                select @procname = @owner + '.' + @q_pname

	end /* if (@procname is not NULL) */
end /* else if (@tmpstr not "default") */

/*
** create temp table for output
** Note that we use this table for outputting defaults as well as the
** non-default option settings; if defaults, then pname is "DEFAULT"
*/
/* pname could be site.db.owner.proc and max length is 30*3 + 255 + 3 */
create table #output (pname varchar(348), opname varchar(14), 
	setting varchar(4), db varchar(30))

/*
** set up cursor for looking at all object options
*/
declare opt_cursor cursor for select name from master.dbo.spt_values
	where (type = @spt_obj and name like "exec%")

/*
** The rest of this proc will be divided into the default <database> case and
** the non-default case.  First we'll do the default case.
*/
if @default = 1
begin
	/*
	** If no dbname was provdided, we'll display the names of
	** all databases that have some sort of default sproc auditing enabled,
	** along with the type of default auditing enabled.
	**
	** If a dbname was provided, we'll display the default auditing status
	** of that database (whether enabled or not).
	*/

	/* signify audit flag for default sproc/trigger */
        select @flag_type = "defpraud"

	select @all = 0

	if @dbname is NULL
	begin
		/*
		** First make sure there's at least one database with
		** an active default. If not, just print a message and
		** return.
		*/
		if (select count(name) from master.dbo.sysdatabases
		    where defpraud > 0) = 0
		begin
			/*
			** 18007, "No databases currently have default
			**	   sproc/trigger auditing enabled.
			*/
			exec sp_getmessage 18007, @msg output
			print @msg
			return (0)
		end
		select @all = 1
                declare db_cursor cursor for select name
                        from master.dbo.sysdatabases
                        where defpraud > 0
                open db_cursor
                fetch db_cursor into @dbname
	end
	else
	if (db_id(@dbname) is NULL)
	begin
		/* 17977, "'%1!' does not exist." */
		raiserror 17977, @dbname
		return (1)
	end

	/*
	** Begin the loop.  Break if a dbname name was provided, or
	** we've run out of databases with active defaults.
	*/
	while (1 = 1)
	begin
		select @dbid = db_id(@dbname) 

                /*
                ** set up option cursor
                */
                open opt_cursor
                fetch opt_cursor into @optstr
 
                /*
                ** get setting for each audit option
                */
                while (@@sqlstatus != 2)
                begin
                        exec sp_aux_getsptaud @dbid, @flag_type, @optstr,
                                @setting output
                        if @setting != "off"
                                insert #output values (@deftstr, @optstr,
                                                       @setting, @dbname)
                        fetch opt_cursor into @optstr
		end
                close opt_cursor

                /*
                ** get next dbname
                */
                if @all = 1
                begin
                        fetch db_cursor into @dbname
                        if @@sqlstatus = 2
                                break
                end
                else /* @all = 0 */
                        break
 
	end	/* while (1 = 1) */
end	/* if @default = 1 */
else
begin	/* regular non-default case */
	/*
	** If no name is provided 
	** display the names and the enabled auditing option(s) of all stored
	** procs and triggers in the current database that have some sort of
	** auditing enabled.
	**
	** If a sproc/trigger name was provided, display the auditing
	** status of that particular sproc/trigger.  
	*/

	/* signify audit flag for objects */
        select @flag_type = "audflags"
 
	select @all = 0

	if (@procname is NULL)
	begin
		/*
		** First make sure there's at least one sproc/trigger
		** with an active default. If not, just print a message
		** and return.
		*/
		if (select count(name) from sysobjects 
		    where audflags > 0 and type in ('TR', 'IT', 'P', 'XP')) = 0
		begin
			/*
			** 18009, "No procs/triggers currently have 
			**	   auditing enabled.
			*/
			exec sp_getmessage 18009, @msg output
			print @msg
			return (0)
		end

                /*
                ** set up the cursor to look through sysobjects
                */
                declare obj_cursor cursor for select id from sysobjects
			where audflags > 0 and type in ('TR', 'IT', 'P', 'XP')
                open obj_cursor
                fetch obj_cursor into @objid

		select  @procname = user_name(uid) + '.' + name from sysobjects 
			where id = @objid

		select @all = 1
	end

	/*
	** Figure out which type of object we have
	*/
	select @ptype = type from sysobjects 
		where id = @objid
	if @ptype = 'P' or @ptype = 'XP'
		select @optstr = "exec_procedure"
	else /* if @ptype = 'TR' or @ptype = 'IT' */
		select @optstr = "exec_trigger"

	/*
	** get the name of the current database for the output 
	*/
	select @userdbname = db_name() 

	/*
	** Begin the loop.  Break if an procname was provided, or we've
	** run out of procs/triggers with auditing enabled.
	*/
	while (1 = 1)
	begin
 		/* 
 		** put the the setting for each command into #output
 		*/

		exec sp_aux_getsptaud @objid, @flag_type, @optstr, 
			@setting output
		if @setting != "off"
			insert #output values (@procname, @optstr, 
				@setting, @userdbname)

                /*
                ** get next object id
                */
                if @all = 1
                begin
                        fetch obj_cursor into @objid
                        if @@sqlstatus = 2
                                break

                        select @procname = user_name(uid) + '.' + name, 
			       @ptype = type from sysobjects where id = @objid

			if @ptype = 'P' or @ptype = 'XP'
				select @optstr = "exec_procedure"
			else /* if @ptype = 'TR' or @ptype = 'IT' */
				select @optstr = "exec_trigger"
                end
                else /* @all = 0 */
                        break
	end	/* while (1 = 1) */
end

/* 
** print output table 
*/ 

exec sp_autoformat @fulltabname = #output,
	@selectlist = "'Procedure/Trigger' = pname, 'Audit Option' = opname, 'Value' = setting, 'Database' = db",  
	@orderby = "order by pname"

return (0)
go
exec sp_procxmode 'sp_aux_displaysprocaud', 'anymode'
go
grant execute on sp_aux_displaysprocaud to sso_role
go
dump transaction master with truncate_only
go
dump transaction sybsystemprocs with truncate_only
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_aux_displaydbaud')
begin
	drop procedure sp_aux_displaydbaud
end
go
print "Installing sp_aux_displaydbaud"
go

/*
** Auxilliary stored procedure for displaying database audit settings
**
** If the database parameter is NULL, the settings for all databases are 
** displayed
**
** Input Parameters:
**	dbname		database name (NULL if none specified)
** 
** Output parameters:
**	none
** Returns:
**	0		everything went ok
**	1		general error
*/

create procedure sp_aux_displaydbaud
@dbname varchar(30) = NULL		/* full name of database */
as

declare @alldb int		/* flag to get all databases */
declare @dbid int		/* flag to get all databases */
declare @msg varchar(1024)
declare @optstr varchar(90)	/* string representation of an audit option */
declare @setting varchar(30)    /* audit option setting */
declare @flag_type varchar(10)
declare @flag_type2 varchar(10)	/* additional database wide options. */ 
declare @flag_type3 varchar(10)
declare @spt_db varchar(2) 

select @spt_db = "Zd"
select @alldb = 0

/* 
** check if user has sso role, proc_role will also do auditing
** if required. proc_role will also print error message if required.
*/

if (proc_role("sso_role") = 0)
	return (1)

/* Don't display rowcount */
set nocount on

if @dbname is NULL
begin
	/*
	** If there aren't any databases that have auditing enabled,
	** just print a message and return.
	*/
	if (select count(name) from master.dbo.sysdatabases
	    where audflags != 0 or  deftabaud != 0  ) = 0
	begin
		/*
		** 17976, "No databases currently have auditing
		**	   enabled."
		*/
		exec sp_getmessage 17976, @msg output
		print @msg
		return (0)
	end

	select @alldb = 1
	/*
	** set up the cursor to look through sysdatabases
	*/
	declare db_cursor cursor for select name from master.dbo.sysdatabases
		where audflags != 0 or  deftabaud != 0 
	open db_cursor
	fetch db_cursor into @dbname
end
else if (db_id(@dbname) is NULL)
begin
	/* 17977, "'%1!' does not exist." */
	raiserror 17977, @dbname
	return (1)
end

/*
** create temp table for output
*/
 
create table #output (dbname varchar(31), OPname varchar(14),
                        setting varchar(4))

/*
** select which audit flag to look at 
*/
select @flag_type = "daudflags"
select @flag_type2 = "daudflags2"
select @flag_type3 = "daudflags"

/*
** set up cursor for looking at all database options
*/
declare opt_cursor cursor for select name from master.dbo.spt_values
	where type = @spt_db

/*
** If @dbname was provided display the auditing status for that database.
** If no db name provided, we'll get the auditing options for all
** databases currently in master.dbo.sysdatabases that have auditing
** enabled.  
**
** Begin the loop.  Break if a db name was provided, or we've run out
** of databases with auditing enabled.
*/

while (1 = 1)
begin
	/* get the dbid for this database */
	select @dbid = db_id(@dbname) 
	/*
	** get first option from cursor
	*/
	open opt_cursor
	fetch opt_cursor into @optstr
	
	/*
	** get setting for each audit option 
	*/
	while (@@sqlstatus != 2)
	begin
		if ( @optstr = "encryption_key" )
		begin
			select @flag_type = @flag_type2
		end
		else
		begin
			select @flag_type = @flag_type3
		end
		
		exec sp_aux_getsptaud @dbid, @flag_type, @optstr, 
			@setting output
		if @setting != "off"
			insert #output values (@dbname, @optstr, @setting) 
		fetch opt_cursor into @optstr
	end
	close opt_cursor

	/*
	** get next dbname
	*/
	if @alldb = 1
	begin
		fetch db_cursor into @dbname
		if @@sqlstatus = 2
			break
	end
	else /* @alldb = 0 */
		break
end

if (select count(*) from #output) = 0
begin
	/*
	** 17976, "No databases currently have auditing
	**	   enabled."
	*/
	exec sp_getmessage 17976, @msg output
	print @msg
	return (0)
end
/*
** print output table
*/

select "Database Name" = dbname, "Audit Option" = OPname,
	"Value" = setting
	from #output
	order by dbname

return (0)
go
exec sp_procxmode 'sp_aux_displaydbaud', 'anymode'
go
grant execute on sp_aux_displaydbaud to sso_role
go
dump transaction master with truncate_only
go
dump transaction sybsystemprocs with truncate_only
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_displayaudit')
begin
	drop procedure sp_displayaudit
end
go
print "Installing sp_displayaudit"
go


/*
**	calls aux_display function based on "audcmd" parameter
**
**     	sp_auditdisplay [ procedure | object | login | database | global 
**		| default_object | default_procedure [ , name ]]
*/

create procedure sp_displayaudit
@audcmd varchar(20) = NULL,		/* type of auditing to display */
@name varchar(511) = NULL		/* name or database to display */
as

declare @msg varchar(1024)

/* check if user has sso role, proc_role will also do auditing
** if required. proc_role will also print error message if required.
*/

if (proc_role("sso_role") = 0)
	return (1)

/*
** distribute to subprocedures 
*/
if @audcmd = "procedure"
	exec sp_aux_displaysprocaud @name
else if (@audcmd = "default_procedure" or @audcmd = "default procedure")
	exec sp_aux_displaysprocaud "default", @name 
else if @audcmd = "object"
	exec sp_aux_displayobjaud @name
else if (@audcmd = "default_object" or  @audcmd = "default object")
	exec sp_aux_displayobjaud "default", @name
else if @audcmd = "login"
	exec sp_aux_displaylogaud @name
else if @audcmd = "database"
	exec sp_aux_displaydbaud @name
else if @audcmd = "global"
	exec sp_aux_displayoptaud @name
else if @audcmd is NULL

/*
** display everything we can
*/
begin
	exec sp_aux_displaysprocaud NULL, NULL
	exec sp_aux_displaysprocaud "default", NULL
	exec sp_aux_displayobjaud NULL, NULL
	exec sp_aux_displayobjaud "default", NULL
	exec sp_aux_displaylogaud NULL, NULL
	exec sp_aux_displaydbaud NULL, NULL
	exec sp_aux_displayoptaud NULL, NULL
end
else
begin
	/*
	** 18556, "Usage: sp_displayaudit [ procedure | object | login | 
	**	database | global | default_object | default_procedure 
	**	[ , name ]]"
	*/
	exec sp_getmessage 18556, @msg output 
	print @msg
	return (0)
end

return (0)

go
exec sp_procxmode 'sp_displayaudit', 'anymode'
go
grant execute on sp_displayaudit to sso_role
go
dump transaction master with truncate_only
go
dump transaction sybsystemprocs with truncate_only
go
if exists (select *
	from sysobjects
		where sysstat & 7 = 4
			and name = 'sp_addaudittable')
begin
	drop procedure sp_addaudittable
end
go
print "Installing sp_addaudittable"
go

/* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
/*      11.0    1.0     12/06/95        sproc/src/addaudittable */

/*
** 18238, "You cannot create a total of more than 8 audit tables."
** 18239, "Creating segment '%1!' on '%2!' device."
** 18240, "Creating table '%1!' on '%2!' segment."
*/

create procedure sp_addaudittable
@devname	varchar(255) = NULL /* device on which the table is to be created */
as
declare @returncode	int		/* from sp_addsegment */
declare @table_count	int		/* # of audit tables */
declare @msg 		varchar(1024)	/* for sp_getmessage */
declare @segname 	varchar(255)	/* for creating segment, if required */

/*
**  If we're in a transaction, disallow this since it might make recovery
**  impossible.
*/
if @@trancount > 0
begin
	/* 17260, "Can't run %1! from within a transaction." */
	raiserror 17260, "sp_addaudittable"
	return (1)
end

/* check if user has sso role, proc_role will also do auditing
** if required. proc_role will also print error message if required.
*/

if (proc_role("sso_role") = 0)
	return (1)

if (db_name() != "sybsecurity")
begin
	/*
	** 17985, "You must be in the sybsecurity database to run
	**	   this procedure"
	*/
	raiserror 17985
	return (-1)
end
else
begin
	set chained off
end

set transaction isolation level 1

/* Get how many audit tables exist currently */
select @table_count = count(*) from sysobjects where name like "sysaudits_%"

/* 
** If no device name is specified, create appropriate segment on the
** device on which sybsecurity database is created
*/
if (@devname is NULL)
begin
	print "Syntax: sp_addaudittable <device_name> | 'default'"
	return(1)
end
else if (@devname = "default")
begin
	/* set device name to be the device on which sybsecurity was created */
	select @devname = b.name from master..sysusages a, 
		master..sysdevices b where a.lstart = 0 and
		a.dbid = db_id ("sybsecurity") and 
		a.vdevno = b.vdevno and
		(b.status | 2) != 0
end

/* Formulate the appropriate segment name */
if ( @table_count < 8)
	select @segname = "aud_seg_0" + convert(char(1), @table_count + 1)
else
begin
		/* 18238, "You cannot create a total of more than 8 audit tables." */
		raiserror 18238
		return (-2)
end

/* create the segment */
exec sp_getmessage 18239, @msg output
print @msg, @segname, @devname

exec sp_addsegment @segname, sybsecurity, @devname

/* create the table on the appropriate segment */
if (@table_count = 0)
begin
	exec sp_getmessage 18240, @msg output
	print @msg, "sysaudits_01", @segname
	create table sysaudits_01
	(
		event 		smallint not null,
		eventmod 	smallint not null,

		spid            smallint not null,

		eventtime 	datetime not null,
		sequence 	smallint not null,
		suid 		int not null,
		dbid 		smallint null,
		objid 		int null,
		xactid		binary(6) null,
		loginname	varchar(30) null,
		dbname		varchar(30) null,
		objname		varchar(255) null,
		objowner	varchar(30) null,
		extrainfo 	varchar(255) null,

		nodeid		tinyint null


	) lock allpages on @segname
end
else if (@table_count = 1)
begin
	exec sp_getmessage 18240, @msg output
	print @msg, "sysaudits_02", @segname
	create table sysaudits_02
	(
		event 		smallint not null,
		eventmod 	smallint not null,

		spid            smallint not null,

		eventtime 	datetime not null,
		sequence 	smallint not null,
		suid 		int not null,
		dbid 		smallint null,
		objid 		int null,
		xactid		binary(6) null,
		loginname	varchar(30) null,
		dbname		varchar(30) null,
		objname		varchar(255) null,
		objowner	varchar(30) null,
		extrainfo 	varchar(255) null,

		nodeid		tinyint null

	) lock allpages  on @segname
end
else if (@table_count = 2)
begin
	exec sp_getmessage 18240, @msg output
	print @msg, "sysaudits_03", @segname
	create table sysaudits_03
	(
		event 		smallint not null,
		eventmod 	smallint not null,

		spid            smallint not null,

		eventtime 	datetime not null,
		sequence 	smallint not null,
		suid 		int not null,
		dbid 		smallint null,
		objid 		int null,
		xactid		binary(6) null,
		loginname	varchar(30) null,
		dbname		varchar(30) null,
		objname		varchar(255) null,
		objowner	varchar(30) null,
		extrainfo 	varchar(255) null,

		nodeid		tinyint null

	) lock allpages on @segname
end
else if (@table_count = 3)
begin
	exec sp_getmessage 18240, @msg output
	print @msg, "sysaudits_04", @segname
	create table sysaudits_04
	(
		event 		smallint not null,
		eventmod 	smallint not null,

		spid            smallint not null,

		eventtime 	datetime not null,
		sequence 	smallint not null,
		suid 		int not null,
		dbid 		smallint null,
		objid 		int null,
		xactid		binary(6) null,
		loginname	varchar(30) null,
		dbname		varchar(30) null,
		objname		varchar(255) null,
		objowner	varchar(30) null,
		extrainfo 	varchar(255) null,

		nodeid		tinyint null

	) lock allpages  on @segname
end
else if (@table_count = 4)
begin
	exec sp_getmessage 18240, @msg output
	print @msg, "sysaudits_05", @segname
	create table sysaudits_05
	(
		event 		smallint not null,
		eventmod 	smallint not null,

		spid            smallint not null,

		eventtime 	datetime not null,
		sequence 	smallint not null,
		suid 		int not null,
		dbid 		smallint null,
		objid 		int null,
		xactid		binary(6) null,
		loginname	varchar(30) null,
		dbname		varchar(30) null,
		objname		varchar(255) null,
		objowner	varchar(30) null,
		extrainfo 	varchar(255) null,

		nodeid		tinyint null

	) lock allpages  on @segname
end
else if (@table_count = 5)
begin
	exec sp_getmessage 18240, @msg output
	print @msg, "sysaudits_06", @segname
	create table sysaudits_06
	(
		event 		smallint not null,
		eventmod 	smallint not null,

		spid            smallint not null,

		eventtime 	datetime not null,
		sequence 	smallint not null,
		suid 		int not null,
		dbid 		smallint null,
		objid 		int null,
		xactid		binary(6) null,
		loginname	varchar(30) null,
		dbname		varchar(30) null,
		objname		varchar(255) null,
		objowner	varchar(30) null,
		extrainfo 	varchar(255) null,

		nodeid		tinyint null

	) lock allpages on @segname
end
else if (@table_count = 6)
begin
	exec sp_getmessage 18240, @msg output
	print @msg, "sysaudits_07", @segname
	create table sysaudits_07
	(
		event 		smallint not null,
		eventmod 	smallint not null,

		spid            smallint not null,

		eventtime 	datetime not null,
		sequence 	smallint not null,
		suid 		int not null,
		dbid 		smallint null,
		objid 		int null,
		xactid		binary(6) null,
		loginname	varchar(30) null,
		dbname		varchar(30) null,
		objname		varchar(255) null,
		objowner	varchar(30) null,
		extrainfo 	varchar(255) null,

		nodeid		tinyint null

	) lock allpages  on @segname
end
else if (@table_count = 7)
begin
	exec sp_getmessage 18240, @msg output
	print @msg, "sysaudits_08", @segname
	create table sysaudits_08
	(
		event 		smallint not null,
		eventmod 	smallint not null,

		spid            smallint not null,

		eventtime 	datetime not null,
		sequence 	smallint not null,
		suid 		int not null,
		dbid 		smallint null,
		objid 		int null,
		xactid		binary(6) null,
		loginname	varchar(30) null,
		dbname		varchar(30) null,
		objname		varchar(255) null,
		objowner	varchar(30) null,
		extrainfo 	varchar(255) null,

		nodeid		tinyint null

	) lock allpages on @segname
end

return(0)
go
exec sp_procxmode 'sp_addaudittable', 'anymode'
go
grant execute on sp_addaudittable to sso_role
go
dump transaction master with truncate_only
go
dump transaction sybsystemprocs with truncate_only
go
dump transaction sybsecurity to diskdump with truncate_only
go
sp_configure "allow updates", 0
go
print 'Security installation is complete.'
go

declare @retval int
exec @retval = sp_version 'installsecurity', NULL, '15.5/EBF 17340 SMP/P/x86_64/Enterprise Linux/ase155/2391/64-bit/OPT/Mon Nov  9 14:15:35 2009', 'end'
if (@retval != 0) select syb_quit()
go

