티스토리 뷰

 

SQL Database V12 – 행 수준 보안(Row-Level Security)

 

이번 글에서는 Azure SQL Database V12에서의 행 수준 보안(Row-Level Security)에 대한 내용을 살펴보려 합니다. 행 수준 보안(RLS)의 경우 SQL Server 2014에서 지원되는 기능으로 사용자별로 SELECT 결과값이 다르게 나오게 하는 것으로 사전 설정된 행 필터에 따라 결과를 표시하므로 사용자마다 다른 결과값을 보게 됩니다.

 

Azure SQL Database V12의 새로운 기능에 대한 내용은 아래 링크를 참고할 수 있습니다.

https://azure.microsoft.com/ko-kr/documentation/articles/sql-database-v12-whats-new/

 

행 수준 보안(RLS)에 대한 내용은 아래 링크를 참고할 수 있습니다. 행 수준 보안을 적용하게 하려면 먼저 사용자별로 연결이 설정되어야 합니다. 일반적인 단일 사용자로만 들어오게 되면 행 수준 보안은 의미가 없으며 여러 사용자별로 실행된다면 행 수준의 액세스를 제어할 수 있습니다.

https://msdn.microsoft.com/library/dn765131.aspx

 

위 링크의 예제를 활용하여 Azure SQL Database V12에서 행 수준 보안을 아래와 같이 적용해보았습니다. 위 링크의 경우 행 수준별 사용자를 한명만 할당하게 되는데 이는 여러 명이 해당 행을 접근할 수 있는 경우라면 비효율적이라 역할 기반으로 수정해서 적용해보았습니다.

 

 

-- 사용자를 생성합니다, Sales Region 별 사용자를 생성해서

-- 지역별 행 수준 보안을 적용해보겠습니다.

CREATE USER Manager WITHOUT LOGIN;

CREATE USER SalesUS WITHOUT LOGIN;

CREATE USER SalesEU WITHOUT LOGIN;

CREATE USER SalesKR WITHOUT LOGIN;

GO

 

-- 한 행당 사용자 한명으로 할당하지 않고 역할 기반을 구성하기 위한

-- 사용자 테이블을 생성합니다.

CREATE TABLE SalesRep

(ID INT IDENTITY PRIMARY KEY,

UserName sysname

)

GO

-- 사용자 여러 명을 하나의 역할로 묶어주는 역할 테이블을 생성합니다.

CREATE TABLE RLSRole

(ID INT IDENTITY PRIMARY KEY

, RoleName sysname

, SalesRepID INT REFERENCES SalesRep(ID)

)

GO

 

INSERT SalesRep VALUES(N'Manager')

INSERT SalesRep VALUES(N'SalesUS')

INSERT SalesRep VALUES(N'SalesEU')

INSERT SalesRep VALUES(N'SalesKR')

 

GO

INSERT RLSRole VALUES(N'ManagerGroup',1)

INSERT RLSRole VALUES(N'USGroup',2)

INSERT RLSRole VALUES(N'EUGroup',3)

INSERT RLSRole VALUES(N'KRGroup',4)

 

-- 최종 결과에서 다시 INSERT 해서 결과를 확인할 구문입니다.

--  INSERT RLSRole VALUES(N'EUGroup',4)

 

GO

 

-- 실제 데이터 값을 가지고 있는 테이블을 생성합니다.

-- RoleName 이라는 열을 행 수준 보안에서 사용할 것입니다.

CREATE TABLE Sales

    (

    OrderID int,

    RoleName sysname,

    Product varchar(10),

    Qty int

    );

 

GO

INSERT Sales VALUES

(1, 'USGroup', 'Valve', 5),

(2, 'USGroup', 'Wheel', 2),

(3, 'EUGroup', 'Valve', 4),

(4, 'EUGroup', 'Bracket', 2),

(5, 'KRGroup', 'Wheel', 5),

(6, 'KRGroup', 'Seat', 5);

 

