/*
buildinstall/11.0.3.3 ESD#6/P-FREE/Linux Intel/Linux 2.2.14 i686/1/OPT/Fri Mar 17 15:47:56 CET 2000

(c) Copyright 1987, 2000.
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. 6475 Christie Avenue, Emeryville, CA 94608, USA.
*/
use master
go
if exists (select *
        from sysobjects
                where name = 'sp_configure')
begin
        execute sp_configure "allow updates", 1
end
go
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_addauditrecord')
begin
	print "Dropping sp_addauditrecord"
	drop procedure sp_addauditrecord
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_auditlogin')
begin
	print "Dropping sp_auditlogin"
	drop procedure sp_auditlogin
end
go

if (select db_id()) > 1
	dump tran sybsystemprocs with truncate_only
else
	dump tran master with truncate_only
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_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

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

set nocount on
go

use sybsecurity
go


if exists (select * from sysobjects where name = "sysauditoptions")
	truncate table sysauditoptions
else
	create table sysauditoptions
	(	num		smallint,
		val		smallint,
		minval		smallint,
		maxval		smallint,
		name		varchar(30),
		sval		varchar(30),
		comment		varchar(255)
	)
go

insert sysauditoptions values (1, 0, 0, 1, "enable auditing", "off",
	"Indicates whether or not system-wide auditing is enabled.")
go
insert sysauditoptions values (3, 0, 0, 3, "logins", "off", "Login auditing")
go
insert sysauditoptions values (4, 0, 0, 1, "logouts", "off", "Logout auditing")
go
insert sysauditoptions values (5, 0, 0, 1, "server boots", "off",
	"Server boot auditing")
go
insert sysauditoptions values (6, 0, 0, 3, "rpc connections", "off",
	"RPC connection auditing")
go
insert sysauditoptions values (7, 0, 0, 3, "roles", "off",
	"Role change auditing")
go
insert sysauditoptions values (8, 0, 0, 3, "sa commands", "off",
	"SA command auditing")
go
insert sysauditoptions values (9, 0, 0, 3, "sso commands", "off",
	"SSO command auditing")
go
insert sysauditoptions values (10, 0, 0, 3, "oper commands", "off",
	"OPER command auditing")
go
insert sysauditoptions values (11, 0, 0, 3, "sybase_ts commands", "off",
	"SYBASE_TS command auditing")
go
insert sysauditoptions values (12, 0, 0, 3, "navigator commands", "off",
	"NAVIGATOR command auditing")
go
insert sysauditoptions values (13, 0, 0, 3, "errors", "off", "error auditing")
go
insert sysauditoptions values (14, 0, 0, 1, "adhoc records", "off",
	"ad hoc auditing")
go
insert sysauditoptions values (15, 0, 0, 3, "replication commands", "off",
	"REPLICATION command auditing")
go

grant select on sysauditoptions to sso_role
go

if exists (select * from sysobjects where name = "sysaudits")
	grant select on sysaudits to sso_role
go


use sybsystemprocs
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 )
**      NOTE: This proc makes no attempt to validate that the dbid is actually
**	      the one corresponding to the dbname.  In fact, this proc doesn't
**	      validate any of the arguments; everything just passes through.
**
**  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(30) = NULL,	/* database name */
@obj_name varchar(30) = NULL,	/* object name */
@owner_name varchar(30) = NULL,	/* owner name */
@dbid int = NULL,		/* database id */


@objid int = NULL		/* object id */

as
declare	@returncode int		/* return from builtin */

select @returncode =


	ad_hoc_audit (@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_auditdatabase')
begin
	drop procedure sp_auditdatabase
end
go
print "Installing sp_auditdatabase"
go

/* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
/*	5.0	1.0	05/20/92	sproc/src/auditdatabase */

/*
**	Messages for sp_auditdatabase:
**
** 17260, "Can't run %1! from within a transaction."
**
** 17976, "No databases currently have auditing enabled."
** 17977, "'%1!' does not exist."
** 17978, "'%1!' has the following auditing options enabled:"
** 17979, "Invalid second argument.  Valid choices are 'ok', 'fail', 'both',
**	   or 'off'." 
** 17980, "Invalid third argument.  Valid choices are 'd', 'u', 'o', 'g', 'r',
**	   or 't'."
** 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."
** 17984, "Error updating the audit flags in memory.  This is a system error.
**	   Contact an SA-role user."
** 18073, "Set command failed. Set your maxwrite and minwrite labels to correct level."
*/

create procedure sp_auditdatabase
@dbname varchar(30) = NULL,		/* full name of database */
@ok_fail_both varchar(30) = NULL,	/* { ok | fail | both | off } */
@duogrt varchar(30) = NULL		/* { d | u | o | g | r | t }* */
as

declare @ofb int		/* off=0, ok=1, fail=2, both=3 */
declare @aud_val int		/* value from flags field in system table */
declare @i int
declare @maxlen int
declare @ch char
declare @alldb int		/* flag to get all databases */
declare @returncode int
declare @msg varchar(255)

/*
**  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." */
        exec sp_getmessage 17260, @msg output
	print @msg, "sp_auditdatabase"
	return (1)
end
else
begin
	set chained off
end

set transaction isolation level 1

/* 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 last two args to lower case with no leading/trailing blanks. */
if @ok_fail_both is not NULL
	select @ok_fail_both = lower(ltrim(rtrim(@ok_fail_both)))
if @duogrt is not NULL
	select @duogrt = lower(ltrim(rtrim(@duogrt)))

/*
**  Check for and process 'database' auditing
**	<dbname>, {"ok" | "fail" | "both" | "off"} [,"{duogrt}"]
**	@dbname   	   @ok_fail_both  	      @duogrt
**
**  If no argument was provided
**	Display the name and enabled auditing options for all databases that
**	have some sort of database auditing enabled.
**
**  If @dbname was provided
**	Display the auditing status for that database.
*/
if (@ok_fail_both is NULL and @duogrt is NULL)
begin
	select @alldb = 0
	/*
	** If no db name provided, we'll get the auditing options for all
	** databases currently in master.dbo.sysdatabases that have auditing
	** enabled.  So, we'll have to set up a loop and step through
	** sysdatabases 1 row at a time in alphabetical order by name.  Start
	** by getting the lowest alphabetical name.
	*/
	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) = 0
		begin
			/*
			** 17976, "No databases currently have auditing
			**	   enabled."
			*/
			exec sp_getmessage 17976, @msg output
			print @msg
			return (0)
		end

		select @alldb = 1
		select @dbname = min(name) from master.dbo.sysdatabases
			where audflags != 0
	end
	else
	if (db_id(@dbname) is NULL)
	begin
		/* 17977, "'%1!' does not exist." */
		exec sp_getmessage 17977, @msg output
		print @msg, @dbname
		return (1)
	end
	/*
	** Begin the loop.  Break if a db name was provided, or we've run out
	** of databases with auditing enabled.
	*/
	while (1 = 1)
	begin
		/* First reset rowcount which may have been set to 1 below. */
		set rowcount 0
		/*
		** 17978, "Database '%1!' has the following auditing options
		**	   enabled:"
		*/
		print ""
		print ""
		exec sp_getmessage 17978, @msg output
		print @msg, @dbname

		/* Get text description of currently enabled db auditing. */

		select a.name
			from master.dbo.spt_values a, master.dbo.sysdatabases b
			where a.type = 'Q' and a.number != -1 and
			b.name = @dbname and (a.number & b.audflags) != 0

		if (@alldb = 0) or
		   ((select count(name) from master.dbo.sysdatabases
		     where name > @dbname and audflags != 0) = 0)
			break
		else
		begin
			/* Set rowcount to 1 so we only get the next dbname. */
			set rowcount 1
			select @dbname = name from master.dbo.sysdatabases
				where name > @dbname and audflags != 0
				order by name
		end
	end

	return (0)
