Oracle Auditing

[tcar]

Ok, so I'm doing my internship with Los Alamos National Laboratory this
summer. I've been assigned to help secure our Oracle Databases, and
told to start with getting some auditing set up. We just had a group
with our DBA team lead and our security man an dthere is a serious
difference of opinion.
The DBA team lead is dead set against auditing anything other than log
on and log off. He says that we don't have the system resources to be
doing more than that, and we don't have the man power to review
anything else.
Our security man would like to at least record the activity, even if we
don't have the man power to review it regularly. At least that way, he
says, we can go back to the logs and find out who got in if there is a
breach.
I think that my Team leader is overestimating the performance impact of
auditing. Furthermore, I think that we could easily set up a few
reports that would only take a few hours a month to review. That would
let us look for some of the more obvious issues atleast.

What do you think?
Is it worth it to audit (really just logging if you don't look at it)
if you are never looking at it?
How much performance is really lost from auditing/
How much auditing is too much?

[John Martinson]

I would say that "too much" is dependant on how valuable the data is.
If someone could log in could they actually make anything out from the
data? Could the data tell them anything that would damage your
employer? Is it a national security issue?

If you decide to log what would you log? Deletes, Updates, inserts,
Queries? Unauthorized look ups? Remember back to IS 420 and writing
triggers. That could be a way to log.

As far as performance hits, I don't know. Just logging log-in
log-outs won't really hurt anything, but updates, and deletes might
not happen very often but result in a valuable log of illegal
activity.

An interesting real world problem.

John G. Martinson

[wheaties.box]

Those are very good questions. I've often wondered where the line
stands between the two options of "I want to know all" and "I want to
know useful information." I can see exactly where you're DBA lead and
security folks are coming from. If I were on the security side of
things, I would like to have the option to know who and when someone
breached my security at the least if it were to happen. If I were the
DBA lead, I would want as many free resources as possible.

In a setting such as what you have at LANL, security is definitely an
issue. You never know when some script kiddie (or a full-on
professional) will want to try something and happen to target your
organization. I've never thought about a way to do this, but perhaps
you could set up a way to filter what activities are more suspect and
which are more trivial? That way certain actions would be logged while
the others would be disregarded. It would still take more resources
than a simple logon/logoff audit system, but somewhere along the line
either resources or security will be sacrificed.

You must ask yourself which is more important--security or system
resources. I can see security outweighing the system resources simply
because the system resources can be augmented rather easily and at low
cost these days.

As for manpower to review the logs, I have no real suggestion. I, for
one, would find it difficult to read through security logs on a regular
basis trying to separate the important from the mundane. Props to
those who do it ;) I like your idea about generating reports that
would consolidate information. Maybe if you were to implement the
activity filter thing I mentioned above you could have different
sections on the reports with the actions performed in the period listed
by category.

Hopefully my rambling helped at least a little ;)

[Tejay Cardon]

Wow, It's good to see that this place does get looked at.  I wasn't sure I'd get any response, much less two in only a few hours. 

First, I am lucky enough that nothing we deal with is classified.  However, we do handle 90% of the labs "sensitive" data.  That means mostly financial and personel info. 

Ultimatly, what we log is the issue.  Unfourtunatly triggers only work on insert, update and delete.  While we are concerned with these, we are also pretty concerned about selects.  Those have to be done with  Oracle's built in auditing, and it doesn't allow you to include much logic.  Pretty much you log it or you  don't. 

The problem with the real world is that you have thousands of users.  We can't log what every one of them does.  So I'm working on a recomendation of the absolute minimum.  My boss is saying logon/logoff, but as far as I'm concerned that is pretty useless.  At best we can point to these "12 people who were logged on and had the permissions to access the data".  Maybe that gives the investigation some direciton, but what we really need is eveidence that can be presented in court.  We need to be able to say that this persons user name, database user and computer were all used to make this specific access.  (you can't say HE did it, but because he is responisble for securing all three of those assets he looks pretty guilty).  We can log all of that, but it takes resources. 

Furthermore, Ultimatly we really want to be able to see this stuff comming.  If our audit files can tell us that something fishy is happening before it does damage, that would be even better than looking for proof after the fact.

[John Martinson]

One of the things that your boss may be also considering is the time
and energy that it is going to take to install a decent auditing
system. When you are working with payroll and personnel databases
there are a lot of business rules that have to go into that. You have
to hunt down the people who know them, get them to meet with you or
send you a copy of the rules and then get the approval for that.

