table audit
/* 참고자료 express edition 에서도 가능합니다. */
create SERVER AUDIT AuditDataAccess
TO FILE ( FILEPATH ='C:\ttt\' )
WHERE object_name = 'SensitiveData' and database_name = 'testtest' ;
create SERVER AUDIT SPECIFICATION [ServerAuditSpec_ObjectAccess]
FOR SERVER AUDIT AuditDataAccess
add (SCHEMA_OBJECT_ACCESS_GROUP)
WITH (STATE = ON)
http://technet.microsoft.com/ko-kr/library/cc280386.aspx
아래 스크립트는 EE 에서만 가능한 스크립트이며, SE 에서 동작하게 수정 할 수 있습니다.
보안 관련해서 국내 몇몇 컨설팅 업체가 EE 만 된다고 강의나 컨설팅을 하는데 잘못된 정보 입니다.
2012에서는 모든 버전에서 predicate 와 server audit 을 이용해 테이블 수준의 감사가 가능합니다.
-- 우리는 특정 데이터베이스의 특정 테이블에 억세스한 로그를 감사 하려고 한다.
-- 1. 대상 데이터베이스 생성
use master
go
if exists (select * from master.dbo.sysdatabases where name ='auditTargetSampleDB')
begin
alter database auditTargetSampleDB set single_user with rollback immediate
drop database auditTargetSampleDB
end
create database auditTargetSampleDB
go
use auditTargetSampleDB
go
create table tsalary
(idx int identity(1,1)
,name nvarchar(100)
,salary int
)
go
insert tsalary values ('minsouk1@sqltag.org', 100000000)
insert tsalary values ('manha@sqltag.org', 100000000)
go
-- 우리가 관심 있는 정보
select * from tsalary --< 궁금하지만 보면 안되는 정보 보면 누가, 언제, 어디서, 어떤쿼리로 조회 했는지 알고 싶다.
-- 우리가 관심 있는 정보
go
-- 2. 로그온 트리거 작성
-- 로그인을 먼저 해야 로그인정보를 남길 수 있으므로, logon trigger 를 이용하기로 한다.
-- 로그온 하면, session reuse count 를 다시 남기기로 했다. server audit 은 너무 많은 로그를 남기고 관리하기 불편하다.
IF EXISTS (SELECT * FROM SYS.SERVER_TRIGGERS WHERE NAME = 'ConnectionInfoLogonTrigger')
DROP TRIGGER ConnectionInfoLogonTrigger ON ALL SERVER
GO
use master
go
if exists (select * from master.dbo.sysdatabases where name ='DBA')
begin
alter database DBA set single_user with rollback immediate
drop database DBA
end
create database DBA
go
USE DBA
GO
if object_id('tLogonTriggerLog') is not null
drop table tLogonTriggerLog
go
create table tLogonTriggerLog
(
EventType nvarchar(100)
,DayPostTime datetime2
,DayLastSessionPostTime datetime2 -- 마지막 일자별 last session time
,SessionResueCnt bigint -- 세션 resue count
,SPID int
,ServerName nvarchar(100)
,LoginName nvarchar(100)
,LoginType nvarchar(100)
,SID nvarchar(100)
,ClientHost nvarchar(100)
,net_transport nvarchar(40)
,protocol_type nvarchar(40)
,auth_scheme nvarchar(40)
,local_net_address nvarchar(48)
,local_tcp_port int
--,client_net_address nvarchar(48)
,physical_net_transport nvarchar(40)
,program_name nvarchar(100)
)
GO
create clustered index cl_tLogonTriggerLog on tLogonTriggerLog (ClientHost, program_name, loginName, DayPostTime)
go
CREATE TRIGGER ConnectionInfoLogonTrigger
ON ALL SERVER
FOR LOGON
AS
set nocount on
set transaction isolation level read uncommitted
declare @sysdatetime datetime2 = SYSDATETIME()
declare @eventdata xml = eventdata()
if exists
(
select *
from dba.dbo.tLogonTriggerLog
where
SPID = @eventdata.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(100)')
and ClientHost = @eventdata.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(100)')
and program_name = program_name()
and loginName = @eventdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)')
and DayPostTime >= convert(varchar(10), getdate(), 120)
)
begin
update dba.dbo.tLogonTriggerLog set
--DayLastSessionPostTime = @eventdata.value('(/EVENT_INSTANCE/PostTime)[1]', 'nvarchar(100)')
DayLastSessionPostTime = @sysdatetime
, SessionResueCnt = SessionResueCnt + 1
where SPID = @eventdata.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(100)')
and ClientHost = @eventdata.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(100)')
and program_name = program_name()
and loginName = @eventdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)')
and DayPostTime >= convert(varchar(10), getdate(), 120)
end else begin
insert into dba.dbo.tLogonTriggerLog values (
@eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
, @sysdatetime
, @sysdatetime
, 0
, @eventdata.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(100)')
, @eventdata.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(100)')
, @eventdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)')
, @eventdata.value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(100)')
, @eventdata.value('(/EVENT_INSTANCE/SID)[1]', 'nvarchar(100)')
, @eventdata.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(100)')
, CAST(CONNECTIONPROPERTY('net_transport') AS NVARCHAR(40))
, CAST(CONNECTIONPROPERTY('protocol_type') AS NVARCHAR(40))
, CAST(CONNECTIONPROPERTY('auth_scheme') AS NVARCHAR(40))
, CAST(CONNECTIONPROPERTY('local_net_address') AS NVARCHAR(48))
, CAST(CONNECTIONPROPERTY('local_tcp_port') AS INT)
--, CAST(CONNECTIONPROPERTY('client_net_address') AS NVARCHAR(48))
, CAST(CONNECTIONPROPERTY('physical_net_transport') AS NVARCHAR(40))
, program_name()
)
end
GO
-- 3. audit 저장소 정의
use master
go
if exists (select * from sys.server_audits where name = 'Specipic_Object_Security_Audit')
begin
ALTER SERVER AUDIT [Specipic_Object_Security_Audit]
WITH (STATE = OFF)
drop server audit [Specipic_Object_Security_Audit]
end
CREATE SERVER AUDIT [Specipic_Object_Security_Audit]
TO FILE
( FILEPATH = N'd:\audit\'
,MAXSIZE = 100 MB -- 충분하게 확장하길 바란다.
,MAX_ROLLOVER_FILES = 30 -- 충분하게 확장하길 바란다.
,RESERVE_DISK_SPACE = ON
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)
go
-- 4. server audit 시작
ALTER SERVER AUDIT [Specipic_Object_Security_Audit] WITH (STATE = ON)
GO
-- 5. database audit 작성
-- 해당 데이터베이스로 이동
USE auditTargetSampleDB ;
GO
-- Create the database audit specification.
CREATE DATABASE AUDIT SPECIFICATION Audit_Salary_Tables
FOR SERVER AUDIT Specipic_Object_Security_Audit
ADD (SELECT , INSERT, update, delete
ON dbo.tsalary BY dbo /* 추가하고 싶은것들 다 삽입 */ )
WITH (STATE = ON) ;
GO
-- 6. 다른세션을 하나 열고 tsalary 를 조회한다.
-- 우리가 관심 있는 정보
use auditTargetSampleDB
go
select * from tsalary --< 궁금하지만 보면 안되는 정보 보면 누가, 언제, 어디서, 어떤쿼리로 조회 했는지 알고 싶다.
-- 우리가 관심 있는 정보
select * from tx
-- 7. audit 조회
-- 아래 쿼리로 확인한다.
-- 만든 파일의 정확한 경로 및 파일을 확인 후 조회한다.
SELECT
SERVER_PRINCIPAL_NAME
, DATEADD(HH,DATEDIFF(HH, GETUTCDATE(), CURRENT_TIMESTAMP), EVENT_TIME) AS [LOCALPOSTTIME]
, PHYSICAL_NET_TRANSPORT
, CLIENTHOST
, LOCAL_TCP_PORT
, ACTION_ID
, DATABASE_NAME
, STATEMENT
FROM SYS.FN_GET_AUDIT_FILE ('D:\audit\Specipic_Object_Security_Audit_06FF647C-0D85-4389-B2BE-37D3B3BCDF2E_0_130151744368530000.sqlaudit',DEFAULT,DEFAULT) A
OUTER APPLY
( SELECT TOP 1 *
FROM DBA.DBO.TLOGONTRIGGERLOG
WHERE LOGINNAME = SERVER_PRINCIPAL_NAME
AND SPID = SESSION_ID
AND DayLastSessionPostTime <= DATEADD(hh,DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time)
ORDER BY DAYLASTSESSIONPOSTTIME DESC
) b
--WHERE ACTION_ID in ('SL', 'IN') -- select insert 만 잡았다. 적절한 action 을 넣어 검사
ORDER BY EVENT_TIME DESC
-- 8. test query
--SELECT DATEADD(hh,DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time) AS [localPostTime], *
-- FROM sys.fn_get_audit_file ('D:\audit\Payrole_Security_Audit_30DA5D0E-FF0C-462D-A735-05FA9B584A4E_0_130117783222490000.sqlaudit',default,default) a
-- outer apply
-- ( select top 1 *
-- from dba.dbo.tLogonTriggerLog
-- where LoginName = server_principal_name
-- and spid = session_id
-- and DayPostTime <= DATEADD(hh,DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time)) b
-- WHERE ACTION_ID ='SL'
-- ORDER BY EVENT_TIME DESC
--SELECT DATEADD(hh,DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time) AS [localPostTime], *
-- FROM sys.fn_get_audit_file ('D:\audit\Payrole_Security_Audit_30DA5D0E-FF0C-462D-A735-05FA9B584A4E_0_130117783222490000.sqlaudit',default,default) a
-- outer apply
-- ( select row_number() over( order by (select null)) rn, *
-- from dba.dbo.tLogonTriggerLog
-- where LoginName = server_principal_name
-- and spid = session_id
-- and DayPostTime <= DATEADD(hh,DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time)) b
-- WHERE ACTION_ID ='SL'
-- ORDER BY EVENT_TIME DESC
-- 검사시
-- SELECT * FROM dba.dbo.tLogonTriggerLog
-- SELECT * FROM sys.fn_get_audit_file ('D:\audit\Payrole_Security_Audit_30DA5D0E-FF0C-462D-A735-05FA9B584A4E_0_130117783222490000.sqlaudit',default,default) order by event_time desc
--USE [master]
--GO
--CREATE SERVER AUDIT SPECIFICATION [HIPPA_Audit_Specification]
--FOR SERVER AUDIT [Specipic_Object_Security_Audit]
--ADD (SUCCESSFUL_LOGIN_GROUP)
--WITH (STATE = OFF)
--GO
http://msdn.microsoft.com/en-us/library/cc280448.aspx