Re: Recognize Data Base Manipulation

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Wed, 14 Aug 2002 16:03:03 +0100
Message-ID: <3d5a7128$0$8506$ed9e5944_at_reading.news.pipex.net>

What I meant was something along the lines below. (I'm afraid I didn't spool the table and trigger creation)

SQL> desc t

 Name                                                              Null?

Type

• --------

  ---

  C1 VARCHAR2(20)

SQL> desc audit_t

 Name                                                              Null?

Type

• --------

  ---

  C1_VALUE VARCHAR2(20) DATECHANGED DATE CHANGEDBY VARCHAR2(64)

SQL> select text from user_source order by line;

TEXT

---

---

TRIGGER I_T
BEFORE INSERT ON T
FOR EACH ROW
DECLARE
CLIENT_INFO VARCHAR2(64);
BEGIN
DBMS_APPLICATION_INFO.READ_CLIENT_INFO(CLIENT_INFO); INSERT INTO AUDIT_T VALUES(:NEW.C1,SYSDATE,CLIENT_INFO); END; 9 rows selected.

SQL> delete t;

4 rows deleted.

SQL> commit;

Commit complete.

SQL> exec dbms_application_info.set_client_info('NIALLS_APP');

PL/SQL procedure successfully completed.

SQL> insert into t values('test');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from audit_t;

C1_VALUE DATECHANG CHANGEDBY
-------------------- --------- ---------------------------------------------

---

TEST                 14-AUG-02
TEST2                14-AUG-02 NIALLSAPP
test                 14-AUG-02 NIALLS_APP

Of course you may not get client_info set for the admin tools so you might also want to record information from v$session for your audit.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************

"Rudolf Golser" <r.golser_at_teamaxess.com> wrote in message
news:3d5a51f0$1_at_e-post.inode.at...
> I found the following explanation of this package:
> The idee of using this package is to enable the DBA to gather more
> statistics of the application running in the database. He could overload
the
> sys package, by changing the public synonym, to acquire his own
statistics.
> Is it possible for the DBA to delete or manipulate this file ?
>
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
> news:3d5a4b94$0$8507$ed9e5944_at_reading.news.pipex.net...
> > I think that what you are looking for is triggers and the
> > DBMS_APPLICATION_INFO supplied package.
> >
> >
> > --
> > Niall Litchfield
> > Oracle DBA
> > Audit Commission UK
> > *****************************************
> > Please include version and platform
> > and SQL where applicable
> > It makes life easier and increases the
> > likelihood of a good answer
> >
> > ******************************************
> > "Rudolf Golser" <r.golser_at_teamaxess.com> wrote in message
> > news:3d5a4045_at_e-post.inode.at...
> > > Does anybody know some mechanisms/algorithms for recognizing changes
> > > to specific data sets in a data base (insert, deletion, change of a
data
> > > set).
> > > The method should work independent of the integrated password security
> > > mechanisms in the data base. The method should not prevent changing
the
> > > data, but should recognize that data was changed not by the specific
> > > security application (hardware/software encryption). Thus, even if a
> data
> > > base administrator has Admin rights, it should be possible to
recognize
> > that
> > > the content was changed directly and not via the service application.
> > >
> > > Thank You
> > >
> > >
> >
> >
>
>

Received on Wed Aug 14 2002 - 17:03:03 CEST