Re: auditing code changes ?

From: Yaping Chen <hangzhoumaster_at_gmail.com>
Date: Fri, 26 Sep 2008 08:09:34 +0800
Message-ID: <170d3ad90809251709m5792ef9ep232a8adb820facdd@mail.gmail.com>

You can query obj$.CTIME,MTIME,STIME to determine the action, such as aleter or complie. If submit create or repalce command on procedure, even though the content is identical, but timestamp in obj$ still changed, in this case, you can use hash value on the procedure to check whether the content be changed. If use hash value to determine objects changed, you must store hash value firstly, then you have base value to compare.

Since Oracle 10g R2, if you set audit_trail=DB_EXTENDED, triggered SQL statement be recorded in aud$.SQLTEXT, but before Oracle 10g, aud$ table doesn't record the SQL statements, for ddl operation, you can use trigger to accomplish it, for select statements, you can use fine grained auditing (DBMS_FGA).

---

– refer to Application Developer's Guide - Fundamentals – logon as sys

whenever sqlerror exit failure;

create table audit_trail

(USERNAME     VARCHAR2(30),
MACHINE       VARCHAR2(64),
CLIENT_IP     VARCHAR2(15),
OS_USER       VARCHAR2(30),
OWNER         VARCHAR2(30),
OBJECT_NAME   VARCHAR2(30),
OBJECT_TYPE   VARCHAR2(18),
ACTION        VARCHAR2(30),
SQL_TEXT      VARCHAR2(4000),
TIME          DATE);

create or replace trigger audit_ddl_trigger   after ddl on database
declare

  n        number;
  stmt     varchar2(4000);

  sql_text ora_name_list_t;
begin

  n := ora_sql_txt(sql_text);
  for i in 1..n loop

      stmt := stmt || sql_text(i);
  end loop;

  insert into
audit_trail(username,machine,client_ip,os_user,owner,object_name,object_type,action,sql_text,time)

values(ora_login_user,SYS_CONTEXT('USERENV','HOST'),SYS_CONTEXT('USERENV','IP_ADDRESS'),

SYS_CONTEXT('USERENV','OS_USER'),ora_dict_obj_owner,ora_dict_obj_name,ora_dict_obj_type,

         ora_sysevent,stmt,sysdate);

exception
  when others then
    NULL;
end;
/

2008/9/25 John Kanagaraj <john.kanagaraj_at_gmail.com>

> Have a look at a paper I wrote for oaug for soxed in dbas. I used a
> ddl trigger to capture all ddls and details of who is changing them
> including os user, client, etc.
>
> John
>
>
>
> On 9/25/08, Powell, Mark D <mark.powell_at_eds.com> wrote:
> > I believe the command you want is AUDIT PROCEDURE. We audit DDL in
> > production and I see all CREATE, ALTER, and DROP statements issued
> > against pretty much all the object types I care about including
> > procedures. I am pretty sure the commands I used were AUDIT object_type
> > though it has been a few years since I set this up.
> >
> >
> > -- Mark D Powell --
> > Phone (313) 592-5148
> >
> >
> >
> >
> > ________________________________
> >
> > From: oracle-l-bounce_at_freelists.org
> > [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Freeman, Donald
> > Sent: Thursday, September 25, 2008 8:48 AM
> > To: 'oracledbaquestions_at_gmail.com'; oracle-l_at_freelists.org
> > Subject: RE: auditing code changes ?
> >
> >
> > I believe you can at least check dba_objects and see the
> > last_compile_time. Won't tell you who did it though.
> >
> > Donald Freeman
> > Database Administrator II
> > Commonwealth of Pennsylvania
> > Department of Health
> > Bureau of Information Technology
> > 2150 Herr Street
> > Harrisburg, PA 17103
> > dofreeman_at_state.pa.us
> >
> >
> >
> > ________________________________
> >
> > From: oracle-l-bounce_at_freelists.org
> > [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Dba DBA
> > Sent: Thursday, September 25, 2008 8:28 AM
> > To: oracle-l_at_freelists.org
> > Subject: auditing code changes ?
> >
> >
> > I have auditing turned on. It does not appear to track when
> > someone recompiles code.
> >
> > I have audit all on.
> >
> > is there a way to track this ?
> >
> >
> >
>
> --
> Sent from Gmail for mobile | mobile.google.com
>
> John Kanagaraj <><
> http://www.linkedin.com/in/johnkanagaraj
> http://jkanagaraj.wordpress.com (Sorry - not an Oracle blog!)
> ** The opinions and facts contained in this message are entirely mine
> and do not reflect those of my employer or customers **
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Regards,
Yaping Chen

http://yaping123.wordpress.com
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 25 2008 - 19:09:34 CDT