SQL Server 변경 추적을 사용하여 SQL Server 감사 생성

이 SQL Server 감사 시리즈는 SQL Server 변경 추적에 관한 내용이며 SQL 업데이트 및 삭제를 포함한 DML 변경 사항을 활성화, 비활성화, 감사하는 개요를 다룹니다.

이것이 SQL Server Audit 시리즈의 첫 번째 읽기 인 경우이 시리즈의 이전 기사 ( 하단 의 TOC 참조 )를 통해 SQL Server Audit의 개념에 대한 확실한 배경을 구축하는 것이 좋습니다. SQL Server 인스턴스 및 데이터베이스를 감사하는 이유와 SQL Server 데이터베이스를 감사하는 데 사용할 수있는 다양한 방법. 이 기사에서는 SQL Server 변경 추적을 사용하여 SQL Server 감사를 수행하는 방법에 대해 설명합니다.

개요

CT라고도하는 SQL Server 변경 추적은 SQL Server 2008에 처음 도입 된 경량 추적 메커니즘으로, SQL Server 데이터베이스 테이블에서 수행 된 DML 변경을 추적하는 데 사용할 수 있습니다. SQL 변경 내용 추적은 무료 Express 버전을 포함한 모든 SQL Server 버전에서 구성 할 수 있습니다.

SQL Server 변경 추적은 동기 추적 메커니즘으로, 변경 데이터 캡처 비동기 메커니즘과 같은 트랜잭션 로그 파일에서 변경 내용을 읽는 동안 지연없이 DML 변경이 커밋되면 변경 정보를 직접 사용할 수 있습니다. . 즉, SQL 변경 내용 추적은 DML 변경 내용을 캡처하거나 쓸 때 SQL 에이전트 작업에 대한 종속성이 없기 때문에 SQL Server 에이전트 서비스를 가동하고 실행할 필요가 없습니다.

SQL Server 변경 내용 추적이 데이터베이스 테이블에서 활성화되면 SQL Server 엔진은 Change_Tracking_ <Object_ID> 라는 이름으로 내부 테이블을 만들어 추적 된 사용자 테이블의 INSERT, UPDATE 및 DELETE 문을 추적합니다. SQL 변경 추적을 변경 데이터 캡처 기능 보다 가볍게 만드는 이유는 추적 된 테이블 내의이 행에 대해 수행 된 DML 변경이 기본 키를 제공함을 언급하여 데이터베이스 테이블 변경을 추적한다는 것입니다.수정 된 행의 열 값, 변경된 열 및 수정 유형, 최소 저장 요구 사항 및 오버 헤드로 삽입 또는 삭제 된 값 또는 업데이트 프로세스 전후의 값을 쓰는 등 변경된 데이터에 대한 자세한 정보를 쓰지 않습니다. 이 키 값은 추적 된 테이블에서 수정 된 행을 식별하는 데 사용되므로 해당 테이블에서 SQL 변경 내용 추적을 활성화하기 위해 데이터베이스 테이블에 기본 키가 있어야하는 이유입니다.

감사 솔루션으로서의 SQL Server 변경 추적

SQL Server 변경 추적은 레거시 SQL Server 감사 솔루션으로 간주되며, 변경된 행, 해당 행의 기본 키 및 무엇과 같은 간단한 감사 질문에 대답하여 데이터베이스 테이블 DML 변경을 추적하고 감사하는 데 사용할 수 있습니다. 해당 행에서 수행 된 변경 유형.

SQL 변경 추적이 SQL Server 데이터베이스 감사 솔루션으로서 덜 바람직하게 만드는 이유는 업데이트 프로세스 전후에 삽입 된 데이터, 삭제 된 데이터 또는 데이터에 대한 정보를 기록하지 않는다는 것입니다. 또한 기본 키 제약 조건이 정의되지 않은 테이블은 기본 키 제약 조건이있는 데이터베이스 테이블에 대해서만 제한되므로 SQL 변경 추적을 사용하여 감사 할 수 없습니다.

반면에 SQL Server 변경 내용 추적은 데이터베이스 테이블에서 수행 된 변경 사항에 대한 기록을 기록하지 않으며 버전 기록을 유지하지 않고 해당 행에서 수행 된 마지막 변경 사항을 기록합니다. 예를 들어 행이 삽입 된 다음 여러 번 업데이트되고 마지막으로 삭제되면 SQL Server Audit 솔루션 인 SQL 변경 추적은 해당 행에서 수행 된 이전 작업을 고려하지 않고 마지막 삭제 문만 기록합니다.

