변경 로그 / 감사 데이터베이스 테이블을위한 최상의 디자인?

변경 로그 / 감사 데이터베이스 테이블을위한 최상의 디자인? [닫은]

---

다른 변경 로그 / 감사 (추가, 삭제, 수정 등)를 저장할 데이터베이스 테이블을 만들어야합니다. 특별히 자세한 정보를 저장할 필요가 없으므로 다음과 같은 내용을 생각하고있었습니다.

• id (이벤트 용)
• 트리거 한 사용자
• 이벤트 이름
• 이벤트 설명
• 이벤트의 타임 스탬프

여기에 뭔가 빠졌나요? 복잡하게 만들 계획은 없지만 (이벤트 유형에 대한 다른 테이블을 만들거나 내 필요를 복잡하게하므로 문제가되지 않음) 디자인을 계속 개선 할 수 있습니다.

---

제가 작업중인 프로젝트에서 감사 로그는 설명하신 것과 같이 매우 최소한의 디자인에서 시작되었습니다.

event ID
event date/time
event type
user ID
description

아이디어는 똑 같았습니다. 단순하게 유지하는 것입니다.

그러나이 최소한의 디자인으로는 충분하지 않다는 것이 금방 분명해졌습니다. 일반적인 감사는 다음과 같은 질문으로 요약됩니다.

Who the heck created/updated/deleted a record 
with ID=X in the table Foo and when?

따라서 SQL을 사용하여 이러한 질문에 신속하게 답할 수 있도록 감사 테이블에 두 개의 추가 열이 추가되었습니다.

object type (or table name)
object ID

이때 감사 로그의 디자인이 실제로 안정화되었습니다 (현재 몇 년 동안).

물론 마지막 "개선"은 서로 게이트 키가있는 테이블에서만 작동합니다. 하지만 그거 알아? 감사 할 가치가있는 모든 테이블에는 이러한 키가 있습니다!

---

테이블 / 열 이름, 업데이트가 이루어진 컴퓨터 / 응용 프로그램 등과 같이 감사 할 수있는 몇 가지 사항이 더 있습니다.

이제 이것은 얼마나 상세한 감사가 실제로 필요한지, 어떤 수준에서 필요한지에 따라 다릅니다.

우리는 자체 트리거 기반 감사 솔루션을 구축하기 시작했고 모든 것을 감사하고 복구 옵션도 마련하고 싶었습니다. 이것은 너무 복잡한 것으로 밝혀 졌기 때문에 트리거 기반의 타사 도구 인 ApexSQL Audit 를 리버스 엔지니어링하여 자체 사용자 지정 솔루션을 만들었습니다.

팁 :

• 이전 / 이후 값 포함

• 기본 키 저장을위한 3-4 개의 열 포함 (복합 키인 경우)

• 이미 Robert가 제안한대로 기본 데이터베이스 외부에 데이터 저장

• 특히 복구에 필요할 수있는 보고서를 준비하는 데 상당한 시간을 할애하십시오.

• 호스트 / 애플리케이션 이름 저장 계획 – 의심스러운 활동을 추적하는 데 매우 유용 할 수 있습니다.

---

또한 감사 세부 정보 테이블에서 감사중인 테이블의 기본 키뿐만 아니라 이전 값과 새 값과 해당 값이있는 열을 기록합니다. 감사 테이블이 필요한 이유를 생각하십니까? 누가 언제 변경했는지 알고 싶을뿐만 아니라 잘못된 변경이 발생했을 때 데이터를 복구하는 빠른 방법을 원합니다.

디자인하는 동안 데이터를 복구하는 코드를 작성해야합니다. 회복이 필요한 경우에는 일반적으로 서둘러서 미리 준비하는 것이 가장 좋습니다.

---

여기와 비슷한 질문에 흥미로운 답변이 많이 있습니다. 개인적인 경험에서 추가 할 수있는 것은 다음과 같습니다.

1. 감사 테이블을 다른 데이터베이스에 넣으십시오. 이상적으로는 원본 데이터와 분리해야합니다. 데이터베이스를 복원해야하는 경우 감사 추적을 복원하고 싶지는 않습니다.

2. Denormalize as much as reasonably possible. You want the table to have as few dependencies as possible to the original data. The audit table should be simple and lightning fast to retrieve data from. No fancy joins or lookups across other tables to get to the data.

---

What we have in our table:-

Primary Key
Event type (e.g. "UPDATED", "APPROVED")
Description ("Frisbar was added to blong")
User Id
User Id of second authoriser
Amount
Date/time
Generic Id
Table Name

The generic id points at a row in the table that was updated and the table name is the name of that table as a string. Not a good DB design, but very usable. All our tables have a single surrogate key column so this works well.

---

There are many ways to do this. My favorite way is:

1. Add a mod_user field to your source table (the one you want to log).

2. Create a log table that contains the fields you want to log, plus a log_datetime and seq_num field. seq_num is the primary key.

3. Build a trigger on the source table that inserts the current record into the log table whenever any monitored field is changed.

Now you've got a record of every change and who made it.

---

In general custom audit (creating various tables) is a bad option. Database/table triggers can be disabled to skip some log activities. Custom audit tables can be tampered. Exceptions can take place that will bring down application. Not to mentions difficulties designing a robust solution. So far I see a very simple cases in this discussion. You need a complete separation from current database and from any privileged users(DBA, Developers). Every mainstream RDBMSs provide audit facilities that even DBA not able to disable, tamper in secrecy. Therefore, provided audit capability by RDBMS vendor must be the first option. Other option would be 3rd party transaction log reader or custom log reader that pushes decomposed information into messaging system that ends up in some forms of Audit Data Warehouse or real time event handler. In summary: Solution Architect/"Hands on Data Architect" needs to involve in destining such a system based on requirements. It is usually too serious stuff just to hand over to a developers for solution.

---

According to the principle of separation:

1. Auditing data tables need to be separate from the main database. Because audit databases can have a lot of historical data, it makes sense from a memory utilization standpoint to keep them separate.

2. Do not use triggers to audit the whole database, because you will end up with a mess of different databases to support. You will have to write one for DB2, SQLServer, Mysql, etc.

---

Late to the party, but I highly recommend the AutoAudit project.
It's 100% free and open source. It's authored by SQL MVPs Paul Nielsen and John Sigouin. It's very stable and is currently on version 3.30.

Simple to install. Just run the SP they provide. It will create an Audit Schema, some maintenance SPs and the triggers necessary to do the auditing. From there, just choose which tables you'd like to audit and with what detail.

참고URL : https://stackoverflow.com/questions/201527/best-design-for-a-changelog-auditing-database-table