SQL Server 감사를위한 변경 데이터 캡처

개요

CDC라고도하는 변경 데이터 캡처는 추가 프로그래밍 작업없이 SQL Server 데이터베이스 테이블에서 수행되는 변경 사항을 추적하고 캡처하는 데 유용한 기능으로 SQL Server 2008 버전에 처음 도입되었습니다. SQL Server 2016 이전에는 SQL Server Enterprise 버전 에서만 SQL Server 데이터베이스에서 변경 데이터 캡처를 사용할 수 있었으며 SQL Server 2016부터는 필요하지 않았습니다.

변경 데이터 캡처는 데이터베이스 테이블에 대한 INSERT, UPDATE 및 DELETE 작업을 추적하고 원본 테이블의 동일한 열 구조와 이러한 변경 사항에 대한 설명을 기록하는 추가 열을 사용하여 미러링 된 테이블에 이러한 변경 사항에 대한 자세한 정보를 기록합니다. SQL Server는 삽입 된 값을 보여주는 각 INSERT 문에 대해 하나의 레코드, 삭제 된 데이터를 표시하는 각 DELETE 문에 대한 레코드와 각 UPDATE 문에 대해 두 개의 레코드를 기록합니다. 첫 번째는 변경 전 데이터를 표시하고 두 번째는 수행 후 데이터를 표시합니다. 변화.

• 참고 : CDC에 대한 자세한 내용은 변경 데이터 캡처 를 사용하여 SQL Server의 변경 내용 모니터링을 참조하세요 .

추가 열에는 다음이 포함됩니다.

• SQL Server 엔진에서 기록 된 변경 사항에 할당 한 커밋 로그 시퀀스 번호 (LSN)를 표시하는 __ $ start_lsn 및 __ $ end_lsn
• 동일한 트랜잭션의 다른 변경과 관련된 변경 순서를 표시하는 __ $ seqval , 변경 작업 유형을 표시하는 __ $ operation ( 여기서 1 = 삭제, 2 = 삽입, 3 = 업데이트 (변경 전) 및 4) = 업데이트 (변경 후)
• __ $ update_mask 는 캡처 된 각 열에 대해 정의 된 비트 마스크이며 업데이트 열을 식별합니다.

이 세부 정보를 통해 보안 또는 감사 목적으로 데이터베이스 변경 사항을 모니터링하거나 T-SQL 또는 ETL 방법을 사용하여 이러한 변경 사항을 OLTP 소스에서 대상 OLAP 데이터웨어 하우스로 점진적으로로드 할 수 있습니다.

설치 및 아키텍처

변경 데이터 캡처를 사용하려면 SQL Server 에이전트가 SQL Server 인스턴스에서 실행되고 있어야합니다. SQL Server 데이터베이스 테이블에서 기능이 활성화되면 해당 데이터베이스에 대해 두 개의 SQL Server 에이전트 작업이 생성됩니다. 첫 번째 작업은 데이터베이스 변경 테이블을 변경 정보로 채우고 두 번째 작업은 구성 가능한 보존 기간 인 3 일보다 오래된 레코드를 삭제하여 변경 테이블을 정리합니다.

변경 데이터 캡처는 데이터 변경의 소스 인 SQL Server 트랜잭션 로그에 의존합니다. 변경이 수행되면이 변경 사항이 트랜잭션 로그 파일에 기록됩니다.

• 확인 SQL 서버 트랜잭션 로그 아키텍처 자세한 내용은.

해당 테이블에서 CDC 기능이 활성화 된 경우 CDC 기능에 대한 캡처 프로세스 역할을하는 트랜잭션 로그 복제 로그 판독기 에이전트는 트랜잭션 로그 파일에서 변경 로그를 읽고 이러한 변경 사항에 대한 메타 데이터 정보를 추가하고 아래와 같이 관련 CDC 변경 테이블 :

감사 솔루션으로서의 변경 데이터 캡처

