Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_ORACheckAdminPrivileges

  No additional text.


Syntax


-- Name:
--		sp_ORACheckAdminPrivileges

-- Description:
--		Validate admin privileges needed for publishing (Oracle specific)

-- Inputs:
--		@publisher		== publisher name

-- Returns:
--		Return code (0 for success, 1 for failure)

-- Security:
--		Internal
-- Requires Certificate signature for catalog access

-- Notes:
--		Used by sp_ORAaddpublisher


CREATE PROCEDURE sys.sp_ORACheckAdminPrivileges
(
	@publisher			sysname
)
AS
BEGIN
	DECLARE @retcode			int
	DECLARE @pubid				int
	DECLARE @grantsCheck		int
	DECLARE @InsColumnList		nvarchar(200)
	DECLARE @SelectColumnList	nvarchar(200)

	-- Get publisher ID
	SELECT	@pubid = srvid
	FROM	master.dbo.sysservers
	WHERE	UPPER(srvname collate database_default) = UPPER(@publisher)
	
	IF @pubid IS NULL
	BEGIN
		RAISERROR(21600, 16, -1, @publisher)
		RETURN (1)
	END

	-- Define sp_ORAremotequery support table
	create table #hquery
	(
		seq	int identity(2,1),
		cmd	nvarchar(4000)
	)

	-- Define #grants support table
	create table #grants
	(
		gcheck	nchar(3),
		value	int
	)

	
	-- Populate a temp table with the permission checks
	
	SELECT @InsColumnList    = 'gcheck, value'
	SELECT @SelectColumnList = '*'

	-- Check to make certain that all needed privileges have been granted
	INSERT INTO #hquery(cmd) VALUES ('SELECT ''ALL'', COUNT(*) ')
	INSERT INTO #hquery(cmd) VALUES ('FROM SESSION_PRIVS SP ')
	INSERT INTO #hquery(cmd) VALUES ('WHERE SP.PRIVILEGE IN ')
	INSERT INTO #hquery(cmd) VALUES ('(''CREATE PROCEDURE'', ')
	INSERT INTO #hquery(cmd) VALUES ('''CREATE PUBLIC SYNONYM'', ')
	INSERT INTO #hquery(cmd) VALUES ('''CREATE SEQUENCE'', ')
	INSERT INTO #hquery(cmd) VALUES ('''DROP PUBLIC SYNONYM'', ')
	INSERT INTO #hquery(cmd) VALUES ('''CREATE SESSION'', ')
	INSERT INTO #hquery(cmd) VALUES ('''CREATE TABLE'', ')
	INSERT INTO #hquery(cmd) VALUES ('''CREATE VIEW'', ')
	INSERT INTO #hquery(cmd) VALUES ('''CREATE ANY TRIGGER'') ')
	INSERT INTO #hquery(cmd) VALUES ('UNION ')
	-- Check to make certain that some grants were made explicitly to admin user or PUBLIC
	INSERT INTO #hquery(cmd) VALUES ('SELECT ''EXP'', COUNT(*) ')
	INSERT INTO #hquery(cmd) VALUES ('FROM USER_SYS_PRIVS RSP ')
	INSERT INTO #hquery(cmd) VALUES ('WHERE RSP.PRIVILEGE IN ')
	INSERT INTO #hquery(cmd) VALUES ('(''CREATE TABLE'', ')
	INSERT INTO #hquery(cmd) VALUES ('''CREATE VIEW'', ')
	INSERT INTO #hquery(cmd) VALUES ('''CREATE ANY TRIGGER'') ')
	INSERT INTO #hquery(cmd) VALUES ('AND (RSP.USERNAME = USER OR RSP.USERNAME = ''PUBLIC'') ')
	INSERT INTO #hquery(cmd) VALUES ('UNION ')
	-- Check to for allocated quota in some tablespace	
	INSERT INTO #hquery(cmd) VALUES ('SELECT ''QTA'', COUNT(*) ')
	INSERT INTO #hquery(cmd) VALUES ('FROM USER_TS_QUOTAS UQ ')
	INSERT INTO #hquery(cmd) VALUES ('WHERE UQ.MAX_BYTES IS NOT NULL ')
	INSERT INTO #hquery(cmd) VALUES ('AND NOT UQ.MAX_BYTES = 0 ')
	INSERT INTO #hquery(cmd) VALUES ('UNION ')
	-- Check for 'PUBLIC' has been explicitly granted 'UNLIMITED TABLESPACE'
	INSERT INTO #hquery(cmd) VALUES ('SELECT ''UPT'', COUNT(*) ')
	INSERT INTO #hquery(cmd) VALUES ('FROM USER_SYS_PRIVS RSP ')
	INSERT INTO #hquery(cmd) VALUES ('WHERE RSP.PRIVILEGE = ''UNLIMITED TABLESPACE'' ')
	INSERT INTO #hquery(cmd) VALUES ('AND (RSP.USERNAME = USER OR RSP.USERNAME = ''PUBLIC'')')

	EXEC @retcode = sp_ORAremotequery
					@Server				= @publisher,
					@SelectColumnList	= @SelectColumnList,
					@InsTable			= '#grants',
					@InsColumnList		= @InsColumnList

	IF  (@@error <> 0 OR @retcode <> 0)
	BEGIN
		RAISERROR (21683, 16, -1, @publisher)
		RETURN (1)
	END
	
	-- Verify grant checks
	-- 1.  All required privileges (8)
	-- 2.  Explicit privileges (3)
	-- 3.  Quota space or unlimited tablespace
	SELECT	@grantsCheck =	CASE
								WHEN	(G1.value = 8) AND
										(G2.value = 3) AND
										(
											(G3.value > 0) OR
											(G4.value = 1)
										) THEN 1
								ELSE	0
							END
	FROM	#grants G1,
			#grants G2,
			#grants G3,
			#grants G4
	WHERE	G1.gcheck = 'ALL'
	  AND	G2.gcheck = 'EXP'
	  AND	G3.gcheck = 'QTA'
	  AND	G4.gcheck = 'UPT'

	IF @grantsCheck <> 1
	BEGIN
		RAISERROR (21684, 16, -1, @publisher)
		RETURN (1)
	END

	RETURN (0)
END

 
Last revision 2008RTM
See also

  sp_CheckOracleAdminPrivileges (Procedure)
sp_MSdrop_repltran (Procedure)
sp_ORAaddpublisher (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash