One of these techniques is database auditing. At a high level, database auditing is basically a facility to track the use of database resources and authority. When auditing is enabled, each audited database operation produces an audit trail of information, including information such as what database object was impacted, who performed the operation and when. The comprehensive audit trail of database operations produced can be maintained over time to allow DBAs and auditors as well as any authorized personnel to perform in-depth analysis of access and modification patterns against data in the database management system (DBMS).
Database auditing helps to answer questions such as, "Who accessed or changed data?" and "When was the data actually changed?" and "What was the old content prior to the change?" Your ability to answer such questions is very important for regulatory compliance. Sometimes it may be necessary to review certain audit data in greater detail to determine how, when and who changed the data.
Why would you need to ask such questions? Consider, the Health Insurance Portability and Accountability Act (HIPAA). This legislation contains language specifying that health care providers must protect individual's health care information even going so far as to state that the provider must be able to provide an individual with a list of everyone who even so much as looked at their information. Think about that. Could you produce a list of everyone who looked at a specific row or set of rows in any database under your control?
Tracking who does what to which specific piece of data is important because there are many threats to the security of your data. External agents trying to compromise your security and access your company data are rightly viewed as a threat to security. But industry studies have shown that the majority of security threats are internal - within your organization. Indeed, some studies have shown that internal threats comprise 60 percent to 80 percent of all security threats. The most typical security threat comes from a disgruntled or malevolent current or ex-employee that has valid access to the DBMS. Auditing is crucial because you may need to find an unauthorized access emanating from an authorized user.
But keep in mind that auditing tracks what a particular user has done once access has been allowed. Auditing occurs postactivity; it does not do anything to prohibit access. Audit trails help promote data integrity by enabling the detection of security breaches, also referred to as intrusion detection. An audited system can serve as a deterrent against users tampering with data because it helps to identify infiltrators.
There are many situations where an audit trail is useful. Your company's business practices and security policies may dictate a comprehensive ability to trace every data change back to the initiating user. Perhaps government regulations (such as the Sarbanes-Oxley Act) require your organization to analyze data access and produce regular reports. You may be required to produce detailed reports on an ongoing basis, or perhaps you just need the ability to identify the root cause of data integrity problems on a case-by-case basis. Auditing is beneficial for all of these purposes.
A typical auditing facility permits auditing at different levels within the DBMS, for example, at the database, database object level, and user levels. One of the biggest problems with existing internal DBMS audit facilities is performance degradation. The audit trails that are produced must be detailed enough to capture before-and-after images of database changes. But capturing so much information, particularly in a busy system, can cause performance to suffer. Furthermore, this audit trail must be stored somewhere, which is problematic when a massive number of changes occur. Therefore, a useful auditing facility must allow for the selective creation of audit records to minimize performance and storage problems.
There are several different names used for database auditing. You may have heard database auditing capabilities referred to as any of the following:
- Data access auditing
- Data monitoring
- Data activity monitoring
Each of these is essentially the same thing: monitoring who did what to which piece of data when. In addition to database auditing, you may wish to include database authorization auditing, which is the process of reviewing who has been granted what level of database access authority. This typically is not an active process, but is useful for regularly reviewing all outstanding authorization to determine if it is still required. For example, database authorization auditing can help to identify ex-employees whose authorization has not yet been removed.
Database Access Auditing Techniques
There are several popular techniques that can be deployed to audit your database structures. Let's briefly discuss three of them and highlight their pros and cons.
The first technique is trace-based auditing. This technique is usually built directly into the native capabilities of the DBMS. Commands or parameters are set to turn on auditing, and the DBMS begins to cut trace records when activity occurs against audited objects. Although each DBMS offers different auditing capabilities, some common items that can be audited by DBMS audit facilities include:
- Login and logoff attempts (both successful and unsuccessful attempts);
- Database server restarts;
- Commands issued by users with system administrator privileges;
- Attempted integrity violations (where changed or inserted data does not match a referential, unique or check constraint);
- Select, insert, update and delete operations;
- Stored procedure executions;
- Unsuccessful attempts to access a database or a table (authorization failures);
- Changes to system catalog tables; and
- Row level operations.
