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
