SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
DBAUDIT: how to use / customize

 
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools View previous topic
View next topic
DBAUDIT: how to use / customize
Author Message
ucbus



Joined: 05 Jul 2005
Posts: 8

Post DBAUDIT: how to use / customize Reply with quote

I have a client-server web application.

The client is JSP based web application with various steps ( say step1,2,3 etc) involved. The web page is a form which is accessed by multiple users at the same time at different steps.

The server application reads the data sent by the client and updates a IBM DB2 database 8.1.

The database updates are carried through a generic ID and not the actual user id of the user.

When a page is loaded for the first time, we populate the data from the database. Users may update some fields on the form and the data is updated to the database.

Now, I would like to find out and see if your dbupdate can help me find out:
1.userid ( not the generic ID that updated) of the actual user
2. date time
3. Fields updated by the user with before and after values

The client-server web application is already running application hence I have little flexibility to change and major modification to the code is not possible.

Any ideas how dbAUDIT can be used for this purpose


Tue Jul 05, 2005 3:09 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Re: DBAUDIT: how to use / customize Reply with quote

Please see my previous message for details.

Also I forgot to confirm that the audit trail records would store everything that you specified as requirements, including specific user name, time, and before/after values for every change.

If you decide to stick with the described method you will need to create a new table, a small procedure (we can provide the necessary code) and make minor changes in one of your JSP pages. We can help you with this also.

In addition, I want to stress that the described method is not limited to your web application. DB Audit can still capture all other changes made directly in the database using other applications including any backdoor access.

: I have a client-server web application.

: The client is JSP based web application with various steps ( say step1,2,3
: etc) involved. The web page is a form which is accessed by multiple users
: at the same time at different steps.

: The server application reads the data sent by the client and updates a IBM
: DB2 database 8.1.

: The database updates are carried through a generic ID and not the actual user
: id of the user.

: When a page is loaded for the first time, we populate the data from the
: database. Users may update some fields on the form and the data is updated
: to the database.

: Now, I would like to find out and see if your dbupdate can help me find out:
: 1.userid ( not the generic ID that updated) of the actual user
: 2. date time
: 3. Fields updated by the user with before and after values

: The client-server web application is already running application hence I have
: little flexibility to change and major modification to the code is not
: possible.

: Any ideas how dbAUDIT can be used for this purpose

Tue Jul 05, 2005 4:14 pm View user's profile Send private message
ucbus



Joined: 05 Jul 2005
Posts: 8

Post Re: DBAUDIT: how to use / customize Reply with quote

Thanks for the reply.
Our situations is little bit complicated.
The client web application sends the database request to the server in the form
of MQseries message. The server
application unwraps the beans from the MQseries message, retrieves the data from
the database, creates reply
MQSeries message and send it back to client. In case of updates,same procedure
is followed.

The application on the client side can access the actual user id. However, the
application on the server uses a
generic id to access the database.

The databae contains 10 tables which we would like to have the auditing. Let us
say these tables as "app-tabels"

Thus is what I have in mind for the dbaudit tool and would like to know if it
can help us.

1.Add "actual user id" to the mqseries message. The server side application
will populate a new table. called "my
user table". This will have three columns user id, page name number( the page
name from where the user submitted
the info), date time stamp.
2.As soon as a new row is created in "my audit table" make dbaudit utility to
read the user id,page name and
datetime stamp, then kick off the "auditing of the data changes" on all of the
10 app-tables.
3. Record the data changed and have the dbaudit to write the changes to another
database table. let us
say "mydbaudit". The key on this would be user id,page name and datetime stamp,
followed by the sequence number.
I would like to record the field changed, before value and the after value into
the "mydbaudit" table.
4. Then use the dbaudit feature for report generation to access the table "mydbaudit" and
retreive and report the data as needed. If dbauidt can not do,
this we could write our own application for thsi I guess

Please let me know if it is doable with dbaudit tool as soon as possible.

: Please see my previous message for details.

: Also I forgot to confirm that the audit trail records would store everything
: that you specified as requirements, including specific user name, time,
: and before/after values for every change.

: If you decide to stick with the described method you will need to create a
: new table, a small procedure (we can provide the necessary code) and make
: minor changes in one of your JSP pages. We can help you with this also.

: In addition, I want to stress that the described method is not limited to
: your web application. DB Audit can still capture all other changes made
: directly in the database using other applications including any backdoor
: access.

Tue Jul 05, 2005 6:46 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Re: DBAUDIT: how to use / customize Reply with quote

Step 1. is ok.