SQL Server Chang Tracking을 사용하여 유용한 SQL Server Audit 솔루션을 구축하려면 다음에 저장된 변경된 행의 기본 키 값을 기반으로 SQL 변경 내용 추적의 내부 테이블을 추적 된 원본 테이블과 조인하는 데 추가적인 코딩 작업이 필요합니다. 변경된 데이터에 대한 완전한 정보를 얻기위한 내부 테이블. 내부 테이블이 표시되지 않고 직접 쿼리 할 수 ​​없기 때문에 나중에 설명 할 이러한 내부 테이블을 기반으로 구축 된 온 디스크 테이블을 사용하는 SQL 변경 추적 기능의 이점을 얻을 수 있습니다. 이 기사.

여기서 고려해야 할 또 다른 사항은 SQL 변경 내용 추적 내부 테이블이 시간이 지남에 따라 점진적으로 증가한다는 것입니다. 제거 프로세스는 자동 정리 스레드에 의해 제어되지만 사전 정의 된 보존 기간 (기본값 2 일)을 기반으로 내부 디스크 테이블에서 오래된 데이터를 제거하는 역할을하지만 적절한 보존 기간을 다음으로 설정해야합니다. SQL Server Audit 솔루션에 사용할 수있는 변경 데이터를 유지합니다. 또한 SQL Server 2016에 추가 된 새로운 저장 프로 시저 를 활용하여 내부 SQL Server 변경 추적 테이블에 대한 수동 정리를 수행 할 수 있습니다.

SQL 변경 추적을 사용하여 SQL Server DML 변경 사항을 감사하는 방법을 살펴 보겠습니다.

SQL 변경 추적 활성화

감사 목적으로 데이터베이스 테이블에서 SQL Server 변경 추적을 사용하려면 내부 디스크 테이블에 대한 보존 기간을 제공하여 ALTER DATABASE T-SQL 문을 사용하여 데이터베이스 수준에서 사용하도록 설정해야합니다. 자동 정리 프로세스를 활성화하면 아래와 같이 보존 기간보다 오래된 내부 디스크 테이블의 데이터가 자동으로 삭제됩니다.

USE master GO ALTER DATABASE [CTAudit] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

또한 아래와 같이 내부 디스크 테이블의 보존 기간을 지정하고 자동 정리 프로세스를 활성화 할 수있는 데이터베이스 속성 창의 변경 내용 추적 탭 에서 SQL Server Management Studio 도구를 사용하여 활성화 할 수도 있습니다.

데이터베이스 수준에서 SQL Server 변경 추적을 활성화 한 후에는 DML 변경 사항을 추적하고 감사 할 각 테이블에서이를 활성화해야합니다. 이는 아래의 ALTER TABLE T-SQL 문을 사용하여 수행 할 수 있습니다.

USE CTAudit GO ALTER TABLE Employee_Main ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)

기본 키가 정의되지 않은 데이터베이스 테이블에서 SQL 변경 내용 추적을 활성화하려고하면 ALTER TABLE 문이 실패하여 아래 오류 메시지와 같이 SQL 변경 내용 추적을 활성화하기 전에 테이블에 기본 키를 만들어야 함을 보여줍니다. :

테이블에 Primary Key 제약 조건을 추가하면 ALTER TABLE 문이 성공적으로 실행됩니다. 또한 아래와 같이 테이블 속성 창의 SQL 변경 내용 추적 탭에서 SQL Server Management Studio를 사용하여 SQL 변경 내용 추적을 활성화 할 수도 있습니다.

SQL 변경 내용 추적 비활성화

데이터베이스 테이블에서 SQL Server 변경 추적을 활성화해도 해당 테이블에서 CT가 비활성화되지 않는 한 실패 할 기본 키의 변경을 제외하고는 해당 테이블에서 DDL 변경을 수행 할 수 없습니다.

아래의 ALTER TABLE T-SQL 문을 사용하여 테이블 수준에서 변경 내용 추적을 비활성화 할 수 있습니다.

테이블 수준에서 비활성화 한 후에는 아래의 ALTER DATABASE T-SQL 문을 사용하여 데이터베이스 수준에서 변경 추적을 쉽게 비활성화 할 수 있습니다.

