允许登录运行存储过程而不能从表中选择 [英] Allow login to run stored procedure without being able to select from table

问题描述

我有一个带有两个数据库的 SQL Server:

I have a SQL Server with two databases:

• 数据库 1
• 数据库 2

登录MyLogin"仅对 Database2 具有读取权限.

Login 'MyLogin' has read access on Database2 only.

Database2 有如下存储过程:

Database2 has a stored procedure as follows:

CREATE PROCEDURE Get_LogData 
       @ProductID int
AS
BEGIN

    If Exists (Select (1) From Database2.dbo.Products Where ProductID = @ProductID)
    Select LogTimeStamp
         , ProductID
         , Description
    from Database1.dbo.Log Where ProductID = @ProductID
    Else
    Print 'Get_LogData: Unable to find ProductID ' + CAST(@ProductID AS VARCHAR)

END;
GO

BEGIN
    GRANT EXEC ON Get_LogData TO [MyLogin];
END;
GO

当MyLogin"尝试运行存储过程时，我们得到错误:

When 'MyLogin' trys to run the stored procedure, we get the error:

服务器主体MyLogin"无法访问数据库当前安全上下文中的Database1".

The server principal "MyLogin" is not able to access the database "Database1" under the current security context.

如何让MyLogin"能够运行此存储过程并从 Database1 获取数据，而不允许它们仅针对 Database1.Log 运行正常的 Select 查询

How do I allow 'MyLogin' the ability to run this stored proc and get the data from Database1, without allowing them to just run a normal Select query against Database1.Log

推荐答案

这是对 Ezlo 链接的答案的扩展 (SQL Server EXECUTE AS 故障)，我将介绍 在 SQL Server 中启用跨数据库访问.

This is expanding on the answer linked by Ezlo (SQL Server EXECUTE AS trouble), on which I'm going to cover Enabling Cross-Database Access in SQL Server.

首先，让我们设置一个快速测试来重现您现在遇到的问题:

Firstly, let's set up a quick test to replicate the issue you have right now:

--Create a couple of sample databases
CREATE DATABASE SampleDB1;
CREATE DATABASE SampleDB2;
GO

USE SampleDB2;
GO
--Create a sample table
CREATE TABLE dbo.SampleTable (ID int, Somestring varchar(25));
GO
USE SampleDB1;
GO
--Create a sample SP and User/Login;
CREATE PROC dbo.SomeProc AS

    SELECT ID,
           SomeString
    FROM SampleDB2.dbo.SampleTable;
GO
CREATE LOGIN SampleCredential WITH PASSWORD = 'abc123', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF, DEFAULT_LANGUAGE = BRITISH;
CREATE USER SampleCredential FOR LOGIN SampleCredential;

GRANT EXEC ON dbo.SomeProc TO SampleCredential;
GO

--Test
EXECUTE AS LOGIN = 'SampleCredential';
GO
--This will fail
EXEC dbo.SomeProc;
GO
REVERT;
GO

如您所见，如果您运行此脚本，则会出现以下错误:

As you can see, if you run this script, you get an error along the lines of:

消息 916，级别 14，状态 1，过程 SomeProc，第 4 行 [批处理开始第 28 行] 服务器主体SampleCredential"无法访问当前安全上下文下的数据库SampleDB2".

Msg 916, Level 14, State 1, Procedure SomeProc, Line 4 [Batch Start Line 28] The server principal "SampleCredential" is not able to access the database "SampleDB2" under the current security context.

那么，什么是跨数据库访问?引用文档:

So, what is Cross-Database access? To quote from the documentation:

跨数据库所有权链接发生在一个过程中数据库依赖于另一个数据库中的对象.跨数据库所有权链的工作方式与所有权链中的所有权链相同单个数据库，除了不间断的所有权链要求所有对象所有者都映射到同一个登录帐户.如果源数据库中的源对象和数据库中的目标对象目标数据库由相同的登录帐户拥有，SQL Server 不会不检查目标对象的权限.