Step 2 is not. The auditing is a kind of "instant". It does not stop or start, and you don't need to fire it up on connection event. It is fired automatically by the database at the time of data changes.

The "my user table" only needs 2 or 3 columns: "app user", "connection id", and optionally "web page". At the time of connection your application simply does 2 simple things:

DELETE FROM "my user table" WHERE "connection id" = utf_get_connection_id();
INSERT INTO "my user table" VALUES "user name", utf_get_connection_id(), "some web page";

The little procedure "hooked" to DB Audit (that's the procedure we mentioned earlier) will simply do SELECT max("app user") FROM "my user _table" WHERE "connection id" = [parameter passed by DB Audit] and then return the name back to DB Audit.

Step 3 and 4. This all will be done for you by DB Audit. Just install it and select which application tables to audit. There will be no "mydbaudit" table, but other then that everything else will be as you wish including the built-in reports

All of this can be probably setup in less then a hour. I don't know how long it may take to change your JSP page responsible for establishing a new database connection.

.
: Thanks for the reply.
: Our situations is little bit complicated.
: The client web application sends the database request to the server in the
: form
: of MQseries message. The server
: application unwraps the beans from the MQseries message, retrieves the data
: from
: the database, creates reply
: MQSeries message and send it back to client. In case of updates,same
: procedure
: is followed.

: The application on the client side can access the actual user id. However,
: the
: application on the server uses a
: generic id to access the database.

: The databae contains 10 tables which we would like to have the auditing. Let
: us
: say these tables as "app-tabels"

: Thus is what I have in mind for the dbaudit tool and would like to know if it
: can help us.

: 1.Add "actual user id" to the mqseries message. The server side
: application
: will populate a new table. called "my
: user table". This will have three columns user id, page name number( the
: page
: name from where the user submitted
: the info), date time stamp.
: 2.As soon as a new row is created in "my audit table" make dbaudit
: utility to
: read the user id,page name and
: datetime stamp, then kick off the "auditing of the data changes" on
: all of the
: 10 app-tables.
: 3. Record the data changed and have the dbaudit to write the changes to
: another
: database table. let us
: say "mydbaudit". The key on this would be user id,page name and
: datetime stamp,
: followed by the sequence number.
: I would like to record the field changed, before value and the after value
: into
: the "mydbaudit" table.
: 4. Then use the dbaudit feature for report generation to access the table
: "mydbaudit" and
: retreive and report the data as needed. If dbauidt can not do,
: this we could write our own application for thsi I guess

: Please let me know if it is doable with dbaudit tool as soon as possible.

Tue Jul 05, 2005 8:29 pm View user's profile Send private message
ucbus



Joined: 05 Jul 2005
Posts: 8

Post Re: DBAUDIT: how to use / customize Reply with quote

Thanks. Based on your response it apperas like the
product is promising. I still have some questions.

1. May be it is mentioned in your document.Could you please
let me know the following.

DELETE FROM "my user table" WHERE "connection id" = utf_get_connection_id();
INSERT INTO "my user table" VALUES "user name", utf_get_connection_id(), "some web page";

a. Why are we deleting and then inserting? is it for uniqueness?
If I add datetime stamp then can't I mainitain
uniqueness?
b.utf_get_connection_id()? is it available on DB2? is it a function call?
What is this?
c. I am not making database connections directly from
the JSP.My server application (java)takes fixed number of connections
at the start and maintains a connection pool. Let us say
we have to update three tables,
-the application takes a connection from the pool,
-calls a stored procedure to update table1
-returns the connection to the pool.
The above process repeats for all the three tables.

In the above situalation how can I use the utf_get_connection_id()
for retrieving the id? May be this is possible, but
I do not know. Please let me know.

2. I need to have a custom table to which the dbaudit
need to write the data change information.
The custom table will have
user id,
page name
datetime stamp,
sequence number
table column name,
table field value before
table field value after
Are you saying it is quite possible to write a table
like above, using dbaudit?

3. I have my database to be monitored located
on an AIX node. Where do I need to install dbaudit?
Can I install dbaudit on a windows box and
monitor a database on AIX node.Is it possible?
4. Regarding the reports: are these reports generated
by dbaudit in html format? If so, I would like to install
a webserver on the windows box, install dbaudit( based
on suitability see questions 1,2) and let users
query the reports feature as needed. Is this possible?

I need to present prototype design for the project
and dbaudit appears to be the way for us.
Thanks

: Step 1. is ok.

: Step 2 is not. The auditing is a kind of "instant". It does not
: stop or start, and you don't need to fire it up on connection event. It is
: fired automatically by the database at the time of data changes.