DML 변경 감사

INSERT 감사

SQL Server 데이터베이스 감사 목적으로 데이터베이스 테이블에서 SQL Server 변경 내용 추적을 사용하면 테이블 행에서 수행되는 모든 DML 변경 내용이 CT 내부 테이블에 기록됩니다. Employe_Main 테스트 테이블에 아래 INSERT 문을 수행한다고 가정합니다.

INSERT 문 이후 내부 테이블에 기록 된 변경 내용 추적 데이터를 가져 오려면 CHANGETABLE 시스템 함수 와 같은 변경 내용 추적 기능을 사용할 수 있습니다 . CHANGETABLE 함수는 지정된 버전 번호 이후에 추적 된 테이블에서 수행 된 모든 변경 사항을 반환합니다. 버전 번호 카운터는 변경된 각 행과 연결되며 추적 된 테이블에 변경 사항이있을 때마다 증가합니다. 아래 T-SQL 스크립트를 사용하여 변경 정보를 검색 할 수 있습니다.

SELECT * FROM CHANGETABLE (CHANGES [Employee_Main],0) as CT ORDER BY SYS_CHANGE_VERSION

INSERT 문을 수행 한 후 CT 내부 테이블에서 반환 된 데이터에는 수행 된 DML 변경의 버전, DML 작업 유형 (이 경우 INSERT의 경우 I)이 표시됩니다. INSERT의 경우와 마지막으로 SQL Server 감사 테이블에 삽입 된 행의 기본 키 값은 아래와 같습니다.

삽입 된 전체 레코드를 얻으려면 아래 T-SQL 스크립트 에서처럼 기본 키 값을 기반으로 추적 된 소스 테이블과 CHANGETABLE 함수를 쉽게 조인 할 수 있습니다.

SELECT CT.SYS_CHANGE_VERSION,   CT.SYS_CHANGE_OPERATION, EM.*   FROM CHANGETABLE (CHANGES [Employee_Main],0) as CT JOIN [dbo].[Employee_Main] EM ON CT.Emp_ID = EM.Emp_ID ORDER BY SYS_CHANGE_VERSION

CT 함수와 추적 된 소스 테이블을 결합하여 반환 된 결과는 삽입 된 데이터에 대한 완전한 정보를 표시하며, 이는 아래와 같이 데이터베이스 테이블을 감사하는 데 유용 할 수 있습니다.

업데이트 감사

동일한 추적 테이블에서 아래 UPDATE 문이 수행되는 경우 :

그런 다음 추적 된 소스 테이블과 CHANGETABLE 함수를 결합하는 이전 쿼리를 실행하면 Emp_ID 값이 2 인 행의 변경 버전 번호가 증가하는 것을 볼 수 있습니다. 또한 해당 행에서 수행 된 변경의 마지막 버전 만 기록되고 이전 INSERT 문이 누락되고 마지막 업데이트 문이 유지됩니다.이 문은 내부적으로 이전 레코드를 삭제 한 다음 새 값으로 레코드를 삽입합니다. 아래 그림과 같이:

감사 삭제

아래 DELETE 문을 실행하여 추적 된 테이블에서 세 번째 행을 삭제한다고 가정합니다.

그런 다음 CHANGETABLE 함수를 추적 된 소스 테이블과 결합하는 동일한 쿼리를 실행하여 삭제 된 레코드 정보를 확인합니다. 아래에 표시된 것처럼 삭제 된 레코드 데이터가 추적 된 소스 테이블에 없기 때문에 표시되지 않습니다.

이전 쿼리에서 JOIN 유형을 LEFT OUTER JOIN으로 변경하면 CHANGETABLE 함수에서 삭제 된 레코드 정보가 검색되는 것을 볼 수 있습니다. 이 정보에는 변경 버전 번호와 변경 유형 (DELETE의 경우 D) 만 포함되며 아래에 표시된 것처럼 삭제 된 레코드에 대한 정보는 없습니다.

이전 결과에서 SQL Server 변경 추적을 제한된 SQL Server 데이터베이스 감사 솔루션으로 사용하여 CT 지원 테이블에서 DML 변경을 추적 할 수 있다는 것이 분명합니다. 이는 업데이트 또는 삭제 작업 이전의 값에 대한 기록 정보없이 수정 된 레코드에 대해 수행 된 마지막 변경 만 반환하기 때문입니다.