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
business risk and meeting the demands of customers and
: 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.
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.
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
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
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:
The solution must have a minimal performance impact with
low maintenance and upgrade overhead.
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.
: The solution must be usable by security and auditing
personnel as well as line of business owners with a
clear and understandable reporting capability.
: 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:
notification of critical events
of audit data streams
and ease of reporting
retention of audit trails
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
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
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
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
Regardless of the database
architecture or specific product, all data audits must
capture this information:
: A full identification of the person viewing or modifying
: A log showing the
specific application procedure and method used to access the
: A reliable date-time-stamp, globalized to Greenwich Mean
: A full listing of all data entities that were viewed or
: Context-based information describing how the data was
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).
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
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.
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
1 : Fetch and label a new
2 - Copy the disk onto tape
3 : Re-process the audit
a) Check the media for parity
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
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
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
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:
A mechanism to
audit the audit request
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.
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
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.
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:
Support : A mechanism to model 'what if' questions, simulation modeling and
: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
The huge benefits of data
mining programs are often quite surprising.
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.
- 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
another major shortcoming of home-grown auditing solutions,
but one that can be easily remedied because of the fast
pay-back period. Many savvy IT managers will show the
projected savings from fraud detection and employee
tracking, and get departmental management to pay the cost of
buying a unified auditing solution and data mining product.
As we see, the benefits of
purchasing a product for auditing are very real and often
pay for themselves very quickly. But even without
considering the benefits to the company as a whole, in-house
auditing solutions carry a host of other exposures and
costs. Let's take a closer look at the issues with
Technical Issues with Independent Auditing Solutions
There are many reasons not to
use the database vendor-supplied tools for your
mission-critical auditing solutions. Yes, they may be
free, but the massive overhead and limited scope makes them
inappropriate for a company-wide solution.
Many industries are now
regulated by Federal laws governing the management,
disclosure and security of personal information.
HIPAA, SOX and other laws and standards required by
government bodies and security organizations make security
and privacy mandatory in many situations. Another law in the
US, the Gramm-Leach-Bliley Act, mandates financial
institutions and their partners to protect non-public
personal information by implementing a variety of access and
It is also a mistake to rely
on customized auditing solutions within the application
layer. Any code that is written into the application
is controlled by your developers and has an innate security
exposure. Some of their major shortcomings include:
Application-side auditing covers only one of many doorways
and results in an unmanageable collection of disparate audit
traces impose severe performance penalties on the database
platform and can be easily altered or disabled by privileged
vendor-specific tools (e.g. Oracle LogMiner, Oracle
Auditing, and Oracle Fine Grain Auditing) are meant to be
used as occasional investigative tools, but were never
intended to be a comprehensive solution.
There are also specific traps
that catch the unsuspecting IT manager. These traps
appear obvious but it is surprising how many of them fail to
be detected until the company data is stolen or a lawsuit is
The Auditing Traps
The common auditing traps are
well-known in the IT security field and are taught in almost
every business school in the
USA. Given the wide knowledge of these exposures it is surprising how
often they are disregarded by the IT manager.
layers - Protecting the application layer is only part of the solution.
A comprehensive auditing solution must check for access at
the web cache layer (cached HTML data), application server
caches, database caches and backdoors, and access violations
at the server level.
privileged users -
Another common trap is allowing privileged employees to
bypass security and audit mechanisms. Your Systems
Administrator and DBA have no business touching your
auditing mechanism, and while they may be responsible for
the integrity of the data, a third-party must be used to
perform all auditing collection, administration and
reporting duties. There have been many serious
lawsuits where a dishonest DBA entered a database and
changed financial data, disclosed confidential information
and violated Federal data access regulations.
how it happened
:Finding a security violation and never being able to
determine the cause creates a huge legal liability for any
corporation, and this is very common among IT shops that
choose to use piecemeal solutions for their privacy and
auditing mechanism. In one case, a user was found to be
committing fraud, but without an audit trail of exactly what
transpired the organization had no way of understanding the
scope of the damage.
:Another common trap is to apply different rules to auditing
of different systems. This is often the result of the
limitations of the application code. For example, you
may be able to add a complete auditing solution to the
system that was developed in-house, but you do not have the
same luxury when using an ERP product (SAP. PeopleSoft) because you cannot touch the application code.
With all of these exposures
and threats the savvy IT manager must be able to cover
themselves from even the most unlikely scenario. Here
are some of the common ways that the IT manager ensures that
they have a compliant, robust and comprehensive solution.
Segregation of Auditing Duties
While general security and
auditing are passive activities, a comprehensive solution to
auditing requires real-time reporting of active attempts to
bypass security. Remember, smart shops close all
back-door data access (e.g. ODBC) and enforce data access
via the application layer. However, we must still
create an alert mechanism for all data access attempts at
all layers, whether malicious or benign. For example,
the Database Administrator (DBA) often needs to view
database information as part of their administrative duties,
and Federal laws mandate that this data access be tracked
just as data access within the application layer.
This issue of 'privilege
user' access is a serious security exposure. Because
the auditing solution must audit the access of Systems
Administrators and DBA's, these employees must not have any
control or responsibilities for the auditing mechanism.
This segregation of duties is
critical because it is considered malfeasance to give the
'Keys to the Kingdom' to anyone charged with maintaining the
servers and databases. In many cases disgruntled
employees may view confidential information for personal
gain and sometimes create mechanisms to disclose the
information if they are terminated from employment (see
horror stories later in this paper).
Shops falling under the scope
of Federal privacy laws such as HIPPA are required to
appoint a full-time employee, independent from the SA and
DBA staff to control the auditing. This job role has
many names including the Security Privacy Auditing Manager
(SPAM), Privacy Access Manager (PAM), Security Privacy
Administrator (SPA) and sundry other job
Regardless of the title, the
SPA must possess a combination of technical, application and management
skills, unique to each organization. For example,
large health care companies normally employ a Medical
Informatacist as the
SPA, usually a highly trained Medical Doctor (MD) with
skills in application design, systems architecture, systems
administration and database administration. Financial
institutions will employ a Certified Public Accountant (CPA)
with a strong technical background.
In sum, the auditing
collection, consolidation and reporting must be the
responsibility of a separate IT entity, solely charged with
managing all data privacy audits. Any access outside
the application layer, whether malicious or part of routine
DBA duties, must set-off alarms for the SPA.
Now, let's change focus and
examine how the IT manager can satisfy their due diligence
requirements while satisfying their auditing challenges.
CYA for the IT Manager
The IT manager has a legal
and fiduciary responsibility for the corporate data resource
. This is a responsibility that should be taken very
For example, HIPAA laws
provides that a leak of information calls for a fine of up
to $250,000 per incident and may result in the imprisonment
of the executive in charge for a period up to 10 years. The
severity of the penalty and the personification of
responsibility is enough to make the executives of many
organizations take this law and the issue of privacy and
information protection very seriously.
As the IT manager you are
also required by law (e.g. HIPAA) to provide a clear
security policy that can be verifiable and, more
importantly, auditable. In the normal course of business in
any organization, some personnel will have to access data
that is considered sensitive, so prohibiting their use is
not feasible. HIPAA does not prohibit that access, but
specifies that normal access be recorded as a policy, which
should specify who can access what data, and any such access
information should be recorded, or in other words, audited.
Even more important, the
discovery phase of litigation against home-grown auditing
solutions can be devastating. Every line of code is
put under a magnifying glass and security experts from
around the world will be called-in to judge the lack of
quality of your solution. In almost every case the
code is found wanting, and the responsible IT manager is
held personally accountable for the exposure. Here are
some tips from the security experts:
Don't Underestimate the Bad Guys
Kevin Mitnick, the noted
computer felon likes to show how security breeches are
commonly the result of employee errors. In his book
'The Art of Deception', Mitnick talks about his techniques
to get trusting employees to disclose confidential
information and privileged passwords. In one case
Mitnick was able to secure a privileged password using the
name Lemonjello, and then bragged about the na'e employee
who handed-over a system password to someone called 'Lemon
Jell-O'. In this case the IT staff was never able to
ascertain the root cause of the breech because their
mechanism for the dissemination and auditing of secure
information was inadequate.
While external fraud remains
a serious issue we must also remember that most data access
violations happen internally, and most are the result of
unintentional access rather than malicious fraud.
Don't lose prospective partners
- Whenever you share data with partner companies their
due diligence requires them to verify your privacy and
security mechanisms. It's far faster and easier to
just name a vendor product than it is to make them
undertake a multi-week examination of your home-made
mechanism. Some Federal regulations also mandate
that you have a standardized information exchange
interface. For example, HIPAA mandates that the
information related to health insurance must be
exchanged in a standard, predefined way. For instance,
all the information that typically goes to the insurance
company from the provider during a claim filing must be
in a certain format, defined by the law.
Don't get sued by customers
- In today's litigious society, almost every breech of
privacy and security is followed by expensive
litigation. On the issue of medical records
privacy, the situation is even more fluid and prone to
severe security lapses. HIPAA addresses this problem by
mandating the audit requirements of these records and
strictly enforcing the requirements by placing stiff
penalties for non-compliance.
goodwill - Security and privacy breeches are big news and slack companies are
pasted across the headlines anytime a major exposure occurs.
This can be crippling to a company's reputation and brand
loyalty, especially in the financial services arena when
companies are judged by their absolute commitment to
There are many common
misconceptions about privacy and security auditing, even
amongst IT management. If you fail to grasp the
volume, scope and complexity of an auditing solution you can
place your entire company at risk. Let's take a closer
look at these common misconceptions.
Security, Privacy and Auditing Misconceptions
After interviewing dozens of
IT managers, a common set of misconceptions arose regarding
compliance with Federal regulations for security and
privacy. This is not surprising, given that the
legalese of the actual laws is almost indecipherable, but
the IT manager needs to know about the realities of these
important new laws. Some of the most common misconceptions
Prevention alone is sufficient
:Traditional security measures focused on 'perimeter'
security (e.g. firewalls) are an important component of
mitigating the risks of inappropriate data access or
changes. But with most error and fraud occurring
within the organization, it's important to have
the ability to understand exactly what is happening to
the data . A complete record of data access and
change provides this 'detective' capability which
augments existing security.
Another important aspect of auditing is recording who
was not granted access, not just who was permitted
access, depending on the privilege setting. This could
be due to a legitimate reason such as a bad password,
but it could also be a hacker trying to break in with
multiple attempts at guessing the password. It could
even be an insider, a disgruntled employee trying to
access information he or she is not authorized for.
Whatever the reason may be, this kind of activity
arouses suspicion and should be investigated.
Application access, privilege controls and logging are
enough : This
is a very serious misconception because it ignores the
other important access areas. As we see, all data
access must be audited directly at the data source.
Preventing fraud is the only goal
: Many IT managers fail to account for the possibility
of human error, which is more prevalent than fraud, in
their auditing plan. A comprehensive solution must
account for legitimate errors by end-users and IT staff.
cheaper to build a custom audit mechanism
: This is untrue - and dangerous. While a
once-over-lightly solution can be cobbled together
quickly, mistakes of omission can cost your company
millions of dollars in sanctions. Worse yet, these
'cost effective' solutions almost always cost more in
the long run as the IT manager discovers the huge costs
associated with reporting, customization and
consolidation with other audit trails. Further,
most IT organizations cannot afford to develop multiple
audit systems to support their multi-platform
environment. We've already discussed that native
tools cannot scale to accommodate the needs of a large
Now that we have seen the
common misconceptions let's examine the importance of
auditing data at the data source, the database management
Auditing Data at the Source
All IT managers know that
simple triggers and code extensions can be used to enforce
security and privacy at the application layer. The
real problem is securing the data source and all
intermediate repositories and providing the ability to
understand the root cause of the violation.
A 'detective' monitoring
approach is used by many successful companies because it
allows you to know what actually happened when a breech
occurs. Simple auditing based only on preventative
measures will not provide this level of insight. Just
like a human detective, the detective monitoring approach
observes all aspects of data access and keeps complete logs
of all database activity. To be fully safe and
compliant, you must keep a complete audit trail for the data
source and have a complete who, where, what, and when
record of access and updates.
There are many challenges
involved with auditing at the database level. If you
want to understand 'how' a violation happens, you must audit
all events of interest,. These events include
privileged access by IT personnel, the auditing of all
changes to the data, auditing all viewing of confidential
data, and recording all changes to the database
infrastructure, both by DDL and changes to executable
database procedures. Let's take a closer look at each
Auditing Privilege/Permission and Logon Events
You must have a complete
record of the users who have data access including: 'who' is
attempting to get it, 'what' they have rights to do with the
data, 'why' they are changing the data, and 'when' the data
was viewed or changed.
While many databases such as
Oracle provide primitive logon triggers for determining
logon events, they don't work with many modern ERP products
(SAP, Oracle Applications, PeopleSoft) because they use pre-spawned
connections to the database. User authentication and
access management is done by the application server and the
individual users are not exposed to the database.
The 'who' aspect of data
auditing can be confounded if you use a tool such as SAP or Oracle applications that pre-spawns anonymous connections to Oracle.
The application controls user access and authenticated users
are directed into the database under the control of the
application (Figure 8).
Figure 8 : External
In these types of
architectures an end-user has no direct privilege against
the data source and the permission to view and access data
is granted via the application. Because the
application controls all database access, you don't have to
be concerned about back-door access with non-application
interfaces such as Crystal Reports or ODBC.
However, it is critical to
audit the activity of privileged users, including DBAs, who
have direct access to the database and can access or modify
the application's underlying data.
Auditing DDL Events
Managing changes to the
schema definition of your database is critical. You
must have a complete record of all changes to your database
system infrastructure and understand the potential security
risks associated with each change. This includes knowing
that a table has been dropped or permissions have been
changed inappropriately. Many open source solutions such as
SCCS are inadequate and many IT managers use third-party
products designed specifically to track schema changes,
such as Merant PVCS (Serena),
Kintana and Oracle Software Configuration Manager.
organization has a policy of placing everything in the
version control system, the changes made are automatically
recorded. But what happens when someone makes an emergency
change without using the proper procedure' The setup fails.
This is a classic case of a system where the integrity can
be guaranteed only when everyone follows the rules and no
one bypasses them.
Auditing DML Events
All auditing solutions must
track changes to any of the data items, right-down to the
column level. It's not enough to know that a
particular financial record was changed; you must also know
exactly what has changed in the data content. This
includes the access method ('how' it was changed), the
before and after values, and the exact time and user ID.
Auditing SELECT Events
Many Federal regulations
mandate that you keep a complete record of access to private
and confidential information. For large active
databases, it is not uncommon to have daily viewing logs
that are larger than the whole database and you must be able
to easily run reports against this huge volume of data.
For example, the new HIPAA regulations allow any medical
patient to request to know who has accessed their data in
the past, and this simple query might involve accessing
trillions of bytes of audit information. When you have
multiple, simultaneous requests for these reports, an
improperly designed audit system might become crippled under
the weight of the data volume.
Auditing Execution and Modification of Stored Procedures
Many database shops
encapsulate their database access inside code snippets
called 'stored procedures'. When using stored
procedures an end-user takes-on the privileges required to
execute the procedure, but only for the duration of the
execution of the procedure. In databases such as
Oracle, stored procedures are written in an interpreted
procedural language called PL/SQL. As every IT manager
should know, any language that is parsed and executed
line-by-line is subject to injection attacks. Hence,
special audit procedures must be employed for any database
that uses stored procedures.
Hardly a week passes without
a report of a company suffering major losses due to an
information security breach. Let's take a look at the
types of exposures faced by companies and see how an
enterprise auditing solution can prevent the threats.
We need not look far to see
the public cases of computer security violations and the
liability suffered by the custodian of the data. With
millions of dollars at stake, there are many resourceful
people waiting for you to make a mistake and expose your
confidential information. These attacks on your
information take many forms, from malicious hackers,
dishonest employees and honest mistakes. Let's look at
some specific ways that companies lose control of their
Security Breaches, Hacks (outside-in)
Threats from hackers remain a
major concern, especially threats from overseas countries in
Eastern Europe and Asia. Some companies report
access attempts by automated hacker 'bots' every few minutes
as these rogue programs constantly sweep the Internet
looking for ports with access vulnerabilities.
These automated bots contain
very sophisticated logic and are designed by criminals to
identify and exploit weaknesses in online computer systems.
Some of the common exploits include:
the user ID : This is where a telnet or FTP access attempt tells you that you have
entered a valid ID, but provided an improper password.
: Hacker routines love systems that do not disable a
user ID after repeated password attempts and run bots to
try hundreds of thousands of password until they gain
Man-in-the middle attacks
: Hackers can gain access to computer systems by
guessing the IP address of a connected user and sending
a TCP/IP packet with that users IP information.
Injection threats : Many database systems have vulnerabilities where access to
confidential data can be gained via a SQL injection, a
technique where a :1=1: string is added to a sign-on
string. For example, this query might return the
:real: password for a user named Jane:
userid = 'jane'
password = 'xxx'
: In these attacks, the web cache buffer is deliberately
overloaded to gain unauthorized entry to the system.
Hacker attempts for
web-enabled systems are constant and many companies report
thousands of attempts every day. A comprehensive
auditing system will record all illegal access attempts and
include the time, referrer IP address and all other relevant
information. Let's take a look at a real-world case.
The Extortion Attack Case
In this case a hacker
exploited a server vulnerability, siphoned confidential
information from the corporate database, and shipped it to a
foreign nation that did not honor U.S copyright law. A
foreign cohort then extorted the company, proving that they
had the data, and threatened to disclose proprietary secrets
to a competitor unless they were paid a significant sum of
Faced with the loss of their
competitive advantage, the company contacted the FBI and was
told that there was no reciprocity with the nation and that
Interpol would not be able to investigate or arrest the
extortionists. Even worse, IT management had not
detected the leak, and had no idea how the thieves had
accessed their database.
Surprisingly, this is not an
uncommon occurrence, and many multi-national companies have
accounts for bribery and extortion expenses because they are
a legitimate requirement for doing business in some overseas
nations. In this case the company quietly paid the
extortionist in return for the promise to destroy the data
and details about how the data was stolen.
While there are always
exposures from the outside world, we must also account for
attacks from within our company firewall. In practice,
'inside jobs' are more common than external attacks, and
they can often have devastating consequences.
Internal Fraud (inside jobs)
IT managers report that
internal fraud is the most common type of threat and special
auditing mechanisms must be used to audit all access by
authorized employees. Inside job threats include the
attacks : In a root kit attack, the operating system is compromised. I
once fixed a client site with a root kit that had
installed a daemon process that was constantly accessing
confidential information and e-mailing it to a
competitor. This attack went undiscovered for more
than a year and virtually all of the company's
proprietary information was lost.
Internal IT personnel have been know to write routines
that trigger a data extraction on the day when their
user ID is removed from the computer system.
Because most IT procedures required pulling the user ID
before notifying the employee, these hackers will return
home to find all of the confidential information waiting
for them in their in-box.
horse : Once
an employee gets the internal IP address of another
employee, they can map-out phony sign-on screens to
their boss and get a privileged password. These
attacks are usually easy using tools such as X-Windows
that allow screen images to be redirected onto other
Privacy tools : Common tools such as PC Anywhere can be used to look-over the
shoulder of a co-employee, snooping into their
activities and passwords.
Inside jobs are the most
difficult to detect, but complete audits will always reveal
the 'who' and 'how' aspects of the attack. For
example, coded implants can be tracked using your source
code control system software that is required by almost all
Federal Regulations including SOX and HIPAA.
Here are many documented
cases of data disclosure by disgruntled employees,
especially 'privileged users' who were given unaudited
access privileges. Let's look at some specific
real-world horror stories. These are not fictional
stories. They actually happened, and they serve as examples
of what can happen when a slack IT manager entrusts their
access and auditing controls to a Systems Administrator or
The Root Kit Case
We received a call from a
client who was complaining of performance problems on their
Oracle database which was running on a standalone Linux
server. The company was in the business of providing
credit information to third-party companies to access an
individual's probability of financial default.
Upon accessing the server, it
was apparent that something was terribly wrong. Even when
idle, the database was performing I/O operations and the
processors were active, even though Linux did not show any
active processes. The Linux 'ps' command failed to
reveal any active processes.
After a Linux expert was
consulted the real issue was discovered. A disgruntled
Systems Administrator had left a time-bomb on the server, to
be activated when their user account was removed from the
/etc/passwd file, indicating that they had been fired.
This time-bomb was activated
when the System Administrator left the company to 'pursue
other opportunities', and the attack was both clever and
devastating. The attacker placed a Linux daemon
process called 'vacuum' on the Linux server and this process
was constantly polling the Oracle database, seeking new
information, and e-mailing it to an overseas mailbox.
This attack has disclosed the
entire database of confidential information to an unknown
party, and the company was held fully responsible because
they failed to institute a third-party employee to manage
their server security.
The attack was very
sophisticated and unobtrusive. The malicious employee
had replaced the standard Linux commands with a 'root kit',
an attack method readily available on the Internet. In
a root Kit attack, the Linux commands are replaced with an
alias to disguise the presence of the data stealing
mechanism. In this case, the process command 'ps' was
replaced with the command ps|grep -i vacuum, such that the
process would not appear within Linux.
Sometimes internal fraud
occurs when employees are entrusted with data that has value
to outside parties. Let's take a look at one such
The Phony College Transcript Case
In this real-world case, a
Database Administrator for a major university was caught
'enhancing' college transcripts to allow people to gain
acceptance to top professional schools. The DBA had
complete control over the database and the auditing
mechanism and was charging friends and acquaintances
thousands of dollars to add courses and improve existing
grades. Because the DBA controlled the audit mechanism
she was able to completely erase all traces of the
This fraud went undetected
for more than five years until a professor discovered the
fraud. The professor was asked questions about a
former student as part of a pre-employment background check
and discovered that the student had never taken his class
even though the official university transcript indicated an
'A' for the course.
Ironically, the bulk of the
fraudulent transcripts were used to gain entrance to law
schools and several of them had graduated and were
practicing law. The losses and penalties from this
access violation were substantial:
Director of Database Systems was fired for malfeasance
for allowing the security loophole.
university suffered a huge loss of credibility and the
accuracy of over 100,000 graduates was tainted, all
because of a single privileged violation.
perpetrator DBA pled guilty to computer fraud and grand
larceny and received 5-10 years in Federal prison.
university had to undertake a grade re-verification
process that cost more than $600,000 dollars.
practicing attorneys were disbarred, but ironically many
of those who had successfully completed their graduate
schools were allowed to retain their degrees, even
though they entered the schools with falsified
Of course, not all privileged
disclosures are malicious. Next, let's look at cases
where honest mistakes can disclose confidential information
to third parties.
In many cases multi-million
dollar losses are the result of human error and bad judgment
on the part of the users of the database, and in some cases,
the IT staff. These types of mistakes can take the
form of trusting a telephone caller who wants a password
(the Kevin Mitnik approach), or a failure to recognize the
impact of the disclosure.
There is also an important
issue when information is aggregated by the IT department
for marketing purposes. The privacy and security laws
allow the sharing of summarized information so long as the
identity of any individual cannot be ascertained.
However, when the summarization includes 'outer bounds' data
then it is sometimes easy to violate disclosure laws.
For example, a report summary of HIV patients, aggregated by
city and profession might reveal the personal identity of
the only Taxidermist in Nome Alaska.
Caution must be taken when
sharing summarized and aggregated personal information to
remove all results with a limited set of participants so
that personal identities are not revealed. Let's take
a look at how honest mistakes can cause irreparable harm to
The Hotel Fiasco Case
In a widely publicized court
case from the 1990s, a major hotel chain collected detailed
information about their weekday guests' use of their hotels.
They employed a data warehouse analyst who created a target
marketing campaign, offering special coupons to those guests
who frequently used the hotel on weekdays. This
targeted mass mailing of weekday-stay coupons were sent to
the home addresses of the guests, with disastrous results.
More than a dozen people were informed about their spouse's
infidelity as a direct result of this coupon campaign and
more than six divorces resulted from the company's actions.
While this action was not in
violation of the privacy laws per se, the result of the
campaign was to disclose private information about
embarrassing information to a third party. A more
appropriate approach in this case would have been to mail
the coupons to the guests work addresses.
These horror stories serve to
remind the IT manager that a comprehensive auditing and
security system must have controls to audit all telephone
disclosures of confidential information, including
verifiable information about the recipient of the
Privacy Issues Associated with Data Viewing
The protection of personal
privacy is an important aspect of system auditing, and the
IT manager must remember that it is their responsibility to
ensure that the information is not improperly disclosed to
third parties. However, an important legal issue
arises when one of your employees accesses private
information for non work-related purposes. These
purposes might include an employee finding 'dirt' on an
ex-friend or previous boss, or using their access to your
computer system for extortion or harassment.
In one important appellate
case, a woman's job description involved accessing
confidential information. As an authorized user, she
accessed embarrassing information about her ex-husband and
used the information to her benefit in a child custody case.
The court ruled that even though the information was
obtained with an unsavory motive, the ex-wife was authorized
to view the data and the damning confidential evidence was
allowed in the case. The ex-husband, outraged at the
privacy violation, sued the ex-wife's employer for millions
of dollars for allowing his ex-spouse access to his
confidential data. In this case, the company needed
safeguards to verify 'why' the data was needed. It's
not enough to issue a blanket authorization and hope that
the privilege is not abused by your employees.
In these cases the IT manager
must have a full audit trail but they must also be also be
able to show 'why' that employee needed access to the
confidential information. To provide this level of detail
the IT audit trail must show contextual information about
the employees work session and show the flow-of-control
within the computer system.
This is another area where a
'detective' approach to audit analysis is valuable.
Sophisticated audit control systems allow for specific
decision rules to be applied to audit trials and these
algorithms are designed to detect unusual patterns of access
to private information.
Taken together, these issues
make IT privacy security auditing a scary and risky
proposition. Let's face it, with something as mission
critical and challenging as auditing and IT manager would be
insane to attempt to create their own solution. It
would be like an IT shop writing a proprietary database
management system. Of course, such things are best
left to companies who specialize in such matters.
Even more importantly, the IT
manager has enough responsibilities without being held
accountable for data security software. By acquiring a
nationally-known and respected product, the scope of
liability for the IT manager drops dramatically and they are
only responsible for the proper installation, administration
and management of the auditing software.
Let's take a look at one of
these proprietary solutions.
Enterprise Data Auditing Products
If you accept the
conventional wisdom that it is foolhardy to attempt to
construct your own auditing solution, the next step is
choosing the right product. Small, homogenous shops
will be happy to find that there are a small number of
database product-centric auditing packages in the
marketplace, but large IT shops are perplexed to find that
there are very few that provide an Enterprise-wide, unified
solution to companies with heterogeneous databases and
As of 2004 the 'Entegra'
product by Lumigent is the dominant data auditing solutions
provider for large IT shops with multiple data sources.
Let's use Entegra as an
example and see why an enterprise solution is appropriate
for many IT shops.
: Entegra captures database activity including DML
(including before and after values), DDL (schema and
permissions changes), and SELECT statements (who viewed
what data, to address privacy concerns).
Alerts for early warning :Alerts
on DDL activity of interest can be sent via email or
recorded in the event log. This enables early
resolution of potential issues before they become
a big problem (e.g. detecting fraud early to mitigate
Great management CYA
: Back in the 1970:s when IBM ruled the hardware arena
there was a saying in Data Processing that 'Nobody ever
got fired for calling IBM'. That principle is
alive-and-well with today's Information Systems, and a
prudent IT manager has never been fired for choosing a
leading vendor tool.
Cost savings :
The job of the IT department is to manage the company's
data, not to write system management software.
Data security and privacy auditing is a complex and
dangerous job, and best left to specialized products.
The unification of the audit mechanism also opens the
door to data mining tools which can rapidly pay for the
Standardization of audit trail database
: Solutions such as Entegra create and consolidate audit data from multiple
database platforms into a uniform audit database.
Remember, your audit trail is your largest database and
a source of valuable corporate information.
Allows for segregation of duties
: A key tenet of auditing
principles is 'segregation of duty' ' avoiding the fox
watching the hen house. With an enterprise
auditing solution such as Entegra you no longer need to
bet your business by relying on internal employee
honesty. Plus, you are protected by having an
audit trail of all employees, including trusted
Unobtrusive audit collection mechanism
: Vendor solutions such
as Entegra are optimized to use existing logs and they
have very little run-time impact on your
mission-critical production systems.
Unified reporting interface
: Tools such as Entegra offer an intuitive user
interface that makes it easy to create, schedule, manage
and distribute reports for management or auditors
(Figure 8). Reporting enables the audit data to
deliver real value to the business by providing insight
into how data is being accessed and used.
Figure 8 : A Screenshot
from the Entegra reporting Interface
- Allows for
segregation of duties : A key
tenet of auditing principles is 'segregation of
duty' avoiding the fox watching the hen house. With an
enterprise auditing solution
such as Entegra, you no longer need to bet your business
by relying on internal
employee honesty. Plus, you are protected by having an
audit trail of all employees,
including trusted employees.
: Having a product
specifically designed to meet the needs of auditing can
greatly reduce IT staff overhead. Vendor-based
solutions allow for standard administrative interfaces
and minimal participation from internal IT staff .
In sum, adopting a solution
such as Entegra is the most robust and cost-effective
solution. As databases and application systems become
more complex, and the demands of regulatory compliance
increase, the possible exposures are multiplied and the
prudent IT manager will opt for an expert data auditing
This whitepaper has
highlighted the core objectives and issues relating to a
successful enterprise-wide security, privacy and auditing
solution. The paper articulated the challenges for the
IT department which can be summarized in two kinds of
business risk categories. First, there is inherent
risk in managing corporate data. IT is responsible for
the integrity and security of the data which the
organization relies on to manage the business. Secondly, the
increasing regulatory environment is creating new demands
from the executive team and auditors that IT be able to
demonstrate exactly whos accessing or changing what data,
We examined the requirements
for an enterprise auditing solution, which can be summarized
Comprehensive capture of all database activity
Supports multiple database platforms
Architected for performance
'backdoors' captures activity of privileged users with
direct database access
Alerting for early detection of issues
Reporting capabilities to derive business value from
Adheres to auditing and IT best practices, including
segregation of duties
About the Author:
Donald Burleson is one of the world's top Oracle Database
experts with more than 20 years of full-time DBA experience.
He specializes in creating secure database architectures for
very large online databases and he has worked with some of
the world's most powerful and complex systems.
Burleson is an author of the bestselling book
Oracle Privacy Security Auditing by Rampant
TechPress, which includes proven techniques for Federal Law
Compliance with HIPAA, Sarbanes-Oxley and the
A former Adjunct Professor Emeritus, Donald Burleson has
written 32 books, published more than 100 articles in
National Magazines, and serves as Senior Consulting Editor
for DBAZine and Series Editor for Rampant TechPress. Don is
a popular lecturer and teacher and is a frequent speaker at
OracleWorld and other international database conferences.
As a leading corporate
database security consultant, Burleson has worked with
numerous Fortune 500 corporations creating secure database
architectures for mission-critical systems. Burleson
is also a noted expert on eCommerce system security, and has
been instrumental in the development of numerous Web-based
systems that support thousands of concurrent users.
professional web sites include www.dba-oracle.com and
If you like Oracle tuning, you
might enjoy my book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
You can buy it direct from the publisher for 30%-off and get instant
access to the code depot of Oracle tuning scripts.