행 수준 보안
적용 대상:
Microsoft Fabric의 Microsoft FabricWarehouse에 있는 SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSQL 분석 엔드포인트
RLS(행 수준 보안)를 사용하면 그룹 멤버 자격 또는 실행 컨텍스트를 사용하여 데이터베이스 테이블의 행에 대한 액세스를 제어할 수 있습니다.
행 수준 보안은 애플리케이션의 보안 설계 및 코딩을 간소화합니다. RLS는 데이터 행 액세스에 대한 제한을 구현하는 데 유용합니다. 예를 들어 작업자가 자신의 부서와 관련된 데이터 행에만 액세스하도록 할 수 있습니다. 또는, 고객의 데이터 액세스를 회사와 관련된 데이터로만 제한할 수도 있습니다.
액세스 제한 논리는 다른 애플리케이션 계층의 데이터와 다소 떨어진 데이터베이스 계층에 위치합니다. 데이터베이스 시스템은 모든 계층에서 데이터 액세스를 시도할 때마다 액세스를 제한합니다. 이렇게 하면 보안 시스템의 노출 영역을 줄임으로써 보안 시스템을 보다 안정적이고 강력하게 만들 수 있습니다.
CREATE SECURITY POLICY Transact-SQL 문을 사용하여 RLS를 구현하고 인라인 테이블 반환 함수로 만든 조건자를 구현합니다.
행 수준 보안은 SQL Server 2016(13.x)에 처음 도입되었습니다.
참고 항목
이 문서는 SQL Server 및 Azure SQL 플랫폼에 초점을 맞췄습니다. Microsoft Fabric의 경우 Microsoft Fabric의 행 수준 보안을 참조 하세요.
설명
RLS(행 수준 보안)는 다음 두 가지 유형의 보안 조건자를 지원합니다.
필터 조건자는 읽기 작업(
SELECTUPDATE및DELETE)에 사용할 수 있는 행을 자동으로 필터링합니다.조건자를 차단하는 것은 조건자를 위반하는 쓰기 작업(
AFTER INSERT,AFTER UPDATE,BEFORE UPDATE,BEFORE DELETE)을 명시적으로 차단합니다.
테이블의 행 수준 데이터에 대한 액세스는 인라인 테이블 반환 함수로 정의된 보안 조건자에 의해 제한됩니다. 그런 다음, 보안 정책에 의해 함수가 호출되고 적용됩니다. 필터 조건자의 경우 애플리케이션은 결과 집합에서 필터링된 행을 인식하지 못합니다. 모든 행이 필터링되면 null 집합이 반환됩니다. 블록 조건자의 경우 조건자를 위반하는 모든 작업이 오류와 함께 실패합니다.
필터 조건자는 기본 테이블에서 데이터를 읽는 동안 적용됩니다. 모든 get 작업에 영향을 줍니다. SELECT, DELETE및 UPDATE. 사용자는 필터링된 행을 선택하거나 삭제할 수 없습니다. 사용자는 필터링된 행을 업데이트할 수 없습니다. 그러나 나중에 필터링되는 방식으로 행을 업데이트할 수 있습니다. 차단 조건자는 모든 쓰기 작업에 영향을 줍니다.
AFTER INSERT및AFTER UPDATE조건자는 사용자가 조건자를 위반하는 값으로 행을 업데이트하지 못하도록 할 수 있습니다.BEFORE UPDATE조건자는 사용자가 현재 조건자를 위반하는 행을 업데이트하지 못하도록 할 수 있습니다.BEFORE DELETE조건자는 삭제 작업을 차단할 수 있습니다.
필터 및 블록 조건자와 보안 정책 모두 다음과 같은 동작이 있습니다.
다른 테이블과 조인하거나 함수를 호출하는 조건자 함수를 정의할 수 있습니다.
SCHEMABINDING = ON(기본값)을 사용하여 보안 정책을 만든 경우에는 조인 또는 함수를 쿼리에서 액세스할 수 있으며, 다른 추가 권한 검사 없이 올바르게 작동합니다. 보안 정책을 사용하여SCHEMABINDING = OFF만든 경우 사용자는 대상 테이블을 쿼리하기 위해 이러한 추가 테이블 및 함수에 대한 권한이 필요합니다SELECT. 조건자 함수가 CLR 스칼라 반환 함수EXECUTE를 호출하는 경우 권한도 필요합니다.보안 조건자가 정의되었지만 사용하지 않도록 설정된 테이블에 대해 쿼리를 실행할 수 있습니다. 필터링되거나 차단된 행은 영향을 받지 않습니다.
dbo 사용자, 역할의
db_owner멤버 또는 테이블 소유자가 보안 정책이 정의되고 사용하도록 설정된 테이블을 쿼리하는 경우 행은 보안 정책에 정의된 대로 필터링되거나 차단됩니다.스키마 바운드 보안 정책에 의해 바인딩된 테이블의 스키마를 변경하려 하면 오류가 발생합니다. 그러나 조건자에 의해 참조되지 않는 열은 변경할 수 있습니다.
지정된 작업에 대해 정의된 조건자가 이미 있는 테이블에 조건자를 추가하려고 하면 오류가 발생합니다. 조건자가 사용되도록 설정된 상태인지에 관계없이 오류가 발생합니다.
스키마 바인딩된 보안 정책 내의 테이블에서 조건자로 사용되는 함수를 수정하려고 하면 오류가 발생합니다.
겹치지 않는 조건자를 포함하는 여러 활성 보안 정책을 정의하면 성공합니다.
필터 조건자는 다음 동작을 수행합니다.
- 테이블의 행을 필터링하는 보안 정책을 정의합니다. 애플리케이션은 ,
UPDATE및DELETE작업에 대해SELECT필터링된 행을 인식하지 못합니다. 모든 행이 필터링되는 상황을 포함합니다. 애플리케이션은 다른 작업 중에 필터링되더라도 행을 사용할 수INSERT있습니다.
블록 조건자의 동작은 다음과 같습니다.
블록 조건자는
UPDATE다음과 같은 별도의 작업으로 분할됩니다BEFOREAFTER. 예를 들어 사용자가 행을 업데이트하여 현재 값보다 높은 값을 갖도록 차단할 수는 없습니다. 이러한 종류의 논리가 필요한 경우 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조건자로 사용되는 함수에 대한 권한과REFERENCESREFERENCES정책에 바인딩되는 대상 테이블에 대한 사용 권한입니다.REFERENCES인수로 사용되는 대상 테이블의 모든 열에 대한 사용 권한입니다.
보안 정책은 데이터베이스의 dbo 사용자를 포함하여 모든 사용자에게 적용됩니다. Dbo 사용자는 보안 정책을 변경하거나 삭제할 수 있습니다. 그러나 보안 정책에 대한 변경 내용을 감사할 수 있습니다. 높은 권한이 있는 사용자(예: sysadmin 또는 db_owner)가 문제를 해결하거나 데이터의 유효성을 검사하기 위해 모든 행을 볼 수 있어야 하는 경우 이를 허용하도록 보안 정책을 작성해야 합니다.
를 사용하여 보안 정책을 만든 SCHEMABINDING = OFF경우 대상 테이블을 쿼리하려면 조건자 함수 및 조건자 함수 내에서 사용되는 추가 테이블, 뷰 또는 함수에 대한 사용 권한이 있어야 합니다 SELECTEXECUTE . 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) 옵션의 영향을 받으므로 오류(예: 오버플로 또는 0으로 나누기)가 발생하는 경우 반환
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는 필터링되지 않은 데이터에 대한 통계를 보고하고, 그렇지 않으면 보안 정책으로 보호되는 정보를 유출할 수 있습니다. 이러한 이유로 행 수준 보안 정책이 있는 테이블에 대한 통계 개체를 볼 수 있는 액세스가 제한됩니다. 사용자가 테이블을 소유해야 하거나 사용자가 고정 서버 역할,db_owner고정 데이터베이스 역할 또는 고정 데이터베이스 역할의sysadmindb_ddladmin멤버여야 합니다.파일 스트림: RLS는 Filestream과 호환되지 않습니다.
PolyBase: RLS는 Azure Synapse 및 SQL Server 2019 CU7 이상의 외부 테이블에서 지원됩니다.
메모리 최적화 테이블: 메모리 최적화 테이블에서 보안 조건자로 사용되는 인라인 테이블 반환 함수는 이 옵션을 사용하여
WITH NATIVE_COMPILATION정의해야 합니다. 이 옵션을 사용하면 메모리 최적화 테이블에서 지원되지 않는 언어 기능이 차단되며 만들 때 해당 오류가 발생합니다. 자세한 내용은 메모리 최적화 테이블의 행 수준 보안을 참조 하세요.인덱싱된 뷰: 일반적으로 뷰 위에 보안 정책을 만들 수 있으며 보안 정책에 바인딩된 테이블 위에 뷰를 만들 수 있습니다. 그러나 인덱스를 통한 행 조회는 정책을 무시하므로 보안 정책이 있는 테이블 위에 인덱싱된 뷰를 만들 수 없습니다.
변경 데이터 캡처: CDC(변경 데이터 캡처)는 테이블에 대해 CDC를 사용할 때 지정된 "게이팅" 역할의
db_owner멤버 또는 멤버로 필터링해야 하는 전체 행을 누수할 수 있습니다. 모든 사용자가 변경 데이터에 액세스할 수 있도록 이 함수NULL를 명시적으로 설정할 수 있습니다. 실제로db_owner이 게이팅 역할의 멤버는 테이블에 보안 정책이 있더라도 테이블의 모든 데이터 변경 내용을 볼 수 있습니다.변경 내용 추적: 변경 내용 추적 둘 다
SELECT권한이VIEW CHANGE TRACKING있는 사용자에게 필터링해야 하는 행의 기본 키를 누설할 수 있습니다. 실제 데이터 값은 유출되지 않습니다. 특정 기본 키가 있는 행에 대해 A 열이 업데이트/삽입/삭제되었다는 사실만 있습니다. 기본 키에 주민등록번호와 같은 기밀 요소가 포함되어 있으면 문제가 됩니다. 그러나 실제로는 최신 데이터를 가져오기 위해 거의 항상 원래 테이블과 조인CHANGETABLE됩니다.전체 텍스트 검색: 행 수준 보안을 적용하고 필터링
semanticsimilaritytableCONTAINSTABLEFREETEXTTABLEsemantickeyphrasetablesemanticsimilaritydetailstable해야 하는 행의 기본 키가 누출되는 것을 방지하기 위해 추가 조인이 도입되었기 때문에 다음 전체 텍스트 검색 및 의미 체계 검색 함수를 사용하는 쿼리에 성능이 저하될 것으로 예상됩니다.Columnstore 인덱스: RLS는 클러스터형 및 비클러스터형 columnstore 인덱스와 호환됩니다. 그러나 행 수준 보안이 함수를 적용하므로 최적화 프로그램에서 일괄 처리 모드를 사용하지 않도록 쿼리 계획을 수정할 수 있습니다.
분할된 뷰: 분할된 뷰에서 블록 조건자를 정의할 수 없으며 블록 조건자를 사용하는 테이블 위에 분할된 뷰를 만들 수 없습니다. 필터 조건자는 분할된 뷰와 호환됩니다.
임시 테이블: 임시 테이블은 RLS와 호환됩니다. 그러나 현재 테이블의 보안 조건자는 기록 테이블에 자동으로 복제본(replica) 없습니다. 현재 및 기록 테이블 모두에 보안 정책을 적용하려면 각 테이블에서 개별적으로 보안 조건자를 추가해야 합니다.
기타 제한 사항:
- Microsoft Fabric 및 Azure Synapse Analytics는 필터 조건자만 지원합니다. 블록 조건자는 현재 Microsoft Fabric 및 Azure Synapse Analytics에서 지원되지 않습니다.
예제
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
필터 조건자로 함수를 추가하는 보안 정책을 만듭니다. 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);
Sales2 이제 Sales1 사용자는 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)에 등록되어야 하며 권한이 있는 Azure Storage 계정이
Storage Blog Data Contributor있어야 합니다. 단계에 따라 Azure SQL Database의 서버에 대한 가상 네트워크 서비스 엔드포인트 및 규칙을 사용합니다. - Azure Storage 계정에 대한 파일 시스템을 생성합니다. Azure Storage Explorer를 사용하여 스토리지 계정을 봅니다. 컨테이너를 마우스 오른쪽 단추로 클릭하고 파일 시스템 만들기를 선택합니다.
필수 구성 요소가 준비되면 서로 다른 액세스 기능을 보여 주는 세 개의 사용자 계정을 만듭니다.
--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())와 같거나 쿼리를 실행하는 사용자가 사용자(USER_NAME() = 'Manager')인 경우 함수가 반환 1 됩니다Manager.
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및 Sales2Manager.로 로그인합니다. 개별 사용자로 다음 명령을 실행합니다.
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
);
각 애플리케이션 사용자에 대해 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. 블록 조건자는 열이 이전에 설정된 열 권한으로 인해 다른 값으로 업데이트할 수 없으므로 이미 필터링되었 AppUserIdAFTER UPDATE 으므로 필요만 있으면 AFTER INSERTBEFORE UPDATEBEFORE DELETE 됩니다.
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);
이제 다른 사용자 ID를 설정한 후 테이블에서 선택하여 Sales 연결 필터링을 시뮬레이션할 수 있습니다 SESSION_CONTEXT(). 실제로 애플리케이션은 연결을 연 후 현재 사용자 ID를 SESSION_CONTEXT() 설정해야 합니다. 연결이 @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. 보안 조건자에서 조회 테이블을 사용하는 시나리오
이 예제에서는 팩트 테이블에서 사용자 식별자를 지정하지 않고 사용자 식별자와 필터링되는 값 간의 링크에 조회 테이블을 사용합니다. 세 명의 사용자를 만들고 팩트 테이블을 Sample.Sales만들고 채웁니다. 여기에는 6개의 행과 두 개의 행이 있는 조회 테이블이 있습니다. 그런 다음 팩트 테이블을 조회에 조인하여 사용자 식별자를 가져오는 인라인 테이블 반환 함수와 테이블에 대한 보안 정책을 만듭니다. 그런 다음, 다양한 사용자에 대해 select 문을 필터링하는 방법을 보여 줍니다.
다양한 액세스 기능을 보여 주는 세 개의 사용자 계정을 만듭니다.
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
);
Sample.Sales 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;
새 스키마 및 인라인 테이블 반환 함수를 만듭니다. 이 함수는 사용자가 팩트 테이블을 쿼리하고 테이블의 열이 열의 팩트 테이블에 Sample.SalesProduct 조인될 때 쿼리를 실행하는 사용자(@SalesRep = USER_NAME())와 동일하거나 쿼리를 실행하는 사용자가 사용자(USER_NAME() = 'Manager')인 경우 반환 1 됩니다Manager.Lk_Salesman_ProductSalesRep
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);
Sales2 이제 Sales1 사용자는 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 FUNCTION및 CREATE SECURITY POLICY를 사용할 CREATE SCHEMA수 있습니다.
이 예제에서는 먼저 스키마 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.comSales2@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)