instcat.sql

来自「VB6.0安装软件.代码和安装软件一起.用起来很不错.」· SQL 代码 · 共 2,211 行 · 第 1/5 页

    /*
    ** We need to create a table which will contain a row for every row to
    ** be returned to the client.
    */

	create table #column_priv1(
		COLUMN_NAME 			varchar(32) NOT NULL,
		grantor 				smallint NOT NULL,
		grantee 				smallint NOT NULL,
		select_privilege		bit,
		select_grantable		bit,
		insert_privilege		bit,
		insert_grantable		bit,
		update_privilege		bit,
		update_grantable		bit,
		references_privilege	bit,
		references_grantable	bit,
		uid 					smallint NOT NULL,
		gid 					smallint NOT NULL)

/*
** insert a row for the table owner (who has all permissions)
*/
	select @owner_uid = (
		select uid
		from sysobjects
		where id = @table_id)

	if (charindex('6.00', @@version) > 0)
	begin
		insert into #column_priv1
			select
				c.name,
				u.uid,
				@owner_uid,
				0,
				1,
				0,
				1,
				0,
				1,
				0,
				1,
				@owner_uid,
				0
			from syscolumns c, sysusers u
			where id = @table_id
				and c.number = 0
				and u.suid = 1		/* grantor is dbo of database */
	end
	else
	begin
		insert into #column_priv1
			select
				c.name,
				u.uid,
				@owner_uid,
				0,
				1,
				0,
				1,
				0,
				1,
				0,
				0,
				@owner_uid,
				0
			from syscolumns c, sysusers u
			where id = @table_id
				and c.number = 0
				and u.suid = 1		/* grantor is dbo of database */
	end
/*
** now stick in a row for every column for every user in the database
** we will need to weed out those who have no permissions later
** (and yes this is a cartesion product: the uid field in sysprotects
** can also have a group id, in which case we need to extend those
** privileges to all group members).
*/

    insert into #column_priv1
		select distinct
			c.name,
			o.uid,
			u.uid,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			u.uid,
			u.gid
		from sysusers u, syscolumns c, sysobjects o
		where o.id = @table_id
			and c.id = o.id
			and c.number = 0
			and u.gid <> u.uid
			and u.uid <> @owner_uid

    /*
    ** we need to create another temporary table to contain all the various
    ** protection information for the table in question
    */
	create table #protects (
				uid 		smallint NOT NULL,
				grantor		smallint NOT NULL,
				action		tinyint NOT NULL,
				protecttype tinyint NOT NULL,
				name		varchar(32) NOT NULL)

    insert into #protects
		select
			p.uid,
			p.uid,
			p.action,
			p.protecttype,
			isnull(col_name(id, c.number), '~All')
			from
				sysprotects p,
				master.dbo.spt_values c,
				master.dbo.spt_values a,
				master.dbo.spt_values b
			where
				convert(tinyint, substring(isnull(p.columns, 0x1), c.low, 1))
					& c.high <> 0
					and c.number <= (
						select count(*)
						from syscolumns
						where id = @table_id)
				and c.type = 'P'
				and a.type = 'T'
				and a.number = p.action
				and p.action in (193,195,197,26)
				and b.type = 'T'
				and b.number = p.protecttype
				and p.id = @table_id
				and p.uid between @low and @high


    update #column_priv1
	set select_privilege = 1
		from #protects p
	where
		p.protecttype = 205
		and p.action = 193
		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 = 193
				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 insert_privilege = 1
		from #protects p
	where
		p.protecttype = 205
		and p.action = 195
		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 = 195
				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 update_privilege = 1
		from #protects p
	where
		p.protecttype = 205
		and p.action = 197
		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_privilege = 1
		from #protects p
	where
		p.protecttype = 205
		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))

    update #column_priv1
	set select_grantable = 1
		from #protects p
	where
		p.protecttype = 204
		and p.action = 193
		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 = 193
				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 insert_grantable = 1
		from #protects p
	where
		p.protecttype = 204
		and p.action = 195
		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 = 195
				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 update_grantable = 1
		from #protects p
	where
		p.protecttype = 204
		and p.action = 197
		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)
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 = '%'