end

/* Check <dbname> argument */
if ((@dbname is NULL) or (db_id(@dbname) is NULL))
begin
	/* 17977 "'%1!' does not exist." */
        exec sp_getmessage 17977, @msg output
	print @msg, @dbname
	return (1)
end

/*  Check { ok | fail | both | off } */

if @ok_fail_both = "off"
	select @ofb = 0
else if @ok_fail_both = "ok"
	select @ofb = 1
else if @ok_fail_both = "fail"
	select @ofb = 2
else if @ok_fail_both = "both"
	select @ofb = 3
else if (@ok_fail_both is not NULL) or
	((@ok_fail_both is NULL) and (@duogrt is not NULL))
begin
	/*
	** 17979, "Invalid second argument.  Valid choices are 'ok', 'fail',
	**	   'both', or 'off'."
	*/
        exec sp_getmessage 17979, @msg output
	print @msg
	return (1)
end

/* get the old audflags value */
select @aud_val = audflags from master.dbo.sysdatabases
	where name = @dbname
/*
** if @aud_val was NULL, meaning it hasn't been touched since upgrade, set it
** so the bitwise operators will work properly
*/
if (@aud_val is NULL)
	select @aud_val = 0

/* If @duogrt is NULL, assume everything. */
if @duogrt is NULL
	select @duogrt = "duogrt"

/*  Determine { duogrt }. */

select @maxlen = datalength(@duogrt)
select @i = 1
while (@i <= @maxlen)
begin
	select @ch = substring(@duogrt, @i, 1)
	if      @ch = "d"
		if (@ofb = 0)
			select @aud_val = @aud_val & ~3
		else
			select @aud_val = @aud_val | (1 * @ofb) 
	else if @ch = "u"
		if (@ofb = 0)
			select @aud_val = @aud_val & ~12
		else
			select @aud_val = @aud_val | (4 * @ofb)
	else if @ch = "o"
		if (@ofb = 0)
			select @aud_val = @aud_val & ~48
		else
			select @aud_val = @aud_val | (16 * @ofb)
	else if @ch = "g"
		if (@ofb = 0)
			select @aud_val = @aud_val & ~192
		else
			select @aud_val = @aud_val | (64 * @ofb)
	else if @ch = "r"
		if (@ofb = 0)
			select @aud_val = @aud_val & ~768
		else
			select @aud_val = @aud_val | (256 * @ofb)
	else if @ch = "t"
		if (@ofb = 0)
			select @aud_val = @aud_val & ~3072
		else
			select @aud_val = @aud_val | (1024 * @ofb)

	else
	begin

		/*
		** 17980, "Invalid third argument.  Valid choices are 'd', 'u',
		**	   'o', 'g', 'r', or 't'."
		*/
        	exec sp_getmessage 17980, @msg output


		print @msg
		return (1)
	end
	select @i = @i + 1
end

begin transaction

/* Update the audflags value. */

update master.dbo.sysdatabases set audflags = @aud_val
	where name = @dbname
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
	exec sp_getmessage 17981, @msg output
	print @msg
	return (1)
end

/*  Also update the audit option in the server memory. */
select @returncode = audit_option(200, @aud_val, 0, db_id(@dbname))

if (@returncode = 1) or (@returncode = 0)
	commit transaction
else
	rollback transaction

/*  Check the @returncode of audit_option builtin.  */
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
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
else
begin
	/*
	** 17984 "Error updating the audit flags in memory.  This is a system
	**	  error.  Contact an SA-role user."
	*/
        exec sp_getmessage 17984, @msg output
	print @msg
        return (1)
end
go
exec sp_procxmode 'sp_auditdatabase', 'anymode'
go
grant execute on sp_auditdatabase 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_auditlogin')
begin
	drop procedure sp_auditlogin
end
go
print "Installing sp_auditlogin"
go

/* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
/*	5.0	1.0	02/20/92	sproc/src/auditlogin */

/*
**	Messages for sp_auditlogin
**
** 17260, "Can't run '%1!' from within a transaction."
**
** 17977, "'%1!' does not exist."
** 17978, "'%1!' has the following auditing options enabled:"
**
** 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."
** 17984, "Error updating the audit flags in memory.  This is a system error.
**	   Contact an SA-role user."
**
** 17991, "No logins currently have auditing enabled."
** 17992, "Invalid second argument.  Valid options are 'table', 'view', or
**	   'cmdtext'."
** 17993, "'%1!' is not a valid argument."
*/

create procedure sp_auditlogin
@loginame varchar(30) = NULL,	/* login name of the login */
@objtype varchar(30) = NULL,	/* {"table" | "view" | "cmdtext"} */
@opsvalue varchar(30) = NULL	/* "on" | {"ok" | "fail" | "both"} | "off" */
as
declare @opvalue int		/* 1=ok/on, 2=fail, 3=both, 0=off */
declare @aud_val int		/* value of audflags in system table */
declare @returncode int
declare @all_logins int		/* flag for all logins */
declare @msg varchar(255)

/*
**  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." */
        exec sp_getmessage 17260, @msg output
	print @msg, "sp_auditlogin"
	return (1)
end
else
begin
	set chained off
end

set transaction isolation level 1

/* 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 last two args to lower case with no leading/trailing blanks. */
if @objtype is not NULL
	select @objtype = lower(ltrim(rtrim(@objtype)))
if @opsvalue is not NULL
	select @opsvalue = lower(ltrim(rtrim(@opsvalue)))
/*
**  If no argument or just the @loginame is specified,
**	Display the name and enabled login auditing options for all
**	logins (if none specified) or only the specified login.
*/
if (@objtype is NULL and @opsvalue is NULL)
begin
	select @all_logins = 0
	/*
	** 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
		select @loginame = min(name) from master.dbo.syslogins
			where audflags != 0
	end
	else
	if (suser_id(@loginame) is NULL)
	begin
		/* 17977, '%1!' does not exist." */
		exec sp_getmessage 17977, @msg output
		print @msg, @loginame
		return (1)
	end
	/*
	** Begin the loop.  Break if a login name was provided, or we've run
	** out of logins with auditing enabled.
	*/
	while (1 = 1)
	begin
		/* First reset rowcount which may have been set to 1 below. */
		set rowcount 0

		/* 17978, "'%1!' has the following auditing options enabled:" */
		print ""
		print ""
		exec sp_getmessage 17978, @msg output
		print @msg, @loginame

		/* Get text description of currently enabled login auditing. */

		select a.name
			from master.dbo.spt_values a, master.dbo.syslogins b
			where a.type = 'U' and a.number != -1 and
			b.name = @loginame and (a.number & b.audflags) != 0

		if (@all_logins = 0) or
		   ((select count(name) from master.dbo.syslogins
		     where name > @loginame and audflags != 0) = 0)
			break
		else
		begin
			/* Set rowcount to 1 so we only get the next login. */
			set rowcount 1
			select @loginame = name from master.dbo.syslogins
				where name > @loginame and audflags != 0
				order by name
		end
	end

	return (0)
