HOW TO CREATE MSSQL Audit (Server, Database level)

MSSQL 2008부터 제공되는 MSSQL 감사 기능은 트리거나 프로파일러와 같은 기능들의 단점을 극복해주는 가장 적절한 감사 기능이다. SQL SERVER 감사를 구성해 서버와 데이터베이스에서 발생하는 모든 동작을 추적하여 그 내역을 기록(감사 로깅)할 수 있다.
무엇보다도 테이블이나 뷰에 대한 select와 프로시저에 대한 EXEC 동작도 감사할 수 있다는 것이 장점이다.
감사를 사용하지 않는다면 트리거를 사용하거나 실시간 실행 배치를 만들어서 떠다니는 특정 쿼리를 잡아서 로깅하거나 어플리케이션 단에서 로깅을 하도록 해야하는데 감사는 출력 파일을 별도 디스크에 담을 수 있어서 유용할 것 같다.

크게 서버수준(로그인 실패성공, 로그아웃 등)과 데이터베이스 수준에서 감사가 가능하다.
쉽게 설명하면 감사를 만들고 감사스펙을 만들고 둘다 ON 시키면 알아서 로깅한다.



먼저 서버 감사를 만든다.

USE master
GO
-- 서버 감사 만들기
-- 최대사이즈 512mb
-- 파일이 100개되면 롤오버되도록
-- 공간 미리 할당(디폴트가 off)
-- 쿼리딜레이 1초, 감사 실패시 계속
CREATE SERVER AUDIT [Audit_test]
TO FILE ( FILEPATH = 'D:\mssql_audit',
MAXSIZE = 512MB,
MAX_ROLLOVER_FILES = 100,
RESERVE_DISK_SPACE = OFF
) WITH (
QUEUE_DELAY = 1000,
ON_FAILURE = CONTINUE
);

-- 아래 구문처럼 Default로도 생성이 가능하다.
-- CREATE SERVER AUDIT [Audit_test] TO FILE (FILEPATH = 'D:\mssql_audit')

-- 서버 감사 ON
ALTER SERVER AUDIT Audit_test
WITH (STATE = ON)
GO

서버 감사의 STATE를 ON으로 바꿔주면 해당 경로에 감사 파일이 생성된다.

서버 감사 스펙을 생성해보자.
로그인 성공, 실패, 로그아웃 세가지 경우를 잡도록 해본다.

-- 서버 감사 스펙 생성
CREATE SERVER AUDIT SPECIFICATION ServerAuditSpecification
FOR SERVER AUDIT [Audit_test]
ADD (SUCCESSFUL_LOGIN_GROUP), --로그인 성공
ADD (LOGOUT_GROUP), --로그아웃
ADD (FAILED_LOGIN_GROUP) --로그인 실패
GO
-- 서버 감사 스펙 ON
ALTER SERVER AUDIT SPECIFICATION ServerAuditSpecification
FOR SERVER AUDIT Audit_test
WITH (STATE = ON)
GO



다음은 Adventureworks2017 DB에 데이터베이스 수준 감사를 생성한다.
Person.Person에 감사를 적용해보도록 하자.

USE ADVENTUREWORKS2017
GO
CREATE DATABASE AUDIT SPECIFICATION Audit_test_spec
FOR SERVER AUDIT Audit_test
ADD (SELECT ON OBJECT::[Person].[Person] BY public)
GO

-- 방금 생성한 데이터베이스 감사 ON

ALTER DATABASE AUDIT SPECIFICATION Audit_test_spec

FOR SERVER AUDIT Audit_test

WITH (STATE = ON)

GO

이제 실제로 동작하는지 확인해보자.

아래의 쿼리를 수행하고 아까 위에서 생성된 파일을 SELECT 해보도록 하자.

SELECT *

FROM Person.Person

WHERE FirstName LIKE '%JO%'

-- 감사 내용 확인

SELECT *

FROM sys.fn_get_audit_file('D:\mssql_audit\Audit_test*',DEFAULT,DEFAULT)

로그아웃을 하고 로그인을 해보고 다시 수행해보자.

SELECT *

FROM sys.fn_get_audit_file('D:\mssql_audit\Audit_test*',DEFAULT,DEFAULT)

로그인, 로그아웃 기록이 남는 것을 확인할 수 있다.

실제로 적용을 한다면 서버 수준부터 데이터베이스 수준까지 다 잡아야하나 라는 생각이 드는데 실제로 무언가 사고(?)가 난다면 이런 감사를 다 검사하는 것도 귀찮은 일이 될 것 같다.

특수한 환경이 아니라면 적당히 타협해서 데이터베이스 수준의 특정 테이블에 액션을 가하는 정도만 잡아도 충분하지 않을까..

전체를 다 감사하기에는 성능적인 측면에서 서버에 무리를 줄 것이고 모든 기록이 다 남는다는 것 자체가 추후 일을 만드는 것 + 개발 시 아무래도 자유도가 떨어져서 퍼포먼스 측면에서도 비효율 적일 것 같다는 생각이 든다.