hotfix.sql

This project is to design the automated system for the inventory of Al-Ahmad Cotton and Oil Mills si

		and (p.name = #column_priv1.COLUMN_NAME
			or name = '~All')
		and (p.uid = 0
			or p.uid = #column_priv1.gid
			or p.uid = #column_priv1.uid)
		and not exists (
			select * from #protects
			where
				protecttype = 206
				and action = 197
				and (name = #column_priv1.COLUMN_NAME
					or name = '~All')
				and ( uid = 0
					or uid = #column_priv1.gid
					or uid = #column_priv1.uid))

    update #column_priv1
	set references_grantable = 1
		from #protects p
	where
		p.protecttype = 204
		and p.action = 26
		and (p.name = #column_priv1.COLUMN_NAME
			or name = '~All')
		and (p.uid = 0
			or p.uid = #column_priv1.gid
			or p.uid = #column_priv1.uid)
		and not exists (
			select * from #protects
			where
				protecttype = 206
				and action = 26
				and (name = #column_priv1.COLUMN_NAME
					or name = '~All')
				and ( uid = 0
					or uid = #column_priv1.gid
					or uid = #column_priv1.uid))

	create table #column_priv2(
		COLUMN_NAME 	varchar(32) NOT NULL,
		grantor 		smallint NULL,
		grantee 		smallint NOT NULL,
		PRIVILEGE		varchar(32) NOT NULL,
		IS_GRANTABLE	varchar(3) NULL)

	insert into #column_priv2
		select
			COLUMN_NAME,
			grantor,
			grantee,
			'SELECT',
			'NO'
		from #column_priv1
		where select_privilege = 1 and select_grantable	= 0

	insert into #column_priv2
		select
			COLUMN_NAME,
			grantor,
			grantee,
			'INSERT',
			'NO'
		from #column_priv1
		where insert_privilege = 1 and insert_grantable = 0

	insert into #column_priv2
		select
			COLUMN_NAME,
			grantor,
			grantee,
			'UPDATE',
			'NO'
		from #column_priv1
		where update_privilege = 1 and update_grantable = 0

	insert into #column_priv2
		select
			COLUMN_NAME,
			grantor,
			grantee,
			'REFERENCES',
			'NO'
		from #column_priv1
		where references_privilege = 1 and references_grantable = 0

	insert into #column_priv2
		select
			COLUMN_NAME,
			grantor,
			grantee,
			'SELECT',
			'YES'
		from #column_priv1
		where select_grantable = 1

	insert into #column_priv2
		select
			COLUMN_NAME,
			grantor,
			grantee,
			'INSERT',
			'YES'
		from #column_priv1
		where insert_grantable = 1

	insert into #column_priv2
		select
			COLUMN_NAME,
			grantor,
			grantee,
			'UPDATE',
			'YES'
		from #column_priv1
		where update_grantable = 1

	insert into #column_priv2
		select
			COLUMN_NAME,
			grantor,
			grantee,
			'REFERENCES',
			'YES'
		from #column_priv1
		where references_grantable = 1

	select
		convert(varchar(32),db_name()) TABLE_QUALIFIER,
		convert(varchar(32),user_name(@owner_uid)) TABLE_OWNER,
		@table_name TABLE_NAME,
		COLUMN_NAME,
		convert(varchar(32),user_name(grantor)) GRANTOR,
		convert(varchar(32),user_name(grantee)) GRANTEE,
		PRIVILEGE,
		IS_GRANTABLE
	from #column_priv2
	where COLUMN_NAME like @column_name
	order by 4, 7
go

if (charindex('6.50', @@version) = 0 and
	charindex('7.00', @@version) = 0 and
	charindex('8.00', @@version) = 0)
begin
	print ''
	print ''
	print 'Warning:'
	print 'you are installing the stored procedures '
	print 'on a pre 6.50 SQL Server.'
	print 'Ignore the following errors.'
end
else
	drop proc sp_column_privileges
go

/*	Procedure for 6.50 server */
CREATE PROCEDURE sp_column_privileges (
			@table_name 		varchar(32),
			@table_owner		varchar(32) = null,
			@table_qualifier	varchar(32) = null,
			@column_name		varchar(96) = null)	/* 3*32 */
as

	declare @table_id	 int

	if @column_name is null /*	If column name not supplied, match all */
		select @column_name = '%'

	if @table_qualifier is not null
    begin
		if db_name() <> @table_qualifier
		begin	/* If qualifier doesn't match current database */
			raiserror (15250, -1,-1)
			return
		end
    end
	if @table_owner is null
	begin	/* If unqualified table name */
		select @table_id = object_id(@table_name)
    end
    else
	begin	/* Qualified table name */
		select @table_id = object_id(@table_owner + '.' + @table_name)
    end

	select
		convert(varchar(32),db_name()) TABLE_QUALIFIER,
		convert(varchar(32),user_name(o.uid)) TABLE_OWNER,
		@table_name TABLE_NAME,
		convert(varchar(32),c.name) COLUMN_NAME,
		convert(varchar(32),user_name(p.grantor)) GRANTOR,
		convert(varchar(32),user_name(u.uid)) GRANTEE,
		convert (varchar(32),case p.action
			 when 193 then 'SELECT'
			 when 195 then 'INSERT'
			 when 197 then 'UPDATE'
			 else 'REFERENCES'
		end) PRIVILEGE,
		convert (varchar(3),case when p.protecttype = 205 then 'NO'
			else 'YES'
		end) IS_GRANTABLE
	from sysprotects p, sysobjects o, sysusers u, master.dbo.spt_values v, syscolumns c
	where
		c.id = @table_id
		and c.name like @column_name
		and c.id = p.id
		and c.id = o.id
		and case substring(p.columns, 1, 1) & 1
				when NULL then 255	/* all columns have permission */
				when 0 then convert(tinyint, substring(p.columns, v.low, 1))
				else (~convert(tinyint, isnull(substring(p.columns, v.low, 1),0)))
			end
			& v.high <> 0			/* permission applies to this column */
		and v.number <= (select count(*) from syscolumns
			where id = @table_id)	/* ranges from 1 to # of columns in table */
		and v.type = 'P'
		and v.number = c.colid
			/* expand groups */
		and ((p.uid = u.uid and u.uid <> u.gid) or
			 (p.uid = u.gid and u.uid <> u.gid))
		and p.protecttype <> 206	/* only grant rows */
		and p.action in (26,193,195,197)
		and o.uid <> u.uid			/* no rows for owner */
		and not exists (			/* exclude revoke'd privileges */
			select *
			from sysprotects p1
			where
				p1.protecttype = 206
				and p1.action = p.action
				and p1.id = p.id
				and p1.uid = u.uid
				and case substring(p1.columns, 1, 1) & 1
						when NULL then 255	/* all columns have permission */
						when 0 then convert(tinyint, substring(p1.columns, v.low, 1))
                                        	else (~convert(tinyint,isnull(substring(p.columns, v.low, 1),0)))
					end
					& v.high <> 0)			/* permission applies to this column */
	union
	select	/*	Add rows for table owner */
		convert(varchar(32),db_name()) TABLE_QUALIFIER,
		convert(varchar(32),user_name(o.uid)) TABLE_OWNER,
		@table_name TABLE_NAME,
		convert(varchar(32),col_name(@table_id, c.colid)) COLUMN_NAME,
		convert(varchar(32),user_name(u.uid)) grantor,
		convert(varchar(32),user_name(o.uid)) grantee,
		convert (varchar(32),case v.number
			when 193 then 'SELECT'
			when 195 then 'INSERT'
			when 197 then 'UPDATE'
			else 'REFERENCES'
		end) PRIVILEGE,
		convert(varchar(3),'YES') IS_GRANTABLE
	from sysobjects o, master.dbo.spt_values v, sysusers u, syscolumns c
	where
		c.id = @table_id
		and c.name like @column_name
		and c.id = o.id
		and u.uid = 1		/* grantor is dbo of database */
		and v.type = 'P'	/* cross product to get all exposed privileges */
		and v.number in (26,193,195,197)
		and not exists (	/* exclude revoke'd privileges */
			select *
			from sysprotects p1
			where
				p1.protecttype = 206
				and p1.action = v.number
				and p1.id = o.id
				and p1.uid = o.uid)
	order by 4, 7
go



if (charindex('7.00', @@version) = 0 and
	charindex('8.00', @@version) = 0)
begin
	print ''
	print ''
	print 'Warning:'
	print 'you are installing the stored procedures '
	print 'on a pre 8.0 SQL Server.'
	print 'Ignore the following errors.'
end
else
	drop proc sp_column_privileges
go

/*	Procedure for 8.0 server */
CREATE PROCEDURE sp_column_privileges (
			@table_name 		sysname,
			@table_owner		sysname = null,
			@table_qualifier	sysname = null,
			@column_name		nvarchar(384) = null)	/* 3*128 */
as

	declare @table_id	 int

	if @column_name is null /*	If column name not supplied, match all */
		select @column_name = '%'

	if @table_qualifier is not null
    begin
		if db_name() <> @table_qualifier
		begin	/* If qualifier doesn't match current database */
			raiserror (15250, -1,-1)
			return
		end
    end
	if @table_owner is null
	begin	/* If unqualified table name */
		select @table_id = object_id(quotename(@table_name))
    end
    else
	begin	/* Qualified table name */
		if @table_owner = N''
		begin	/* If empty owner name */
			select @table_id = 0
		end
		else
		begin
			select @table_id = object_id(quotename(@table_owner) +
				'.' + quotename(@table_name))
		end
    end

	select
		convert(sysname,db_name()) TABLE_QUALIFIER,
		convert(sysname,user_name(o.uid)) TABLE_OWNER,
		@table_name TABLE_NAME,
		convert(sysname,c.name) COLUMN_NAME,
		convert(sysname,user_name(p.grantor)) GRANTOR,
		convert(sysname,user_name(u.uid)) GRANTEE,
		convert (varchar(32),case p.action
			 when 193 then 'SELECT'
			 when 195 then 'INSERT'
			 when 197 then 'UPDATE'
			 else 'REFERENCES'
		end) PRIVILEGE,
		convert (varchar(3),case when p.protecttype = 205 then 'NO'
			else 'YES'
		end) IS_GRANTABLE
	from sysprotects p, sysobjects o, sysusers u, master.dbo.spt_values v, syscolumns c, sysmembers m
	where
		c.id = @table_id
		and c.name like @column_name
		and c.id = p.id
		and c.id = o.id
		and case substring(p.columns, 1, 1) & 1
				when NULL then 255	/* all columns have permission */
				when 0 then convert(tinyint, substring(p.columns, v.low, 1))
				else (~convert(tinyint, isnull(substring(p.columns, v.low, 1),0)))
			end
			& v.high <> 0			/* permission applies to this column */
		and v.number <= (select count(*) from syscolumns
			where id = @table_id)	/* ranges from 1 to # of columns in table */
		and v.type = N'P'
		and v.number = c.colid
			/* expand groups - AKUNDONE: only 1 level of group unrolling here. Need more?? */
		and (u.uid > 0 and u.uid < 16384)
		and ((p.uid = u.uid) or 
			 (p.uid = m.groupuid and u.uid = m.memberuid))
		and p.protecttype <> 206	/* only grant rows */
		and p.action in (26,193,195,197)
		and o.uid <> u.uid			/* no rows for owner */
		and not exists (			/* exclude revoke'd privileges */
			select *
			from sysprotects p1
			where
				p1.protecttype = 206
				and p1.action = p.action
				and p1.id = p.id
				and p1.uid = u.uid
				and case substring(p1.columns, 1, 1) & 1
						when NULL then 255	/* all columns have permission */
						when 0 then convert(tinyint, substring(p1.columns, v.low, 1))
                                        	else (~convert(tinyint,isnull(substring(p.columns, v.low, 1),0)))
					end
					& v.high <> 0)			/* permission applies to this column */
	union
	select	/*	Add rows for table owner */
		convert(sysname,db_name()) TABLE_QUALIFIER,
		convert(sysname,user_name(o.uid)) TABLE_OWNER,
		@table_name TABLE_NAME,
		convert(sysname,col_name(@table_id, c.colid)) COLUMN_NAME,
		convert(sysname,user_name(u.uid)) grantor,
		convert(sysname,user_name(o.uid)) grantee,
		convert (varchar(32),case v.number
			when 193 then 'SELECT'
			when 195 then 'INSERT'
			when 197 then 'UPDATE'
			else 'REFERENCES'
		end) PRIVILEGE,
		convert(varchar(3),'YES') IS_GRANTABLE
	from sysobjects o, master.dbo.spt_values v, sysusers u, syscolumns c
	where
		c.id = @table_id
		and c.name like @column_name
		and c.id = o.id
		and u.uid = 1		/* grantor is 'dbo' of database */
		and v.type = N'P'	/* cross product to get all exposed privileges */
		and v.number in (26,193,195,197)
		and not exists (	/* exclude revoke'd privileges */
			select *
			from sysprotects p1
			where
				p1.protecttype = 206
				and p1.action = v.number
				and p1.id = o.id
				and p1.uid = o.uid)
	order by 4, 7
go


grant execute on sp_column_privileges to public
go

dump tran master with no_log
go

print 'creating sp_columns'
go

/*	Procedure for pre-6.0 server */
CREATE PROCEDURE sp_columns (
				 @table_name		varchar(96),
				 @table_owner		varchar(96) = null,
				 @table_qualifier	varchar(32) = null,
				 @column_name		varchar(96) = null,
				 @ODBCVer			int = 2)
AS
	DECLARE @full_table_name varchar(193)
	DECLARE @table_id        int

	if @ODBCVer <> 3
		select @ODBCVer = 2
	if @column_name is null /*	If column name not supplied, match all */
		select @column_name = '%'
	if @table_qualifier is not null
	begin
		if db_name() <> @table_qualifier
		begin	/* If qualifier doesn't match current database */
			raiserror 20001 '~~Rush_5~~'
			return
		end
	end
	if @table_name is null
	begin	/*	If table name not supplied, match all */
		select @table_name = '%'
	end