개요
SQL 데이터베이스에서 사용자의 활동 로그를 저장하는 것은 보안 및 감사 목적에 매우 중요합니다. 이러한 로그는 사용자가 데이터베이스에 접속하고 쿼리를 실행하는 시간과 내용을 기록합니다. 이 문서에서는 SQL 데이터베이스에서 로그인한 사용자의 활동을 추적하고 저장하기 위한 방법에 대해 알아보겠습니다.
로그 기록 테이블 생성
먼저, 로그 기록을 위한 새로운 테이블을 생성해야 합니다. 예를 들어, ‘user_activity_log’라는 테이블을 생성할 수 있습니다. 이 테이블에는 다음과 같은 열을 포함시킬 수 있습니다.
CREATE TABLE user_activity_log (
log_id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL,
login_time TIMESTAMP NOT NULL,
query TEXT NOT NULL
);
위의 예시에서는 ‘log_id’ 열을 기본키로 사용하고, ‘user_id’ 열은 로그인한 사용자의 식별자를 저장하기 위해 사용합니다. ‘login_time’ 열은 사용자가 로그인한 시간을 저장하고, ‘query’ 열은 사용자가 실행한 쿼리 내용을 저장합니다.
로그인 트리거 생성
로그인한 사용자의 활동을 자동으로 로그로 기록하기 위해 트리거를 생성해야 합니다. 이를 위해 데이터베이스에서 로그인 트리거를 생성할 수 있습니다. 예를 들어, Postgres 데이터베이스에서 다음과 같은 트리거를 생성할 수 있습니다.
CREATE TRIGGER log_user_activity
AFTER LOGIN ON DATABASE
FOR EACH STATEMENT
EXECUTE FUNCTION log_user_activity_function();
위의 예시에서는 ‘log_user_activity’라는 트리거를 생성하고, 데이터베이스에 로그인이 발생할 때마다 ‘log_user_activity_function()’ 함수가 실행됩니다.
로그 기록 함수 생성
로그인한 사용자의 활동 로그를 기록하는 함수를 작성해야 합니다. 예를 들어, Postgres 데이터베이스에서 다음과 같은 함수를 작성할 수 있습니다.
CREATE FUNCTION log_user_activity_function() RETURNS TRIGGER AS $$
BEGIN
INSERT INTO user_activity_log (user_id, login_time, query)
VALUES (current_user_id(), current_timestamp, current_query());
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
위의 예시에서는 ‘log_user_activity_function()’ 함수를 생성하고, ‘user_activity_log’ 테이블에 사용자의 식별자, 로그인 시간, 실행한 쿼리를 삽입합니다.
정리
위의 방법을 사용하여 SQL 데이터베이스에서 로그인한 사용자의 활동 로그를 추적하고 저장할 수 있습니다. 이를 통해 보안 및 감사 요구 사항을 충족시킬 수 있으며, 문제가 발생한 경우 트랜잭션 로그를 통해 이해하고 문제 해결에 도움을 받을 수 있습니다. 이러한 로그는 데이터베이스 관리 및 성능 튜닝에도 유용하게 사용될 수 있습니다.