在SQL Server 2016中隐藏用户的所有sys和INFORMATION_SCHEMA视图和存储过程 [英] Hide All sys and INFORMATION_SCHEMA views and Store Procedures from User in SQL server 2016

问题描述

我们有一个应用程序，我们需要限制访问。该应用程序将使用登录TestApp。我一直在Sql Server Management Studio v 17.9.1下进行测试，我们正在使用SQL Server 2016.这个Login可以访问所有表Store procs，
视图，函数和触发器在自己的模式中测试数据库：测试，并且仅测试我们在TestAPP用户的权限中指定的dbo Schema中的某些表。我已经使用以下脚本来实现这一点。我们遇到的问题是
，Login可以访问所有系统视图和存储过程，我们不希望这种级别的访问。 （附加图片此登录不应该在模式TEST下执行。

1）从Test schema创建，删除，更改表

2）从Test schema创建，更改，删除视图

3）创建，更改，删除来自Test Schema的存储过程。

我们正在尝试使用Roles，这样我们就不必指定我们想要拒绝访问的每个sys对象。

我创建了角色[db_TestExecutor]并将角色附加到用户。然后为dbo表提供了特定权限，但他们仍然可以看到所有sys对象。

使用的SQL代码如下：

   USE     [ 测试 ]   
 GO 
  创建    SCHEMA     [ 测试 ]     AUTHORIZATION     [  dbo  ]   
 
 GO 
 
  创建 角色  [  db_TestExecutor  ]   
 GO 
 
   GRANT     EXECUTE     ON     SCHEMA   ::  测试  TO     [  db_TestExecutor  ]   
 GO 
 
   GRANT     SELECT  ，    INSERT  ，   更新 ，   删除    ON     SCHEMA   ::  测试  TO     [  db_TestExecutor  ]   
 
 
   USE     [  mASTER  ]   
 GO 
 
   IF    不    EXISTS    （  SELECT     1     FROM   sys  。  server_principals   WHERE     [ 名称 ]     =    'TestApp' ）  
 
   BEGIN   
  创建 登录TestApp   WITH  密码  =   N  'Password123' ，  DEFAULT_DATABASE   = [  tempdb  ]，  DEFAULT_LANGUAGE   = [ 英国 ]，  CHECK_EXPIRATION   =   OFF  ，  CHECK_POLICY   =   ON   
  结束  
 
   USE     [ 测试 ]   
 GO 
  创建    USER     [  TestApp  ]     FOR  登录  [  TestApp  ]   
 GO 
   ALTER  角色  [  db_TestExecutor  ]     ADD  会员  [  TestApp  ]   
 GO 
   GRANT     INSERT     ON     [  dbo  ]。[  tblOrders  ]     TO     [  TestApp  ]   
 GO 
   GRANT     SELECT     ON     [  dbo  ]。[  tblOrders  ]     TO     [  TestApp  ]   
 GO 
   GRANT    更新    ON     [  dbo  ]。[  tblOrders  ]     TO     [  TestApp  ]   
 GO 
   GRANT     SELECT     ON     [  dbo  ]。[ 测试 ]     TO     [  TestApp  ]   

我希望TestApp登录只能看到来自Test模式的对象，只能查看某些dbo表。

解决方案

< blockquote>

您可能需要这样：

- 拒绝架构级别的权限

DENY SELECT ON SCHEMA： ：sys TO [db_TestExecutor];
$
DENY SELECT On SCHEMA :: INFORMATION_SCHEMA To [db_TestExecutor]

GO

We have an application that we need to restrict access to. The application will be using login TestApp. I've been testing this under Sql Server Management Studio v 17.9.1 and we are using SQL Server 2016. This Login will have access to all tables Store procs, views , functions and triggers within its own schema Test in Database: Testing, and only to certain tables from dbo Schema that we specify in the permissions of the TestAPP user. I've used the following script to achieve that. The problem we are having is that the Login can access all System Views and Store Procs and we don't want this level of access. (Attached pictures This login Shouldn't be able to do those under schema TEST.

1) Create, drop, alter tables from Test schema

2) Create, alter, drop views from Test schema

3) create , alter , drop store procs from Test Schema.

We are trying to use Roles so that we don't have to specify each and every sys object that we want to deny access to.

I've created the Role [db_TestExecutor] and attached the role to the user. and then gave specific permissions for the dbo tables but still they can see all sys objects.

Used SQL code Below:

USE [Testing]
GO
CREATE SCHEMA [Test] AUTHORIZATION [dbo]

GO

CREATE ROLE [db_TestExecutor]
GO

GRANT EXECUTE ON SCHEMA::Test TO [db_TestExecutor]
GO

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::Test TO [db_TestExecutor]


USE [mASTER]
GO

IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE [name] = 'TestApp')

                    BEGIN
                        CREATE LOGIN TestApp WITH PASSWORD=N'Password123', DEFAULT_DATABASE=[tempdb], DEFAULT_LANGUAGE=[British], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
                    END

USE [Testing]
GO
CREATE USER [TestApp] FOR LOGIN [TestApp] 
GO
ALTER ROLE [db_TestExecutor] ADD MEMBER  [TestApp]
GO
GRANT INSERT ON [dbo].[tblOrders] TO [TestApp]
GO              
GRANT SELECT ON [dbo].[tblOrders] TO [TestApp]
GO              
GRANT UPDATE ON [dbo].[tblOrders] TO [TestApp]
GO
GRANT SELECT ON [dbo].[test] TO [TestApp]

I expect the TestApp login to be able to see only objects from Test schema and only certain dbo tables.

解决方案

You may need this:

-- Deny the permission at the schema level
DENY SELECT ON SCHEMA::sys TO [db_TestExecutor];
DENY SELECT On SCHEMA::INFORMATION_SCHEMA To [db_TestExecutor]
GO

这篇关于在SQL Server 2016中隐藏用户的所有sys和INFORMATION_SCHEMA视图和存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！