변경 데이터 캡처는 비동기 SQL Server 감사 솔루션으로 사용되어 SELECT 문을 추적하는 옵션없이 INSERT, UPDATE 또는 DELETE 작업과 같은 테이블의 DML 변경을 추적하고 감사 할 수 있습니다.

Change Data Capture를 좋은 SQL Server Audit 솔루션으로 만드는 이유는 몇 가지 T-SQL 명령을 사용하여 쉽게 구성 할 수 있고 수정 프로세스 이전의 값에 대한 기록 정보를 제공하며 데이터 수정 프로세스에 대한 자세한 정보를 제공한다는 것입니다.

변경 데이터 캡처를 사용하여 SQL Server DML 변경 사항을 감사하는 방법을 살펴 보겠습니다.

CDC 활성화

감사를 위해 특정 테이블에서 변경 데이터 캡처를 사용하려면 먼저 SYSADMIN 고정 서버 역할의 구성원이 아래에 표시된대로 sys.sp_cdc_enable_db 시스템 저장 프로 시저를 사용하여 데이터베이스 수준에서 CDC를 먼저 사용하도록 설정해야 합니다. :

USE [CDCAudit] GO EXEC sys.sp_cdc_enable_db GO

해당 데이터베이스에서 CDC가 활성화되었는지 확인하기 위해 다음과 같이 CDC가 활성화 된 데이터베이스 목록에 대해 sys.databases DMV를 쿼리합니다.

데이터베이스 수준에서 CDC를 활성화 한 후에는 sys.sp_cdc_enable_table 시스템 저장 프로 시저를 사용하여 db_owner 고정 데이터베이스 역할의 구성원이 데이터베이스 테이블의 DML 변경 사항을 추적하고 감사 할 수 있습니다. 아래에 표시된 것처럼 @captured_column_list 매개 변수로 지정된 열 목록의 변경 사항 과 @filegroup_name 매개 변수로 지정된 별도의 파일 그룹에 변경 테이블을 만듭니다 .

USE [CDCAudit] GO   EXEC sys.sp_cdc_enable_table   @source_schema = N'dbo',   @source_name   = N'Employee_Main',   @role_name     = NULL,   @filegroup_name = NULL,   @supports_net_changes = 0 GO

해당 테이블에서 CDC가 활성화되었는지 확인하기 위해 아래와 같이 CDC가 활성화 된 현재 데이터베이스 아래의 모든 테이블에 대해 sys.tables DMV를 쿼리합니다.

테이블에서 CDC가 활성화되면 CDC 관련 정보를 저장하기 위해 데이터베이스의 CDC 스키마 아래에 여러 시스템 테이블이 생성됩니다. 이 테이블에는 다음이 포함됩니다.

• 캡처 된 열 목록이 포함 된 CDC .captured_columns 테이블
• 캡처에 사용할 수있는 테이블 목록이 포함 된 CDC .change_tables 테이블
• 캡처 데이터가 활성화 된 이후의 모든 DDL 변경 내역을 기록하는 CDC .ddl_history 테이블
• 변경 테이블과 연관된 모든 인덱스를 포함하는 CDC .index_columns 테이블
• LSN 번호를 시간과 매핑하는 데 사용되는 CDC .lsn_time_mapping 테이블과 마지막으로 소스 테이블에서 DML 변경 사항을 캡처하는 데 사용되는 각 CDC 사용 테이블에 대한 하나의 변경 테이블이 아래와 같이 표시됩니다.

… 그리고 CDC 지원 테이블, 캡처 및 정리 작업과 관련된 SQL 에이전트 작업은 아래와 같이 생성됩니다.

CDC 비활성화

변경 데이터 캡처는 아래와 같이 sys.sp_cdc_disable_table 시스템 저장 프로 시저를 사용하여 특정 테이블에서 쉽게 비활성화 할 수 있습니다 .

