跨不同数据库授予权限（模式）

I'm securing the DB by only allowing interaction with the DB through a series of Sprocs; pretty common fare.

我通过一系列Sprocs只允许与DB交互来保护数据库;非常普通的票价。

I've dug up and modified a script which loops through and assigns the user EXECUTE permission for all non-system SProcs. It works a treat except that I'd ideally like to add it to the Master DB so that I can easily use it for any subsequent projects. Yes, I could save simple as a .sql file but I'd prefer it this way.

我挖出并修改了一个循环遍历的脚本,并为所有非系统SProc分配用户EXECUTE权限。除了我理想地将它添加到主数据库以便我可以轻松地将它用于任何后续项目之外,它还可以使用它。是的,我可以将简单保存为.sql文件,但我更喜欢这种方式。

The problem is that I don't know how to dynamically refer to objects in another DB. For example, I can easily query on MyDB.dbo.INFORMATION_SCHEMA.ROUTINES, but if the DB name is dynamic (e.g. @MyDBName), how can I query the objects in this DB?

问题是我不知道如何动态引用另一个DB中的对象。例如,我可以轻松查询MyDB.dbo.INFORMATION_SCHEMA.ROUTINES,但如果数据库名称是动态的(例如@MyDBName),我该如何查询此数据库中的对象?

Edit: Thanks to the posters below, I now have a working solution:

编辑:感谢下面的海报,我现在有了一个有效的解决方案:

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[spGrantExec] 
@User sysname,
@DB varchar(50),
@Target varchar(50)
AS 
/*---------------------------- SQL 2005 + -------------------------------*/

SET NOCOUNT ON

-- 1 - Variable declarations
DECLARE @SQL varchar(8000)

-- 2 - Create temporary table
Set @SQL =
'USE @DB

DECLARE @MAXOID int
DECLARE @OwnerName varchar(128)
DECLARE @ObjectName varchar(128)
DECLARE @CMD1 varchar(8000)

CREATE TABLE #StoredProcedures
(OID int IDENTITY (1,1),
StoredProcOwner varchar(128) NOT NULL,
StoredProcName varchar(128) NOT NULL)

-- 3 - Populate temporary table

INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)
SELECT ROUTINE_SCHEMA, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_NAME LIKE ''' + @Target + '%''
 AND ROUTINE_TYPE = ''PROCEDURE''

-- 4 - Capture the @MAXOID value
SELECT @MAXOID = MAX(OID) FROM #StoredProcedures

-- 5 - WHILE loop
WHILE @MAXOID > 0
 BEGIN 

    -- 6 - Initialize the variables
    SELECT @OwnerName = StoredProcOwner,
    @ObjectName = StoredProcName
    FROM #StoredProcedures
    WHERE OID = @MAXOID

    -- 7 - Build the string

    SELECT @CMD1 = ''GRANT EXEC ON '' + ''['' + @OwnerName + '']'' + ''.'' + ''['' + @ObjectName + '']'' + '' TO @user''

    -- 8 - Execute the string
    Print @CMD1
    EXEC(@CMD1)

    -- 9 - Decrement @MAXOID
    SET @MAXOID = @MAXOID - 1
 END

-- 10 - Drop the temporary table
DROP TABLE #StoredProcedures'

Set @SQL = REPLACE(REPLACE(REPLACE(@SQL, '@DB', @DB), '@User', @User), '@Target', @Target)
--Select @SQL
--Print @SQL
Exec (@SQL)
SET NOCOUNT OFF

3 个解决方案

#1

Similiar to @Cade's answer, the way to do this is to use dynamic sql. Before each call to a database table, add '@DbName.' Then replace the @DbName with the actual database name (the database name can't be passed as a variable in SQL, so you have to do the replace).

与@ Cade的答案类似,这样做的方法是使用动态sql。在每次调用数据库表之前,添加“@DbName”。然后将@DbName替换为实际的数据库名称(数据库名称不能作为SQL中的变量传递,因此您必须执行替换)。

Also Cursors are normally considered evil for performance reasons, however using one in this case makes sense. For one, it would greatly simplify the procedure, plus since you're only going to run this once during application updates, you probably won't notice a performance hit, even if it added an extra second or two (which I doubt it would add anywhere near that much).

此外,由于性能原因,游标通常被认为是邪恶的,但在这种情况下使用游标是有道理的。首先,它会大大简化程序,而且由于你只是在应用程序更新期间只运行一次,你可能不会注意到性能损失,即使它增加了一两秒(我怀疑它会添加任何附近的地方)。

ALTER PROCEDURE [dbo].[spGrantExec]
@User SysName,
@DbName VarChar(512)
AS
BEGIN
DECLARE @Sql VarChar(1024)


SET @Sql = 'DECLARE @OwnerName varchar(128) DECLARE @ObjectName varchar(128) DECLARE @Cmd1 VarChar(128) DECLARE ProcCursor CURSOR FOR SELECT ROUTINE SCHEMA, ROUTINE NAME FROM @DbName.INFORMATION SCHEMA.ROUTINES WHERE ROUTINENAME NOT LIKE ''dt %'' AND ROUTINE TYPE = ''PROCEDURE'' OPEN ProcCursor FETCH NEXT FROM ProcCursor INTO @OwnerName, @ObjectName WHILE @@FETCH STATUS = 0 BEGIN SET @CMD1 = ''GRANT EXEC ON '' + ''['' + @OwnerName + '']'' + ''.'' + ''['' + @ObjectName + '']'' + '' TO '' + ''@user'' EXEC (@CMD1)
SET @Sql ='DECLARE @OwnerName varchar(128)DECLARE @ObjectName varchar(128)DECLARE @ Cmd1 VarChar(128)DECLARE PROCursor CURSOR for SELECT ROUTINE SCHEMA,ROUTINE NAME FROM @ DbName.INFORMATION SCHEMA.ROUTINES ROUTINENAME NOT LIKE'' DT%'和日常TYPE = '' 过程 '' OPEN ProcCursor FETCH NEXT FROM ProcCursor INTO @ownername,@ObjectName WHILE @@ FETCH STATUS = 0 BEGIN SET @ CMD1 = '' GRANT EXEC ON '' + ''[ '' + @OwnerName +'''''+''。''+''[''+ @ ObjectName +'''''+''TO''''''@''''''''''''''''''''''''''''''''''''

FETCH NEXT FROM ProcCursor INTO @OwnerName, @ObjectName END CLOSE ProcCursor DEALLOCATE ProcCursor '
FETCH NEXT FROM ProcCursor INTO @OwnerName,@ ObjectName END CLOSE ProcCursor DEALLOCATE ProcCursor'

SET @Sql = Replace(Replace(@Sql, '@DbName', @DbName), '@user', @User) EXEC (@Sql)
SET @Sql =替换(替换(@Sql,'@ DigName',@ DigName),'@ user',@ User)EXEC(@Sql)

END 

You can call this using: EXEC [spGrantExec] 'bob', 'Northwind'

Sorry the spacing is a little off in the sp. Developed using Sql 2005.

对不起,sp的间距有点偏。使用Sql 2005开发。

#2

You can use the double exec technique.

您可以使用双执行技术。

In your case, instead of just:

在您的情况下,而不仅仅是:

EXEC(@CMD1)

You would have:

你将会拥有:

SET @CMD1 =
    'USE OtherDatabase;
    EXEC (''' + REPLACE(@CMD1, '''', '''''') + ''')'
EXEC(@CMD1)

#3

I found another technique, which I think is cleaner:

我找到了另一种技术,我认为它更清洁:

SELECT @sql = 'CREATE VIEW ...'
SELECT @sp_executesql = quotename(@dbname) + '..sp_executesql'
EXEC @sp_executesql @sql 

This relies on setting the database context by calling sp_executesql in the other database (just like one could call an SP in any database).

这依赖于通过在另一个数据库中调用sp_executesql来设置数据库上下文(就像在任何数据库中调用SP一样)。

In your case it would be equivalent to:

在你的情况下,它将相当于:

SELECT @sp_executesql = quotename(@dbname) + '..sp_executesql'
EXEC @sp_executesql @CMD1