Abstract
Today, most organisations own
databases with enormous amounts of highly sensitive data. These databases hold
sensitive information of the companies such as financial records, employee
salary figures and other sensitive data. Due to frequently reported cases of
fraud and data theft it has become a top priority for the DBAs to secure their
databases. Database auditing involves the logging, analyzing and reporting of
any malicious transaction that has or is happening on the database. DBAs adopt
different auditing strategies and techniques depending on their policy
requirement and resources available. We will discuss these techniques and find
out how they address the databases’ security needs.
1.
Introduction
The last two decades saw an
exponential growth in internet and information system usage. This growth
brought with itself new security concerns since a considerable amount of the
data now being made available on the web is sensitive. Millions of transactions
take place on an organization’s online system every week. All organisations
need to secure their systems to ensure the integrity, availability and
confidentiality of data. While most of the efforts are made to prevent
unauthorized access from outsiders, reports reveal that insider attacks cause
equally severe damage to organizations’ data and assets. Such requirements are
driving the growth of new and improved softwares, methods and techniques.
One of these techniques is
database auditing. Auditing has long been recognized as being a critical
element in secure systems. Using the audit data, it is possible to reconstruct
events in a system. Therefore, auditing can be used to provide accountability for
all user actions, including those affecting security. [1] The development of
techniques to audit information systems arises from the need to trace access to
sensitive or important information stored on these systems. Logging records events or statistics to
provide information about system use and performance. Auditing analyzes these records to present information about the
system in a clear and understandable manner. [2] But for a successful logging
and auditing one has to know what information to log, and what information to
audit. These constitute the auditing policies which may differ from
organisation to organisation.
A good database access auditing
solution should be able to provide answers to the following questions:
1.
Who accessed the data?
2.
When (date and time)?
3.
From where (IP address)?
4.
What query was issued to get the
data?
5.
Was the request successful? (If
yes) how many rows of data were retrieved?
6.
What data was changed?
One of the biggest problems with
existing audit strategies is performance degradation. The audit trails that are
generated must be detailed enough to capture before-and-after values of
database changes. But capturing so much information, particularly in a busy
system, can cause performance to suffer. Therefore, a good auditing facility
must allow for the selective creation of audit records to minimize performance
and storage problems.
The objective in this article is
to discuss the various approaches and techniques used in auditing transactions
in databases. Section 2 gives an overview of basic designing of auditing
techniques. The next section discusses the merits and demerits of different
auditing approaches like server side and client side auditing. Section 4
discusses the auditing techniques in detail with each of their pros and cons.
Role of triggers in auditing databases has been discussed in section 5 along
with brief descriptions of DDL triggers and Oracle FGA. Securing audit logs is
the last and one of the most important part in database auditing and has been
discussed in section 6 of this paper.
2.
Basic Design of Auditing System
The implementation of most
of the auditing strategies requires the design and implementation of effective
logging and the traceability of events taking place in the information system. Auditors
need to know the data accessed and the action done on these data to prepare him
against a possible tampering of data. This can be achieved through analysing
the audit trail records. Audit trails contain data in structured format, with
audit information presented as a set of time-stamped events with other user
identifying attributes. This information can be easily analyzed using any available
database reporting tools. The first and foremost question is on which tables of
information and which events the audit trail should be applied because it is
important to keep the size of the audit log to the minimum possible. For this
reason, normal events such as insertion and review of records are not
recommended for an audit logging.
The audit logging needs to
be more innovative in design so that it is easy for the auditor to analyze. It should
give proactive signals and help prevent security incidents rather than throw a
heap of information on auditors/security administrators.
While logging the
modifications, there can be two modes:
·
Horizontal logging—Log the
entire row as available before any modification.
·
Vertical logging—Log only
the columns that have changed during the operations.
Both have their advantages
and disadvantages.
2.1 Horizontal Logging
As stated before, this
design logs the entire row of data as it was before undergoing any modification
or deletion. This should be used only when the change per record (changed
bytes/record size) is large else it would waste the disk space unnecessarily. However,
it is ideal for deleted record logs because the entire record needs to be
logged. Due to large data per record (the complete row) it becomes difficult to
point out the change. Hence it is difficult to input auto alerts based on the
nature or frequency of changes, since change cannot be analyzed easily.
Detecting and reporting only the changes is a difficult process in horizontal
logging.
Fig 1. Horizontal Logging – Entire row is logged.
2.1 Vertical Logging
This logs only the columns
that have changed during the modification. This should be used when the change
per record (changed bytes/record size) is less and hence proves to be
economical as disk space used is less. It is quite easy to point out changes in
this case since vertical logging maintains only the changed items and hence it
is also simple to generate output. Auto alerts can be easily raised in this
case. For example, an alert would be raised if data in the columns like name or
salary is changed twice or more times in a day.
Fig 2. Vertical Logging – Only the required column is logged.
3. Auditing Approach
Auditing of the database
can be performed within the client (client side), within the database (server
side) or between the client and the database. Auditing within the client
involves using the client’s database access tools to obtain a list of activities
that the user has performed through the database. In order for the auditor to
obtain a meaningful audit trail, all transactions that occurred would have to
have occurred through these client tools. It is possible that the user uses any
tool other than these for carrying out transactions on the database. Therefore
this option is the worst option available to the auditor.
The second option involves server
side database auditing. This approach brings with itself several advantages. Server-side
auditing is the only method allowing auditing of every type of database access
by any type of user, regardless of whether users are network based or local to
the server. Server-side auditing can also audit all types of local access to
the database server performed via remote system access protocols such as
Telnet, SSH etc. It also allows auditing user activities performed using encrypted
network protocols (SSH, SSL etc.) and encrypted database client-server communication
protocols (Oracle TCPS, MySQL compression etc.). All these encrypted database
communication protocols can be audited just as well as regular non-encrypted
protocols. Server-side auditing records data to audit log tables or optionally
to external files in a real-time and the recorded data is immediately available
for use in reporting and alerting processes. Where as in the client-side
approach reports and alerts can not be made available to the user in real time
easily. Server-side auditing is the only solution for monitoring every type of
back-door access to the server and the data. No changes or functionality
sacrifices are required on the client-side in any existing applications and
systems when server-side auditing is deployed.
But like everything there
are drawbacks to this approach, primarily due to the limited audit
functionality of DBMS, the inconsistency across DBMS types and the performance
penalty that occurs when the audit mechanism is enabled. Every additional
processing on the database server such as auditing creates additional input
output operations and requires additional CPU resources. But if the auditing is
setup properly and focused on what is really needed the performance degradation
would be really negligible and that too will be offset by the benefits offered
by the server-side auditing. Auditing within the database is certainly better
than auditing within the client.
The third and next approach
is auditing between the client and the database. It involves auditing the
communications between the client and the database. The data packets between
the client and server are captured, interpreted and then audited. This process
can be accomplished using third party commercial software.
4. Implementation
Techniques
The most basic aim of
auditing is to keep a track of all the modifications taking place on the
database. Here modification refers to any insertion, deletion and updating of
records in the database. This may also include any change in the audit policy
of the system. There are several approaches which may be implemented to detect
modifications in data records. For choosing the technique to be implemented in
a particular situation one has to consider each technique’s advantages,
disadvantages and the existence of the necessary features in the operational database.
We would refer to the table upon which auditing has to be performed as the
operational table. In the following section we will discuss some of the most
used implementation method for auditing transactions in databases.
4.1
Logging the Entire Modified and Original Row into a New Table
Method:
Create a table audit_<tablename> where <tablename> is the name of
the table in operational database which has to be audited for modifications.
This table has the same structure as that of the operational table including
exact integrity constraints, except for the primary keys in some cases. This
table includes some additional fields like timestamp, user name and a column identifying
the change operation which was performed. Whenever any change occurs on any
field in a row, the entire original and new values of the fields in the row are
logged into the audit_<tablename> table. This method generally employs
the use of triggers.
Necessary
features: The DBMS must support triggers. Enough disk space should be available
as the entire rows are being logged.
Advantages: It
is assured that the entire record is mapped into the log even when all the
values of a row changes.
Disadvantages: Causes
overload on server as the entire row is first read from the old table and then written
again with the changes into the audit table. Server performance degrades
further if triggers are used for this purpose. Requires large storage space on
disk which becomes uneconomical if unwanted data are also logged. Requires
frequent cleaning of the audit log as large amount of data gets piled up very
quickly.
When
to implement: When logging the entire fields in the row becomes really necessary.
Eg. Logging deleted entries of a table. Also when there are very less number of
fields in a table one can go for this method as it won’t affect the disk space
much. Eg. A table containing only
the following columns: CUST_ID and ACC_NO.
4.2
Logging Only the Modified Data into a New Table
Method:
Create an audit table containing the following fields: DBNAME
(name of the operational database), TABNAME (name of the operational table),
COLNAME (name of the column where the target data to be modified
resides), PKIDREC (Primary key value of the record where the target data to be
modified resides, provided the primary key of the table is never modified).
These fields are required to reach at the location of a particular data on the
database. Other fields in this table would include ‘old_value’, ’new_value’, ‘user_name’, timestamp and a
column identifying the change operation which was performed.
Whenever any data is changed in the operational table the old
and modified values are logged into the audit table along with the user name,
timestamp etc. The other fields in the audit table give the exact location of
the data which was modified. Generally this method too employs the use of
triggers.
Necessary
features: The DBMS must support triggers.
Advantages:
It logs only the necessary (original and modified) data and
hence utilizes lesser disk space than that in the previous method and hence it
becomes easier to identify the changes at one glance. This makes audit
reporting easier. Doesn’t causes much overload on server except when the
triggers (if implemented) are called.
Disadvantages: If
auto increment index is not present in a table there should be one such field
in the table which can never be modified. This field indicates the position of
the row in the table.
When
to implement: When there are large number of fields in a table and changes
are occurring on only a few columns and it is not feasible to log the entire
row because of disk space limitation.
4.3
Adding an Extra Column on the Operational Table for Indicating and Storing the Modification.
Method: Create
some extra columns in the table identifying the last change operation performed
and when it took place and by whom.
Necessary
features: The DBMS must support triggers.
Advantages: Decrease in the need for storage space. Very less
server overload as the process requires only shifting the data from one column
of the table to another column in the same table.
Disadvantages: The
entire record of changes taking place in the operational table is not stored,
only the last change is logged.
When
to implement: When the logging of all changes performed in operational
database is not necessary, only the last change is required or only one or two
columns of a table is to be audited and very few changes take place in them in
a given period of time.
4.4
Auditing from Database Transaction Log.
Method:
Transaction logs are created by database engines to assist with
backup recovery. Typically when a database restore is requested, the database
administrator first restores the last full backup of the database and then
incrementally applies the transaction log backups until the desired time of
restoration is accomplished. Since these transaction logs track the changes to
data, the transaction logs can additionally be used for audit purposes provided
the user has access to a transaction log viewer.
Necessary
features: The DBMS should have implemented the transactions log control. The
translation from the DBMS transactions log of SQL commands for insert, update
and delete should be possible.
Advantages: There
is no overload on the operational database.
Full record of all the transaction is available.
Disadvantages: Requires
large disk space. Transaction log viewers are merely report data that were
created solely for backup recovery and is therefore limited in its ability to
provide selective auditing of data changes within a specific set of criteria. When the size of transaction log crosses its
limit cycling of transaction log takes place and these results into transaction
lost from the log. Thus there is need for controlling the transaction log area
size by the DBA. There is always a possible risk of lost of changes because of
human, physical failure or by an unexpected operation by the DBA.
When
to implement: When the mapping of all or part of the changes occurred in the
operational database is necessary
4.5
Auditing Using Database Snapshots
Database Snapshots allows you to create a read-only copy of your
database that can be used for other purposes, such as reporting, auditing or
recovering data.
Method:
When you create a snapshot, an empty structure of your database
file is created. When data is read from the snapshot, it is actually being read
from the operational database. As data changes in the operational database DBMS
writes the original data (data before modification) into a spare file. The snapshot
will then read that spare file for the data that has been changed and continue
to read the primary database for data that has not changed. Using the snapshot is just like using any
other database. You log in to the database and execute your queries.
Necessary
features: Database Snapshot feature must be available in the DBMS.
Advantages:
The initial creation of the snapshot
or subsequent snapshots is extremely fast. As the data changes it gets written
to a spare file, so the overall disk space don't double each time a new
snapshot is created. Several
snapshots can be created for the same database, so snapshots can be taken frequently
for auditing purposes.
Disadvantages: Snapshot
is a read-only copy, so you can't issue UPDATE, DELETE or INSERT statements to
it. The snapshot is tied to the operational database, so the
operational database cannot be taken offline. If it goes offline, you won't be
able to access any of the snapshots related to that. If data changes rapidly,
this method may not offer benefits for auditing purpose.
When
to implement: When the DBMS does not support triggers or when the
implementation of triggers is not allowed or where there is no transaction log
in DBMS or where there is no possibility for translating the transaction log.
5.
Role of Database Triggers in Auditing
As discussed in the previous
section triggers play a very important role in implementing database auditing.
In this section we will discuss
major advantages and disadvantages of using triggers in the context of
auditing. A trigger is a named database object that is associated with a table
and is activated when a particular event occurs for the table. A trigger-based auditing
approach captures the change at the source, hence the recording of old and new
values is guaranteed. Other than this there are several other advantages of
using triggers. Triggers can improve performance slightly because of the
reduction in the number of messages passed from the client to the database
server. For example, if the trigger fires five SQL statements, the client saves
at least 10 messages passed between the client and database server (one to send
the SQL statement and one for the reply after the database server executes the
SQL statement). Triggers improve performance the most when they execute more
SQL statements and the network speed is comparatively slow.
While logging the modifications
done on the operational database to an audit database it is very important to
maintain a sequential index of all the records. Any row of record can then be
referred by this value. Generally this index is generated automatically by
using the auto increment feature and hence it is also the primary key for the
audit tables. Using a database trigger to populate a primary key with a
sequence value - or any unique value for that matter - is the right thing to
do. Depending on the APIs to generate primary keys is a dangerous approach. For
example, if a developer decides to manually insert rows into the audit table
(by bypassing the APIs) he queries the last number from the table’s primary
key, and then inserts rows with hardcoded numbers in the key. He adds 1 to the
number each time he inserts a new row. He does not use the auto incremented
next value. The next time the API is called to create a new row, the sequence
generates its next number, but this is the same number that the developer has
already used. Unique constraint is violated. An error will be reported and the
transaction will abort. On the other hand if the primary key is populated through
a trigger this error would not happen as every time transaction takes place
whether through an API or manually the primary key gets incremented.
Triggers run every time when the
database fields are updated and it is overhead on system. This fear of overhead
leads DBAs to minimize the number of modifications recorded or the period over
which they are recorded.
Another drawback of triggers is
that they cannot capture data views or changes to schema and permissions. Data
manipulating statements such as INSERT, UPDATE or DELETE can be captured by
using normal triggers. Because simple SELECT statements are non data manipulating,
they neither fire a trigger nor go into archived logs that can be mined later,
so triggers fall short where SELECT statements are concerned. Some DBMSs have
come out with new techniques to overcome this problem. Oracle9i Database
introduced a new feature called fine-grained auditing (FGA). This feature provides
the functionality to audit individual SELECT statements along with exact
statements issued by users. In addition to simply tracking statements, FGA
provides a way to simulate a trigger for SELECT statements by executing a code
whenever a user selects a particular set of data. Apart from the conventional
DML triggers Microsoft SQL Server provides the feature of DDL Triggers. DDL
Triggers are a special kind of trigger that fire in response to a variety of
Data Definition Language (DDL) events. These events primarily correspond to
Transact-SQL statements that start with the keywords CREATE, ALTER and DROP.
They can be used to perform administrative tasks in the database such as
auditing and regulating database operations.
Hanson
and Noronha [3] propose a simple model for a time-driven
triggering and alerting system. Such a system can be used with relational and
object-relational databases systems. They are relatively easy to implement
since they can be built using a middleware program that simply runs SQL statements
against a DBMS. Also, they can check certain types of conditions, such as
"a value did not change" or "a value did not change by more than
10% in six months."
In cases where it may be perfectly
reasonable to be notified only once an hour, once a day, or once a week when an
interesting change occurs this approach can be found useful. This is especially
true when we are interested to audit the change on an aggregation, such as a
sum, average or count.
6.
Security of Audit Logs
In order to implement a best
possible auditing solution it is important to focus on the security of audit
logs. The audit logs must be protected by employing techniques based on
cryptographically strong one-way hash functions that prevent an intruder, including
an auditor or an employee or even an unknown bug within the DBMS itself, from
silently corrupting the audit log. The DBMS should store additional information
in the database to enable a separate audit log validator to examine the
database along with this extra information and state conclusively whether the
audit log has been compromised. [4] Furthermore the following measures can be
taken to secure the audit logs against tampering: The access rights to audit
logs should not be with the operations department or the supervisor/administrator.
They should be accessible only to the internal/external auditors. The audit log
table should have an audit log on itself to record any tampering.
7.
Conclusion
The need for auditing data changes has
increased with the increase in the volume of transactions and the opening up of
organizations’ data to the external world. The increased volume of transactions
has further posed a challenge for the selective logging of possible security
events. In this paper various design and
implementation of auditing techniques have been discussed. The advantages and
disadvantages in each technique have been mentioned clearly along with the
scenarios under which these techniques could be used. The auditor must keep in
mind his requirements while selecting any of these methods for auditing.
Triggers play an important role in adopting many of these techniques. Triggers
cause only a slight performance impact that can be offset by the decreased
number of messages passed between the client and the server.
References
[1]
Kogan
B., Jajodia S., “An audit model for
object-oriented databases”, Computer Security Applications Conference, 1991.
Proceedings, Seventh Annual.
[2]
Bishop et al., "Goal-Oriented Auditing and Logging", submitted to
IEEE Transactions on Computing Systems, 1996.
[3]
Eric N. Hanson, et al., Timer-Driven Database Triggers and Alerters: Semantics
and a Challenge, SIGMOD Record, Dec. 1999, pp. 11-16, vol. 28 No. 4.
[4]
Richard
T. Snodgrass, Stanley Yao and Christian Collberg, "Tamper Detection in
Audit Logs," In Proceedings of the International Conference on Very Large
Databases, Toronto, Canada, August–September 2004, pp. 504–515.