end

/* Check <loginame> argument */
if (suser_id(@loginame) is NULL)
begin
	/* 17977, "'%1!' does not exist." */
        exec sp_getmessage 17977, @msg output
	print @msg, @loginame
	return (1)
end

/* Make sure there is a non-NULL second argument if there is a third one. */
if (@objtype is NULL and @opsvalue is not NULL)
begin
	/*
	** 17992, "Invalid second argument.  Valid options are 'table', 'view',
	**	  or 'cmdtext'."
	*/
       	exec sp_getmessage 17992, @msg output
	print @msg
	return (1)
end

/* Get the old audflags value from syslogins.  */

select @aud_val = audflags from master.dbo.syslogins
	where name = @loginame
/*  
**  If audflags was NULL, which means it hadn't been touched since upgrade,
**  we need to set it to 0 before using bitwise operators on it.
*/
if (@aud_val is NULL)
	select @aud_val = 0

/*  Translate string @opsvalue into corresponding numeric @opvalue. */

select @opvalue = 0
if @opsvalue = "off"
	select @opvalue = 0

else if (@opsvalue = "on" and @objtype = "cmdtext") or
	(@opsvalue = "ok" and @objtype != "cmdtext")
	select @opvalue = 1

else if (@opsvalue = "fail" and @objtype != "cmdtext")
	select @opvalue = 2 

else if (@opsvalue = "both" and @objtype != "cmdtext")
	select @opvalue = 3

else if @opsvalue is not NULL
begin
	/* 17993, "'%1!' is not a valid argument." */
        exec sp_getmessage 17993, @msg output
	print @msg, @opsvalue
	return (1)
end
/*
**  Check for and process 'table' auditing
**	<loginame>, "table", {{"ok" | "fail" | "both"} | "off"}
**	@loginame  @objtype  @opsvalue
*/
if @objtype = "table"
begin
	if @opsvalue is NULL
	begin
		/* 17978, "'%1!' has the following auditing options enabled:" */
		exec sp_getmessage 17978, @msg output
		print @msg, @loginame
		select a.name
			from master.dbo.spt_values a, master.dbo.syslogins b
			where a.type = "U" and a.number != -1 and
			b.name = @loginame and (a.number & 3) != 0 and
			(a.number & b.audflags) != 0
		return (0)
	end
	/*
	** Note that for user table auditing,
	** @opvalue = 1,2,3 are already the correct values.
	*/
	if @opvalue = 0
		select @aud_val = @aud_val & ~3
	else
		select @aud_val = @aud_val | @opvalue
end
/*
**  Check for and process 'view' auditing
**	<loginame>, "view", {{"ok" | "fail" | "both"} | "off"}
**	@loginame  @objtype  @opsvalue
*/
else if @objtype = "view"
begin
	if @opsvalue is NULL
	begin
		/* 17978, "'%1!' has the following auditing options enabled:" */
		exec sp_getmessage 17978, @msg output
		print @msg, @loginame
		select a.name
			from master.dbo.spt_values a, master.dbo.syslogins b
			where a.type = "U" and a.number != -1 and
			b.name = @loginame and (a.number & 12) != 0 and
			(a.number & b.audflags) != 0
		return (0)
	end

	if      @opvalue = 1
		select @aud_val = @aud_val | 4
	else if @opvalue = 2
		select @aud_val = @aud_val | 8
	else if @opvalue = 3
		select @aud_val = @aud_val | 4 | 8
	else
		select @aud_val = @aud_val & ~12
end

/*
** Check for and process 'cmdtext' auditing.
**	<loginame>, "cmdtext", {"on" | "off"}
**	@loginame  @objtype  @opsvalue
*/
else if @objtype = "cmdtext"
begin
	if @opsvalue is NULL
	begin
		/* 17978, "'%1!' has the following auditing options enabled:" */
		exec sp_getmessage 17978, @msg output
		print @msg, @loginame
		select a.name
			from master.dbo.spt_values a, master.dbo.syslogins b
			where a.type = "U" and a.number != -1 and
			b.name = @loginame and (a.number & 16) != 0 and
			(a.number & b.audflags) != 0
		return (0)
	end

	if @opvalue = 1
		select @aud_val = @aud_val | 16
	else
		select @aud_val = @aud_val & ~16
end
else
begin
	/*
	** 17992, "Invalid second argument.  Valid options are 'table', 'view',
	**	  or 'cmdtext'."
	*/
	exec sp_getmessage 17992, @msg output
	print @msg
	return (1)
end

begin transaction

/*
**  Finally, update the audflags field of master.dbo.syslogins on disk.
**  Check @@rowcount to see if it works.
*/
update master.dbo.syslogins set audflags = @aud_val
	where name = @loginame

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
	exec sp_getmessage 17981, @msg output
	print @msg
	return (1)
end

/*  Also update the audit option in the server memory.  */

select @returncode = audit_option(205, @aud_val, suser_id(@loginame), 0)

if (@returncode = 1) or (@returncode = 0)
	commit transaction
else
	rollback transaction

/*  Check the @returncode of audit_option builtin.  */
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
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
else
begin
	/*
	** 17984 "Error updating the audit flags in memory.  This is a system
	**	  error.  Contact an SA-role user."
	*/
        exec sp_getmessage 17984, @msg output
	print @msg
        return (1)
end
go
exec sp_procxmode 'sp_auditlogin', 'anymode'
go
grant execute on sp_auditlogin 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_auditobject')
begin
	drop procedure sp_auditobject
end
go
print "Installing sp_auditobject"
go

/* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
/*	5.0	1.0	02/20/92	sproc/src/auditobject */
/*	Messages for sp_auditobject
**
** 17260, "Can't run %1! from within a transaction."
** 17460, "Object must be in the current database."
** 17461, "Object does not exist in this database."
** 17977, "'%1!' does not exist."
** 17978, "'%1!' has the following auditing options enabled:"
**
** 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."
** 17984, "Error updating the audit flags in memory.  This is a system error.
**	   Contact an SA-role user."
**
** 17994, "Only 'default table' or 'default view' is allowed."
** 17995, "Only 'ok', 'fail', 'both' or 'off' can be specified."
** 17996, "You must provide 'ok', 'fail', 'both', or 'off' preceding
**	   the 'dusi' string."
** 17997, "Error:  An invalid letter was specified.  Use only 'd', 'u', 's', or
**	   'i'."
** 17998, "No databases currently have %1! auditing enabled."
** 17999, "'%1!' has the following %2! auditing options enabled."
** 18000, "No objects currently have auditing enabled."
** 18001, "An object name must be provided."
** 18011, "Object name must be qualified with the owner name."
** 18021, "Object name may be qualified with the owner name only;
**	   please don't qualify with the database name".
** 18074, "Curwrite label could not be set to the object level. Set your maxwrite and minwrite labels correctly."
*/
create procedure sp_auditobject
@arg1 varchar(61) = NULL,
@arg2 varchar(30) = NULL,
@arg3 varchar(30) = NULL,
@arg4 varchar(30) = NULL

