행 수준 보안
적용 대상:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics
행 수준 보안을 통해 그룹 멤버 자격 또는 실행 컨텍스트를 사용하여 데이터베이스 테이블의 행에 대한 액세스를 제어할 수 있습니다.
RLS(행 수준 보안)는 애플리케이션의 보안 설계 및 코딩을 간소화합니다. RLS는 데이터 행 액세스에 대한 제한을 구현하는 데 유용합니다. 예를 들어 작업자가 자신의 부서와 관련된 데이터 행에만 액세스하도록 할 수 있습니다. 또는, 고객의 데이터 액세스를 회사와 관련된 데이터로만 제한할 수도 있습니다.
액세스 제한 논리는 다른 애플리케이션 계층의 데이터와 다소 떨어진 데이터베이스 계층에 위치합니다. 데이터베이스 시스템은 모든 계층에서 데이터 액세스를 시도할 때마다 액세스를 제한합니다. 이렇게 하면 보안 시스템의 노출 영역을 줄임으로써 보안 시스템을 보다 안정적이고 강력하게 만들 수 있습니다.
CREATE SECURITY POLICY Transact-SQL 문을 사용하여 RLS를 구현하고 인라인 테이블 반환 함수로 만든 조건자를 구현합니다.
행 수준 보안은 SQL Server 2016(13.x)에 처음 도입되었습니다.
참고 항목
Azure Synapse는 필터 조건자만 지원합니다. 차단 조건자는 현재 Azure Synapse에서 지원되지 않습니다.
설명
RLS는 두 가지 유형의 보안 조건자를 지원합니다.
필터 조건자는 읽기 작업(SELECT, UPDATE 및 DELETE)에 사용할 수 있는 행을 자동으로 필터링합니다.
차단 조건자는 조건자를 위반하는 쓰기 작업(AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE)을 명시적으로 차단합니다.
테이블의 행 수준 데이터에 대한 액세스는 인라인 테이블 반환 함수로 정의된 보안 조건자에 의해 제한됩니다. 그런 다음, 보안 정책에 의해 함수가 호출되고 적용됩니다. 필터 조건자의 경우 애플리케이션은 결과 집합에서 필터링된 행을 인식하지 못합니다. 모든 행이 필터링되면 null 세트가 반환됩니다. 블록 조건자의 경우 조건자를 위반하는 모든 작업이 오류와 함께 실패합니다.
필터 조건자는 기본 테이블에서 데이터를 읽는 동안 적용됩니다. 모든 가져오기 작업(SELECT, DELETE 및 UPDATE)에 영향을 줍니다. 사용자는 필터링된 행을 선택하거나 삭제할 수 없습니다. 사용자는 필터링된 행을 업데이트할 수 없습니다. 그러나 나중에 필터링되는 방식으로 행을 업데이트할 수 있습니다. 차단 조건자는 모든 쓰기 작업에 영향을 줍니다.
AFTER INSERT 및 AFTER UPDATE 조건자는 사용자가 조건자를 위반하는 값으로 행을 업데이트하는 것을 방지할 수 있습니다.
BEFORE UPDATE 조건자는 사용자가 현재 조건자를 위반하는 행을 업데이트하지 못하도록 할 수 있습니다.
BEFORE DELETE 조건자는 삭제 작업을 차단할 수 있습니다.
필터 및 블록 조건자와 보안 정책 모두 다음과 같은 동작이 있습니다.
다른 테이블과 조인하거나 함수를 호출하는 조건자 함수를 정의할 수 있습니다.
SCHEMABINDING = ON(기본값)을 사용하여 보안 정책을 만든 경우에는 조인 또는 함수를 쿼리에서 액세스할 수 있으며, 다른 추가 권한 검사 없이 올바르게 작동합니다. 보안 정책을 사용하여SCHEMABINDING = OFF만든 경우 사용자는 대상 테이블을 쿼리하기 위해 이러한 추가 테이블 및 함수에 대한 SELECT 권한이 필요합니다. 조건자 함수가 CLR 스칼라 반환 함수를 호출하는 경우 EXECUTE 권한이 추가로 필요합니다.보안 조건자가 정의되었지만 사용하지 않도록 설정된 테이블에 대해 쿼리를 실행할 수 있습니다. 필터링되거나 차단된 행은 영향을 받지 않습니다.
dbo 사용자, db_owner 역할의 멤버 또는 테이블 소유자가 보안 정책이 정의되고 사용하도록 설정된 테이블을 쿼리하는 경우 행은 보안 정책에 정의된 대로 필터링되거나 차단됩니다.
스키마 바운드 보안 정책에 의해 바인딩된 테이블의 스키마를 변경하려 하면 오류가 발생합니다. 그러나 조건자에 의해 참조되지 않는 열은 변경할 수 있습니다.
지정된 작업에 대해 정의된 조건자가 이미 있는 테이블에 조건자를 추가하려고 하면 오류가 발생합니다. 조건자가 사용되도록 설정된 상태인지에 관계없이 오류가 발생합니다.
스키마 바인딩된 보안 정책 내의 테이블에 대한 조건자로 사용되는 함수를 수정하려고 하면 오류가 발생합니다.
겹치지 않는 조건자를 포함하는 여러 활성 보안 정책을 정의하면 성공합니다.
필터 조건자는 다음 동작을 수행합니다.
- 테이블의 행을 필터링하는 보안 정책을 정의합니다. 애플리케이션은 SELECT, UPDATE 및 DELETE 작업에 대해 필터링된 행을 인식하지 못합니다. 모든 행이 필터링되는 상황을 포함합니다. 애플리케이션은 다른 작업 중에 필터링되더라도 행을 삽입할 수 있습니다.
블록 조건자의 동작은 다음과 같습니다.
UPDATE에 대한 블록 조건자는 BEFORE 및 AFTER에 대한 별도의 작업으로 분할됩니다. 예를 들어 사용자가 행을 업데이트하여 현재 값보다 높은 값을 갖도록 차단할 수는 없습니다. 이러한 종류의 논리가 필요한 경우 DELETED 및 INSERTED 중간 테이블과 함께 트리거를 사용하여 이전 값과 새 값을 함께 참조해야 합니다.
조건자 함수에서 사용하는 열이 변경되지 않은 경우 최적화 프로그램은 AFTER UPDATE 블록 조건자를 확인하지 않습니다. 예를 들어 Alice는 급여를 100,000보다 크게 변경할 수 없습니다. Alice는 조건자에서 참조된 열이 변경되지 않는 한 급여가 이미 100,000보다 큰 직원의 주소를 변경할 수 있습니다.
BULK INSERT를 포함하여 대량 API에 적용된 변경 내용은 없습니다. 즉, AFTER INSERT 블록 조건자는 일반 삽입 작업과 마찬가지로 대량 삽입 작업에 적용됩니다.
사용 사례
다음은 RLS를 사용하는 방법에 대한 디자인 예제입니다.
병원은 간호사가 환자의 데이터 행만 볼 수 있도록 하는 보안 정책을 만들 수 있습니다.
은행은 직원의 비즈니스 부서 또는 회사의 역할에 따라 금융 데이터 행에 대한 액세스를 제한하는 정책을 만들 수 있습니다.
다중 테넌트 애플리케이션은 다른 모든 테넌트의 행에서 각 테넌트의 데이터 행을 논리적으로 분리하는 정책을 만들 수 있습니다. 효율성은 단일 테이블의 많은 테넌트에 대한 데이터 스토리지를 통해 달성됩니다. 각 테넌트는 자체의 데이터 행만 볼 수 있습니다.
RLS 필터 조건자는 WHERE 절을 추가하는 것과 기능적으로 동일합니다. 조건자는 업무 관례 명령처럼 복잡해질 수 있으며, 또는 절은 WHERE TenantId = 42처럼 간단해질 수 있습니다.
더 공식적인 용어로, RLS는 조건자 기반 액세스 제어를 도입합니다. 유연한 중앙 집중식 조건자 기반 평가를 제공합니다. 조건자는 메타데이터 또는 관리자가 적절하게 결정하는 다른 조건을 기반으로 할 수 있습니다. 조건자는 사용자 특성에 따라 데이터에 대한 적절한 액세스 권한이 사용자에게 있는지 여부를 결정하는 기준으로 사용됩니다. 레이블 기반 액세스 제어는 조건자 기준 액세스 제어를 사용하여 구현할 수 있습니다.
사용 권한
보안 정책을 만들거나 변경하거나 삭제하려면 ALTER ANY SECURITY POLICY 권한이 필요합니다. 보안 정책을 만들거나 삭제하려면 스키마에 대한 ALTER 권한이 필요합니다.
또한 추가된 각 조건자에는 다음 권한이 필요합니다.
조건자로 사용되는 함수에 대한 SELECT 및 REFERENCES 권한입니다.
정책에 바인딩되는 대상 테이블에 대한 REFERENCES 권한입니다.
인수로 사용하는 대상 테이블의 모든 열에 대한REFERENCES 권한.
보안 정책은 데이터베이스의 dbo 사용자를 포함하여 모든 사용자에게 적용됩니다. Dbo 사용자는 보안 정책을 변경하거나 삭제할 수 있습니다. 그러나 보안 정책에 대한 변경 내용을 감사할 수 있습니다. 높은 권한이 있는 사용자(예: sysadmin 또는 db_owner)가 문제를 해결하거나 데이터의 유효성을 검사하기 위해 모든 행을 볼 수 있어야 하는 경우 이를 허용하도록 보안 정책을 작성해야 합니다.
를 사용하여 보안 정책을 만든 SCHEMABINDING = OFF경우 대상 테이블을 쿼리하려면 조건자 함수에 대한 SELECT 또는 EXECUTE 권한과 조건자 함수 내에서 사용되는 추가 테이블, 뷰 또는 함수가 있어야 합니다. SCHEMABINDING = ON (기본값)을 사용하여 보안 정책을 생성된 경우 사용자가 대상 테이블을 쿼리할 때 이러한 권한 검사는 무시됩니다.
좋은 연습 방법
조건자 함수 및 보안 정책과 같은 RLS 개체에 대한 별도의 스키마를 만드는 것이 좋습니다. 이렇게 하면 이러한 특수 개체에 필요한 사용 권한을 대상 테이블과 분리할 수 있습니다. 다중 테넌트 데이터베이스에서는 다른 정책 및 조건자 함수를 추가로 분리해야 할 수 있지만 모든 경우에 대한 표준은 아닙니다.
ALTER ANY SECURITY POLICY 권한은 높은 권한의 사용자(예: 보안 정책 관리자)를 위한 것입니다. 보호하는 테이블에 대한 SELECT 권한은 보안 정책 관리자에게 필요하지 않습니다.
잠재적인 런타임 오류를 방지하기 위해 조건자 함수에서 형식 변환을 방지합니다.
성능 저하를 방지하려면 조건자 함수에서 가능한 재귀를 피하십시오. 쿼리 최적화 프로그램은 직접 재귀를 검색하려고 하지만 간접 재귀를 찾을 수 있는 것은 아닙니다. 간접 재귀는 두 번째 함수가 조건자 함수를 호출하는 위치입니다.
성능을 최대화하려면 조건자 함수에서 과도한 테이블 조인을 사용하지 마세요.
세션별 SET 옵션에 종속된 조건자 논리를 사용하지 않도록 합니다. 실제 애플리케이션에서 사용할 가능성은 낮지만 사용자가 임의 쿼리를 실행할 수 있는 경우 특정 세션별 SET 옵션에 따라 논리가 의존하는 조건자 함수가 정보를 누수할 수 있습니다. 예를 들어 문자열을 암시적으로 datetime 으로 변환하는 조건자 함수는 현재 세션에 대해 SET DATEFORMAT 옵션을 기반으로 여러 행을 필터링할 수 있습니다. 일반적으로 조건자 함수는 다음과 같은 규칙을 준수해야 합니다.
이러한 변환은 SET DATEFORMAT(Transact-SQL) 및 SET LANGUAGE(Transact-SQL) 옵션의 영향을 받으므로 조건자 함수는 문자열을 date, smalldatetime, datetime, datetime2 또는 datetimeoffset 또는 그 반대로 암시적으로 변환해서는 안 됩니다. 대신 CONVERT 함수를 사용하고 스타일 매개 변수를 명시적으로 지정합니다.
이 값은 SET DATEFIRST(Transact-SQL) 옵션의 영향을 받으므로 조건자 함수는 요일의 값을 사용하지 않아야 합니다.
이 동작은 SET ANSI_WARNINGS(Transact-SQL), SET NUMERIC_ROUNDABORT(Transact-SQL) 및 SET ARITHABORT(Transact-SQL) 옵션의 영향을 받으므로 오류 발생 시 NULL을 반환하는 산술 또는 집계 식에 의존해서는 안 됩니다.
이 동작은 SET CONCAT_NULL_YIELDS_NULL(Transact-SQL) 옵션의 영향을 받으므로 조건자 함수는 연결된 문자열을 NULL과 비교해서는 안 됩니다.
보안 정보: 사이드 채널 공격
악의적인 보안 정책 관리자
중요한 열 위에 보안 정책을 만들 수 있는 충분한 권한과 인라인 테이블 반환 함수를 만들거나 변경할 수 있는 권한이 있는 악의적인 보안 정책 관리자는 데이터를 유추하기 위해 사이드 채널 공격을 사용하도록 설계된 인라인 테이블 반환 함수를 악의적으로 만들어 데이터 반출을 수행하기 위해 테이블에 대한 선택 권한이 있는 다른 사용자와 공모할 수 있음을 관찰하는 것이 중요합니다. 이러한 공격에는 공모(또는 악의적인 사용자에게 부여되는 과도한 권한)가 필요하고, 정책을 수정하고(스키마 바인딩을 중단하기 위해 조건자를 제거할 수 있는 권한 필요) 인라인 테이블 반환 함수를 수정하고 대상 테이블에서 select 문을 반복적으로 하는 실행하는 작업을 여러 번 반복해야 합니다. 필요에 따라 권한을 제한하고 의심스러운 활동을 모니터링하는 것이 좋습니다. 행 수준 보안과 관련된 정책 및 인라인 테이블 반환 함수와 같은 작업을 모니터링해야 합니다.
신중하게 작성된 쿼리
오류를 활용하는 신중하게 작성된 쿼리를 사용하여 정보 유출을 일으킬 수 있습니다. 예를 들어, 악의적인 사용자가 SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe' 을(를) 통해 John Doe의 급료가 $100,000임을 알게 됩니다. 악의적인 사용자가 다른 사용자의 급여를 직접 쿼리하지 못하도록 하기 위한 보안 조건자가 있지만 사용자는 쿼리에서 0으로 나누기 예외를 반환하는 시기를 결정할 수 있습니다.
기능 간 호환성
일반적으로 행 수준 보안은 기능 간에 예상대로 작동합니다. 그러나 몇 가지 예외가 있습니다. 이 섹션에서는 SQL Server의 다른 특정 기능과 함께 행 수준 보안을 사용하기 위한 몇 가지 참고 사항 및 주의 사항을 설명합니다.
DBCC SHOW_STATISTICS는 필터링되지 않은 데이터에 대한 통계를 보고하고, 그렇지 않으면 보안 정책으로 보호되는 정보를 누출할 수 있습니다. 이러한 이유로 행 수준 보안 정책이 있는 테이블에 대한 통계 개체를 볼 수 있는 액세스가 제한됩니다. 사용자는 테이블의 소유자이거나 sysadmin 고정 서버 역할, db_owner 고정 데이터베이스 역할 또는 db_ddladmin 고정 데이터베이스 역할의 구성원이여야 합니다.
파일 스트림: RLS는 Filestream과 호환되지 않습니다.
PolyBase: RLS는 Azure Synapse 및 SQL Server 2019 CU7 이상의 외부 테이블에서 지원됩니다.
메모리 최적화 테이블: 메모리 최적화 테이블에서 보안 조건자로 사용되는 인라인 테이블 반환 함수는 이 옵션을 사용하여
WITH NATIVE_COMPILATION정의해야 합니다. 이 옵션을 사용하면 메모리 최적화 테이블에서 지원되지 않는 언어 기능이 차단되며 만들 때 해당 오류가 발생합니다. 자세한 내용은 메모리 최적화 테이블 소개에서 메모리 최적화 테이블의 행 수준 보안을 참조하세요.인덱싱된 뷰: 일반적으로 뷰 위에 보안 정책을 만들 수 있으며 보안 정책에 바인딩된 테이블 위에 뷰를 만들 수 있습니다. 그러나 인덱스를 통한 행 조회는 정책을 무시하므로 보안 정책이 있는 테이블 위에 인덱싱된 뷰를 만들 수 없습니다.
변경 데이터 캡처: 변경 데이터 캡처는 테이블에 대해 CDC를 사용하도록 설정할 때 지정된 "게이팅" 역할의 멤버인 db_owner 멤버 또는 사용자로 필터링해야 하는 전체 행을 누출할 수 있습니다(참고: 모든 사용자가 변경 데이터에 액세스할 수 있도록 이 함수 를 NULL 로 명시적으로 설정할 수 있음). 실제로 이 게이팅 역할의 db_owner 멤버는 테이블에 보안 정책이 있더라도 테이블의 모든 데이터 변경 내용을 볼 수 있습니다.
변경 내용 추적: 변경 내용 추적은 SELECT 및 VIEW 변경 내용 추적 권한이 있는 사용자에게 필터링해야 하는 행의 기본 키를 누설할 수 있습니다. 실제 데이터 값은 유출되지 않습니다. B 기본 키가 있는 행에 대해 A 열이 업데이트/삽입/삭제되었다는 사실만 있습니다. 기본 키에 주민등록번호와 같은 기밀 요소가 포함되어 있으면 문제가 됩니다. 그러나 실제로 이 CHANGETABLE 은 최신 데이터를 가져오기 위해 거의 항상 원래 테이블과 조인됩니다.
전체 텍스트 검색: 행 수준 보안을 적용하고 필터링해야 하는 행의 기본 키(CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasetable, semanticsimilaritydetailstable, semanticsimilaritydetailstable, semanticsimiimilarlartable)를 방지하기 위해 추가 조인이 도입되었기 때문에 다음 전체 텍스트 검색 및 의미 체계 검색 함수를 사용하는 쿼리에 성능이 저하될 것으로 예상됩니다.
Columnstore 인덱스: RLS는 클러스터형 및 비클러스터형 columnstore 인덱스와 호환됩니다. 그러나 행 수준 보안이 함수를 적용하므로 최적화 프로그램에서 일괄 처리 모드를 사용하지 않도록 쿼리 계획을 수정할 수 있습니다.
분할된 뷰: 분할된 뷰에서 블록 조건자를 정의할 수 없으며 블록 조건자를 사용하는 테이블 위에 분할된 뷰를 만들 수 없습니다. 필터 조건자는 분할된 뷰와 호환됩니다.
임시 테이블: 임시 테이블은 RLS와 호환됩니다. 그러나 현재 테이블의 보안 조건자는 기록 테이블에 자동으로 복제되지 않습니다. 현재 및 기록 테이블 모두에 보안 정책을 적용하려면 각 테이블에서 개별적으로 보안 조건자를 추가해야 합니다.
예
A. 데이터베이스에 인증하는 사용자에 대한 시나리오
이 예제에서는 세 명의 사용자를 만들고 6개의 행으로 테이블을 만들고 채웁니다. 그런 다음 인라인 테이블 반환 함수와 테이블에 대한 보안 정책을 만듭니다. 그런 다음, 다양한 사용자에 대해 select 문을 필터링하는 방법을 보여 줍니다.
다양한 액세스 기능을 보여 주는 세 개의 사용자 계정을 만듭니다.
CREATE USER Manager WITHOUT LOGIN;
CREATE USER SalesRep1 WITHOUT LOGIN;
CREATE USER SalesRep2 WITHOUT LOGIN;
GO
데이터를 보유하는 테이블을 만듭니다.
CREATE SCHEMA Sales
GO
CREATE TABLE Sales.Orders
(
OrderID int,
SalesRep nvarchar(50),
Product nvarchar(50),
Quantity smallint
);
각 영업 담당자별로 세 개의 주문을 보여 주는 6개의 데이터 행으로 테이블을 채웁니다.
INSERT INTO Sales.Orders VALUES (1, 'SalesRep1', 'Valve', 5);
INSERT INTO Sales.Orders VALUES (2, 'SalesRep1', 'Wheel', 2);
INSERT INTO Sales.Orders VALUES (3, 'SalesRep1', 'Valve', 4);
INSERT INTO Sales.Orders VALUES (4, 'SalesRep2', 'Bracket', 2);
INSERT INTO Sales.Orders VALUES (5, 'SalesRep2', 'Wheel', 5);
INSERT INTO Sales.Orders VALUES (6, 'SalesRep2', 'Seat', 5);
-- View the 6 rows in the table
SELECT * FROM Sales.Orders;
각 사용자에게 테이블에 대한 읽기 권한을 부여합니다.
GRANT SELECT ON Sales.Orders TO Manager;
GRANT SELECT ON Sales.Orders TO SalesRep1;
GRANT SELECT ON Sales.Orders TO SalesRep2;
GO
새 스키마 및 인라인 테이블 반환 함수를 만듭니다. 열의 행 SalesRep 이 쿼리를 실행하는 사용자(@SalesRep = USER_NAME())와 같거나 쿼리를 실행하는 사용자가 관리자 사용자(USER_NAME() = 'Manager')인 경우 함수가 반환 1 됩니다. 사용자 정의 테이블 반환 함수의 이 예제는 다음 단계에서 만든 보안 정책에 대한 필터 역할을 하는 데 유용합니다.
CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.tvf_securitypredicate(@SalesRep AS nvarchar(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS tvf_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';
GO
필터 조건자로 함수를 추가하는 보안 정책을 만듭니다. 정책을 사용하도록 설정하려면 ON 상태를 설정해야 합니다.
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.tvf_securitypredicate(SalesRep)
ON Sales.Orders
WITH (STATE = ON);
GO
함수에 tvf_securitypredicate 대한 SELECT 권한을 허용합니다.
GRANT SELECT ON Security.tvf_securitypredicate TO Manager;
GRANT SELECT ON Security.tvf_securitypredicate TO SalesRep1;
GRANT SELECT ON Security.tvf_securitypredicate TO SalesRep2;
이제 각 사용자로 테이블에서 선택하여 Sales.Orders 필터링 조건자를 테스트합니다.
EXECUTE AS USER = 'SalesRep1';
SELECT * FROM Sales.Orders;
REVERT;
EXECUTE AS USER = 'SalesRep2';
SELECT * FROM Sales.Orders;
REVERT;
EXECUTE AS USER = 'Manager';
SELECT * FROM Sales.Orders;
REVERT;
관리자는 6개의 행을 모두 표시해야 합니다. Sales1 및 Sales2 사용자는 자신의 판매 행만 볼 수 있습니다.
정책을 사용하지 않도록 보안 정책을 변경합니다.
ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);
이제 Sales1 및 Sales2 사용자는 6개 행을 모두 볼 수 있습니다.
SQL 데이터베이스에 연결하여 이 샘플 연습에서 리소스를 정리합니다.
DROP USER SalesRep1;
DROP USER SalesRep2;
DROP USER Manager;
DROP SECURITY POLICY SalesFilter;
DROP TABLE Sales.Orders;
DROP FUNCTION Security.tvf_securitypredicate;
DROP SCHEMA Security;
DROP SCHEMA Sales;
B. Azure Synapse 외부 테이블에서 행 수준 보안을 사용하는 시나리오
이 간단한 예제에서는 3명의 사용자와 6개의 행이 있는 외부 테이블을 생성합니다. 그런 다음 외부 테이블에 대한 인라인 테이블 반환 함수 및 보안 정책을 생성합니다. 예에서는 select 문이 다양한 사용자를 필터링하는 방법을 보여줍니다.
전제 조건
- 전용 SQL 풀이 있어야 합니다. 전용 SQL 풀 만들기 참조
- 전용 SQL 풀을 호스팅하는 서버는 Azure AD에 등록되어야 하며 권한이 있는 Azure Storage 계정이
Storage Blog Data Contributor있어야 합니다. 여기의 단계를 따릅니다. - Azure Storage 계정에 대한 파일 시스템을 생성합니다. Azure Storage Explorer를 사용하여 스토리지 계정을 봅니다. 컨테이너를 마우스 오른쪽 단추로 클릭하고 파일 시스템 만들기를 선택합니다.
필수 조건이 충족되면 서로 다른 액세스 기능을 보여 주는 3개의 사용자 계정을 생성합니다.
--run in master
CREATE LOGIN Manager WITH PASSWORD = '<user_password>'
GO
CREATE LOGIN Sales1 WITH PASSWORD = '<user_password>'
GO
CREATE LOGIN Sales2 WITH PASSWORD = '<user_password>'
GO
--run in both the master database and in your dedicated SQL pool database
CREATE USER Manager FOR LOGIN Manager;
CREATE USER Sales1 FOR LOGIN Sales1;
CREATE USER Sales2 FOR LOGIN Sales2 ;
데이터를 보유하는 테이블을 만듭니다.
CREATE TABLE Sales
(
OrderID int,
SalesRep sysname,
Product varchar(10),
Qty int
);
각 영업 담당자별로 세 개의 주문을 보여 주는 6개의 데이터 행으로 테이블을 채웁니다.
INSERT INTO Sales VALUES (1, 'Sales1', 'Valve', 5);
INSERT INTO Sales VALUES (2, 'Sales1', 'Wheel', 2);
INSERT INTO Sales VALUES (3, 'Sales1', 'Valve', 4);
INSERT INTO Sales VALUES (4, 'Sales2', 'Bracket', 2);
INSERT INTO Sales VALUES (5, 'Sales2', 'Wheel', 5);
INSERT INTO Sales VALUES (6, 'Sales2', 'Seat', 5);
-- View the 6 rows in the table
SELECT * FROM Sales;
방금 만든 테이블에서 Azure Synapse 외부 테이블을 Sales 만듭니다.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<user_password>';
CREATE DATABASE SCOPED CREDENTIAL msi_cred WITH IDENTITY = 'Managed Service Identity';
CREATE EXTERNAL DATA SOURCE ext_datasource_with_abfss WITH (TYPE = hadoop, LOCATION = 'abfss://<file_system_name@storage_account>.dfs.core.windows.net', CREDENTIAL = msi_cred);
CREATE EXTERNAL FILE FORMAT MSIFormat WITH (FORMAT_TYPE=DELIMITEDTEXT);
CREATE EXTERNAL TABLE Sales_ext WITH (LOCATION='<your_table_name>', DATA_SOURCE=ext_datasource_with_abfss, FILE_FORMAT=MSIFormat, REJECT_TYPE=Percentage, REJECT_SAMPLE_VALUE=100, REJECT_VALUE=100)
AS SELECT * FROM sales;
만든 외부 테이블에 Sales_ext 있는 세 명의 사용자에 대해 SELECT를 부여합니다.
GRANT SELECT ON Sales_ext TO Sales1;
GRANT SELECT ON Sales_ext TO Sales2;
GRANT SELECT ON Sales_ext TO Manager;
새 스키마를 만들고, 인라인 테이블 반환 함수를 만들면 예제 A에서 이를 완료했을 수 있습니다. SalesRep 열이 사용자가 실행한 쿼리와 동일한 경우(@SalesRep = USER_NAME()) 또는 쿼리를 실행한 사용자가 Manager 사용자(USER_NAME() = 'Manager')인 경우 함수는 1을 반환합니다.
CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';
필터 조건자로 인라인 테이블 반환 함수를 사용하는 외부 테이블에 대한 보안 정책을 생성합니다. 정책을 활성화하려면 상태는 ON으로 설정해야 합니다.
CREATE SECURITY POLICY SalesFilter_ext
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)
ON dbo.Sales_ext
WITH (STATE = ON);
이제 외부 테이블에서 선택하여 필터링 조건자를 테스트합니다 Sales_ext . 각 사용자, Sales1, Sales2 및 Manager로 로그인합니다. 개별 사용자로 다음 명령을 실행합니다.
SELECT * FROM Sales_ext;
관리자는 6개의 행을 모두 볼 수 있습니다. Sales1 및 Sales2 사용자에게는 자신의 판매만 표시되어야 합니다.
정책을 사용하지 않도록 보안 정책을 변경합니다.
ALTER SECURITY POLICY SalesFilter_ext
WITH (STATE = OFF);
이제 Sales1 및 Sales2 사용자가 6개의 행을 모두를 볼 수 있습니다.
Azure Synapse 데이터베이스에 연결하여 이 샘플 연습에서 리소스를 정리합니다.
DROP USER Sales1;
DROP USER Sales2;
DROP USER Manager;
DROP SECURITY POLICY SalesFilter_ext;
DROP TABLE Sales;
DROP EXTERNAL TABLE Sales_ext;
DROP EXTERNAL DATA SOURCE ext_datasource_with_abfss ;
DROP EXTERNAL FILE FORMAT MSIFormat;
DROP DATABASE SCOPED CREDENTIAL msi_cred;
DROP MASTER KEY;
논리 서버의 master 데이터베이스에 연결하여 리소스를 정리합니다.
DROP LOGIN Sales1;
DROP LOGIN Sales2;
DROP LOGIN Manager;
C. 중간 계층 애플리케이션을 통해 데이터베이스에 연결하는 사용자 시나리오
참고 항목
이 예제 블록에서는 조건자 기능이 현재 Azure Synapse에 대해 지원되지 않으므로 잘못된 사용자 ID의 행을 삽입해도 Azure Synapse에서 차단되지 않습니다.
이 예제에서는 중간 계층 애플리케이션에서 애플리케이션 사용자(또는 테넌트)가 동일한 SQL Server 사용자(애플리케이션)를 공유하는 연결 필터링을 구현하는 방법을 보여 줍니다. 애플리케이션은 데이터베이스에 연결한 후 SESSION_CONTEXT(Transact-SQL)에서 현재 애플리케이션 사용자 ID를 설정한 다음 보안 정책은 이 ID에 표시되지 않아야 하는 행을 투명하게 필터링하고 사용자가 잘못된 사용자 ID에 대한 행을 삽입하지 못하도록 차단합니다. 다른 앱 변경은 필요하지 않습니다.
데이터를 보유하는 테이블을 만듭니다.
CREATE TABLE Sales (
OrderId int,
AppUserId int,
Product varchar(10),
Qty int
);
각 애플리케이션 사용자에 대해 3개의 주문을 표시하는 6개의 데이터 행으로 테이블을 채웁니다.
INSERT Sales VALUES
(1, 1, 'Valve', 5),
(2, 1, 'Wheel', 2),
(3, 1, 'Valve', 4),
(4, 2, 'Bracket', 2),
(5, 2, 'Wheel', 5),
(6, 2, 'Seat', 5);
애플리케이션이 연결하는 데 사용할 권한이 낮은 사용자를 만듭니다.
-- Without login only for demo
CREATE USER AppUser WITHOUT LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON Sales TO AppUser;
-- Never allow updates on this column
DENY UPDATE ON Sales(AppUserId) TO AppUser;
SESSION_CONTEXT 저장된 애플리케이션 사용자 ID를 사용하여 행을 필터링하는 새 스키마 및 조건자 함수를 만듭니다.
CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.fn_securitypredicate(@AppUserId int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE
DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('AppUser')
AND CAST(SESSION_CONTEXT(N'UserId') AS int) = @AppUserId;
GO
이 함수를 필터 조건자 및 블록 조건자로 추가하는 보안 정책을 만듭니다 Sales. 차단 조건자에는 AFTER INSERT만 필요합니다. BEFORE UPDATE 및 BEFORE DELETE 는 이미 필터링되었고 AFTER UPDATE 는 이전에 설정된 열 권한으로 인해 AppUserId 열을 다른 값으로 업데이트할 수 없어 필요 없기 때문입니다.
CREATE SECURITY POLICY Security.SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(AppUserId)
ON dbo.Sales,
ADD BLOCK PREDICATE Security.fn_securitypredicate(AppUserId)
ON dbo.Sales AFTER INSERT
WITH (STATE = ON);
이제 SESSION_CONTEXT 다른 사용자 ID를 Sales 설정한 후 테이블에서 선택하여 연결 필터링을 시뮬레이션할 수 있습니다. 실제로 애플리케이션은 연결을 연 후 SESSION_CONTEXT 현재 사용자 ID를 설정해야 합니다.
EXECUTE AS USER = 'AppUser';
EXEC sp_set_session_context @key=N'UserId', @value=1;
SELECT * FROM Sales;
GO
/* Note: @read_only prevents the value from changing again until the connection is closed (returned to the connection pool)*/
EXEC sp_set_session_context @key=N'UserId', @value=2, @read_only=1;
SELECT * FROM Sales;
GO
INSERT INTO Sales VALUES (7, 1, 'Seat', 12); -- error: blocked from inserting row for the wrong user ID
GO
REVERT;
GO
데이터베이스 리소스를 정리합니다.
DROP USER AppUser;
DROP SECURITY POLICY Security.SalesFilter;
DROP TABLE Sales;
DROP FUNCTION Security.fn_securitypredicate;
DROP SCHEMA Security;
D. 보안 조건자에서 조회 테이블을 사용하는 시나리오
이 예제에서는 팩트 테이블에서 사용자 식별자를 지정하지 않고 사용자 식별자와 필터링되는 값 간의 링크에 조회 테이블을 사용합니다. 3명의 사용자를 만들고 6개 행이 있는 팩트 테이블과 2개 행이 있는 조회 테이블을 만들어서 채웁니다. 그런 다음 팩트 테이블을 조회에 조인하여 사용자 식별자를 가져오는 인라인 테이블 반환 함수와 테이블에 대한 보안 정책을 만듭니다. 그런 다음, 다양한 사용자에 대해 select 문을 필터링하는 방법을 보여 줍니다.
다양한 액세스 기능을 보여 주는 세 개의 사용자 계정을 만듭니다.
CREATE USER Manager WITHOUT LOGIN;
CREATE USER Sales1 WITHOUT LOGIN;
CREATE USER Sales2 WITHOUT LOGIN;
데이터를 저장할 샘플 스키마 및 팩트 테이블을 만듭니다.
CREATE SCHEMA Sample;
GO
CREATE TABLE Sample.Sales
(
OrderID int,
Product varchar(10),
Qty int
);
팩트 테이블을 6개 행의 데이터로 채웁니다.
INSERT INTO Sample.Sales VALUES (1, 'Valve', 5);
INSERT INTO Sample.Sales VALUES (2, 'Wheel', 2);
INSERT INTO Sample.Sales VALUES (3, 'Valve', 4);
INSERT INTO Sample.Sales VALUES (4, 'Bracket', 2);
INSERT INTO Sample.Sales VALUES (5, 'Wheel', 5);
INSERT INTO Sample.Sales VALUES (6, 'Seat', 5);
-- View the 6 rows in the table
SELECT * FROM Sample.Sales;
조회 데이터를 저장할 테이블을 만듭니다. 이 경우 두 테이블 간의 Salesrep 관계입니다 Product.
CREATE TABLE Sample.Lk_Salesman_Product
( Salesrep sysname,
Product varchar(10)
) ;
조회 테이블을 샘플 데이터로 채우고 각 영업 담당자에게 연결 Product 합니다.
INSERT INTO Sample.Lk_Salesman_Product VALUES ('Sales1', 'Valve');
INSERT INTO Sample.Lk_Salesman_Product VALUES ('Sales2', 'Wheel');
-- View the 2 rows in the table
SELECT * FROM Sample.Lk_Salesman_Product;
각 사용자에게 팩트 테이블 읽기 액세스 권한을 부여합니다.
GRANT SELECT ON Sample.Sales TO Manager;
GRANT SELECT ON Sample.Sales TO Sales1;
GRANT SELECT ON Sample.Sales TO Sales2;
새 스키마 및 인라인 테이블 반환 함수를 만듭니다. 이 함수는 사용자가 팩트 테이블을 쿼리하고 SalesRep 테이블 Lk_Salesman_Product 의 열이 열의 팩트 Product 테이블에 Sales 조인될 때 쿼리를 실행하는 사용자(@SalesRep = USER_NAME())와 동일하거나 쿼리를 실행하는 사용자가 관리자 사용자(USER_NAME() = 'Manager')인 경우 1을 반환합니다.
CREATE SCHEMA Security ;
GO
CREATE FUNCTION Security.fn_securitypredicate
(@Product AS varchar(10))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN ( SELECT 1 as Result
FROM Sample.Sales f
INNER JOIN Sample.Lk_Salesman_Product s
ON s.Product = f.Product
WHERE ( f.product = @Product
AND s.SalesRep = USER_NAME() )
OR USER_NAME() = 'Manager'
) ;
필터 조건자로 함수를 추가하는 보안 정책을 만듭니다. 정책을 활성화하려면 상태는 ON으로 설정해야 합니다.
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(Product)
ON Sample.Sales
WITH (STATE = ON) ;
fn_securitypredicate 함수에 대한 SELECT 권한 허용
GRANT SELECT ON Security.fn_securitypredicate TO Manager;
GRANT SELECT ON Security.fn_securitypredicate TO Sales1;
GRANT SELECT ON Security.fn_securitypredicate TO Sales2;
이제 각 사용자로 테이블에서 선택하여 Sales 필터링 조건자를 테스트합니다.
EXECUTE AS USER = 'Sales1';
SELECT * FROM Sample.Sales;
-- This will return just the rows for Product 'Valve' (as specified for 'Sales1' in the Lk_Salesman_Product table above)
REVERT;
EXECUTE AS USER = 'Sales2';
SELECT * FROM Sample.Sales;
-- This will return just the rows for Product 'Wheel' (as specified for 'Sales2' in the Lk_Salesman_Product table above)
REVERT;
EXECUTE AS USER = 'Manager';
SELECT * FROM Sample.Sales;
-- This will return all rows with no restrictions
REVERT;
관리자는 6개의 행을 모두 볼 수 있습니다. Sales1 및 Sales2 사용자는 자신의 판매 행만 볼 수 있습니다.
정책을 사용하지 않도록 보안 정책을 변경합니다.
ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);
이제 Sales1 및 Sales2 사용자는 6개 행을 모두 볼 수 있습니다.
SQL 데이터베이스에 연결하여 이 샘플 연습에서 리소스를 정리합니다.
DROP USER Sales1;
DROP USER Sales2;
DROP USER Manager;
DROP SECURITY POLICY SalesFilter;
DROP FUNCTION Security.fn_securitypredicate;
DROP TABLE Sample.Sales;
DROP TABLE Sample.Lk_Salesman_Product;
DROP SCHEMA Security;
DROP SCHEMA Sample;
다음 단계
- CREATE SECURITY POLICY(Transact-SQL)
- ALTER SECURITY POLICY(Transact-SQL)
- DROP SECURITY POLICY(Transact-SQL)
- CREATE FUNCTION(Transact-SQL)
- SESSION_CONTEXT (Transact-SQL)
- sp_set_session_context(Transact-SQL)
- sys.security_policies (Transact-SQL)
- sys.security_predicates (Transact-SQL)
- 사용자 정의 함수 만들기(데이터베이스 엔진)