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]
Code
-- 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
as
begin
if (sys_context('userenv','session_user') = 'APEX_PUBLIC_USER'
AND
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
then
return 1;
else
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)
as
l_msg varchar2(32767);
function t_row(
p_label in varchar2,
p_data in varchar2)
return varchar2
is
begin
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;
begin
l_msg := '<html><head><style type="text/css">body{font-family:helvetica}</style></head><body>'||
'<table style="boder:0px;">'||
t_row('Schema',schema1)||
t_row('Table',table1)||
t_row('Policy',policy1)||
t_row('User',user)||
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>')||
'</table></body></html>';
utl_mail.send(
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
BEGIN
DBMS_FGA.DROP_POLICY
(object_schema => 'FGA',
object_name => 'EMP',
policy_name => 'CHECK_FOOTPRINT');
END;
/
set termout on
BEGIN
DBMS_FGA.add_policy
(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);
END;
/
-- Audit entries are stored in sys.dba_fga_audit_trail
Tyler Muth's Blog 
Pete Finnigan said
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.
cheers
Pete
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.
hamza
Phil Winfield said
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?
Thanks
Phil
Earl Lewis said
Tyler,
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.
Earl
prescription glasses for sports said
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.
prescription glasses for preschoolers said
I always emailed this blog post page to all my contacts,
since if like to read it then my contacts will too.
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.
preparing for an Interview said
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.
Desert Nights Casino said
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.
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.
www.losco.co.kr said
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”.
imrankhan said
HOW CAN I enable audit for a prablamtic table in oracle 11g