Tejay, I know you probably already know that, but for some other
readers this brings me back to one of the first things we learned in
system analysis and design in regards to project success. If the
project risk vs. payoff is not good it shouldn't be done.

One of the points that is made in the Systems Analysis and Design
book, (I no longer own a copy or know the author's name) is that if
you do not have the support of the upper management there is a lower
probability for success. This may be one of the factors. On the flip
side, getting this done and showing upper management that you can get
it done... can be good thing for your career. And I'm not only
talking to Tejay here, but everyone.

[Tejay Cardon]

You make a great point.  In this case the overhead is lessend. Oracle already has the auditing capability, so we don't have to install.  However, you are right on the second point.  We have to configure the whole thing, and that does require us to meet with users and find out who needs what when.  Although this is less of an issue with auditing, because it is non-intrusive.  If we audit too much it won't hurt anything. It just produces a lot of archive data. 

Thanks for the input.  I'll post again next week and let you know what I recommend and how the recommendation is recieved by our security quality circle.

[John Martinson]

I have been writing about this topic on my blog. Mostly some things that I am reading about. This topic has really peeked my interest. You can read it at http://jgmartinson.blogspot.com/ .

[Tejay Cardon]

Time for an update.  First I'll show you my reccomendation for a MINIMUM audit level for productions systems.  Then I'll list a few of my sources and some good reading on Oracle Auditing and on Oracle Security in general. Finally, I'll post again later today to let you know how this recommendation is recieved by our security quality circle. (that meeting is in 2 hours or so):


Recomendation document:

"Audit the minimum number of statements, users, or objects required to get the targeted information." – Oracle DBA Guide 9i

 

"Use the AUIDT_SYS_OPERATIONS initialization parameter to specify I user SYS is audited." – Oracle DBA Guide 9i

 

"In a multi-tier environment, Oracle preserves the identity of the client through all tiers.   This enables auditing of actions taken on behalf of the client." – Oracle DBA Guide 9i

 

"Misconceptions about Oracle auditing claims that it eats up resources faster than you can blink and that it slows down performance so much that yo8u shouldn't even try to use it.  These are, as stated, misconceptions." – SANS Institute

 

"[The performance hit] has been tested to be between 5% and 10% difference in response times by various people." – SANS Institute

 

"By auditing selectively and auditing things that should not happen, you can make the performance issue irrelevant." – SANS Institute

                                                          

"Such items that are covered by the HIPAA or Sarbanes-Oxley or Gramm-Leach-Bliley fall into [the always audit] category" – SANS Institute

 

 

Recommended base (always on) Auditing for Prod:

·                     NOT_EXISTS – when the SQL target object does not exist

·                     SESSION – connection, disconnections, timestamps, I/Os etc.

·                     ALTER ANY TABLE

·                     ALTER USER

·                     CREATE statements

·                     CREATE ROLE

·                     CREATE USER

·                     DROP statements

·                     DROP ANY PROCEDURE

·                     DROP ANY TABLE

·                     GRANT ANY PRIVILEGE

·                     GRANT ANY ROLE

·                     INSERT statements (failures only)

·                     AUDIT ALL ON SYS.AUD$ BY ACCESS

 

In a production system, DDL is rare.  Aside from the occasional creation of a new user, and perhaps a system update, these audits should only produce records on a rare occasion.  When the system is being updated auditing can be turned off temporarily to prevent garbage logs.

Sources:

Auditing:

SANS  Security 509.4 Auditing Oracle  (Only available if you take the really expensive class, I borrowed it)
Oracle 9i Database Administrator's guide Release 2
Oracle Database 10g DBA handbook (by Oracle Press)
http://www.petefinnigan.com
Introduction to simple Oracle Auiding (great read by pete finnigan)

General Oracle Security:
A simple Oracle Host-Based Scanner
Oracle Database Security Benchmark v 1.2 (for 8i and 9i) and v 2.0 (for 10g)  (This is put out by the Center for Internet Security, a great source for benchmark documents, and measuring tools, for many common industry software)
cisecurity.org/tools2/oracle/CIS_Oracle_Benchmark_v1.2.pdf
cisecurity.org/tools2/oracle/CIS_Oracle_Benchmark_v2.0.pdf

[Tejay Cardon]

Well things went OK.  I've got permission to start auditing on one development instance, however I have to move sys.aud$ to it's own tablespace.  That is not supported by Oracle, and I think it's a mistake, but I am willing to let them deal with the headache it will cause.