Sql-Server

遞歸 CTE 多級

  • November 13, 2020

我有一個表格,顯示哪些角色遞歸地授予對數據庫中哪些資源的訪問權限。例如:

在此處輸入圖像描述

Default_Role 授予對 App_Role 的訪問權限,App_Role 授予對 Security_Role 的訪問權限,Security_Role 授予對三個表(customers、sales、users)的訪問權限。因此,Default_Role 的成員被授予所有這些權限,但 Default_Role 沒有被授予對 Sys_Role 的訪問權限,也沒有被授予對 System 或 Admin 表的訪問權限。

CREATE TABLE SQLTest(
    DBName     NVARCHAR(100) NULL
   ,Privilege    NVARCHAR(100) NULL
   ,PrivilegeType NVARCHAR(100) NULL
   ,PrivilegeDetail NVARCHAR(100) NULL
   ,TableName NVARCHAR(100) NULL
)

INSERT INTO SQLTest
VALUES 
('TSDB','Default_Role','Role','App_Role',NULL),
('TSDB','App_Role','Role','Security_Role',NULL),
('TSDB','Sys_Role','Role','Security_Role',NULL),
('TSDB','Security_Role','Table','Customers','Customers'),
('TSDB','Security_Role','Table','Sales','Sales'),
('TSDB','Security_Role','Table','Users','Users'),
('TSDB','Sys_Role','Table','System','System'),
('TSDB','Sys_Role','Table','Admin','Admin')

將其展平的最佳方法是什麼,以便您可以查看授予 Default_Role 的所有訪問權限(角色和表),而不會顯示未授予 Default_Role 的其他訪問權限?像這樣:

在此處輸入圖像描述

試著把這個例子放在一起,但不起作用。

http://www.sqlfiddle.com/#!18/f9a27/2

由於 CTE 的第一個測試很難理解,我寫了一個方法,所有級別都在最後

最後的選項是不需要的,但是當你試圖弄清楚會發生什麼時,特別是要減少,你應該以較低的數字添加它

WITH MyTest as
(
 SELECT P.DBName, P.Privilege,p.PrivilegeType,P.PrivilegeDetail,P.TableName , CAST(P.PrivilegeDetail AS VarChar(Max)) as Level
 FROM SQLTest P
 WHERE P.Privilege = 'Default_Role'

 UNION ALL

 SELECT P1.DBName, P1.Privilege,p1.PrivilegeType,P1.PrivilegeDetail,p1.TableName , CAST(P1.PrivilegeDetail AS VarChar(Max)) + ', ' + M.Level
 FROM SQLTest P1  
 INNER JOIN MyTest M
 ON M.PrivilegeDetail = P1.Privilege
)
SELECT * From MyTest

OPTION (MAXRECURSION 50);

結果

DBName  Privilege       PrivilegeType   PrivilegeDetail     TableName   Level
TSDB    Default_Role    Role            App_Role            (null)       App_Role
TSDB    App_Role        Role            Security_Role       (null)       Security_Role, App_Role
TSDB    Security_Role   Table           Customers           Customers    Customers, Security_Role, App_Role
TSDB    Security_Role   Table           Sales               Sales        Sales, Security_Role, App_Role
TSDB    Security_Role   Table           Users                Users       Users, Security_Role, App_Role

引用自:https://dba.stackexchange.com/questions/279665