SELECT * FROM Sales;

 

-- 테이블에 SELCT 권한을 부여합니다.

GRANT SELECT ON Sales TO Manager;

GRANT SELECT ON Sales TO SalesUS;

GRANT SELECT ON Sales TO SalesEU;

GRANT SELECT ON Sales TO SalesKR;

 

 

CREATE SCHEMA Security;

GO

-- 사용자 함수를 생성합니다. 매개변수는 역할 이름이며

-- 역할테이블과 사용자테이블을 조인하여 현재 사용자 정보로 필터링하는 함수입니다.

-- Manager 일 경우 전체가 반환되도록 되어 있으며

-- 비지니스 로직을 적용하여 행 수준 보안을 구성하기 위한 함수 입니다.

CREATE FUNCTION Security.fn_securitypredicate

(@RoleName AS sysname)

    RETURNS TABLE

WITH SCHEMABINDING

AS

    RETURN SELECT 1 AS fn_securitypredicate_result

             FROM dbo.RLSRole AS r INNER JOIN dbo.SalesRep AS s

             ON r.SalesRepID =s.ID

WHERE (@RoleName =r.RoleName AND s.UserName= USER_NAME() )

                           OR ( USER_NAME() = 'Manager');

 

 

 

-- 행 수준 보안의 핵심이며 ADD FILTER PREDICATE 정책을 Sales 테이블에 적용하는 구문입니다.

-- 행 필터는 위에서 생성한 함수이며 Sales 테이블의 역할 이름을 매개변수로 전달합니다.

CREATE SECURITY POLICY SalesFilter

ADD FILTER PREDICATE Security.fn_securitypredicate(RoleName)

ON dbo.Sales

WITH (STATE = ON);

 

-- EXECUTE AS 구문을 통해서 테스트합니다.

EXECUTE AS USER = 'SalesUS';

SELECT * FROM Sales;

REVERT;

 

/*

1            USGroup             Valve     5

2            USGroup             Wheel    2

*/

 

EXECUTE AS USER = 'SalesEU';

SELECT * FROM Sales;

REVERT;

/*

3            EUGroup             Valve     4

4            EUGroup             Bracket  2

*/

 

EXECUTE AS USER = 'SalesKR';

SELECT * FROM Sales;

REVERT;

/*

5            KRGroup             Wheel    5

6            KRGroup             Seat       5

*/

 

 

EXECUTE AS USER = 'Manager';

SELECT * FROM Sales;

REVERT;

/*

1            USGroup             Valve     5

2            USGroup             Wheel    2

3            EUGroup             Valve     4

4            EUGroup             Bracket  2

5            KRGroup             Wheel    5

6            KRGroup             Seat       5

*/

 

 

 

 

--  INSERT RLSRole VALUES(N'EUGroup',4)

-- 구문을 실행하여 SalesKR 사용자는 EUGroup 역할에도 포함하게 합니다.

-- EUGroup 역할에 속하기 때문에 실행 결과는 유럽 지역까지 결과에 나타납니다.

 

EXECUTE AS USER = 'SalesKR';

SELECT * FROM Sales;

REVERT;

/*

3            EUGroup             Valve     4

4            EUGroup             Bracket  2

5            KRGroup             Wheel    5

6            KRGroup             Seat       5

*/

 

 

-- 아래는 수준 보안을 해제합니다.

-- 테이블이나 함수의 스키마를 변경하려면 해제해야 합니다.

ALTER SECURITY POLICY SalesFilter

WITH (STATE = OFF);

 

 

행 수준 보안을 적용하게 하려면 먼저 사용자별로 연결이 설정되어야 합니다. 또한 사용자 함수를 기반으로 하기 때문에 데이터 건수가 많은 테이블의 경우 성능에 대한 테스트도 필요할 것으로 보입니다.

이번 글을 통해 Azure SQL Database V12에서도 RLS 를 지원한다는 것을 간략히 살펴보았습니다.

 

 

댓글