Oracle Auditing - Real World Best Practice

Posted on 2010-01-06
Last Modified: 2013-12-11
Hello, we have recently moved to Oracle from SQL Server and I am a newbie at a lot of Oracle standards.  We turned on the oracle auditing features which I think are really robust however, I'm wondering what do people in the real world do with auditing?  Do you audit EVERYTHING or do you only turn on certain things... do you write your own auditing logic... should we create separate tables to only store certain auditing fields?

We turned it on and of course its a TON of data... I assume we should prune it at some point?  What interval do you recommend?  

We have a process that takes 4 hours to run and without auditing it takes 3.5 hours to run.  So, the auditing didn't seem to impact it all that much.  

Any advice in the right direction is appreciated.
Question by:Roxanne25
    LVL 29

    Assisted Solution

    The best practice on using the AUDIT command is: DONT, only use when necesary.
    You need first to establish the auditing requirements, what is it you  need to keep track and record?
    LVL 40

    Assisted Solution

    As Mike said, you need to determine what your auditing requirements are first, then let us know and we can help with a recommendation.

    I'd recommend fine grained auditing first.

    You can also selectively audit DBA operations without auditing your general users.
    Or vice-versa.

    The standard auditing functionality imposes a bit of overhead, perhaps 5% performance degradation. But you can selectively use FGA to get around that.

    It is generally not necessary to use trigger based custom auditing, most of the time people use it that don't know about the full set of builtin audit features.

    Also, if you keep backups of archive logs, you potentially have the redo stream at your disposal for later mining if need be with LogMiner, without auditing overhead. It is retroactive auditing. You may need to turn on supplemental logging to use LogMiner effectively.


    Author Comment

    Well I have been tasked to find out what auditing we don't need.... lol.  I just wanted real world examples of how YOU guys do auditing.  I guess I'm hearing its best to not turn on everything and do selective...but just wondered how other organizations do their auditing.  

    I work for the gov' their answer when asked what they need is everything lol.
    LVL 29

    Expert Comment

    You hardly don't need to use the AUDIT statement due to the built-in functionality Oracle provides (as  mrjoltcola pointed out).
    However, the most "pressing" questions about auditing surface when someone (mainly an executive) wants to know "Who changed this data?" or "Who dropped that table?" or "Who modified ...blah, blah, blah...?. For this kind of situations you could use logminer and/or AUDIT for tracking "special" operations (or the recomended FGA).

    Author Comment

    Well we just had a conference call and I tried to explain its best not to turn on EVERYTHING and recommended to only track session activity, table auditing and invalid login attemps and they shot me down... the CFO said he wanted us to turn on EVERYTHING and then see if we need to adjust it from there.  They also reiterated that they want me to tell them what kind of things I can turn off... which I thought I did.  

    I tried to say that it was best practice to do specific things but they were like "is that best practice for the gov't??  We have different standards".  
    LVL 40

    Accepted Solution

    Sounds like your customer / CFO doesn't know how to articulate what they want, perhaps because they don't know what is availabe.

    You certainly don't want to "speak geek" with them and tell them you can audit the audit of the audit...

    What you want to do is present them with a list of choices with non-technical explanations of what each choice is.

    You must somehow explain to them that Oracle auditing is configurable and simply saying "EVERYTHING" is not useful, because you are required to choose what you audit, not choose what not to audit. Its an opt-in approach, not an opt-out. Yes, Oracle can "audit all", however, that doesn't allow you to turn off selective audits as needed, so you need to specify what you audit specifically so each feature can be adjusted individually. The customer needs to work with you to provide a requirement. You need to educate the customer in the language that they understand and if someone is speaking "we have different standards" then they need to provide you with those standards. There are specific standards, such as Sarbanes-Oxley and HIPAA. The gov't should also be able to provide a standard for you. Hand-wavy terms is not appropriate. Specifics are. Often it is a MIL std.

    There are two uses of information, DML (insert, update, delete) and query (select). You likely need to audit both.

    1) System / DBA activities - Low overhead, some audited by default. With this customer, I would audit all DBA activities.

    2) Sensitive / identifying information - Ensure people are only accessing data as needed to complete their job, not just to poke around and be nosy. It is best only to audit sensitive tables and not all tables. Some tables, like dictionary / lookup tables of states, cities, etc. do not need auditing, but may be accessed frequently. Best to compile a list of tables and group them by feature or some other high-level language that your customer can understand, and decide which tables to audit DML and which tables to audit all, and there may be tables to completely ignore.

    Sounds like your CFO and/or customer is looking to you for recommendation, but you have some work to do to present your recommendation in such a way that wins their trust.

    Author Comment

    Thank you very much that definately has given me a better direction... I think the approach of saying its an opt in rather than opt out methodolgy will greatly improve my communication with them. :)  

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Suggested Solutions

    Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now