Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

Fine Grained Auditing

Posted by Tyler Muth on June 26, 2007

Below is a short screencast (code in action, no PowerPoint) of Fine Grained Auditing in the context of Application Express. It’s in response to this article, which only dedicates a single sentence to the topic of trip-wires, but I do credit them for raising the issue. The code is posted below the screencast. Other things to keep in mind:

  • This policy will fire for every insert,update,delete, or select statement for this table, but not every row
  • It would be easy to defeat this policy by setting module yourself and logging in as the APEX user, but the idea is that this is more of a silent alarm meant to catch people who are not aware of this policy, or at least not aware of exactly what it’s looking for
  • It would be a good idea to wrap the CHECK_FOOTPRINT function, making it difficult to see what it is looking for
  • Fine Grained Auditing requires Enterprise Edition (no SE or XE)
  • The FGA_NOTIFY procedure uses UTL_MAIL which means you need to set the initialization parameter SMTP_OUT_SERVER to the servername:port of your email server

ScreenCast Click here for the full-size version [splashcast IOMS3823UM]

-- Make sure you set the spfile parameter SMTP_OUT_SERVER to the
-- name and port of your mail server: yourmailserver.com:25
-- Grant execute on utl_mail to FGA;
-- Grant execute on DBMS_FGA to FGA;
-- Also make sure you change the "sender" and "recipients"
-- parameters in the call to utl_mail

create or replace function check_footprint
return number
    if (sys_context('userenv','session_user') = 'APEX_PUBLIC_USER'
        sys_context('userenv','module') = 'APEX:APPLICATION 106')
        -- Other thoughts:
        -- Business Hours
        -- (to_char(sysdate, 'D') between 2 and 6 and to_char(sysdate, 'HH24') between 8 and 18
        return 1;
        return 0;
    end if;
end check_footprint;

grant execute on check_footprint to public;

create or replace procedure fga_notify (
    schema1 in varchar2,
    table1  in varchar2,
    policy1 in varchar2)
    l_msg   varchar2(32767);

    function t_row(
        p_label     in varchar2,
        p_data      in varchar2)
    return varchar2
        return '<tr><td style="text-align:right;">'||p_label||'</td>'||
    '    <td style="font-weight:bold;">'||p_data||'</td></tr>'||utl_tcp.crlf;
    end t_row;

    l_msg := '<html><head><style type="text/css">body{font-family:helvetica}</style></head><body>'||
    '<table style="boder:0px;">'||
    t_row('Client Info',sys_context('userenv', 'client_info'))||
    t_row('Client Identifier',sys_context('userenv', 'client_identifier'))||
    t_row('IP Address',sys_context('userenv', 'ip_address'))||
    t_row('Auth Type',sys_context('userenv', 'authentication_type'))||
    t_row('Session ID',sys_context('userenv', 'sessionid'))||
    t_row('DB Name',sys_context('userenv', 'db_name'))||
    t_row('Host',sys_context('userenv', 'host'))||
    t_row('OS User',sys_context('userenv', 'os_user'))||
    t_row('External Name',sys_context('userenv', 'external_name'))||
    t_row('Current SQL','<pre> '||sys_context('userenv', 'current_sql')||'</pre>')||

        SENDER      => 'orcl_db@demo1',
        RECIPIENTS  => 'oracle@localhost',
        --CC        => '',
        --BCC       => '',
        SUBJECT     => 'Policy Violation',
        MESSAGE     => l_msg,
        MIME_TYPE   => 'text/html',
        PRIORITY    => 1);
end fga_notify;
show errors
set termout off
(object_schema       => 'FGA',
     object_name         => 'EMP',
     policy_name         => 'CHECK_FOOTPRINT');
set termout on
   (object_schema       => 'FGA',
    object_name         => 'EMP',
    policy_name         => 'ONLY_APEX_APP',
    audit_condition     => 'FGA.CHECK_FOOTPRINT = 0',
    audit_column        => null,
    handler_schema      => 'FGA',
    handler_module      => 'FGA_NOTIFY',
    statement_types     => 'INSERT,UPDATE,DELETE,SELECT',
    ENABLE              => TRUE);
-- Audit entries are stored in sys.dba_fga_audit_trail

12 Responses to “Fine Grained Auditing”

  1. Hi Tyler,

    Nice short article and webcast, i find most sites do not have audit enabled or if they do its not used for security or is woefully inadequate or if they collect the data they dont look at it. there ia a tide of change though recently and people are becoming aware of the necessity to audit access to databases and data.



  2. Hamza said

    tyler – this is an excellent example of how audio, video and raw code can be combined to demonstrate a simple solution to a very common problem. i am looking forward to your series in this form.


  3. Tyler

    Excellent presentation – I’ve not used this for auditing but the only issue I can see is hard coding the app ID. Can an application alias be used instead?



  4. Earl Lewis said


    Found this article very useful. Thanks for posting it. One comment I wanted to make is that it seems that your embedded video is for an encrypted RMAN backup video rather than the FGA video you intended to embed. Just wanted to let you know about this.


  5. We are a group of volunteers and opening a new scheme in our community.
    Your website provided us with valuable information to work on.
    You’ve performed a formidable process and our entire community will likely be grateful to you.

  6. I always emailed this blog post page to all my contacts,
    since if like to read it then my contacts will too.

  7. Waldo said

    It’s awesome to visit this website and reading the views of all mates on the topic of this paragraph, while I am also zealous of getting know-how.

  8. Hi everyone, it’s my first pay a quick visit at this site, and post is in fact fruitful for me, keep up posting such content.

  9. Customer support is a superb news merchandise here, obtainable 24/7 via electronic mail, cell phone and chat, you’re in no way far by an answer, particularly since Casino vestibule consists of a good generally available conversation window you can use in order to talk with them. For prosperous online game playing, you truly have to address your hard earned dollars shift worries.

  10. Anonymous said

    If yyou hav? kids, th?n ??u need t? make sure th?? are eating foods th?t definitely are balanced for
    them. The author’s check ?ut is th?t our eating behavior expresses ?ur emotions, everyday living method, ?nd even h?w we check out God.

  11. It seriously ?s true, even th?ugh, that food plan will perform a essential part ?n ?ll essential programs ?f one’s system, ?nd therefore h??
    a function ?n acne elimination. This ?? as a result
    ?f ? learn cleanse or a? most men ?nd women simply call ?t “The lemonade diet program”.

  12. imrankhan said

    HOW CAN I enable audit for a prablamtic table in oracle 11g

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: