Sql-Server
遞歸 CTE 多級
我有一個表格,顯示哪些角色遞歸地授予對數據庫中哪些資源的訪問權限。例如:
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 的其他訪問權限?像這樣:
試著把這個例子放在一起,但不起作用。
由於 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