Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_CheckOracleAdminPrivileges

  No additional text.


Syntax


-- Name:
--		sp_CheckOracleAdminPrivileges

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

-- Inputs:
--		@publisher		== publisher name
--		@login			== replication user login
--		@password		== replication user password
--		@provider		== Oracle OLEDB provider

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

-- Security:
--		internal

-- Notes:
--		Called by sp_ORAcheckpublisher to validate login authorizations.
--		Note:  If the logic in sp_ORACheckAdminPrivileges changes, make those
--		       same changes to this stored procedure.

CREATE PROCEDURE sys.sp_CheckOracleAdminPrivileges
(
	@publisher		sysname,
	@login			sysname,
	@password		sysname,
	@provider		sysname
)
AS
BEGIN

	SET NOCOUNT ON

	DECLARE	@command		nvarchar(MAX)
	DECLARE	@retcode		int
	DECLARE @grantsCheck		int

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

	-- Populate a temp table with the permission checks
	SELECT @command = N'INSERT INTO #grants SELECT * FROM OPENROWSET( ' + QUOTENAME(@provider,'''') +
		N' ,' + QUOTENAME(@publisher,'''') + N'; ' + QUOTENAME(@login,'''') +
		N'; ' + QUOTENAME(@password,'''') + N', ' +
		N'''SELECT ''''ALL'''', COUNT(*) ' +
		N'FROM SESSION_PRIVS SP ' +
		N'WHERE SP.PRIVILEGE IN ' +
		N'(''''CREATE PROCEDURE'''', ' +
		N' ''''CREATE PUBLIC SYNONYM'''', ' +
		N' ''''CREATE SEQUENCE'''', ' +
		N' ''''DROP PUBLIC SYNONYM'''', ' +
		N' ''''CREATE SESSION'''', ' +
		N' ''''CREATE TABLE'''', ' +
		N' ''''CREATE VIEW'''', ' +
		N' ''''CREATE ANY TRIGGER'''') ' +
		N'UNION ' +
		-- Check to make certain that some grants were made explicitly to admin user or PUBLIC
		N'SELECT ''''EXP'''', COUNT(*) ' +
		N'FROM USER_SYS_PRIVS RSP ' +
		N'WHERE RSP.PRIVILEGE IN ' +
		N'(''''CREATE TABLE'''', ' +
		N'''''CREATE VIEW'''', ' +
		N'''''CREATE ANY TRIGGER'''') ' +
		N'AND (RSP.USERNAME = USER OR RSP.USERNAME = ''''PUBLIC'''') ' +
		N'UNION ' +
		-- Check to for allocated quota in some tablespace	
		N'SELECT ''''QTA'''', COUNT(*) ' +
		N'FROM USER_TS_QUOTAS UQ ' +
		N'WHERE UQ.MAX_BYTES IS NOT NULL ' +
		N'AND NOT UQ.MAX_BYTES = 0 ' +
		N'UNION ' +
		-- Check for 'PUBLIC' has been explicitly granted 'UNLIMITED TABLESPACE'
		N'SELECT ''''UPT'''', COUNT(*) ' +
		N'FROM USER_SYS_PRIVS RSP ' +
		N'WHERE RSP.PRIVILEGE = ''''UNLIMITED TABLESPACE'''' ' +
		N'AND (RSP.USERNAME = USER OR RSP.USERNAME = ''''PUBLIC'''')'')'

	EXEC (@command)
	IF  @@error <> 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_MSdrop_repltran (Procedure)
sp_ORAcheckpublisher (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