SQL Server Audit 설정: 로그인, 권한 변경, 테이블 접근

명령어

USE master;
CREATE SERVER AUDIT [Audit_DBA]
TO FILE (FILEPATH = N'D:\Audit\', MAXSIZE = 256 MB, MAX_ROLLOVER_FILES = 20, RESERVE_DISK_SPACE = OFF)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
ALTER SERVER AUDIT [Audit_DBA] WITH (STATE = ON);

-- Server-wide (covers all databases):
CREATE SERVER AUDIT SPECIFICATION [AuditSpec_Server]
FOR SERVER AUDIT [Audit_DBA]
    ADD (FAILED_LOGIN_GROUP),
    ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
    ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
    ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
    ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
    ADD (SERVER_PERMISSION_CHANGE_GROUP),
    ADD (DATABASE_PERMISSION_CHANGE_GROUP),
    ADD (AUDIT_CHANGE_GROUP)
WITH (STATE = ON);

-- Database: access to a sensitive table:
USE [Musteri];
CREATE DATABASE AUDIT SPECIFICATION [AuditSpec_Musteri]
FOR SERVER AUDIT [Audit_DBA]
    ADD (SELECT, INSERT, UPDATE, DELETE ON OBJECT::[dbo].[Siparis] BY [public])
WITH (STATE = ON);

-- Read:
SELECT event_time, action_id, succeeded, server_principal_name, database_name,
       schema_name, object_name, LEFT(statement, 200) AS stmt, client_ip, application_name
FROM   sys.fn_get_audit_file(N'D:\Audit\Audit_DBA_*.sqlaudit', DEFAULT, DEFAULT)
WHERE  event_time >= DATEADD(HOUR, -24, SYSUTCDATETIME())
ORDER BY event_time DESC;

하는 일

파일에 기록하는 server audit를 생성한다. 서버 전체의 로그인 실패, 롤 멤버십, principal 및 권한 변경과 데이터베이스 수준에서 선택한 테이블에 대한 SELECT/DML 접근을 기록하고, T-SQL로 기록을 읽는다.

사용 시점

감사/컴플라이언스 요구 사항(GDPR, PCI, SOX), "누가 이 테이블을 읽거나 변경했나" 질문, 또는 권한 상승이 의심될 때.

주의

바쁜 테이블의 SELECT 감사는 기록을 매우 빠르게 생성하고 성능 비용이 들므로 객체와 principal로 범위를 좁힌다. ON_FAILURE = SHUTDOWN은 audit를 기록할 수 없을 때 서버를 중지하므로 의무 사항일 때만 사용한다. audit 폴더는 SQL Server 서비스 계정이 쓸 수 있어야 하고 DBA가 아닌 사용자가 삭제할 수 없어야 한다. event_time은 UTC다. database audit specification은 데이터베이스와 함께 복원되지만 server audit GUID가 일치하지 않으면 고아 상태로 남는다.