as
declare @objname varchar(92)		/* target object */
declare @dbname varchar(30)		/* dbname for default auditing */
declare @ok_fail_both varchar(30)	/* { ok | fail | both | off } */
declare @dusi_string varchar(30)	/* { d | u | s | i }*     */
declare @ofb int		/* off=0, ok=1, fail=2, both=3 */
declare @aud_val int		/* value from flags field in system table */
declare @i int
declare @maxlen int
declare @dusi int
declare @dusi_was_null int	/* flag to indicate dusi was not provided */
declare @objid int		/* object id of table or view    */
declare @optn int		/* option number for audit_option builtin */
declare @ch char
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; 2=def.view */
declare @returncode int
declare @all int		/* flag for displaying all db's or obj's */
declare @tmpstr varchar(61)	/* for various string usage */
declare @msg varchar(255)

/*
**  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." */
        exec sp_getmessage 17260, @msg output
	print @msg, "sp_auditobject"
	return (1)
end
else
begin
	set chained off
end

set transaction isolation level 1

/* 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 = @arg2
select @ok_fail_both = lower(ltrim(rtrim(@arg3)))
select @dusi_string = lower(ltrim(rtrim(@arg4)))
select @default_table_view = 0
if @tmpstr like "default %"
begin
	if @tmpstr like "% table"
	begin
		select @default_table_view = 1
		select @tmpstr = "default table"
	end
	else if @tmpstr like "% view"
	begin
		select @default_table_view = 2
		select @tmpstr = "default view"
	end
	else
	begin
		/* 17994 "Only 'default table' or 'default view' is allowed." */
        	exec sp_getmessage 17994, @msg output
		print @msg
		return (1)
	end
end
else
begin
	/*
	** Load the @objname variable.  Check for the owner.objname syntax
	** and load the @owner variable if applicable.
	*/
	select @objname = @arg1
	select @owner = NULL
	if @objname is not NULL
	begin
		if @objname like "%.%.%"
		begin
			/*
			** 18021, "Object name may be qualified 
			** with the owner name only; please don't 
			** qualify with the database name".
			*/
			exec sp_getmessage 18021, @msg output
			print @msg
			return (1)
		end

		/* A zero returned from charindex() means no '.' was found. */
		select @i = charindex(".", @objname)
		if (@i != 0)
			select @owner = substring(@objname, 1, @i-1)
	end

end

if (@default_table_view = 0 and @objname is not NULL)
begin
	/* dbname specified should be the current dbname */
	if (@dbname is not NULL and @dbname != db_name())
	begin
		/*
		** 17460, "Object must be in the current database."
		*/
		exec sp_getmessage 17460, @msg output
		print @msg
		return (1)
	end

	/*
	** If no owner name was provided, make sure the object name is unique
	** in the database.  If not, an owner name must be provided.  If so,
	** set up @objname in the owner.object format so throughout the rest
	** of this proc, we can use object_id(@objname).
	*/
	if (@owner is NULL)
	begin
		select @i = count(*) from sysobjects where name = @objname and
			type in ('U', 'V', 'S')
		if (@i = 0)
		begin
			/* 17461, "Object does not exist in this database."  */
			exec sp_getmessage 17461, @msg output
			print @msg
			return (1)
		end
		else if (@i > 1)
		begin
			/*
			** 18011, "Object name must be qualified with the
			**	   owner name."
			*/
			exec sp_getmessage 18011, @msg output
			print @msg
			return (1)
		end
		else
		begin
			select @owner = user_name(uid) from sysobjects
				where name = @objname and
				type in ('U', 'V', 'S')
			select @objname = @owner + '.' + @objname
		end
	end
	else if (object_id(@objname) is NULL)
	begin
		/* 17461, "Object does not exist in this database."  */
		exec sp_getmessage 17461, @msg output
		print @msg
		return (1)
	end
	select @objid = object_id(@objname)
end

/*  Check { ok | fail | both | off } */

if @ok_fail_both = "off"
	select @ofb = 0
else if @ok_fail_both = "ok"
	select @ofb = 1
else if @ok_fail_both = "fail"
	select @ofb = 2
else if @ok_fail_both = "both"
	select @ofb = 3
else if @ok_fail_both is not NULL
begin
	/* 17995, "Only 'ok', 'fail', 'both' or 'off' can be specified." */
        exec sp_getmessage 17995, @msg output
	print @msg
	return (1)
end
else if @dusi_string is not NULL
begin
	/*
	** 17996, "You must provide 'ok', 'fail', 'both', or 'off' preceding
	**	   the 'dusi' string."
	*/
	exec sp_getmessage 17996, @msg output
	print @msg
	return (1)
end

/* If @dusi is NULL, assume everything.  Remember that it was NULL, however. */
if @dusi_string is NULL
begin
	select @dusi_string = "dusi"
	select @dusi_was_null = 1
end
else
	select @dusi_was_null = 0

/*  Determine { dusi } */

select @dusi = 0
select @maxlen = datalength(@dusi_string)
select @i = 1
while (@i <= @maxlen)
begin
	select @ch = substring(@dusi_string, @i, 1)
	if      @ch = "d"
		if (@ofb = 0)
			select @dusi = @dusi & ~3
		else
			select @dusi = @dusi | (1 * @ofb) 
	else if @ch = "u"
		if (@ofb = 0)
			select @dusi = @dusi & ~12
		else
			select @dusi = @dusi | (4 * @ofb)
	else if @ch = "s"
		if (@ofb = 0)
			select @dusi = @dusi & ~48
		else
			select @dusi = @dusi | (16 * @ofb)
	else if @ch = "i"
		if (@ofb = 0)
			select @dusi = @dusi & ~192
		else
			select @dusi = @dusi | (64 * @ofb)
	else
	begin
		/*
		** 17997, "Error:  An invalid letter was specified.  Use only
		**	   'd', 'u', 's', or 'i'."
		*/
        	exec sp_getmessage 17997, @msg output
		print @msg
		return (1)
	end
	select @i = @i + 1