Cross-database ownership chaining occurs when a procedure in one database depends on objects in another database. A cross-database ownership chain works in the same way as ownership chaining within a single database, except that an unbroken ownership chain requires that all the object owners are mapped to the same login account. If the source object in the source database and the target objects in the target databases are owned by the same login account, SQL Server does not check permissions on the target objects.

但是，请注意，此方法会引入主要安全漏洞.因此，如果这与您的环境有关，那么这不是适合您的解决方案.再次，来自文档:

Note, however, that there are major security flaws that can be introduced with this method. Thus, if this is a concern for your environment this is not the solution for you. Again, from the documentation:

跨数据库的所有权链接默认关闭.Microsoft 建议您禁用跨数据库所有权链接，因为它会使您面临以下安全风险:

Ownership chaining across databases is turned off by default. Microsoft recommends that you disable cross-database ownership chaining because it exposes you to the following security risks:

• 数据库所有者和 db_ddladmin 或 db_owners 数据库角色的成员可以创建其他用户拥有的对象.这些对象可能以其他数据库中的对象为目标.这意味着如果您启用跨数据库所有权链接，您必须完全将所有数据库中的数据信任给这些用户.

• Database owners and members of the db_ddladmin or the db_owners database roles can create objects that are owned by other users. These objects can potentially target objects in other databases. This means that if you enable cross-database ownership chaining, you must fully trust these users with data in all databases.

具有 CREATE DATABASE 权限的用户可以创建新数据库并附加现有数据库.如果跨数据库所有权链接是启用，这些用户可以访问其他数据库中的对象可能没有新创建或附加的权限他们创建的数据库.

Users with CREATE DATABASE permission can create new databases and attach existing databases. If cross-database ownership chaining is enabled, these users can access objects in other databases that they might not have privileges in from the newly created or attached databases that they create.

好的，现在警告已经解决了，该怎么办.继续上面的脚本，我们需要在服务器上启用跨数据库所有权链接(如果尚未启用).您可以通过运行以下命令来实现:

OK, now that caveat is out of the way, what do to. Continuing on from the script above, we need to enable cross db ownership chaining on the server if it isn't already. You can do so by running the following:

EXEC sp_configure 'show advanced', 1;
RECONFIGURE;  
GO
EXEC sp_configure 'cross db ownership chaining', 1;  
RECONFIGURE;  
GO
EXEC sp_configure 'show advanced', 0;
RECONFIGURE;  
GO

现在已启用，您可以在 2 数据库上启用 DB_CHAINING:

Now that is enabled you can enable DB_CHAINING on the 2 databases:

ALTER DATABASE SampleDB1 SET DB_CHAINING ON;
ALTER DATABASE SampleDB2 SET DB_CHAINING ON;
GO

然后您需要为您在另一个数据库上的登录创建用户，但不需要为其授予任何权限，因此这将很好"(它不会允许他们SELECT from，SP外的表):

You'll then need to create the user for your login on the other database, but won't need to give it any permissions, so this will be "fine" (it won't allow them to SELECT from, the table outside of the SP):

USE SampleDB2;
GO
CREATE USER SampleCredential FOR LOGIN SampleCredential;
GO

最后，您可以再次测试:

Finally, you can then test again:

USE SampleDB1;
--Test
EXECUTE AS LOGIN = 'SampleCredential';
GO
--It works (0 rows returned)
EXEC dbo.SomeProc;
GO
REVERT;
GO

如果您想仔细检查用户无法从数据库中选择，您可以这样做:

If you want to double check the user can't select from the database, you can do so:

USE SampelDB1;
GO
--This will fail
SELECT *
FROM SampleDB2.dbo.SampleTable;
GO

最后是清理(为了更好的措施):

And finally the cleanup (for good measure):

--Cleanup
USE master;
DROP DATABASE SampleDB2;
DROP DATABASE SampleDB1;
DROP LOGIN SampleCredential;
EXEC sp_configure 'show advanced', 1;
RECONFIGURE;  
GO
EXEC sp_configure 'cross db ownership chaining', 0;  
RECONFIGURE;  
GO
EXEC sp_configure 'show advanced', 0;
RECONFIGURE;  
GO

这篇关于允许登录运行存储过程而不能从表中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！