Who Audit Security
Week 3: Reading
The following are the reading articles:
- Definitions
- Data Modeling
- Common Lookup
- Who Audit Security
Who-Audit Security
Learning Outcomes
- Learn about who-audit security.
- Learn how to implement who-audit columns.
Lesson Materials
Commercially designed software takes many precautions. One of those precautions requires identifying who and when a row is created or updated. As a rule, they create at least:
- A column that stores who created the row.
- A column that stores when the row was created by a user.
- A column that stores who last updated the row.
- A column that stores when the row was last updated by a user.
In large commercial grade application software, they also create two other columns. They’re reserved for capturing the who-audit information for core processes or batch processing jobs. They’re typically implemented as:
- A column that stores which process created the row.
- A column that stores when the row was created by a process.
- A column that stores which process last updated the row.
- A column that stores when the row was last updated by a process.
Application software, like web forms, create and update the user who-audit columns while leaving the process who-audit columns empty. Likewise, application software, like background or batch processing jobs create and update the process who-audit columns while leaving the user who-audit columns empty.
The following table lists the typical user who-audit columns:
| Any Table | |||||
|---|---|---|---|---|---|
| Column Name | Constraint | Data Type |
Physical Size |
||
| Type | Reference Table | Reference Column | |||
| CREATED_BY | FOREIGN KEY | SYSTEM_USER | SYSTEM_USER_ID | Integer | Maximum |
| NOT NULL | |||||
| CREATION_DATE | NOT NULL | DATE | Date | ||
| LAST_UPDATED_BY | FOREIGN KEY | SYSTEM_USER | SYSTEM_USER_ID | Integer | Maximum |
| NOT NULL | |||||
| LAST_UPDATE_DATE | NOT NULL | DATE | Date | ||
The following table lists the typical process who-audit columns:
| Any Table | |||||
|---|---|---|---|---|---|
| Column Name | Constraint | Data Type |
Physical Size |
||
| Type | Reference Table | Reference Column | |||
| PROCESS_ID | FOREIGN KEY | SYSTEM_USER | SYSTEM_USER_ID | Integer | Maximum |
| NOT NULL | |||||
| CREATION_DATE | NOT NULL | DATE | Date | ||
| UPDATED_BY_PROCESS | FOREIGN KEY | SYSTEM_USER | SYSTEM_USER_ID | Integer | Maximum |
| NOT NULL | |||||
| LAST_UPDATE_DATE | NOT NULL | DATE | Date | ||
You should note that the who-audit columns are NOT NULL constrained, which means you can’t create a row without providing a valid value. Valid values should exist in the SYSTEM_USER_ID column of the SYSTEM_USER table.