… 또는 아래와 같이 sys.sp_cdc_disable_db 시스템 저장 프로 시저를 사용하여 CDC 활성화 테이블에서 하나씩 비활성화 할 필요없이 데이터베이스 수준에서 완전히 비활성화합니다 .

DML 변경 감사

데이터베이스 테이블에서 CDC를 활성화 한 후 해당 테이블에서 아래의 세 가지 데이터베이스 DML 변경 (INSERT, UPDATE, DELETE)을 수행하고 다음과 같이 CDC 기능을 사용하여 이러한 변경 사항을 감사하는 방법을 확인합니다.

앞서 언급했듯이 데이터 수정 사항은 CDC 사용 테이블과 관련된 변경 테이블에 기록됩니다 . 여기서는 [cdc]. [dbo_Employee_Main_CT] 테이블입니다. 소스 테이블에 최근 삽입 된 모든 레코드를보기 위해 유형 2의 모든 작업에 대한 변경 테이블을 쿼리 할 수 ​​있으며 삽입 된 값을 포함하여 INSERT 작업에 대한 전체 정보가 아래와 같이 표시됩니다.

유형 1의 모든 작업에 대한 변경 테이블을 쿼리하면 원본 테이블에서 최근에 삭제 된 모든 레코드가 반환되고 삭제 된 레코드의 값이 아래와 같이 표시됩니다.

마지막으로 유형 3 및 4의 작업에 대한 변경 테이블을 쿼리하여 UPDATE 문을 추적 할 수 있습니다. 그러면 업데이트 이전의 값이 작업 유형 3에, 변경 후 값이 작업 유형 4에 표시됩니다. 아래 그림과 같이:

변경 테이블 쿼리는 Microsoft에서 권장하지 않습니다. 대신 아래와 같이 CDC 사용 테이블과 관련된 CDC.fn_cdc_get_all_changes 시스템 함수를 쿼리 할 수 있습니다.

CDC.fn_cdc_get_all_changes 함수는 아래와 같이 모든 DML 변경 정보를 검색 하는 @from_lsn , @to_lsn , @row_filter_option 매개 변수 를 제공하여 쿼리 할 수 ​​있습니다 .

한계

변경 데이터 캡처를 사용 하면 데이터베이스 DML 변경 사항 만 감사 할 수 있지만 SELECT 문을 모니터링하는 옵션은 없지만 구성 작업은 무시할 수 있습니다. 반면 CDC를 SQL Server Audit 솔루션으로 고려하려면 상당한 유지 관리 및 관리 노력이 필요합니다. 여기에는 추적 데이터가 구성 가능한 일 수 동안 변경 테이블에 보관되고 동일하거나 다른 데이터 파일에 저장되기 때문에 보관 메커니즘 자동화가 포함됩니다.

또한 변경 테이블은 각 데이터베이스 아래에 저장되며 추적 된 각 테이블에 대해 함수가 생성됩니다. 이로 인해 번거롭고 동일한 데이터베이스의 모든 테이블, 동일한 인스턴스의 모든 데이터베이스 또는 여러 인스턴스의 DML 변경 정보를 읽는 통합 감사 보고서를 만드는 데 상당한 프로그래밍 노력이 필요합니다.

SQL Server Audit 솔루션으로서 CDC 기능에 대한 또 다른 제한은 CDC 사용 테이블에서 DDL 변경을 처리하는 데 필요한 어려운 프로세스입니다. 소스 테이블에서 변경 데이터 캡처를 사용하면 해당 테이블에서 DDL 변경을 수행하는 것을 방해하지 않기 때문입니다.

또한 SQL Server 에이전트 서비스가 실행되고 있지 않으면 CDC 캡처 작업이 작동하지 않으며 데이터베이스 복구 모델이 단순하더라도 CHECKPOINT가 실행 중이더라도 로그 잘림이 진행되지 않기 때문에 데이터베이스 로그 파일이 빠르게 증가합니다. 캡처를 기다리는 모든 변경 사항이 CDC 캡처 프로세스에 의해 수집 될 때까지 수행됩니다.