: The "my user table" only needs 2 or 3 columns: "app
: user", "connection id", and optionally "web
: page". At the time of connection your application simply does 2
: simple things: DELETE FROM "my user table" WHERE
: "connection id" = utf_get_connection_id();
: INSERT INTO "my user table" VALUES "user name",
: utf_get_connection_id(), "some web page";

: The little procedure "hooked" to DB Audit (that's the procedure we
: mentioned earlier) will simply do SELECT max("app user") FROM
: "my user _table" WHERE "connection id" = [parameter
: passed by DB Audit] and then return the name back to DB Audit.

: Step 3 and 4. This all will be done for you by DB Audit. Just install it and
: select which application tables to audit. There will be no
: "mydbaudit" table, but other then that everything else will be
: as you wish including the built-in reports

: All of this can be probably setup in less then a hour. I don't know how long
: it may take to change your JSP page responsible for establishing a new
: database connection.

: .

Tue Jul 05, 2005 11:42 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Re: DBAUDIT: how to use / customize Reply with quote

I will follow you numeration.

1.a DELETE/INSERT are done for both uniqueness and for performance. At the time of data changes this table is queried for user names and you want this table to be as small as possible so that the name querying has no performance impact on your database processing. This is especially important for batch updates.

1. b If you are running DB2 8.2 you can use the built-in SQL function APPLICATION_ID( ). If you use an earlier version you will need to create this function yourself. IBM has several ready to use examples documented on their web site, how to create this function. "utf_get_connection_id()" is just a name I copied from our documents.

1.c As long as every web-user session is associated with a database connection, it is all the same. Whether the connection is established each time a request is executed or taken from a pool it does not make any difference, because you can always track it back to the web user. Still you need to slightly modify some of your code (whether this is JSP or Java class) to add DELETE/INSERT commands or even better create a stored procedure in the database running the pair of DELETE/INSERT and from your code call the stored procedure passing user name as a parameter. Again "utf_get_connection_id()" is just a sample name of database function which you call use in any SQL.

2. Using single audit trail table to track all updates is a big performance problem. Try to avoid this method if possible. For performance reasons DB Audit internally generates separate audit trail table for each monitored application table. The structure of the trail table is similar to the structure of the audited table. This is done on purpose in order to avoid tons of manipulations with column names, data type conversions, etc… You just don't realize how much code you would need to execute to fit all possible updates into a single audit trail table. Some audit products follow this approach and guess what they can make your database really slow. On contrary DB Audit is designed to enable data change auditing with minimal or no visible performance impact.

If needed you can always write a stored procedure or report or even create a materialized view to consolidate all updates into a single summary table. The big difference is that it does not affect the application performance. It only takes more time to run a report. That's it.

3. Yes, the database could be on any system, AIX, Linux, whatever. The DB Audit front-end requires Windows, but this is just a graphical interface to configure things and run graphical reports.

4. They are graphical reports optimized for screen display, filtering, sorting, searching, grouping, aliasing, resizing, adjusting, zooming in/out, etc… in other words, designed for data analysis, viewing and printing and to reduce mountains of audit trail data to meaningful audit results. If you want you can export them to HTML, XLS, CSV, TXT files and a number of other formats, but this has to be done manually using the front-end.

In addition, DB Audit provides built-in methods to create custom user-defined reports. You can install just the Report Viewer option on your end-user computers to allow them running pre-built audit reports and creating their own without full access to the database auditing system.

: Thanks. Based on your response it apperas like the
: product is promising. I still have some questions.

: 1. May be it is mentioned in your document.Could you please
: let me know the following.

: DELETE FROM "my user table" WHERE "connection id" =
: utf_get_connection_id();
: INSERT INTO "my user table" VALUES "user name",
: utf_get_connection_id(), "some web page";

: a. Why are we deleting and then inserting? is it for uniqueness?
: If I add datetime stamp then can't I mainitain
: uniqueness?
: b.utf_get_connection_id()? is it available on DB2? is it a function call?
: What is this?
: c. I am not making database connections directly from
: the JSP.My server application (java)takes fixed number of connections
: at the start and maintains a connection pool. Let us say
: we have to update three tables,
: -the application takes a connection from the pool,
: -calls a stored procedure to update table1
: -returns the connection to the pool.
: The above process repeats for all the three tables.

: In the above situalation how can I use the utf_get_connection_id()
: for retrieving the id? May be this is possible, but
: I do not know. Please let me know.

