행 수준 보안
적용 대상: 
SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Microsoft Fabric 내 SQL 분석 엔드포인트 Microsoft Fabric 내 Warehouse
RLS(행 수준 보안)를 통해 그룹 구성원 자격 또는 실행 컨텍스트를 사용하여 데이터베이스 테이블 내 행에 대한 액세스를 제어할 수 있습니다.
행 수준 보안은 애플리케이션의 보안 설계 및 코딩을 간소화합니다. RLS는 데이터 행 액세스에 대한 제한을 구현하는 데 유용합니다. 예를 들어 작업자가 자신의 부서와 관련된 데이터 행에만 액세스하도록 할 수 있습니다. 또는, 고객의 데이터 액세스를 회사와 관련된 데이터로만 제한할 수도 있습니다.
액세스 제한 논리는 다른 애플리케이션 계층의 데이터와 다소 떨어진 데이터베이스 계층에 위치합니다. 데이터베이스 시스템은 모든 계층에서 데이터 액세스를 시도할 때마다 액세스를 제한합니다. 이렇게 하면 보안 시스템의 노출 영역을 줄임으로써 보안 시스템을 보다 안정적이고 강력하게 만들 수 있습니다.
RLS는 CREATE SECURITY POLICY Transact-SQL 문과 인라인 테이블 반환 함수로 만들어진 조건자를 사용하여 구현합니다.
행 수준 보안은 SQL Server 2016(13.x)에서 처음 도입되었습니다.
참고 항목
이 문서는 SQL Server 및 Azure SQL 플랫폼을 중심으로 합니다. Microsoft Fabric의 경우 Microsoft Fabric의 행 수준 보안을 참조하세요.
설명
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작업을 필터링으로 제외한 행을 인식하지 못합니다. 모든 행이 필터링으로 제외되는 상황을 포함합니다. 애플리케이션은 다른 작업 중에 행이 필터링으로 제외되더라도 이를INSERT할 수 있습니다.
차단 조건자는 다음 동작을 수행합니다.
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 옵션을 기반으로 여러 행을 필터링할 수 있습니다. 일반적으로 조건자 함수는 다음과 같은 규칙을 준수해야 합니다.
조건자 함수는 암시적으로 문자열에서 date, smalldatetime, datetime, datetime2 또는 datetimeoffset로 변환 또는 그 반대로 변환해서는 안 됩니다. 이러한 변환은 SET DATEFORMAT(Transact-SQL) 및 SET LANGUAGE(Transact-SQL) 옵션의 영향을 받기 때문입니다. 대신
CONVERT함수를 사용하고 스타일 매개 변수를 명시적으로 지정합니다.조건자 함수는 주의 첫 번째 요일 값에 의존하지 않아야 합니다. 이 값은 SET DATEFIRST(Transact-SQL) 옵션의 영향을 받기 때문입니다.
조건자 함수는 오류(예: 오버플로 또는 0으로 나누기)가 발생하는 경우
NULL을(를) 반환하는 산술 또는 집계 식에 의존해서는 안 됩니다. SET ANSI_WARNINGS(Transact-SQL), SET NUMERIC_ROUNDABORT(Transact-SQL), SET ARITHABORT(Transact-SQL) 옵션의 영향을 받기 때문입니다.조건자 함수는 연결된 문자열을
NULL과(와) 비교해서는 안 됩니다. 이 동작은 SET CONCAT_NULL_YIELDS_NULL(Transact-SQL) 옵션의 영향을 받기 때문입니다.
보안 메모: 부채널 공격
악의적인 보안 정책 관리자
중요한 열을 기반으로 보안 정책을 만들 수 있는 충분한 권한과 인라인 테이블 반환 함수를 만들거나 변경할 수 있는 권한이 있는 악의적인 보안 정책 관리자가 데이터를 유추하기 위해 부채널 공격을 사용하도록 설계된 인라인 테이블 반환 함수를 악의적으로 만들어 데이터 반출을 수행하기 위해 테이블에 대한 선택 권한이 있는 다른 사용자와 공모할 수 있음을 관찰하는 것이 중요합니다. 이러한 공격에는 공모(또는 악의적인 사용자에게 부여되는 과도한 권한)가 필요하고, 정책을 수정하고(스키마 바인딩을 중단하기 위해 조건자를 제거할 수 있는 권한 필요) 인라인 테이블 반환 함수를 수정하고 대상 테이블에서 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는 파일 스트림과 호환되지 않습니다.
PolyBase: RLS는 Azure Synapse 및 SQL Server 2019 CU7 이상의 버전에서 외부 테이블에 지원됩니다.
메모리 최적화 테이블: 메모리 최적화 테이블에서 보안 조건자로 사용되는 인라인 테이블 반환 함수는
WITH NATIVE_COMPILATION옵션을 사용하여 정의해야 합니다. 이 옵션을 사용하면 메모리 최적화 테이블에서 지원되지 않는 언어 기능이 차단되며 만들 때 해당 오류가 발생합니다. 자세한 내용은 메모리 최적화 테이블의 행 수준 보안을 참조하세요.인덱싱된 뷰: 일반적으로 보기를 기반으로 보안 정책을 만들 수 있으며 보안 정책에 바인딩된 테이블을 기반으로 보기를 만들 수 있습니다. 그러나 인덱스를 통한 행 조회는 정책을 무시하므로, 보안 정책이 있는 테이블을 기반으로 인덱싱된 뷰를 만들 수는 없습니다.
변경 데이터 캡처: CDC(변경 데이터 캡처)는 필터링해야 하는 전체 행을
db_owner의 멤버 또는 테이블에 대해 CDC를 사용하도록 설정할 때 지정된 "gating(제어)" 역할의 멤버인 사용자에게 누출시킬 수 있습니다. 이 함수를 명시적으로NULL(으)로 설정하면 모든 사용자가 변경 데이터에 액세스하게 할 수 있습니다. 실제로db_owner및 이 게이팅 역할의 구성원은 테이블에 보안 정책이 있더라도 테이블의 모든 데이터 변경 내용을 볼 수 있습니다.변경 내용 추적: 변경 내용 추적은
SELECT및VIEW CHANGE TRACKING권한이 둘 다 있는 사용자에게 필터링되어야 하는 행의 기본 키를 누출할 수 있습니다. 실제 데이터 값은 유출되지 않습니다. 특정 기본 키가 있는 행의 A열에서 업데이트/삽입/삭제가 수행되었다는 사실만 있습니다. 기본 키에 사회 보장 번호와 같은 기밀 요소가 포함되어 있으면 이것이 문제가 됩니다. 그러나 실제로는 이CHANGETABLE은(는) 최신 데이터를 가져오기 위해 거의 항상 원본 테이블에 조인되어 있습니다.전체 텍스트 검색:
CONTAINSTABLE,FREETEXTTABLE,semantickeyphrasetable,semanticsimilaritydetailstable,semanticsimilaritytable의 전체 텍스트 검색 및 의미 체계 검색 함수를 사용하는 쿼리는 행 수준 보안을 적용하고 필터링해야 하는 행의 기본 키 누출을 방지하기 위해 추가 조인이 도입되므로 성능 저하가 예상됩니다.Columnstore 인덱스: RLS는 클러스터형 columnstore 인덱스 및 비클러스터형 columnstore 인덱스 모두와 호환됩니다. 그러나 행 수준 보안이 함수에 적용되기 때문에 최적화 프로그램에서 배치 모드를 사용하지 않도록 쿼리 계획을 수정할 수 있습니다.
분할 뷰: 분할 뷰에는 차단 조건자를 정의할 수 없으며, 차단 조건자를 사용하는 테이블을 기반으로 분할 뷰를 만들 수 없습니다. 필터 조건자는 분할 뷰와 호환됩니다.
temporal 테이블: temporal 테이블은 RLS와 호환됩니다. 그러나 현재 테이블의 보안 조건자는 기록 테이블에 자동으로 복제되지 않습니다. 현재 및 기록 테이블 모두에 보안 정책을 적용하려면 각 테이블에서 개별적으로 보안 조건자를 추가해야 합니다.
기타 제한 사항:
- Microsoft Fabric 및 Azure Synapse Analytics는 필터 조건자만 지원합니다. 차단 조건자는 현재 Microsoft Fabric 및 Azure Synapse Analytics에서 지원되지 않습니다.
예제
A. 데이터베이스에 인증하는 사용자에 대한 시나리오
이 예제에서는 3명의 사용자를 만들고 6개의 행이 있는 외부 테이블을 생성하고 채웁니다. 그런 다음 테이블에 대한 인라인 테이블 반환 함수 및 보안 정책을 생성합니다. 그러면 예제에서 SELECT 문이 다양한 사용자를 필터링하는 방법을 보여줍니다.
서로 다른 액세스 기능을 보여 주는 3개의 사용자 계정을 생성합니다.
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
필터 조건자로 함수를 추가하는 보안 정책을 만듭니다. 정책을 활성화하려면 STATE을(를) 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 풀을 호스팅하는 서버는 Microsoft Entra ID(구 Azure Active Directory)가 등록되어 있어야 하며 사용자는
Storage Blog Data Contributor권한이 있는 Azure Storage 계정이 있어야 합니다. Azure SQL Database에서 서버에 대한 Virtual Network 서비스 엔드포인트 및 규칙 사용 단계를 따릅니다. - 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;
생성된 Sales 테이블에서 Azure Synapse 외부 테이블을 만듭니다.
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';
필터 조건자로 인라인 테이블 반환 함수를 사용하는 외부 테이블에 대한 보안 정책을 생성합니다. 정책을 활성화하려면 STATE을(를) 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;
Manager은(는) 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. 중간 계층 애플리케이션을 통해 데이터베이스에 연결하는 사용자에 대한 시나리오
참고 항목
이 예제에서는 차단 조건자 기능이 현재 Microsoft Fabric 및 Azure Synapse에 대해 지원되지 않으므로 잘못된 사용자 ID의 행을 삽입해도 차단되지 않습니다.
이 예는 중간 계층 애플리케이션이 애플리케이션 사용자(또는 테넌트)가 동일한 SQL Server 사용자(애플리케이션)을 공유하는 연결 필터링을 구현하는 방법을 보여줍니다. 애플리케이션이 데이터베이스 연결 후 SESSION_CONTEXT로 현재 애플리케이션 사용자 ID를 설정한 다음, 보안 정책은 이 ID에 표시되지 않아야 하는 행을 투명하게 필터링하고 사용자가 잘못된 사용자 ID에 대해 행을 삽입하지 못하도록 차단합니다. 그 외의 앱 변경은 필요 없습니다.
데이터를 보유하는 테이블을 만듭니다.
CREATE TABLE Sales (
OrderId int,
AppUserId int,
Product varchar(10),
Qty int
);
각 애플리케이션 사용자별로 세 개의 주문을 보여 주는 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 설정을 담당합니다. 연결이 닫힐 때까지(연결 풀로 반환됨) 값이 다시 변경되지 않도록 @read_only 매개 변수를 1(으)로 설정합니다.
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개 행이 있는 팩트 테이블 Sample.Sales와(과) 2개 행이 있는 조회 테이블을 만들어서 채웁니다. 그런 다음 팩트 테이블을 조회에 조인하여 사용자 식별자를 가져오는 인라인 테이블 반환 함수와 테이블에 대한 보안 정책을 만듭니다. 그러면 예제에서 SELECT 문이 다양한 사용자를 필터링하는 방법을 보여줍니다.
서로 다른 액세스 기능을 보여 주는 3개의 사용자 계정을 생성합니다.
CREATE USER Manager WITHOUT LOGIN;
CREATE USER Sales1 WITHOUT LOGIN;
CREATE USER Sales2 WITHOUT LOGIN;
데이터를 저장할 Sample 스키마 및Sample.Sales 팩트 테이블을 만듭니다.
CREATE SCHEMA Sample;
GO
CREATE TABLE Sample.Sales
(
OrderID int,
Product varchar(10),
Qty int
);
6개의 데이터 행으로 Sample.Sales을(를) 채웁니다.
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;
새 스키마 및 인라인 테이블 반환 함수를 만듭니다. 이 함수는 사용자가 팩트 테이블 Sample.Sales을(를) 쿼리하고 Product열의 팩트 테이블에 조인될 때 테이블 Lk_Salesman_Product의 SalesRep 열이 쿼리를 실행하는 사용자(@SalesRep = USER_NAME())와 동일하거나 쿼리를 실행하는 사용자가 Manager 사용자(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'
) ;
필터 조건자로 함수를 추가하는 보안 정책을 만듭니다. 정책을 활성화하려면 STATE을(를) 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;
이제 각 사용자 권한으로 Sample.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;
Manager은(는) 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;
E. Microsoft Fabric의 행 수준 보안 시나리오
Microsoft Fabric에서 행 수준 보안 웨어하우스 및 SQL 분석 엔드포인트를 시연할 수 있습니다.
다음 예제에서는 Microsoft Fabric에서는 Warehouse를 사용하지만 SQL 분석 엔드포인트에서는 기존 테이블을 사용하는 샘플 테이블을 만듭니다. SQL 분석 엔드포인트에서는 CREATE TABLE을(를) 사용할 수 없지만 CREATE SCHEMA, CREATE FUNCTION, CREATE SECURITY POLICY은(는) 사용할 수 있습니다.
이 예제에서는 먼저 스키마 sales, 테이블 sales.Orders을(를) 만듭니다.
CREATE SCHEMA sales;
GO
-- Create a table to store sales data
CREATE TABLE sales.Orders (
SaleID INT,
SalesRep VARCHAR(100),
ProductName VARCHAR(50),
SaleAmount DECIMAL(10, 2),
SaleDate DATE
);
-- Insert sample data
INSERT INTO sales.Orders (SaleID, SalesRep, ProductName, SaleAmount, SaleDate)
VALUES
(1, 'Sales1@contoso.com', 'Smartphone', 500.00, '2023-08-01'),
(2, 'Sales2@contoso.com', 'Laptop', 1000.00, '2023-08-02'),
(3, 'Sales1@contoso.com', 'Headphones', 120.00, '2023-08-03'),
(4, 'Sales2@contoso.com', 'Tablet', 800.00, '2023-08-04'),
(5, 'Sales1@contoso.com', 'Smartwatch', 300.00, '2023-08-05'),
(6, 'Sales2@contoso.com', 'Gaming Console', 400.00, '2023-08-06'),
(7, 'Sales1@contoso.com', 'TV', 700.00, '2023-08-07'),
(8, 'Sales2@contoso.com', 'Wireless Earbuds', 150.00, '2023-08-08'),
(9, 'Sales1@contoso.com', 'Fitness Tracker', 80.00, '2023-08-09'),
(10, 'Sales2@contoso.com', 'Camera', 600.00, '2023-08-10');
Security 스키마, 함수 Security.tvf_securitypredicate 및 보안 정책 SalesFilter을(를) 만듭니다.
-- Creating schema for Security
CREATE SCHEMA Security;
GO
-- Creating a function for the SalesRep evaluation
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@contoso.com';
GO
-- Using the function to create a Security Policy
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.tvf_securitypredicate(SalesRep)
ON sales.Orders
WITH (STATE = ON);
GO
보안 정책을 적용하고 함수를 만든 후에는, Sales1@contoso.com 및 Sales2@contoso.com 사용자가 sales.Orders 테이블에서 자신의 데이터만 볼 수 있습니다. 여기서 열 SalesRep은(는) 기본 제공 함수 USER_NAME에서 반환된 고유한 사용자 이름과 같습니다. Fabric 사용자 manager@contoso.com은(는) sales.Orders 테이블의 모든 데이터를 볼 수 있습니다.
관련 콘텐츠
- 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)
- 사용자 정의 함수 만들기(데이터베이스 엔진)
- 개체 사용 권한 부여(Transact-SQL)