For complete details on Oracle auditing and Oracle
forensics, see these recommended books:
This whitepaper is a
comprehensive overview of database auditing best practices
and methods for the IT manager. With the introduction
of rigorous Federal laws, the IT manager must plan to fully
monitor and audit access to mission-critical and
confidential information, all while maintaining a complete
and reliable auditing framework.
Managers have realized that
the information gleaned from audit trails of database
activity can be the company’s single largest data resource.
They also recognize that their audit trails provide a
temporal “third dimension” of their information, a valuable
time-series view of their production systems that contains
all-important behavioral aspects of their data access.
While there are various approaches to auditing critical
database platforms, implementing an enterprise class
solution that provides a comprehensive auditing and
reporting capability is not an easy task. We’ll begin
with a summary of the most important concerns of the IT
manager and then examine various methods of implementing a
successful enterprise auditing solution.
The main points of this
whitepaper address the issues of the highest concern for IT
management.
·
Avoiding
business risk and meeting the demands of customers and
business partners
– While the laws demand a thorough and comprehensive
approach to privacy and auditing, the most important
reason for protecting your data integrity is your
professional reputation. The standards are high,
and it is necessary to have a complete top-down auditing
and protection solution to work with other businesses.
Your partners must cover themselves and they are not
likely to have the time, money or patience to audit a
complicated home-grown solution. Remember, the
driving force is your business need and your customer
demands for data integrity and privacy.
·
Satisfying the auditors – Implementing best practices
including segregation of duties
– When considering the
Build vs. Buy approach, it should be carefully
considered that systems administrators, database
administrators and developers cannot have direct access
to the auditing solution because exposures result when
they have intimate knowledge of the internals of the
audit mechanism. Any auditing solution must have the
capability of providing for segregation of duties to
ensure that these users can be denied access to the
resulting audit trail to ensure the integrity of audit
reports generated by the system.
·
Avoiding
civil and criminal penalties
- Data asset management practices must address business,
operational, legal and compliance needs. Many
Federal laws such as the Health Insurance Portability
and Accountability Act of 1996 (HIPAA), the Sarbanes
Oxley Act (SOX) and the Gramm Leach Bliley Act (GLBA)
change the way that databases are secured and audited
and some of these federal regulations impose severe
criminal penalties for non-compliance and malfeasance
with protected data. Non-compliance with these
regulations can also expose your company to
multi-million dollar civil lawsuits from customers if
their private information has been improperly disclosed.
·
Choosing
the right auditing approach
– Many database vendors (e.g., Microsoft, Oracle) offer
product-specific utilities to enable auditing, but these
audit and trace tools are generally meant to be used
only sporadically for investigative and forensic
activity. Piecemeal solutions to auditing are
difficult to scale, generally impose significant
performance impact on the systems, and are very
difficult to manage. Approaching auditing and
privacy efforts at the application layer leaves direct
access to the database unaudited, and results in
incomplete coverage and a hodge-podge of in-house and
third-party audit logs that are impossible to manage and
reconcile.
These are just a few of the
IT managers’ concerns in this brave new world of security,
privacy and regulatory compliance. Your customers and
business partners expect you to have a complete privacy
auditing solution. Let’s take a closer look at the
issues and see how you can protect yourself from common
pitfalls and implement a comprehensive and manageable
solution.
Developing a Corporate-wide Auditing Framework
The IT manager must view
Auditing as a homogenous system, spanning all applications
and database platforms. This is especially important
with the new Federal laws that put the onus of maintaining
the security and auditing policy on the custodians of the
data, the IT management. The Federal laws do not
specify or require specific technologies or standards to be
followed, and it is your responsibility to decide the best
possible approach to assure compliance. However, it is
precisely the implementation that requires an exercise of
due diligence to select a rigorous security policy.
For any large company,
manageability, reliability and scalability are the critical
success factors of an auditing solution:
·
Performance –
The solution must have a minimal performance impact with
low maintenance and upgrade overhead.
·
Manageable –
The SA, DBA and developer staff cannot be involved in
the auditing or have any privileged access rights.
The solution must be segregated, unified and platform
independent. The solution must be flexible and
easy to extend and maintain as IT database requirements
change. The system should include centralized
ability to configure and deploy across numbers of
servers, and regardless of database platform.
·
Provide
business value
– The solution must be usable by security and auditing
personnel as well as line of business owners with a
clear and understandable reporting capability.
·
Complete
– The solution must be complete and comprehensive.
Because many applications span database platforms, it
should have a unified interface for all databases,
regardless of platform. It must be reliable and
have an automated and secure mechanism for long-term
archival management. Successful companies view
their privacy and auditing as a system in-itself, not as
a strap-on to existing systems.
Ensuring a Complete Enterprise Solution
Creating an auditing
architecture from diverse data sources and applications is a
huge challenge. The IT manager must ensure that every
important aspect of privacy, security and auditing are
covered and they must do so while ensuring that their
solution in easy to manage and scalable. A n effective
auditing solution must have these characteristics:
·
Reliability and
completeness
·
Real-time
notification of critical events
·
Consolidation
of audit data streams
·
Reporting value
and ease of reporting
·
Long-term
retention of audit trails
·
Manageability
and scalability
While simple in concept,
these requirements are extremely complex and difficult to
implement, especially with the huge volumes of data that
must be archived. Because auditing is required
by both IT best practices and U.S. Federal laws, IT managers
typically adopt products designed specifically for this
purpose.
Reliability and Completeness
Many IT shops fail to realize
that a haphazard “sampling” approach to auditing is
insufficient. A continuous audit is required and the audit
must be archived for long-term access.
This is not an easy task.
In cases where you must audit the viewing of confidential
data you might need to archive a volume of data greater than
the size of the whole database, everyday, 365 days a year.
With many shops archiving hundreds of gigabytes of data
every day, it becomes critical that all of the archived data
be accessible and complete.
For example, HIPAA
requirements clearly state that user accesses to the
database be recorded and monitored for possible abuse.
Remember, this intent is not only to catch hackers but also
to document the accesses to medical databases by authorized
end-users. In today’s litigious society, prudent
companies capture the “who”, “where”, “what”, “when” and
“why” for all access to confidential information. The
“why” aspect is critical because authorized end-users may
access confidential information for unsavory purposes.
The data volumes of audit
information can be staggering. Larger shops may
capture trillions of bytes of auditing information every
week, archive and store this data for several years, and
have an automated mechanism to easily extract information
about any individual in their database.
A comprehensive solution must
also have the ability to audit all possible points of entry
to the data. It must audit access from the operating
system (at the data file level), from the database
management layer, the network and from the application layer
(Figure 1).
Figure
1 – The multi-layer data exposure issue
In a typical organization,
data access occurs at many levels - - - at the end user
presentation layer, at the middle tier, at the application
server layer, at the web server layer, at the standalone
application screens and finally, at the database level
directly. A properly compliant security implementation knows
that it is almost impossible to clearly identify and secure
all the remote data access points and that proper security
and auditing is firmly in-place at the data source.
Attempting to audit data from multiple remote layers is
suicide, especially when hackers have learned to access
information from outside the application layer, accessing
the data directly from within the database or accessing the
data files directly from the server.
The ability to capture data
access at the data source is an absolute requirement for
reliable data auditing. While all legitimate data
access is done via the application malicious hackers rarely
access the system via the application screens. Instead
they access the data directly from the files on the
operating system or gather the data directly from the
database layer. We also see hackers gathering
confidential information directly from the web cache layer,
using buffer overflow techniques to grab information from
outbound HTML pages.
Even at the database layer
there are opportunities to bypass the application.
Ad-hoc query tools such as SQL*Plus, Crystal Reports and
ODBC tools provide backdoors for legitimate users to bypass
application layer auditing.
Consolidation of Audit Data Streams
Very few IT shops have a
single database source and it can be a nightmare to try to
consolidate auditing archives from heterogeneous database
platforms. Each database product manages archives in
differing formats and cross-database issues can be
impossible to resolve without centralization. Audits
from different database products are archived with different
character sets, different formats and different
organizations (Figure 2).
Figure 2 – The problem of
auditing diverse data
Here the problem is
consolidating audit information along two dimensions, the
multi-layer dimension and the multi-product dimension.
The key to success in this type of heterogeneous environment
is to simplify the sources for data collection and to
collect audit information at the source, the database layer.
For those using relational databases such as Oracle, SQL
Server and Sybase, using the traditional “grant” access to
authorize end-users allows them to access the data via
alternative methods such as ODBC interfaces.
For example, it is nearly
impossible to track data viewing at the “intrusion” levels
(i.e. ODBC, Crystal Reports, SQL*Plus) with
application-layer auditing tools. Even if we attempt
to close backdoors, there is no guarantee that all data
access will happen from within the application.
By auditing the data
disclosure at the source, we eliminate the need to track
access from multiple points and we greatly simplify the data
auditing model (Figure 3).
Figure 3 – Cross-product
data auditing
Now that we have ensured that
all data access auditing is done at the source of the data,
our only remaining issue is dealing with audits from
multiple data sources. This is especially problematic
for shops with a mix of database architectures such as
relational databases (Oracle), object-oriented databases (Ontos),
network databases (CA-IDMS) and hierarchical databases
(IMS).
Regardless of the database
architecture or specific product, all data audits must
capture this information:
·
Who
– A full identification of the person viewing or modifying
the data
·
Where
– A log showing the
specific application procedure and method used to access the
data
·
When
– A reliable date-time-stamp, globalized to Greenwich Mean
Time (GMT)
·
What
– A full listing of all data entities that were viewed or
modified
·
Why
– Context-based information describing how the data was
disclosed
By using a database
independent vendor package you can put the audit logs in an
identical format and provide a unified audit trail for the
all-important reporting interface.
Remember, the audit trail is
a database too, and for most shops it is the single largest
data repository for the entire company. Just as you
purchase a database product that is designed to meet your
application needs, many companies choose an auditing
solution that is specifically designed for the needs of
auditing (Figure 4).
Figure
4 – A unified database for managing audit information
Now that we see the
high-level architecture of the privacy auditing collection
and consolidation mechanism, let’s dive deeper and explore
how these giant audit databases are managed.
Minimizing Auditing Performance Overhead
Creating an unobtrusive
auditing solution is a primary requirement for many shops.
Those companies who have tried to cobble-together auditing
using generic database tools often find a huge overhead.
For example, Oracle shops are often tempted to use database
“triggers”, a generic mechanism that fires an event when a
database object is changed. The overhead of using
database triggers is significant and can double the
resources required to perform database updates, resulting in
declining performance and unnecessary hardware stress.
A more reasonable alternative
is a passive solution that uses data recovery mechanisms.
For example, all relational databases have update logs that
are archived and used in cases where disk recovery is
required. These logs are the ideal source for auditing
changes to the database because they do not add additional
processing. We also find that successful enterprise
auditing solutions utilize these logs in order to achieve
the auditing goal within the absolute minimum overhead.
Now, let’s take a look at the
characteristics of a successful enterprise data auditing
solution.
Real-time Notification of Critical Events
A comprehensive solution will
allow for the ad-hoc definition of alert threshold events
and provide a mechanism for real-time notification via
e-mail, text mail or pager (Figure 5). Successful
companies apply sophisticated filters to the audit trails at
data capture time and spot suspicious trends and patterns in
data access. Many of these companies report that the
system pays for itself in just a few months in cost savings
from early-warning fraud detection.
Figure
5 – Critical real-time exception notification
Archiving Issues with Data Audits
Remember, your audit trails
will be your single largest data management responsibility,
eclipsing your online systems by orders of magnitude.
To fully appreciate the data volumes and complexity of
privacy auditing, lets take a look at the issues for a
typical company. Consider a financial database with
500 end-users and one terabyte of information. Because
each end-user is constantly viewing personal financial
information as a legitimate part of their job, every week,
the audit trail must be able to archive viewing details of
over 100 times the size of the original database, in some
cases over 300,000,000,000,000 bytes of archived data.
Even though disk become
cheaper every year, the expense of archiving trillions of
bytes per week on online storage is cost-prohibitive.
They are forced to develop a mechanism to archive these vast
volumes of data using semi-automated mechanisms.
Audit Trail archive
processing uses a tertiary storage (tape) jukebox and a tape
management system to clearly label the header of every audit
tape. The archiving process involves special hardware,
complex interfaces and built-in error checking. As
each online audit disk requires archiving, an automated
process will:
1 – Fetch and label a new
tape
2 - Copy the disk onto tape
media
3 – Re-process the audit
tape:
a) Check the media for parity
errors
b) Make a copy of the tape
for off-site archiving and storage
c) Apply data mining programs
to locate unobtrusive trends and provide real-time alerts
4 – Re-initialize the online
disk
This archiving mechanism must
be seamless, complete and have built-in redundancy and error
checking. When we add-in the additional dimension of
data from multiple databases and auditing from multiple
access layers, the problem can become unfathomable.
But it gets worse.
Archiving the data is just the front-end and you must also
develop the ability to allow timely access to the archived
data. Let’s take a closer look at how this works.
Long-term Retention of Audit Trails
Long-term data retention is
often mandated by business practices and legal requirements
and the auditing of data access has imposed a huge burden on
many companies. The archival storage of audit trails
is often 95% of the company’s data, yet it is only accessed
1% of the time (Figure 6).
Figure 6 – The anomaly of
archival data
This data anomaly also presents challenges due to the
temporal nature of the audit capture and the low volume of
access. Once lost, the data can never be reclaimed,
and the sheer volume of data often means that media
verification (duplicitous parity checks) is prohibitive.
Many IT managers have come to
realize that their point-in-time production databases only
tell a small part of the story and the real value of their
database is the temporal dimension. Let’s take a look
at how establishing a time-series interface allows complete
reporting, data mining and fraud detection capabilities.
Reporting Value with Data Audits
In addition to meeting
compliance regulations, many companies discover that they
have a valuable data resource in their audit trails.
Home-grown solutions often lack an easy-to-use interface and
analyzing the valuable hidden information in the audit
trails is often impossible. Ad-hoc interfaces
are usually non-existent, and it can be extremely difficult
to apply data mining techniques to detect unobtrusive
patterns of fraud and access violations. What’s needed
is an enterprise reporting capability that provides the
means to derive business value from the audit data.
Any online database is
nothing more than a fixed, point-in-time snapshot of the
current information. To get the whole picture you must
add a temporal dimension to the database, and develop
mechanisms to harvest your time-series information (Figure
7).
In the following chart
capitalization needs to be fixed (lower case “t” in “To” in
headline. Lower case “t” in “Trends”
Figure 7 – Time, the third
dimension of Database Management
Even though disk costs fall
10x every year, online access to petabytes of audit data is
prohibitive and this presents special challenges to the IT
manager. To confound the issue, simultaneous requests
present a unique challenge because of the linear limitations
of tertiary storage. To minimize human intervention,
the reporting solution must have these characteristics:
·
An easy-to-use
interface
·
A mechanism to
audit the audit request
·
A complex
status-tracking facility
·
A notification
and delivery mechanism for the completed report
·
The ability to
access audit information from the application layer,
database layer and server layer
·
The ability to
access audit data from multiple database products
The reporting mechanism must
be able to serve the needs of requests from the external
community and support your in-house reporting needs.
The sheer volume of auditing data makes this reporting
unique. Answering this simple query might take hours,
require mounting thousands of tapes, and involve reading
trillions of bytes of data from multiple databases.
External Reporting
Your customers and clients
may request complete audit trails of access to their
confidential information. In financial and medical
systems, Federal laws mandate that your company be able to
service these requests, providing complete reports in a
timely manner.
For example, in a health care
database, any patient may request a report showing all users
who have viewed their confidential patient information,
including who they were, what they viewed, when they viewed
the data and why they needed to see their information.
We also have the important
business need to have access to the third dimension of your
production database. The value of the temporal
dimension of the database can be worth millions of dollars
and internal reporting capabilities provide a competitive
edge to many companies.
Internal Reporting
Internally, the reporting
mechanism must also allow interfaces for in-house reporting,
especially in the areas of financial and marketing
management. These in-house reporting facilities fall
into two general categories:
·
Decision
Support – A mechanism to model “what if” questions, simulation modeling and
hypothesis testing.
·
Data Mining
–Support for multivariate correlation analysis, fraud
detection, trend identification and signature analysis.
As your largest database,
your audit trails contain valuable hidden information.
Because the audit trails provide a time-series view of your
online systems they contain information about the patterns
and behavior of the end-users and a time-series view of how
the data has changed over time.
Using standard data mining
products you can interface with your audit trail database to
determine “typical” processing patterns and quickly identify
suspicious patterns of data usage. Data mining
products are the result of decades of refinement and are
very sophisticated in their ability to spot patterns and
trends. The programs are constantly analyzing your
audit data, seeking statistically significant data patterns
and trends.
The huge benefits of data
mining programs are often quite surprising.
·
Savings from
Early Warnings -
Financial institutions have discovered the hidden value in
their audit trails for proactive fraud detection. By
analyzing patterns of known fraud from the audit trails, IT
management can apply detection mechanisms to the online
system, sending immediate alarms of untypical data access
patterns, often preventing the fraud before any financial
loss occurs. This technique has saved banks and credit
companies millions of dollars, and easily justifies the
expense of purchasing an enterprise auditing solution.
·
Optimizing
Employee Productivity
- Companies can also use their audit trails to track
employee productivity. The audit trails provide an
excellent unobtrusive measure of end-user value to the
company and this information can be used to spot sub-optimal
workers by comparing their data viewing behavior with those
of known, productive employees.
Sadly, many companies are
unable to reap the benefits of data mining because they do
not have a standardized, unified audit trail. This is
