时间戳

首页 > 解决方案 > 如何在 SQL Server 上创建一个数据库角色，该角色的用户只能从一个视图中选择，而不能从视图中使用的表中选择？

sql - 如何在 SQL Server 上创建一个数据库角色，该角色的用户只能从一个视图中选择，而不能从视图中使用的表中选择？

问题描述

我想创建一个角色，用户只能从 1 个单一视图中查看和选择，而不能从视图中使用的表中进行选择。

到目前为止我有这个代码：

use enterprise_product_prod
go
DECLARE @SQL NVARCHAR(3000)
DECLARE @RoleName NVARCHAR(100) = 'only_view_test2';
DECLARE @Schema NVARCHAR(100) = 'Ignite';
DECLARE @Name NVARCHAR(100) = 'GAN_Amounts';
DECLARE GrantExec_Cursor CURSOR FAST_FORWARD READ_ONLY
for
SELECT 'GRANT SELECT ON [' + @Schema  + '].[' + 
                             @Name  + '] TO [' + @RoleName + ']; ' AS SQLstatement
FROM INFORMATION_SCHEMA.VIEWS
OPEN GrantExec_Cursor
FETCH NEXT FROM GrantExec_Cursor INTO @SQL

WHILE @@FETCH_STATUS = 0
BEGIN --Grant Permissions
    EXEC(@SQL)
    FETCH NEXT FROM GrantExec_Cursor INTO @SQL
END --Grant Permissions

CLOSE GrantExec_Cursor
DEALLOCATE GrantExec_Cursor
GO

它工作正常，如果我向这个角色添加一个测试用户，我只能看到视图“Ignite.Gan_amounts”，但是当我想在这个视图上运行一个简单的选择时，我收到以下错误：

对象“Tbl_FX”、数据库“ENTERPRISE_PRODUCT_PROD”、模式“SCLR”的 SELECT 权限被拒绝。

Tbl_FX 是视图中使用的表之一。

我如何才能使该角色的用户只能从该视图中查看和选择而不会出现此错误？我不希望他们能够从原始表格中进行选择。

标签： sqlsql-serverdatabaseroles

解决方案

您将需要一个表函数而不是视图，或者将视图放在表函数上。然后，该表函数应该使用一个EXECUTE AS OWNER子句，以便能够单独授予该函数的执行权限。

这是一个以所有者身份执行的基本TVF ：

create function testTVF() returns @testTable table (a int)
with execute as owner as
begin
  insert into @testTable select 1 a union all select 2 a;
  return;
end;
go

我试图创建一个内联 TVF 但失败了An invalid option was specified for the statement "CREATE/ALTER FUNCTION"

仅凭视图无法做到这一点。请参阅SQL Server 视图中的模拟？.

推荐阅读