IDS 7.3 - Missing Records 1

[govmule]

Hello Everyone,

I have some records that are just disappearing after being entered into our IDS 7.3 database. I know that there is not a ghost in the machine so I was wondering of anyone knew of a way to log info regarding every database change so I could track what process is removing these records.

Any ideas?

Thanks,

Jack

 

[vpshriyan]

Hi,

Secure auditing features can be used to trace the successful or unsuccessful user activities in a database.

Get the some help:
onaudit

Current status:
onaudit -c

You can turn on auditing in one of the following:
1) onaudit -l 1
2) ADTMODE 1 #Auditing mode (configuration para)

Specify the directory for audit dump files:
1) onaudit -p /usr1/dbaudit
2) ADTPATH /usr1/dbaudit (configuration para)

Trace the manipulations (INSERT,UPDATE,DELETE) of the user jonathan.

onaudit -a -u jonathan -e +INRW,UPRW,DLRW

TIP: If the user list is huge, produce the input instructions in a form of a file. Each line of the instruction file should be in the form:
targetmask templatemask events

For example:
jonathan - +INRW,UPRW,DLRW
hari _sec2 +ALTB,DLRW -CRTR

To analyze the Audit File:
onshowaudit -u jonathan

It is to be noted that, implementation of secured audit features in a database will have it's own overhead and may cause certain degree of performance degradation.

Following is the link to informix Trusted Facility Manual here in you will find more about onaudit facility:

http://elib.cs.berkeley.edu/admin/informix/doc/answers/pubs/pdf/4808.pdf

Regards,
Shriyan
"The hidden flaw never remains hidden."

 

[govmule]

Hello Shriyan,

Thank you very much for the info. I'll research today and set it up tonight.

I'll let you know how I make out.

Thanks,

Jack

 

[vpshriyan]

Jack:

Alternatively, if you have a specific table in mind, and wants to trace user operation on it, you may rely on trigger machanism to discover the manipulations.

Example:

create trigger tab1_del delete on tab1
referencing old as pre_del
for each row
(
execute procedure tab1_proc(f1,f2,f3)
);

create procedure tab1_proc(
mf1 char(8),
mf2 char(7),
mf3 decimal(15,2))

define cnt int;
define mrem varchar(50);

set lock mode to wait;
let mrem='DELETED ON ' || today || ' BY ' || user;

while 1=1
insert into tab2 (mf1, mf2, mf3, mrem);

select dbinfo('sqlca.sqlerrd2') into cnt from systables where tabid=1;
if cnt is null then
let cnt=0;
end if
if cnt = 1 then
exit while;
end if
end while

end procedure;

You may download an e-book (pdf) version of Trigger from:

http://publibfi.boulder.ibm.com/epubs/pdf/7230a.pdf

Regards,
Shriyan
"Maturity is knowing when and where to be immature."

 

[govmule]

Hey vpshriyan,

Awesome! This auditing facility is great. I should have had this running all along. A very good lesson in simple DBA tactics. I also learned a good bit about the checks and balances possible to keep everyone honest including the DBA (I'm keeping it to myself but it's good to know).

Anyway, where going to enter the phantom records again on Tuesday. Once I track down what's causing them to evaporate I'll drop you a line.

Thanks again,

Jack Luster