拒絕訪問 SQL Server 中的資訊架構

我正在尋找禁用SQL Server 中使用者/組訪問sys.tables/的最佳方法。Information Schema

我從 2008 年發現了這個執行緒

它顯示了一種如何拒絕訪問的方法，[sys].[something]如下所示：

DENY SELECT ON [sys].[columns] TO DenySystemTableSelectRole
GO
DENY SELECT ON [sys].[tables] TO DenySystemTableSelectRole
GO
DENY SELECT ON [sys].[syscolumns] TO DenySystemTableSelectRole
GO
DENY SELECT ON [sys].[sysobjects] TO DenySystemTableSelectRole
GO

但沒有辦法如何禁用訪問Information Schema：

DENY SELECT ON INFORMATION_SCHEMA.TABLES To DenySystemTableSelectRole

這似乎不起作用。

如何禁用對 information_schema 的訪問？

有沒有更簡單的方法禁用對所有sys/的訪問information_schema？

更新： 實際上我不能同時執行以下語句：

DENY SELECT ON [sys] TO reducedDBO
GO
DENY SELECT ON INFORMATION_SCHEMA To reducedDBO
GO

我試圖在使用者存在的特定數據庫上執行它們，並且我還嘗試在“主”上執行它們。

我仍然可以執行：

SELECT * from
INFORMATION_SCHEMA.TABLES 

–> 仍然返回結果

SELECT * from
sys.TABLES 

–> 沒有結果了

包括SCHEMA::在查詢中使得創建安全對象成為可能

DENY SELECT ON SCHEMA::[sys] TO reducedDBO
GO
DENY SELECT ON SCHEMA::INFORMATION_SCHEMA To reducedDBO
GO

但現在我仍然可以從數據庫中選擇所有資訊。

我查看了 Management Studio 2008 中使用者屬性視窗中的“Securables”選項卡，它看起來像這樣：

確實阻止選擇 sys.tables 的條目

架構：sys，名稱：表，類型：視圖

sys.tables 的權限：選中 Permission:Select、Grantor:dbo、Deny

不阻止任何選擇的條目

架構：，名稱：INFORMATION_SCHEMA，類型：架構

INFORMATION_SCHEMA 的權限：Permission:Select, Grantor:dbo, Deny 未選中（我試圖檢查它，但沒有機會..）

權限：選擇，授予者：INFORMATION_SCHEMA，拒絕選中

---

我試圖在 GUI 上設置權限，但是我得到了同樣的錯誤，即只能在主數據庫上設置權限。但是我沒有將使用者/登錄名添加到主數據庫安全性中。

解決方案：

我可以完成deny工作的唯一方法information_schema是將使用者添加到主數據庫並在主數據庫上執行deny select：

DENY SELECT ON [sys].[tables] TO reducedDBO
GO
DENY SELECT ON INFORMATION_SCHEMA.TABLES To reducedDBO
GO

和在這段程式碼中一樣，它只能對單個表執行。

您應該能夠拒絕整個架構sys和整個information_schema架構的權限：

DENY SELECT On SCHEMA::sys To [user_name]
DENY SELECT On SCHEMA::INFORMATION_SCHEMA To [user_name]

這基本上應該只是阻止該使用者在這兩個模式中進行任何選擇。

首先，您是正確的，因為（有點違反直覺）阻止訪問

$$ sys $$和$$ INFORMATION_SCHEMA $$schemas 是首先確保登錄（嗯，伺服器級主體）作為使用者（erm，數據庫級主體）存在於 master 數據庫中。 為簡單起見，假設您有一個 SQL 登錄名：

CREATE LOGIN [testy] WITH PASSWORD=N'SCoBIqlJELGzrY9zYsKWC5z3kHtMsyCAP6yBHLUYQ0w='
go

現在在 master 數據庫中創建一個對應的使用者：

use [master]
go
CREATE USER [testy] FOR LOGIN [testy]
go

現在你想阻止這個登錄訪問系統提供的模式中的任何表 -

$$ sys $$和$$ INFORMATION_SCHEMA $$. SQL Server 2008 R2 和 SQL Server 2012 之間的行為似乎發生了變化：

在 SQL Server 2012（可能是更高版本）中，在

$$ master $$數據庫按照您的預期執行：

DENY SELECT, VIEW DEFINITION ON SCHEMA::[sys] to [testy];
GO
DENY SELECT, VIEW DEFINITION ON SCHEMA::[INFORMATION_SCHEMA] to [testy];
GO

但是，在 SQL Server 2008 R2（可能還有更早的版本）中，stock grant 語句將這些架構中的對象的訪問權限授予

$$ public $$似乎覆蓋了上述 DENY 語句，這對我來說似乎是一大堆失敗。因此，在 2008 R2 上，您需要為每個 GRANT 明確拒絕$$ public $$. 這是一個執行此操作的腳本：

declare
   @database_principal sysname,
   @cur cursor,
   @sql nvarchar( 4000 );

set @database_principal = 'testy';

set @cur = cursor local forward_only static for
   select 
       'DENY ' +
       permission_name + ' on ' +
       case class 
           when 1 then
               case minor_id
                   when 0 then 'OBJECT'
                   else 'COLUMN'
               end
           else
               class_desc
       end + '::' +
       case class
           when 0 then db_name()
           when 1 then quotename( OBJECT_SCHEMA_NAME(major_id) ) + '.' + quotename( object_name( major_id ) ) + case minor_id when 0 then '' else ( select '.' + quotename( name ) collate database_default from sys.columns where column_id=minor_id) end
           when 3 then schema_name( major_id )
       end + ' to ' +
       quotename( @database_principal )
   from
       sys.database_permissions
   where
       [grantee_principal_id] = 0 -- public
       and
       [state_desc] = 'GRANT'
       and
       [permission_name] = 'SELECT'
;

open @cur;

while
   1 = 1
begin
   fetch @cur into @sql;
   if @@fetch_status <> 0 break;

   print @sql;
   exec sys.sp_executesql @sql;
end;

close @cur;

deallocate @cur;

在 master 數據庫中執行上述內容，您就刪除了對這些模式內容的訪問權限。

筆記：

1. 因為這些是顯式的 DENY 語句，所以它們在腳本執行時是正確的。如果有人隨後更改授予公共的權限（例如，服務包創建一個新的系統表），那麼這將暴露給被拒絕的使用者
2. 使用數據庫角​​色作為 DENY 語句的目標並將被拒絕的使用者置於該角色中是個好主意。
3. 您可以通過將 DENY 更改為 REVOKE 來撤消此操作
4. 如果您在上面的腳本中註釋掉以下兩行：

   and
   [permission_name] = 'SELECT'

它將具有撤消所有公共預設 GRANT 的效果。這將阻止訪問例如 sys.sp_tables 並因此破壞例如 Microsoft Access 列舉表的能力，但在高安全性場景中這樣做很有用，因此使用者只能在您明確授予的地方獲得訪問權限它。

引用自：https://dba.stackexchange.com/questions/25667