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
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
June 27, 2007 at 3:47 am
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
June 27, 2007 at 12:28 pm
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
July 6, 2007 at 3:53 am
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
March 3, 2008 at 12:21 pm
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