end
/*
** 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>,
**		{"ok" | "fail" | "both" | "off"} [,"{dusi}"]
*/
if @default_table_view > 0
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)
	*/
	if (@ok_fail_both is NULL and @dusi_was_null = 1)
	begin
		select @all = 0
		/*
		** If no dbname provided, we'll get the default auditing options
		** for all databases currently in master.dbo.sysdatabases that
		** have an "active" default auditing status.  So, we'll have to
		** set up a loop and step through sysdatabases 1 row at a time
		** in alphabetical order by name.  Start by getting the lowest
		** alphabetical name.
		*/
		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 ((@default_table_view = 1) and
			   ((select count(name) from master.dbo.sysdatabases
			     where deftabaud != 0) = 0)) or
			   ((@default_table_view = 2) and
			   ((select count(name) from master.dbo.sysdatabases
			     where 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
			if @default_table_view = 1
				select @dbname = min(name)
				    from master.dbo.sysdatabases
				    where deftabaud != 0
			else
				select @dbname = min(name)
				    from master.dbo.sysdatabases
				    where defvwaud != 0
		end
		else
		if (db_id(@dbname) is NULL)
		begin
			/* 17977, "'%1!' does not exist." */
			exec sp_getmessage 17977, @msg output
			print @msg, @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
			/*
			** First reset rowcount which may have been set to 1
			** below.
			*/
			set rowcount 0
			/*
			** 17999, "'%1!' has the following %2! auditing
			**	   options enabled:"
			*/
			print ""
			print ""
			exec sp_getmessage 17999, @msg output
			print @msg, @dbname, @tmpstr
			/*
			** Get text description of currently enabled
			** default object auditing.
			*/
			if @default_table_view = 1
				select a.name from master.dbo.spt_values a,
				    master.dbo.sysdatabases b
				    where a.type = 'M' and a.number != -1 and
				    b.name = @dbname and
				    (a.number & b.deftabaud) != 0
			else
				select a.name from master.dbo.spt_values a,
				    master.dbo.sysdatabases b
				    where a.type = 'M' and a.number != -1 and
				    b.name = @dbname and
				    (a.number & b.defvwaud) != 0

			if (@all = 0) or
			   ((@default_table_view = 1) and
			   ((select count(name) from master.dbo.sysdatabases
			     where name > @dbname and deftabaud != 0) = 0)) or
			   ((@default_table_view = 2) and
			   ((select count(name) from master.dbo.sysdatabases
			     where name > @dbname and defvwaud != 0) = 0))
				break
			else
			begin
				/*
				** Set rowcount to 1 so we only get the next
				** database.
				*/
				set rowcount 1
				if @default_table_view = 1
					select @dbname = name
					    from master.dbo.sysdatabases
					    where name > @dbname and
					    deftabaud != 0 order by name
				else
					select @dbname = name
					    from master.dbo.sysdatabases
					    where name > @dbname and
					    defvwaud != 0 order by name
			end
		end	/* while (1 = 1) */

		return (0)
	end

	/* Check <dbname> argument */
	if ((@dbname is NULL) or (db_id(@dbname) is NULL))
	begin
		/* 17977 "'%1!' does not exist." */
        	exec sp_getmessage 17977, @msg output
		print @msg, @dbname
		return (1)
	end

	/* Get the old audit default value. */
	if @default_table_view = 1
	begin
		select @aud_val = deftabaud from master.dbo.sysdatabases
			where name = @dbname
		select @optn = 201
	end
	else
	begin
		select @aud_val = defvwaud from master.dbo.sysdatabases
			where name = @dbname
		select @optn = 202
	end
	/*
	** If @aud_val is NULL, it hasn't been touched since upgrade.
	** Set it to 0 so that the bitwise operators work properly.
	*/
	if (@aud_val is NULL)
		select @aud_val = 0

	/* Setup new "default" auditing flags */
	if @ofb = 0 /* off */
		select @aud_val = @aud_val & @dusi
	else
		select @aud_val = @aud_val | @dusi

	/* Put in the new flags value */

	begin transaction

	/* Put in the new flags value */
	if @default_table_view = 1
		update master.dbo.sysdatabases set deftabaud = @aud_val
			where name = @dbname

	else
		update master.dbo.sysdatabases set defvwaud = @aud_val
			where name = @dbname

	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
		exec sp_getmessage 17981, @msg output
		print @msg
		return (1)
	end

	/* Update the audit option in the server memory. */
	select @returncode = audit_option(@optn, @aud_val, 0, db_id(@dbname))

	if (@returncode = 1) or (@returncode = 0)
		commit transaction
	else
		rollback transaction

end	/* if @default_table_view > 0 */
else
begin
	/*
	** For regular, non-default object auditing...
	** Check for and process 'table' and 'view' auditing
	** <tablename | viewname>, {"ok" | "fail" | "both" | "off"} [,"{dusi}"]
	*/

	if (@ok_fail_both is NULL and @dusi_was_null = 1)
	begin
		/*
		** If we get into this block, we will only be "reporting" on
		** current auditing settings.  No flag setting will be done,
		** and we will exit the procedure at the end of this block.
		*/
		select @all = 0

		/*
		** 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 in
		** numerical order by object id.
		**
		** 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 @objname to be the object with the lowest
			** objid that has auditing enabled.
			*/
			select @objid = min(id) from sysobjects where
				audflags != 0 and type in ('S', 'U', 'V')
			select @objname = user_name(uid) + '.' + name from
				sysobjects where id = @objid
			select @all = 1
		end
		/*
		** Begin the loop.  Break if an objname was provided, or we've
		** run out of objects with auditing enabled.
		*/
		while (1 = 1)
		begin
			/*
			** First reset rowcount which may have been set to 1
			** below.
			*/
			set rowcount 0
			/*
			** 17978, "'%1!' has the following auditing
			**	   options enabled:"
			*/
			print ""
			print ""
			exec sp_getmessage 17978, @msg output
			print @msg, @objname
			/*	
			** Get text description of currently enabled object
			** auditing.
			*/
			select a.name from master.dbo.spt_values a,
			    sysobjects b where a.type = 'M' and
			    a.number != -1 and b.id = @objid and 
			    (a.number & b.audflags) != 0
	
			if (@all = 0) or
			   ((select count(id) from sysobjects
			     where id > @objid and audflags != 0
			     and type in ('S','U','V')) = 0)
				break
			else
			begin
				/*
				** Set rowcount to 1 so we only get the next
				** objname.
				*/
				set rowcount 1
				select @objid = id,
					@objname = user_name(uid) + '.' + name
					from sysobjects where id > @objid and
					audflags != 0 and type in ('S','U','V')
					order by id
			end
		end

		/* We're done reporting, so good bye. */
		return (0)
	end

	/* If we reach here, we will be setting auditing flags. */
	if @objname is NULL
	begin
		/* 18001, "An object name must be provided." */
		exec sp_getmessage 18001, @msg output
		print @msg
		return (1)
	end

	/* Get the old audflags value. */
	select @aud_val = audflags from sysobjects where id = @objid

	/*
	** If audflags was NULL, which means it's never been touched since
	** upgrade, set it to 0 before operating on it with bitwise operators.
	*/
	if (@aud_val is NULL)
		select @aud_val = 0

	/* Setup new object auditing flags. */
	if @ofb = 0 /* off */
		select @aud_val = @aud_val & @dusi
	else
		select @aud_val = @aud_val | @dusi

	/* Put in the new flags value. */

	begin transaction
	update sysobjects set audflags = @aud_val where id = @objid 

	if @@rowcount != 1
	begin
		/*
		** 17981, "Error updating the audit flags in the system
		**	   catalogs.  This is a system error. Contact an
		**	   SA-authorized user."
		*/
		rollback transaction
		exec sp_getmessage 17981, @msg output
		print @msg
		return (1)
	end

	/* Also update the audit option in the server memory.  */
	select @returncode = audit_option(204, @aud_val, @objid, db_id())

	if (@returncode = 1) or (@returncode = 0)
		commit transaction
	else
		rollback transaction

end	/* else case of if @default_table_view > 0 */

/*  Check the @returncode of audit_option builtin.  */
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
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
else
begin
	/*
	** 17984 "Error updating the audit flags in memory.  This is a system
	**        error.  Contact an SA-role user."
	*/
        exec sp_getmessage 17984, @msg output
	print @msg
        return (1)
end
go
exec sp_procxmode 'sp_auditobject', 'anymode'
go
grant execute on sp_auditobject 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_auditoption')
begin
	drop procedure sp_auditoption
end
go
print "Installing sp_auditoption"
go

/* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
/*	5.0	1.0	02/20/92	sproc/src/auditoption */

/*
**	Messages for sp_auditoption
**
** 17260, "Can't run %1! from within a transaction."
**
** 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."
** 17984, "Error updating the audit flags in memory.  This is a system error.
**	   Contact an SA-role user."
**
** 18002, "You must provide an audit option."
** 18003, "Audit option '%1!' does not exist.  Valid options are:"
** 18004, "Audit option '%1!' is ambiguous.  Ambiguous options are:"
** 18005, "'%1!' is an invalid audit option string in this context."
*/
create procedure sp_auditoption
@opkeywd  varchar(30)  = NULL,		/* audit option name  */
@opsvalue varchar(255) = NULL		/* audit option string value */
as
declare @opvalue	int		/* audit option numeric value */
declare @optcnt		int		/* number of matching options */
declare @optn		int		/* audit option number */
declare @maxval		int		/* maxval of the option */
declare @on_off		int		/* true if option only takes on/off */
declare @returncode	int
declare @msg		varchar(255)	/* for sp_getmessage */
declare @oldval		int		/* old value of option */	

/*
**  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." */
	exec sp_getmessage 17260, @msg output
	print @msg, "sp_auditoption"
	return (1)
end
else
begin
	set chained off
end

set transaction isolation level 1

/* 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 @opsvalue is not NULL
	select @opsvalue = lower(ltrim(rtrim(@opsvalue)))

/*
** If no arguments or "all" @opkeywd with no @opsvalue, then display all the
** current settings.
*/
if @opkeywd is NULL or @opkeywd = "all"
begin
	if @opsvalue is NULL
	begin
		/*
		** Exclude sybase_ts commands (number 11) from the output,
		** since the whole notion of sybase_ts is undocumented.
		*/
		select name, sval from sybsecurity.dbo.sysauditoptions
			where num != 11 
		return (0)
	end
	else if @opkeywd is NULL
	begin
		/* 18002, "You must provide an audit option." */
		exec sp_getmessage 18002, @msg output
		print @msg
		return (1)
	end
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 and @opkeywd != "all"
begin
	/* 18003, "Audit option '%1!' does not exist. Valid options are:" */
	exec sp_getmessage 18003, @msg output
	print @msg, @opkeywd
	select name from sybsecurity.dbo.sysauditoptions where num != 11
	return (1)
end

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

/*  If just the name is given, display only it's setting. */
if @opsvalue is NULL
begin
	select name, sval from sybsecurity.dbo.sysauditoptions
		where name like "%" + @opkeywd + "%"
	return (0)
end

/* Set the on_off flag for options that only allow 'on' or 'off'. */

if ("enable auditing" like "%" + @opkeywd + "%") or
   ("all" like "%" + @opkeywd + "%") or
   ("logouts" like "%" + @opkeywd + "%") or
   ("server boots" like "%" + @opkeywd + "%") or
   ("adhoc records" like "%" + @opkeywd + "%")

	select @on_off = 1

else if ("errors" like "%" + @opkeywd + "%")
	select @on_off = -1	/* special case for error auditing */
else
	select @on_off = 0
/*
**  Convert the string @opsvalue to the numeric @opvalue.
**  Only check for the following strings: "off", "on", "ok", "fail",
**  "nonfatal", "fatal", and "both".
*/

select @oldval = val 
from sybsecurity.dbo.sysauditoptions
where name like "%" + @opkeywd + "%"

if @opsvalue = "off"
	select @opvalue = 0

else if (@opsvalue = "on" and @on_off = 1)
		select @opvalue = 1 

else if (@opsvalue = "ok" and @on_off = 0) or
	(@opsvalue = "nonfatal" and @on_off = -1)
begin
	if (@oldval = 2 or @oldval = 3) 
	begin
		select @opvalue = 3 
		select @opsvalue = "both" 
	end
	else
		select @opvalue = 1 
end

else if (@opsvalue = "fail" and @on_off = 0) or
	(@opsvalue = "fatal" and @on_off = -1)
begin
	if (@oldval = 1 or @oldval = 3)
	begin
		select @opvalue = 3 
		select @opsvalue = "both" 
	end
	else
		select @opvalue = 2 
end

else if (@opsvalue = "both" and @on_off != 1)
	select @opvalue = 3
else
begin
	/* 18005, "'%1!' is an invalid audit option string in this context." */
	exec sp_getmessage 18005, @msg output
	print @msg, @opsvalue
	return (1)
end
/*
**  Everything is OK.  Now, update the table on disk first.
**  There is a special "all" case where the option value should be set to
**  either maximum (1 for "on"; 3 for "both") or minimum (0 for "off").
*/

begin transaction

if (@opkeywd = "all")
begin
	/*
	**  Set AO_GLOBAL option number to turn on/off
	**  all global auditing options.
	*/
	select @optn = 2

	if @opvalue = 1
	begin
		update sybsecurity.dbo.sysauditoptions
			set val = 3, sval = "both"
			where maxval = 3
		if @@rowcount = 0
		begin
			/*
			** 17981, "Error updating the audit flags in the system
			**	   catalogs.  This is a system error. Contact an
			**	   SA-role user."
			*/
			rollback transaction
			exec sp_getmessage 17981, @msg output
			print @msg
			return (1)
		end

		/*  Don't change the "enable auditing" row.  */

		update sybsecurity.dbo.sysauditoptions set val = 1, sval = "on"
			where maxval = 1 and num != 1
	end
	else
		update sybsecurity.dbo.sysauditoptions
			set val = 0, sval = "off"
			where num != 1

	if @@rowcount = 0
	begin
		/*
		** 17981, "Error updating the audit flags in the system
		**	   catalogs.  This is a system error. Contact an
		**	   SA-role user."
		*/
		rollback transaction
		exec sp_getmessage 17981, @msg output
		print @msg
		return (1)
	end
end
else
begin
	select @optn = num from sybsecurity.dbo.sysauditoptions
		where name like "%" + @opkeywd + "%"
	update sybsecurity.dbo.sysauditoptions
		set val = @opvalue, sval = @opsvalue
		where num = @optn
	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
		exec sp_getmessage 17981, @msg output
		print @msg
		return (1)
	end
end

/*  Update the audit option in the server memory.  */
select @returncode = audit_option (@optn, @opvalue, 0, 0)

if (@returncode = 1) or (@returncode = 0)
	commit transaction
else
	rollback transaction

/*  Check the @returncode of audit_option builtin.  */
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
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
else
begin
	/*
	** 17984 "Error updating the audit flags in memory.  This is a system
	**	  error.  Contact an SA-role user."
	*/
        exec sp_getmessage 17984, @msg output
	print @msg
        return (1)
end
go
exec sp_procxmode 'sp_auditoption', 'anymode'
go
grant execute on sp_auditoption 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_auditsproc')
begin
	drop procedure sp_auditsproc
end
go
print "Installing sp_auditsproc"
go

/* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
/*	5.0	1.0	02/20/92	sproc/src/auditsproc */

/*	Messages for sp_auditdproc
**
** 17260, "Can't run %1! from within a transaction."
**
** 17977, "'%1!' does not exist."
** 17978, "'%1!' has the following auditing options enabled:"
**
** 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."
** 17984, "Error updating the audit flags in memory.  This is a system error.
**	   Contact an SA-role user."
**
** 17995, "Only 'ok', 'fail', 'both', or 'off' can be specified."
**
** 18006, "The third argument was not necessary; therefore, it was ignored."
** 18007, "No databases currently have default sproc/trigger auditing enabled."
** 18008, "'%1!' has the following default sproc/trigger auditing enabled:"
** 18009, "No sprocs/triggers currently have auditing enabled."
** 18010, "A sproc/trigger name or 'all' must be provided."
** 18012, "Sproc/trigger name must be qualfied with the owner name."
** 18022, "Procedure/trigger name may be qualfied with the owner name only; please don't qualify with the database name."
** 18065, "Illegal fourth argument specified. Valid arguments are: 'access',
**	   'trusted_invocation'"
** 18074, "Curwrite label could not be set to the object level. Set your maxwrite and minwrite labels correctly."
*/
create procedure sp_auditsproc
@arg1 varchar(61) = NULL,	/* <sproc/trigger name> | "all" | "default" */
@arg2 varchar(30) = NULL,	/* <dbname> */


@arg3 varchar(30) = NULL	/* {"ok" | "fail" | "both" | "off"} */

as
declare @opvalue int		/* 1=ok, 2=fail, 3=both, 0=off */
declare @aud_val int		/* value of audflags or defpraud */
declare @default int		/* "default" flag */
declare @dbname varchar(30)
declare @owner varchar(30)
declare @procname varchar(61)
declare @objid int
declare @ofb varchar(10)	/* ok | fail | both | off flag */
declare @all int
declare @i int
declare @sysproc int		/* 0 = No way this is an system proc
				** 1 = "sp_%", owner is "dbo", *in* current db
				** 2 = sys proc, but need to find it in master
				*/
declare @tmpstr varchar(61)	/* owner.object and other uses */
declare @returncode int
declare @msg varchar(255)	/* for sp_getmessage */

/*
**  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." */
	exec sp_getmessage 17260, @msg output
	print @msg, "sp_auditsproc"
	return (1)
end
else
begin
	set chained off
end

set transaction isolation level 1

/* 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 = @arg2
select @ofb = lower(ltrim(rtrim(@arg3)))


/* Determine the argument semantics based on the first argument */
if (@tmpstr = "default")
	select @default = 1
else
begin
	select @default = 0
	/*
	** Load the @procname variable.  Check for owner.procname syntax and
	** load the @owner variable if necessary.
	*/
	select @procname = @arg1
	select @owner = NULL
	if (@procname is not NULL)
	begin
		if @procname like "%.%.%"
		begin
			/*
			** 18022, Procedure/trigger name may be 
			** qualfied with the owner name only;
			** please don't qualify with the database name.
			*/
			exec sp_getmessage 18022, @msg output
			print @msg
			return (1)
		end
		/* A zero returned from charindex() means no '.' was found. */
		select @i = charindex(".", @procname)
		if (@i != 0)
			select @owner = substring(@procname, 1, @i - 1)
	end
end

if (@default = 0 and @procname is not NULL)
begin
        /* dbname specified should be the current dbname */
        if (@dbname is not NULL and @dbname != db_name())
        begin
                /*
                ** 17460, "Object must be in the current database."
                */
                exec sp_getmessage 17460, @msg output
                print @msg
                return (1)
        end
	/*
	** If procname isn't "all", make sure proc/trigger exists and
	** is unique in this database.
	*/
	if (lower(ltrim(rtrim(@procname))) != "all")
	begin
		/*
		** If no owner name was provided, make sure the sproc/trigger
		** is unique in this database.  If not, an owner name must be
		** provided.  If so, set up @procname in the owner.procname
		** format so that we can use object_id(@procname) from now on.
		*/
		if (@owner is NULL)
		begin
			select @i = count(*) from sysobjects
				where name = @procname and type in ('P', 'TR')
			if (@i = 0)
        		begin
				/*
                		** 17461, "Object does not exist in this	
				** database."
				*/
                		exec sp_getmessage 17461, @msg output
                		print @msg
                		return (1)
			end
			else if (@i > 1)
			begin
				/*
				** 18012, "Sproc/trigger name must be qualified
				**	   with the owner name."
				*/
				exec sp_getmessage 18012, @msg output
				print @msg
				return (1)
			end
			else
			begin
				select @owner = user_name(uid) from sysobjects
					where name = @procname and
					type in ('P', 'TR')
				select @procname = @owner + '.' + @procname
			end
		end
		else if (object_id(@procname) is NULL)
		begin
			/* 17461, "Object does not exist in this database." */
			exec sp_getmessage 17461, @msg output
			print @msg
			return (1)
		end
		select @objid = object_id(@procname)
        end
end

/*
** If "ok | fail | both | off" argument was provided, translate string @ofb
** into corresponding numeric @opvalue.
*/
if @ofb is not NULL
begin
	if ( @ofb not in ( "ok", "fail", "both", "off" ))
	begin
		/*
		** 17995, "Only 'ok', 'fail', 'both' or 'off' can be
		**	   specified."
		*/
		exec sp_getmessage 17995, @msg output
		print @msg
		return (1)
	end


	if @ofb = "off"
		select @opvalue = ~768

	else if @ofb = "ok"
		select @opvalue = 256

	else if @ofb = "fail"
		select @opvalue = 512

	else if @ofb = "both"
		select @opvalue = 768

end
/*
** 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 there was no "ok | fail | both | off" argument, we will generate
	** a "report".  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).
	*/
	if @ofb is NULL
	begin
		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
			select @dbname = min(name) from master.dbo.sysdatabases
			    where defpraud != 0
		end
		else
		if (db_id(@dbname) is NULL)
		begin
			/* 17977, "'%1!' does not exist." */
			exec sp_getmessage 17977, @msg output
			print @msg, @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
			/*
			** First reset rowcount which may have been set to 1
			** below.
			*/
			set rowcount 0
			/*
			** 18008, "'%1!' has the following default sproc/trigger
			**	   auditing options enabled:"
			*/
			print ""
			print ""
			exec sp_getmessage 18008, @msg output
			print @msg, @dbname
			/*
			** Get text description of currently enabled
			** default sproc/trigger auditing.
			*/
			select a.name from master.dbo.spt_values a,
			    master.dbo.sysdatabases b
			    where a.type = 'M' and a.number != -1 and
			    b.name = @dbname and (a.number & b.defpraud) != 0

			if (@all = 0) or 
			   ((select count(name) from master.dbo.sysdatabases
			     where name > @dbname and defpraud != 0) = 0)
				break
			else
			begin
				/*
				** Set rowcount to 1 so we only get the next
				** database.
				*/
				set rowcount 1
				select @dbname = name
				    from master.dbo.sysdatabases
				    where name > @dbname and
				    defpraud != 0 order by name
			end
		end	/* while (1 = 1) */

		return (0)
	end
	/*
	** If we reach here, that means "ok | fail | both | off" was provided.
	** Make sure a dbname was provided and that it is valid.
	*/
	if ((@dbname is NULL) or (db_id(@dbname) is NULL))
	begin
		/* 17977, "'%1!' does not exist." */
		exec sp_getmessage 17977, @msg output
		print @msg, @dbname
		return (1)
	end

	select @aud_val = defpraud from master.dbo.sysdatabases
		where name = @dbname
	/*
	** If @aud_val is NULL, it hasn't been touched since upgrade.  Set it
	** to 0 so the bitwise operators work properly.
	*/
	if (@aud_val is NULL)
		select @aud_val = 0

	if @ofb = "off"
		select @aud_val = @aud_val & @opvalue 
	else
		select @aud_val = @aud_val | @opvalue


	begin transaction

	/* Finally, update the defpraud of master.dbo.sysdatabases on disk. */
	update master.dbo.sysdatabases set defpraud = @aud_val
		where name = @dbname

	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
		exec sp_getmessage 17981, @msg output
		print @msg
		return (1)
	end
	/*
	** Note that defpraud in sysdatabases are default values for future
	** new sprocs.  We still need to update the in-memory audit option.
	*/
	select @returncode = audit_option(203, @aud_val, 0, db_id(@dbname))

	if (@returncode = 1) or (@returncode = 0)
		commit transaction
	else
		rollback transaction

end	/* if @default = 1 */
else
begin	/* regular non-default case */
	/*
	** If no args provided or the first and only provided arg is 'all',
	** 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 only a sproc/trigger name was provided, display the auditing
	** status of that particular sproc/trigger.  Also, check for the
	** possibility of a system stored procedure.
	*/
	if ((@owner is NULL and substring(@procname, 1, 3) = "sp_") or
	    (@owner = "dbo" and substring(@procname, 5, 3) = "sp_"))
		select @sysproc = 1
	else
		select @sysproc = 0

	if @ofb is NULL
	begin
		/*
		** Begin "reporting" block.  We'll exit this proc at the end
		** of this block.
		*/
		select @all = 0
		if ((@procname is NULL) or
		    (lower(ltrim(rtrim(@procname))) = "all"))
		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', 'P')) = 0
			begin
				/*
				** 18009, "No procs/triggers currently have 
				**	   auditing enabled.
				*/
				exec sp_getmessage 18009, @msg output
				print @msg
				return (0)
			end
			select @objid = min(id) from sysobjects where
				audflags != 0 and type in ('TR', 'P')
			select @procname = user_name(uid) + '.' + name from
				sysobjects where id = @objid
			select @all = 1
		end
		/*
		** Begin the loop.  Break if an procname was provided, or we've
		** run out of procs/triggers with auditing enabled.
		*/
		while (1 = 1)
		begin
			/*
			** First reset rowcount which may have been set to 1
			** below.
			*/
			set rowcount 0
			/*
			** 17978, "'%1!' has the following auditing
			**	   options enabled:"
			*/
			print ""
			print ""
			exec sp_getmessage 17978, @msg output
			print @msg, @procname
			/*	
			** Get text description of currently enabled
			** sproc/trigger auditing.  Make sure we don't have
			** to explicitly specify master.dbo.sysobjects to
			** find a system sproc
			*/
			if (@sysproc = 0)
				select a.name from master.dbo.spt_values a,
				    sysobjects b where a.type = 'M' and
				    a.number != -1 and b.id = @objid and
				    (a.number & b.audflags) != 0
			else
				select a.name from master.dbo.spt_values a,
				    master.dbo.sysobjects b where a.type = 'M'
				    and a.number != -1 and b.id = @objid and
				    (a.number & b.audflags) != 0
	
			if (@all = 0) or
			   ((select count(id) from sysobjects
			     where id > @objid and audflags != 0 and
			     type in ('TR','P')) = 0)
				break
			else
			begin
				/*
				** Set rowcount to 1 so we only get the next
				** procname.
				*/
				set rowcount 1
				select @objid = id,
					@procname = user_name(uid) + '.' + name
					from sysobjects
					where id > @objid and
					audflags != 0 and type in ('TR','P')
					order by id
			end
		end	/* while (1 = 1) */

		/* End of "reporting" block". */
		return (0)
	end

	if @procname is NULL
	begin
		/* 18010, "A sproc/trigger name or 'all' must be provided." */
		exec sp_getmessage 18010, @msg output
		print @msg
		return (1)
	end

	/*
	** If we reach here, we'll be setting auditing flags.  We need
	** another loop here in case 'all' was specified.
	*/
	select @all = 0
	if (lower(ltrim(rtrim(@procname))) = "all")
	begin
		select @all = 1
		select @objid = min(id) from sysobjects
		    where type in ('TR', 'P')
		select @procname = user_name(uid) + '.' + name from sysobjects
		    where id = @objid
	end
	/*
	** Begin the loop.  Break if a real procname was provided, or there
	** aren't any left.
	*/
	while (1 = 1)
	begin
		/* First reset rowcount which may have been set to 1 below.  */
		set rowcount 0

		/* Get the old audflags value. */
		select @aud_val = audflags from sysobjects where id = @objid

		/* If not found and it was a sysproc, find it in master */
		if (@@rowcount != 1 and @sysproc = 1)
		begin
			select @aud_val = audflags from master.dbo.sysobjects
				where id = @objid
			/*
			** Set @sysproc to a new value to let the update know
			** that it should update master.dbo.sysobjects.
			*/
			select @sysproc = 2
		end
		/*
		** If audflags was NULL, it hasn't been touched since upgrade.
		** Set it to 0 before operating on it with bitwise operators.
		*/
		if (@aud_val is NULL)
			select @aud_val = 0

		/* Setup new object auditing flags. */
		if @ofb = "off"
			select @aud_val = @aud_val & @opvalue 
		else
			select @aud_val = @aud_val | @opvalue

		/* Put in the new flags value. */

		begin transaction
		if @sysproc = 2
			update master.dbo.sysobjects set audflags = @aud_val
				where id = @objid

		else
			update sysobjects set audflags = @aud_val
				where id = @objid


		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
			exec sp_getmessage 17981, @msg output
			print @msg
			return (1)
		end

		/* Also update the audit option in the server memory.  */
		select @returncode =
			audit_option(204, @aud_val, @objid, db_id())

		if (@returncode = 1) or (@returncode = 0)
			commit transaction
		else
			rollback transaction

		/* Check the @returncode here and exit on error. */
		if (@returncode < 0)
		begin
			/*
			** 17984 "Error updating the audit flags in memory.
			**	  This is a system error.  Contact an
			**	  SA-role user."
			*/
        		exec sp_getmessage 17984, @msg output
			print @msg
		        return (1)
		end

		if (@all = 0) or
		   ((select count(id) from sysobjects where id > @objid and
		     type in ('TR','P')) = 0)
			break
		else
		begin
			/*
			** Set rowcount to 1 so we only get the next
			** sproc/trigger.
			*/
			set rowcount 1
			select @objid = id from sysobjects
				where id > @objid and type in ('TR','P')
				order by id
		end
	end	/* while (1 = 1) */
end	/* else case of if @default_table_view = 1 */

/* Check the @returncode of audit_option builtin. */
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
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
else
begin
	/*
	** 17984 "Error updating the audit flags in memory.  This is a system
	**	  error.  Contact an SA-role user."
	*/
        exec sp_getmessage 17984, @msg output
	print @msg
        return (1)
end
go
exec sp_procxmode 'sp_auditsproc', 'anymode'
go
grant execute on sp_auditsproc 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