如何批次設定禁止特定角色只能存取某些Table
應用程式開發不論是前端AP或後端資料庫，通常會建議使用所謂群組或角色為基礎來控管權限。以SQL Server為例，若將某些物件的存取權限設定給特定角色，再將使用者加入這個角色，即可立即擁有與角色相同的存取權限，取消存取權限也只要將該使用者自開角色中移除即可。本文將針對上述情境撰寫一些簡單的TSQL敘述來快速設定權限。
- 建立role1角色
1: CREATE ROLE role1 AUTHORIZATION dbo
2: GO
- 使用sp_addrolemember預存程序將User1加入至role1。
1: EXEC sp_addrolemember 'role1','User1'
2: GO
- 利用sys.tables和sys.schemas找出目前database有哪些 table。
1: SELECT b.name + '.' + a.name
2: FROM sys.tables a
3: INNER JOIN sys.schemas b
4: on a.schema_id = b.schema_id
- 假設要讓role1只能存取dbo.TSBDISC2和dbo.TSBDISCTEST資料表，下列程式使用Cursor來進行批次設定。
1: use test
2: go
3:
4: DECLARE @tablename varchar(50)
5: DECLARE @schemaname varchar(50)
6:
7: DECLARE cur_tables CURSOR FOR
8: SELECT b.name as schemaname,a.name as tablename
9: FROM sys.tables a
10: INNER JOIN sys.schemas b
11: ON a.schema_id = b.schema_id
12:
13: OPEN cur_tables
14: FETCH NEXT FROM cur_tables INTO @schemaname,@tablename
15:
16: WHILE @@FETCH_STATUS = 0
17: BEGIN
18: IF @schemaname + '.' + @tableName = 'dbo.TSBDISC2'
19: OR @schemaname + '.' + @tableName = 'dbo.TSBDISCTEST'
20: EXEC ('GRANT SELECT ON ' + @schemaname + '.[' + @tablename + '] TO role1')
21: ELSE
22: EXEC ('DENY SELECT ON ' + @schemaname + '.[' + @tablename + '] TO role1')
23: FETCH NEXT FROM cur_tables INTO @schemaname,@tablename
24: END
25:
26: CLOSE cur_tables
27: DEALLOCATE cur_tables
- 下左圖利用User1登入SQL Server後執行查詢dbo.TSBDISC2和dbo.TSBDISCTEST以外的Table，會收到沒有select權限的錯誤訊息，查詢dbo.TSBDISC2和dbo.TSBDISCTEST則可順利完成，如下圖右。
參考資料：
http://msdn.microsoft.com/zh-tw/library/ms173724.aspx