SQL Server:查看表的权限 [英] SQL Server : View permission to table

问题描述

是否可以授予用户访问视图的权限，但限制用户访问该视图从中选择的表?

Is it possible to grant a user access to a view but restrict the user from accessing the table the view selects from?

CREATE TABLE [dbo].[tUsers](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Username] [nvarchar](50) NULL,
    [Password] [nvarchar](50) NULL,
    [TenantID] int
) ON [PRIMARY]

CREATE VIEW [scmTenant1].[vUsers]
AS
SELECT     ID, Username, Password
FROM         dbo.tUsers
WHERE        TenantID = 1

SQL Server用户帐户(usrTenant1)可以访问架构scmTenant1，但是不能访问dbo架构.我以usrTenant1身份登录到SQL Server Management Studio，并尝试执行此查询:

The SQL Server user account (usrTenant1) has access to schema scmTenant1 but does not have access to the dbo schema. I log into SQL Server Management Studio as usrTenant1 and try executing this query:

SELECT * FROM scmTenant1.vUsers

给我错误:

对对象'tUsers'，数据库'Sandbox'，模式'dbo'的SELECT权限被拒绝.

如果我授予该帐户对dbo架构的访问权限，则查询可以正常执行.

If I grant this account access to the dbo schema the query executes fine.

我认为我真的想授予表查看权限.但是我的问题是，您可以授予用户访问视图的权限，并限制该用户访问该视图选择的基础表吗?

I think what I would really like to grant the view permission to the table. But my question is can you grant a user access to a view and restrict that user from accessing the underlying table the view selects from?

推荐答案

如果您将视图的所有者设为dbo而不是scmTenant-或创建一个执行相同操作的中间视图-那么您可以授予该视图权限而无需授予基础表的权限.

If you make the owner of the view dbo rather than scmTenant - or create an intermediate view that does the same - then you can grant permission to the view without granting permissions to the underlying table.

即

CREATE VIEW dbo.vUsers_T1
AS 
SELECT     ID, Username, Password 
FROM         dbo.tUsers 
WHERE        TenantID = 1

CREATE VIEW [scmTenant1].[vUsers] as
SELECT * FROM dbo.vUsers_T1

或者，您可以创建一个返回当前租户ID的函数，并创建一个引用dbo中的视图的视图.

Alternately, you could create a function that returns the ID of the current tenant and create a view that refers to that in dbo.

CREATE VIEW dbo.vUsers
AS 
SELECT     ID, Username, Password 
FROM         dbo.tUsers 
WHERE        TenantID = dbo.GetCurrentTenant()

这篇关于SQL Server:查看表的权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！