CTE đệ quy nhiều cấp độ [trùng lặp]

Question

Tôi có một bảng hiển thị các vai trò cấp quyền truy cập một cách đệ quy vào tài nguyên nào trong cơ sở dữ liệu. Ví dụ:

Default_Role cấp quyền truy cập vào App_Role, App_Role cấp quyền truy cập vào Security_Role và Security_Role cấp quyền truy cập vào ba bảng (khách hàng, bán hàng, người dùng). Vì vậy, một thành viên của Default_Role được cấp tất cả những điều này, nhưng Default_Role không được cấp quyền truy cập vào Sys_Role và không được cấp quyền truy cập vào các bảng Hệ thống hoặc Quản trị viên.

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')

Cách tốt nhất để làm phẳng điều này là gì để bạn có thể xem tất cả quyền truy cập (vai trò và bảng) được cấp cho Default_Role mà không hiển thị quyền truy cập bổ sung không được cấp cho Default_Role? Như thế này:

Đã thử kết hợp ví dụ này, nhưng không hiệu quả.

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

Answer 1

Vì CTE dành cho bài kiểm tra đầu tiên khá khó hiểu, tôi đã viết một cách tiếp cận, với tất cả các Cấp ở cuối

Tùy chọn ở cuối là nit cần thiết, nhưng để giảm bớt sự sợ hãi khi bạn đang cố gắng tìm hiểu điều gì sẽ xảy ra, bạn nên thêm nó ở một số thấp

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);

Kết quả

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