如何向视图中的所有同义词授予权限？

Question

我必须授予视图的权限。然而，dbo.my_view这个视图连接了许多表，以及其他视图和同义词。

使用以下脚本，我可以在服务器中找到所有同义词：

列出同义词内容的脚本

SELECT
name as synonymName,
base_object_name as synonymDefinition,
COALESCE(PARSENAME(base_object_name,4),@@SERVERNAME) AS serverName,
COALESCE(PARSENAME(base_object_name,3),DB_NAME(DB_ID())) AS dbName,
COALESCE(PARSENAME(base_object_name,2),SCHEMA_NAME(SCHEMA_ID())) AS schemaName,
PARSENAME(base_object_name,1) AS objectName
FROM sys.synonyms
ORDER BY serverName,dbName,schemaName,objectName

如果我必须在dbo.myview上授予select，如何才能在任何其他由于同义词也需要授予的数据库中找到需要授予的select权限？

Answer 1

我想马塞洛被问到了这样的疑问：

select  d.referenced_entity_name,
        s.name as synonymName,
        base_object_name as synonymDefinition,
        COALESCE(PARSENAME(s.base_object_name,4),@@SERVERNAME) AS serverName,
        COALESCE(PARSENAME(s.base_object_name,3),DB_NAME(DB_ID())) AS dbName,
        COALESCE(PARSENAME(s.base_object_name,2),SCHEMA_NAME(SCHEMA_ID())) AS schemaName,
        PARSENAME(s.base_object_name,1) AS objectName
from sys.sql_expression_dependencies d
     join sys.synonyms s
        on d.referenced_id = s.object_id
where d.referencing_id = object_id('dbo.myView'); 

此查询仅显示视图dbo.myView中使用的同义词及其使用Marcello查询的定义。

虽然证书是一种有效的方法，但可能不可能将所有视图都转换到UDF，而且，我不确定是否可以在不同的服务器之间实现cerfificate方法，我的意思是有些同义词可以引用链接的服务器。

这个查询显示了使用同义词和同义词定义的所有视图：

select  distinct
        o.name as view_name,
        --d.referenced_entity_name,
        s.name as synonymName,
        base_object_name as synonymDefinition,
        COALESCE(PARSENAME(s.base_object_name,4),@@SERVERNAME) AS serverName,
        COALESCE(PARSENAME(s.base_object_name,3),DB_NAME(DB_ID())) AS dbName,
        COALESCE(PARSENAME(s.base_object_name,2),SCHEMA_NAME(SCHEMA_ID())) AS schemaName,
        PARSENAME(s.base_object_name,1) AS objectName
from sys.sql_expression_dependencies d
     join sys.objects o
        on o.object_id = d.referencing_id and o.type = 'V'
     join sys.synonyms s
        on d.referenced_id = s.object_id
order by 1;

Answer 2

跨数据库权限是相当棘手的。因此，类似于我在这里的回答：

为了让用户能够检查服务器代理服务的状态，我需要向用户提供什么最低权限？

我建议使用模块签名来实现这一点，因为最终它将比其他选项更安全，也更不复杂。这将需要将视图更改为多语句表值函数，以便可以对其进行签名，但仍可从中选择。

只需做以下几点：

1. 在包含此视图的DB中，创建一个证书(我的首选是指定密码，而不是依赖数据库主密钥(DMK)进行保护)。
2. 使用ADD SIGNATURE与证书签署TVF
3. 将证书备份到文件(公钥a.k.a的.cer文件)。“证书”和私钥的.pvk文件)
4. 使用ALTER CERTIFICATE删除证书的私钥
5. USE [master];
6. 从.cer文件创建相同的证书(不需要从.pvk文件创建私钥)
7. 从该证书创建登录
8. 授予登录任何必要的权限以完成目标(即.a)。这可能是CONNECT ANY DATABASE (我认为那是在2016年)和其他东西的组合，甚至可能是CONTROL SERVER的组合。如果没有权限组合，尝试将这个登录添加到sysadmin固定服务器角色中。

授予基于证书的登录的权限有点大，但不能模拟登录(即EXECUTE AS LOGIN = N'that_Login';)，也不能登录；登录仅仅是额外权限的容器，而且只适用于使用该证书签名的内容，在这种情况下，该证书只是一个TVF，它只是一个SELECT语句。

如果您需要更改该TVF中的代码，或通过使用ADD SIGNATURE签名将这些权限授予另一个模块，则如果要签名的新代码位于另一个DB中，则需要在另一个DB中创建相同的证书，或者需要使用ALTER CERTIFICATE语句将私钥还原到此证书中。虽然可以使用两个内置函数将公钥和私钥提取为VARBINARY十六进制字节字符串，然后从这些VARBINARY字符串(从Server 2012开始)创建新的证书，但只能从.pvk文件恢复证书的私钥。因此，上面的步骤3使用BACKUP CERTIFICATE，因为它既可以用于在任何其他DB中创建完整的证书，也可以用于将私钥还原到现有的证书中。