: 2. I need to have a custom table to which the dbaudit
: need to write the data change information.
: The custom table will have
: user id,
: page name
: datetime stamp,
: sequence number
: table column name,
: table field value before
: table field value after
: Are you saying it is quite possible to write a table
: like above, using dbaudit?

: 3. I have my database to be monitored located
: on an AIX node. Where do I need to install dbaudit?
: Can I install dbaudit on a windows box and
: monitor a database on AIX node.Is it possible?
: 4. Regarding the reports: are these reports generated
: by dbaudit in html format? If so, I would like to install
: a webserver on the windows box, install dbaudit( based
: on suitability see questions 1,2) and let users
: query the reports feature as needed. Is this possible?

: I need to present prototype design for the project
: and dbaudit appears to be the way for us.
: Thanks

Wed Jul 06, 2005 9:16 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Re: DBAUDIT: how to use / customize Reply with quote

Some thoughts... I understand that your goal is to audit your web application usage, but don't make a common mistake on focusing in this area only. You really need to audit data changes in the database including any backdoor access to the data. Because if you don't and one day somebody will make changes not using the web-based front-end your data integrity and security will be compromised and you will not even know that! The right approach is to consider web-based access as just one of legitimate data-access methods. When you will start auditing you will be surprised how many changes are done using various backdoor and non-authorized methods and how often this happens without management knowledge and approval.

: I will follow you numeration.

: 1.a DELETE/INSERT are done for both uniqueness and for performance. At the
: time of data changes this table is queried for user names and you want
: this table to be as small as possible so that the name querying has no
: performance impact on your database processing. This is especially
: important for batch updates.

: 1. b If you are running DB2 8.2 you can use the built-in SQL function
: APPLICATION_ID( ). If you use an earlier version you will need to create
: this function yourself. IBM has several ready to use examples documented
: on their web site, how to create this function.
: "utf_get_connection_id()" is just a name I copied from our
: documents.

: 1.c As long as every web-user session is associated with a database
: connection, it is all the same. Whether the connection is established each
: time a request is executed or taken from a pool it does not make any
: difference, because you can always track it back to the web user. Still
: you need to slightly modify some of your code (whether this is JSP or Java
: class) to add DELETE/INSERT commands or even better create a stored
: procedure in the database running the pair of DELETE/INSERT and from your
: code call the stored procedure passing user name as a parameter. Again
: "utf_get_connection_id()" is just a sample name of database
: function which you call use in any SQL.

: 2. Using single audit trail table to track all updates is a big performance
: problem. Try to avoid this method if possible. For performance reasons DB
: Audit internally generates separate audit trail table for each monitored
: application table. The structure of the trail table is similar to the
: structure of the audited table. This is done on purpose in order to avoid
: tons of manipulations with column names, data type conversions, etc… You
: just don't realize how much code you would need to execute to fit all
: possible updates into a single audit trail table. Some audit products
: follow this approach and guess what they can make your database really
: slow. On contrary DB Audit is designed to enable data change auditing with
: minimal or no visible performance impact.

: If needed you can always write a stored procedure or report or even create a
: materialized view to consolidate all updates into a single summary table.
: The big difference is that it does not affect the application performance.
: It only takes more time to run a report. That's it.

: 3. Yes, the database could be on any system, AIX, Linux, whatever. The DB
: Audit front-end requires Windows, but this is just a graphical interface
: to configure things and run graphical reports.

: 4. They are graphical reports optimized for screen display, filtering,
: sorting, searching, grouping, aliasing, resizing, adjusting, zooming
: in/out, etc… in other words, designed for data analysis, viewing and
: printing and to reduce mountains of audit trail data to meaningful audit
: results. If you want you can export them to HTML, XLS, CSV, TXT files and
: a number of other formats, but this has to be done manually using the
: front-end.

: In addition, DB Audit provides built-in methods to create custom user-defined
: reports. You can install just the Report Viewer option on your end-user
: computers to allow them running pre-built audit reports and creating their
: own without full access to the database auditing system.

Wed Jul 06, 2005 9:29 am View user's profile Send private message
ucbus



Joined: 05 Jul 2005
Posts: 8

Post Re: DBAUDIT: how to use / customize Reply with quote

>>1.c As long as every web-user session is associated with a database
connection, it is all the same.
I guess this is the problem. My web application is some kind of a workflow application. User signs on the system
where his ID is validated. Then a asession is established. Now in that session user
my process different work items ( each work item is unique and has ID assoicated).
I would like to trace the user activity based on the
work item. You may consider work tiems as seperate web form
assoiciated with seperate submit process.
As per the web session there is only one session.
In one session the user may do multiple transactions on the database.

