감사 로깅을위한 데이터베이스 디자인

감사 로깅을위한 데이터베이스 디자인

---

새 데이터베이스를 디자인해야 할 때마다 변경 사항에 대한 감사 로그를 유지하기 위해 데이터베이스 스키마를 설정하는 방법에 대해 꽤 많은 시간을 할애합니다.

여기에 대해 이미 몇 가지 질문이 있었지만 모든 시나리오에 대해 최선의 단일 접근법이 있다는 것에 동의하지 않습니다.

• 개정을위한 데이터베이스 설계
• 변경 로그 감사 데이터베이스 테이블을위한 최상의 디자인
• 감사 추적을 캡처하기위한 데이터베이스 디자인에 대한 아이디어

또한 각 접근 방식의 장단점을 나열하는 데이터베이스 변경 로그 유지 관리에 대한 이 흥미로운 기사를 우연히 발견했습니다 . 매우 잘 작성되어 있고 흥미로운 정보가 있지만 내 결정을 더욱 어렵게 만들었습니다.

내 질문은 : 내가 사용할 수있는 참조가 있습니까, 책이나 의사 결정 트리와 같은 어떤 입력 변수를 기반으로 어떤 방법으로 갈지를 결정할 수 있습니까?

• 데이터베이스 스키마의 성숙
• 로그를 쿼리하는 방법
• 레코드를 다시 작성해야 할 확률
• 더 중요한 것 : 쓰기 또는 읽기 성능
• 기록되는 값의 특성 (문자열, 숫자, 얼룩)
• 사용 가능한 저장 공간

내가 아는 접근법은 다음과 같습니다.

1. 생성 및 수정 된 날짜 및 사용자에 대한 열 추가

테이블 예 :

• 신분증
• value_1
• value_2
• value_3
• created_date
• modified_date
• created_by
• modified_by

주요 단점 : 우리는 수정의 역사를 잃었습니다. 커밋 후 롤백 할 수 없습니다.

2. 테이블 만 삽입

테이블 예 :

• 신분증
• value_1
• value_2
• value_3
• ...에서
• 에
• 삭제됨 (부울)
• 사용자

주요 단점 : 외래 키를 최신 상태로 유지하는 방법은 무엇입니까? 필요한 큰 공간

3. 각 테이블에 대해 별도의 히스토리 테이블 작성

히스토리 테이블 예 :

• 신분증
• value_1
• value_2
• value_3
• value_4
• 사용자
• 삭제됨 (부울)
• 타임 스탬프

주요 단점 : 모든 감사 된 테이블을 복제해야합니다. 스키마가 변경되면 모든 로그도 마이그레이션해야합니다.

4. 모든 테이블에 대한 통합 히스토리 테이블 작성

히스토리 테이블 예 :

• table_name
• 들
• 사용자
• new_value
• 삭제됨 (부울)
• 타임 스탬프

주요 단점 : 필요한 경우 레코드 (롤백)를 쉽게 다시 만들 수 있습니까? new_value 열은 거대한 문자열이어야하므로 다른 모든 열 유형을 지원할 수 있습니다.

---

One method that is used by a few wiki platforms is to separate the identifying data and the content you're auditing. It adds complexity, but you end up with an audit trail of complete records, not just listings of fields that were edited that you then have to mash up to give the user an idea of what the old record looked like.

So for example, if you had a table called Opportunities to track sales deals, you would actually create two separate tables:

Opportunities
Opportunities_Content (or something like that)

The Opportunities table would have information you'd use to uniquely identify the record and would house the primary key you'd reference for your foreign key relationships. The Opportunities_Content table would hold all the fields your users can change and for which you'd like to keep an audit trail. Each record in the Content table would include its own PK and the modified-by and modified-date data. The Opportunities table would include a reference to the current version as well as information on when the main record was originally created and by whom.

Here's a simple example:

CREATE TABLE dbo.Page(  
    ID int PRIMARY KEY,  
    Name nvarchar(200) NOT NULL,  
    CreatedByName nvarchar(100) NOT NULL, 
    CurrentRevision int NOT NULL, 
    CreatedDateTime datetime NOT NULL

And the contents:

CREATE TABLE dbo.PageContent(
    PageID int NOT NULL,
    Revision int NOT NULL,
    Title nvarchar(200) NOT NULL,
    User nvarchar(100) NOT NULL,
    LastModified datetime NOT NULL,
    Comment nvarchar(300) NULL,
    Content nvarchar(max) NOT NULL,
    Description nvarchar(200) NULL

I would probably make the PK of the contents table a multi-column key from PageID and Revision provided Revision was an identity type. You would use the Revision column as the FK. You then pull the consolidated record by JOINing like this:

SELECT * FROM Page
JOIN PageContent ON CurrentRevision = Revision AND ID = PageID

There might be some errors up there...this is off the top of my head. It should give you an idea of an alternative pattern, though.

---

If you are using SQL Server 2008, you probably should consider Change Data Capture. This is new for 2008 and could save you a considerable amount of work.

---

I don't know of any reference, but I'm sure someone has written something.

However, if the purpose is simply to have a record of what happened—the most typical use of an audit log—then why not simply keep everything:

timestamp
username
ip_address
procedureName (if called from a stored procedure)
database
table
field
accesstype (insert, delete, modify)
oldvalue
newvalue

Presumably this is maintained by a trigger.

---

We’ll create a small example database for a blogging application. Two tables are required:

blog: stores a unique post ID, the title, content, and a deleted flag. audit: stores a basic set of historical changes with a record ID, the blog post ID, the change type (NEW, EDIT or DELETE) and the date/time of that change. The following SQL creates the blog and indexes the deleted column:

CREATE TABLE `blog` (
    `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
    `title` text,
    `content` text,
    `deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    KEY `ix_deleted` (`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='Blog posts';

The following SQL creates the audit table. All columns are indexed and a foreign key is defined for audit.blog_id which references blog.id. Therefore, when we physically DELETE a blog entry, it’s full audit history is also removed.

CREATE TABLE `audit` (
    `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
    `blog_id` mediumint(8) unsigned NOT NULL,
    `changetype` enum('NEW','EDIT','DELETE') NOT NULL,
    `changetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `ix_blog_id` (`blog_id`),
    KEY `ix_changetype` (`changetype`),
    KEY `ix_changetime` (`changetime`),
    CONSTRAINT `FK_audit_blog_id` FOREIGN KEY (`blog_id`) REFERENCES `blog` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

---

I think there is nothing like a decision tree. Since some of the pros and cons (or the requirements) are not really countable. How do you measure Maturity for instance?

So just line up your business requirements for your audit logging. Try to predict how these requirements might change in the future and generate your technical requirements. Now you can compare it to the pros and cons and choose the right/best option.

And be assured, it doesn't matter how you decide, there will always someone who think you made the wrong decision. However, you did your homework and you justify your decision.

참고URL : https://stackoverflow.com/questions/2015232/database-design-for-audit-logging