재귀 CTE 다중 수준 [중복]

Question

어떤 역할이 데이터베이스 내의 어떤 리소스에 대한 액세스 권한을 재귀 적으로 부여하는지 보여주는 표가 있습니다. 예를 들면 :

Default_Role은 App_Role에 대한 액세스 권한을 부여하고 App_Role은 Security_Role에 대한 액세스 권한을 부여하며 Security_Role은 세 개의 테이블 (고객, 판매, 사용자)에 대한 액세스 권한을 부여합니다. 따라서 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

Answer 1

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