Any ideas how to do track single web session to multiple database transcations.

: Some thoughts... I understand that your goal is to audit your web application
: usage, but don't make a common mistake on focusing in this area only. You
: really need to audit data changes in the database including any backdoor
: access to the data. Because if you don't and one day somebody will make
: changes not using the web-based front-end your data integrity and security
: will be compromised and you will not even know that! The right approach is
: to consider web-based access as just one of legitimate data-access
: methods. When you will start auditing you will be surprised how many
: changes are done using various backdoor and non-authorized methods and how
: often this happens without management knowledge and approval.

Wed Jul 06, 2005 12:22 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Re: DBAUDIT: how to use / customize Reply with quote

Well, without detailed knowledge of your application I am unable to provide specific instructions on how to link database transactions to web users.
You said that each user is assigned a unique session id which is can be linked to unique workflow item id and so on. Maybe in "my user table" you need to have multiple "id" columns that would let you link back workflow item to session and then session to user. Anyway the concept is still the same – linkage of database connection to some application sessions, in other words mapping of a unique database connection (don't confuse this with transaction) to outside world. Also don't confuse connection pooling (which is reusing) with connection sharing.

: >>1.c As long as every web-user session is associated with a database
: connection, it is all the same.
: I guess this is the problem. My web application is some kind of a workflow
: application. User signs on the system
: where his ID is validated. Then a asession is established. Now in that
: session user
: my process different work items ( each work item is unique and has ID
: assoicated).
: I would like to trace the user activity based on the
: work item. You may consider work tiems as seperate web form
: assoiciated with seperate submit process.
: As per the web session there is only one session.
: In one session the user may do multiple transactions on the database.

: Any ideas how to do track single web session to multiple database
: transcations.

Wed Jul 06, 2005 1:05 pm View user's profile Send private message
ucbus



Joined: 05 Jul 2005
Posts: 8

Post Re: DBAUDIT: how to use / customize Reply with quote

Thanks for the help. While I consider this is right product
I am looking forward to see if I can control auditing from my
application. For instance as I mentioned in my initial posting,
as soon as a record is inserted into "my user table"
I would like to "start the dbaudit". I know DB2 allows
"cascading triggers" and i am not sure if it can be
used with dbaudit

: Well, without detailed knowledge of your application I am unable to provide
: specific instructions on how to link database transactions to web users.
: You said that each user is assigned a unique session id which is can be
: linked to unique workflow item id and so on. Maybe in "my user
: table" you need to have multiple "id" columns that would
: let you link back workflow item to session and then session to user.
: Anyway the concept is still the same – linkage of database connection to
: some application sessions, in other words mapping of a unique database
: connection (don't confuse this with transaction) to outside world. Also
: don't confuse connection pooling (which is reusing) with connection
: sharing.

Wed Jul 06, 2005 4:30 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Re: DBAUDIT: how to use / customize Reply with quote

"cascading triggers" what it has to do with DB Audit?

: Thanks for the help. While I consider this is right product
: I am looking forward to see if I can control auditing from my
: application. For instance as I mentioned in my initial posting,
: as soon as a record is inserted into "my user table"
: I would like to "start the dbaudit". I know DB2 allows
: "cascading triggers" and i am not sure if it can be
: used with dbaudit

Wed Jul 06, 2005 4:53 pm View user's profile Send private message
ucbus



Joined: 05 Jul 2005
Posts: 8

Post Re: DBAUDIT: how to use / customize Reply with quote

I am giving it as an example. "cascading triggers " allow one trigger to fire another trigger.
Similarly I would like to start the dbaudit if a record is inserted in " my user table"

Please let me know the e-mail address so that I can forward my question to you.

: "cascading triggers" what it has to do with DB Audit?

Wed Jul 06, 2005 5:14 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Re: DBAUDIT: how to use / customize Reply with quote

support@softtreetech.com

: I am giving it as an example. "cascading triggers " allow one
: trigger to fire another trigger.
: Similarly I would like to start the dbaudit if a record is inserted in "
: my user table"

: Please let me know the e-mail address so that I can forward my question to
: you.

Wed Jul 06, 2005 5:32 pm View user's profile Send private message
ucbus



Joined: 05 Jul 2005
Posts: 8

Post Re: DBAUDIT: how to use / customize Reply with quote

I have sent the process description to the
e-mail address. Please let me know
: support@softtreetech.com

Thu Jul 07, 2005 9:40 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.