Sarma Vadlamani
asked on
Oracle Forms
Hi,
I have an application in Oracle Forms using Oracle Database.Requirement is as a part of audit tracking of some of the screens we need to capture the following.
Need to be able to audit by user name;
this will need to be a historical record thereby tracking all users.
When a specific user is requested provide the information in an excel document.
TABLE: client, Paypackage, dept, employee
REQUIRED:
Need to be able to go back and forward regardless of date asked: i.e. Audit asked for on 2014-10-31 but Parameter for report is 2010-10-31 - 2014-09-25.
So I created a parameter screen for search creteria.
Parameter Screen:
1. Can select Client Id or Username
2. Select specific date frame: from - to
3) generate report.
But the problem is I used Audit trail table but was not capturing data correctly as if it is search with client ID i can capture the client ID,
but if it is search by wild character of all clients then the result is not capturing the client ID.
sameis the case with Update and delete as the forms are storing rowid for update/delete transactions.
so overall the data captured in Audit trail is not accurate.
Can you please advise what else solution is recommended to get accurate audit information of all sorts search creteria and update and delete transactions,
Search creteria can be as follows.
1) search by individual client ID
2) search by wild card character to return matching clients
3) searach can be based on birthdate of that employee
4) search can be on gender
5) search can be on nationality etc.
update and delete always doing using the Rowid.
Thanks in advance.
I have an application in Oracle Forms using Oracle Database.Requirement is as a part of audit tracking of some of the screens we need to capture the following.
Need to be able to audit by user name;
this will need to be a historical record thereby tracking all users.
When a specific user is requested provide the information in an excel document.
TABLE: client, Paypackage, dept, employee
REQUIRED:
Need to be able to go back and forward regardless of date asked: i.e. Audit asked for on 2014-10-31 but Parameter for report is 2010-10-31 - 2014-09-25.
So I created a parameter screen for search creteria.
Parameter Screen:
1. Can select Client Id or Username
2. Select specific date frame: from - to
3) generate report.
But the problem is I used Audit trail table but was not capturing data correctly as if it is search with client ID i can capture the client ID,
but if it is search by wild character of all clients then the result is not capturing the client ID.
sameis the case with Update and delete as the forms are storing rowid for update/delete transactions.
so overall the data captured in Audit trail is not accurate.
Can you please advise what else solution is recommended to get accurate audit information of all sorts search creteria and update and delete transactions,
Search creteria can be as follows.
1) search by individual client ID
2) search by wild card character to return matching clients
3) searach can be based on birthdate of that employee
4) search can be on gender
5) search can be on nationality etc.
update and delete always doing using the Rowid.
Thanks in advance.
ASKER
1) DBA_COMMON_AUDIT_TRAIL - oracle audit table not custom table
2) this is old application and now they want the audit information to be tracked using some way. not from past
but from now onward they want to capture the audit information
2) this is old application and now they want the audit information to be tracked using some way. not from past
but from now onward they want to capture the audit information
You can turn on auditing options in Oracle to capture the kinds of information that Oracle collects automatically (plus a lot of information you may not need). But, if you do use the Oracle-provided audit trail table, you *MUST* plan for managing the large amount of data that will be captured there. If you don't monitor this you could fill up your SYSTEM or SYS_AUX tablespace. It may be better to create a custom table to contain just the audit columns you need, then set up a scheduled process (maybe to run each night at midnight) to copy the latest audit information from the default audit table to your custom table, then delete the rows from the default audit table that you no longer need there.
Do you just need to report user login activity? That is easy from the information that Oracle collects if you turn on auditing. Or, do you also need to report on data adds, changes or deletes that each user makes? That information is *NOT* captured by the default auditing that Oracle offers. For that, you need to explore the fine-grained auditing or non-default auditing that Oracle supports. Or, you may need to modify the application capture this level of detail.
Do you just need to report user login activity? That is easy from the information that Oracle collects if you turn on auditing. Or, do you also need to report on data adds, changes or deletes that each user makes? That information is *NOT* captured by the default auditing that Oracle offers. For that, you need to explore the fine-grained auditing or non-default auditing that Oracle supports. Or, you may need to modify the application capture this level of detail.
ASKER
yes it is not just the Oracle User, i know we can get the oracle user, but when a logged in Oracle user
searche for a particular employee/client and his information then
it is difficult to capture i already tried with audit trail table.
yes search,updates and deletes. updates and delete in oracle forms using rowid., but search is giving me hard time
as each time the search creteeria is different and the dba audit trail is not holding the data in a consistent manner.
i think some custom design is good but not sure how to do
thanks.
searche for a particular employee/client and his information then
it is difficult to capture i already tried with audit trail table.
yes search,updates and deletes. updates and delete in oracle forms using rowid., but search is giving me hard time
as each time the search creteeria is different and the dba audit trail is not holding the data in a consistent manner.
i think some custom design is good but not sure how to do
thanks.
If you actually need to audit searches, they are more complicated. Inserts, updates and deletes are not so difficult via Oracle's standard auditing options. I have never explored Oracle's "fine grained auditing" options, but they may include the ability to actually capture the search criteria used in queries. If they don't, you will need to add or modify key-execute-query triggers in your forms to capture this information in a custom audit table that you create for this purpose.
ASKER
yes I tried with fine grained auditing but that is also having limited capabilities and yes I need more of audit searches
because generally users try to search the confidential information but they dont try to do any changes.
how can I use the key execute query triggers when i search in other forms.
because generally users try to search the confidential information but they dont try to do any changes.
how can I use the key execute query triggers when i search in other forms.
Every Oracle Form can support a key-execute-query trigger at either the block level or the form level. If your organization really needs to be able to audit every query, then you will have to modify every block of every form that allows queries, to either add logic to, or create a key-execute-query trigger that will record: the table (or view_ name) being queried, the user logon name, the date and time and the non-blank values that were queried for.
I recommend that you write a stored PL\SQL procedure in the database (or a procedure in a database package) that is an "AUTONOMOUS TRANSACTIN" that includes a "commit" and accepts the values you need to capture as input parameters. Then you can call this from your key-execute-query triggers, and just pass it the values from the form that need to be saved.
There may be a slightly-simpler trigger approach that you can use. After a query is executed, Oracle Forms stores the text of the query in the built-in Forms variable: SYSTEM.LAST_QUERY. So, you could try using a POST-SELECT trigger to capture this (string) value and pass that entire string, or maybe just the "where" clause portion of the query to your autonomous procedure. I've never tested this though to see if the POST-SELECT trigger on the current query contains the current LAST_QUERY text, or the previous query.
I recommend that you write a stored PL\SQL procedure in the database (or a procedure in a database package) that is an "AUTONOMOUS TRANSACTIN" that includes a "commit" and accepts the values you need to capture as input parameters. Then you can call this from your key-execute-query triggers, and just pass it the values from the form that need to be saved.
There may be a slightly-simpler trigger approach that you can use. After a query is executed, Oracle Forms stores the text of the query in the built-in Forms variable: SYSTEM.LAST_QUERY. So, you could try using a POST-SELECT trigger to capture this (string) value and pass that entire string, or maybe just the "where" clause portion of the query to your autonomous procedure. I've never tested this though to see if the POST-SELECT trigger on the current query contains the current LAST_QUERY text, or the previous query.
ASKER
thanks very much i will start working on this, and not closing the question as I may need some more help while
working on the form but the information is very useful thanks.
working on the form but the information is very useful thanks.
ASKER
Hi can you please post the procedure to capture the audit information on Emp table, say empno, and the dates when it is searched as you mentioned above.
thanks very much.
thanks very much.
No, I don't have your table definitions or your form details, so I can't write the exact procedure you need. I described earlier a basic approach that you could use for this that includes a custom audit table and a PL\SQL procedure that inserts records into this table that you can call from a key-execute-query trigger on each form block.
ASKER
when trying to to use Key_EXEQuery the system.last_query is not showing up i just tried to show the messages from the front end. nothing is coming up. also update and delete are not showing up in last_query the result when updated in last_query is
Select row_id,empno from emp where empno=1001. i want to capture the update and insert transactions. how can I do please advise.thanks.
declare
lst_q varchar2(4000);
client_q varchar2(4000);
begin
lst_q := :system.last_query;
message('lst_q' ||lst_q);
client_q := SUBSTR(lst_q,(INSTR(lst_q,
message('client_q' ||client_q);
message('client_q' ||client_q);
end;
Select row_id,empno from emp where empno=1001. i want to capture the update and insert transactions. how can I do please advise.thanks.
declare
lst_q varchar2(4000);
client_q varchar2(4000);
begin
lst_q := :system.last_query;
message('lst_q' ||lst_q);
client_q := SUBSTR(lst_q,(INSTR(lst_q,
message('client_q' ||client_q);
message('client_q' ||client_q);
end;
The "system.last_query" variable in Oracle Forms only contains the text of the last query executed in the block. This does *NOT* include: insert, update or delete statements done in the block. And, I'm not sure exactly when "system.last_query" gets populated in relation to the key-exequery trigger (it may be only after that one fires).
Insert, update and/or delete statements can be captured by database auditing and/or database triggers. You don't need logic in Oracle Forms to capture those actions. And, Oracle Forms does not keep the actual SQL statements that it uses for inserts, updates and deletes (at least not in any place that is accessible to users or developers).
Queries can be captured via logic in the form (and not by database triggers). The "system.last_query" variable contains that information.
I would change your trigger (or program unit) to test this, like this:
declare
client_q varchar2(4000);
begin
message('last query: '||:system.last_query);
client_q := SUBSTR(:system.last_query,
message('client_q' ||client_q);
message(' ');
end;
Insert, update and/or delete statements can be captured by database auditing and/or database triggers. You don't need logic in Oracle Forms to capture those actions. And, Oracle Forms does not keep the actual SQL statements that it uses for inserts, updates and deletes (at least not in any place that is accessible to users or developers).
Queries can be captured via logic in the form (and not by database triggers). The "system.last_query" variable contains that information.
I would change your trigger (or program unit) to test this, like this:
declare
client_q varchar2(4000);
begin
message('last query: '||:system.last_query);
client_q := SUBSTR(:system.last_query,
message('client_q' ||client_q);
message(' ');
end;
ASKER
Is the database triggers for each row is good option, or it will reduce the performance of the applciation
ASKER
can we use system.record_status to capture the update,insert delete of the record in oracle forms
Any auditing option that you add is likely to reduce the performance of the application. But, if your organization has decided that auditing these actions is important, they have to be prepared for the additional cost of this auditing.
"can we use system.record_status to capture the update,insert delete of the record in oracle forms?"
No. That variable does not contain the level of detail that you are looking for. As I said above:
Oracle Forms does not keep the actual SQL statements that it uses for inserts, updates and deletes (at least not in any place that is accessible to users or developers).
"can we use system.record_status to capture the update,insert delete of the record in oracle forms?"
No. That variable does not contain the level of detail that you are looking for. As I said above:
Oracle Forms does not keep the actual SQL statements that it uses for inserts, updates and deletes (at least not in any place that is accessible to users or developers).
ASKER
thanks I am trying to use the above information, how can I
get the number after the "=" operator. I am trying to extract the only the empno from search criteria of last_query.
where empno='18'
how can I get the 18 number thanks.
get the number after the "=" operator. I am trying to extract the only the empno from search criteria of last_query.
where empno='18'
how can I get the 18 number thanks.
I think using a "regular expression" would be the simplest way to extract the numeric digits from a string of characters that includes numeric digits, but I am not an expert on using "regular expressions".
This could certainly be done with a combination of "instr" and "substr".
But, is the problem as simple and as consistent as this? That is, is this the *ONLY* field in the block that the users can enter search criteria into? And, will users never try searching like these examples:
where empno>'17'
where empno<>'19'
where empno between '10 ' and '20'
I often do queries like these examples in Oracle Forms.
This could certainly be done with a combination of "instr" and "substr".
But, is the problem as simple and as consistent as this? That is, is this the *ONLY* field in the block that the users can enter search criteria into? And, will users never try searching like these examples:
where empno>'17'
where empno<>'19'
where empno between '10 ' and '20'
I often do queries like these examples in Oracle Forms.
ASKER
no it is always only one client = i tried with regular expression
my query returns as follows after where.
WHERE(APP_100_EMPLOYEE,EMP
my query returns as follows after where.
WHERE(APP_100_EMPLOYEE,EMP
You can use "instr" twice to get the positions of the "=" and ")" characters in that string, then use "substr" something like this:
create or replace function get_id (v_text in varchar2) return number as
v_start number(2,0);
v_end number(2,0);
v_long number(2,0);
begin
v_start := instr(v_text,'=') +2;
v_end := instr(v_text,')') -1;
v_long := v_end - v_start;
return to_number(substr(v_text,v_
exception
when others then
return 0;
end ;
create or replace function get_id (v_text in varchar2) return number as
v_start number(2,0);
v_end number(2,0);
v_long number(2,0);
begin
v_start := instr(v_text,'=') +2;
v_end := instr(v_text,')') -1;
v_long := v_end - v_start;
return to_number(substr(v_text,v_
exception
when others then
return 0;
end ;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
thanks for the answers. in a trigger how can Iget the value
for eg. when i insert,update,delete i wrote trigger to capture the old and new value, but I also
want to capture the Employee ID (even if it is not changed) in my audit table.
so i can genereate a report which employee ID row is changed,
can you please advise. I wrote a procedure to insert into audit table.
create or replace package audit_pkg
as
procedure check_val(l_tname in varchar2,
l_cname in varchar2,
l_new in varchar2,
l_old in varchar2 ,l_action in varchar2, CLIENT_ID IN NUMBER);
procedure check_val(l_tname in varchar2,
l_cname in varchar2,
l_new in date,
l_old in date,l_action in varchar2, CLIENT_ID IN NUMBER);
procedure check_val( l_tname in varchar2,
l_cname in varchar2,
l_new in number,
l_old in number ,
l_action in varchar2 ,CLIENT_ID IN NUMBER);
end;
create or replace package body audit_pkg
as
procedure check_val(l_tname in varchar2,
l_cname in varchar2,
l_new in varchar2,
l_old in varchar2,l_action in varchar2)
is
begin
if ( l_new <> l_old or
(l_new is null and l_old is not NULL) or
(l_new is not null and l_old is NULL) )
then
insert into audit_tbl values
( sysdate, user, upper(l_tname), upper(l_cname),
l_old, l_new,l_action, CLIENT_ID);
end if;
end;
procedure check_val( l_tname in varchar2, l_cname in varchar2,
l_new in date, l_old in date ,l_action in varchar2)
is
begin
if ( l_new <> l_old or
(l_new is null and l_old is not NULL) or
(l_new is not null and l_old is NULL) )
then
insert into audit_tbl values
( sysdate, user, upper(l_tname), upper(l_cname),
to_char( l_old, 'dd-mon-yyyy hh24:mi:ss' ),
to_char( l_new, 'dd-mon-yyyy hh24:mi:ss' ),l_action, CLIENT_ID );
end if;
end;
procedure check_val( l_tname in varchar2, l_cname in varchar2,
l_new in number, l_old in number ,l_action in varchar2 ,CLIENT_ID IN NUMBER)
is
begin
if ( l_new <> l_old or
(l_new is null and l_old is not NULL) or
(l_new is not null and l_old is NULL) )
then
insert into audit_tbl values
( sysdate, user, upper(l_tname), upper(l_cname),
l_old, l_new ,l_action, CLIENT_ID);
end if;
end;
end audit_pkg;
thanks for the answers. in a trigger how can Iget the value
for eg. when i insert,update,delete i wrote trigger to capture the old and new value, but I also
want to capture the Employee ID (even if it is not changed) in my audit table.
so i can genereate a report which employee ID row is changed,
can you please advise. I wrote a procedure to insert into audit table.
create or replace package audit_pkg
as
procedure check_val(l_tname in varchar2,
l_cname in varchar2,
l_new in varchar2,
l_old in varchar2 ,l_action in varchar2, CLIENT_ID IN NUMBER);
procedure check_val(l_tname in varchar2,
l_cname in varchar2,
l_new in date,
l_old in date,l_action in varchar2, CLIENT_ID IN NUMBER);
procedure check_val( l_tname in varchar2,
l_cname in varchar2,
l_new in number,
l_old in number ,
l_action in varchar2 ,CLIENT_ID IN NUMBER);
end;
create or replace package body audit_pkg
as
procedure check_val(l_tname in varchar2,
l_cname in varchar2,
l_new in varchar2,
l_old in varchar2,l_action in varchar2)
is
begin
if ( l_new <> l_old or
(l_new is null and l_old is not NULL) or
(l_new is not null and l_old is NULL) )
then
insert into audit_tbl values
( sysdate, user, upper(l_tname), upper(l_cname),
l_old, l_new,l_action, CLIENT_ID);
end if;
end;
procedure check_val( l_tname in varchar2, l_cname in varchar2,
l_new in date, l_old in date ,l_action in varchar2)
is
begin
if ( l_new <> l_old or
(l_new is null and l_old is not NULL) or
(l_new is not null and l_old is NULL) )
then
insert into audit_tbl values
( sysdate, user, upper(l_tname), upper(l_cname),
to_char( l_old, 'dd-mon-yyyy hh24:mi:ss' ),
to_char( l_new, 'dd-mon-yyyy hh24:mi:ss' ),l_action, CLIENT_ID );
end if;
end;
procedure check_val( l_tname in varchar2, l_cname in varchar2,
l_new in number, l_old in number ,l_action in varchar2 ,CLIENT_ID IN NUMBER)
is
begin
if ( l_new <> l_old or
(l_new is null and l_old is not NULL) or
(l_new is not null and l_old is NULL) )
then
insert into audit_tbl values
( sysdate, user, upper(l_tname), upper(l_cname),
l_old, l_new ,l_action, CLIENT_ID);
end if;
end;
end audit_pkg;
This is a somewhat-complex package with an over-loaded procedure to accept different combinations of datatypes for the l_new and l_old parameters. This should work for you. But, is this the simplest and most-efficient solution to the problem? Maybe, but maybe not.
We used a different technique here to capture audit data for some key tables that we needed audit information kept on. We were concerned about capturing audit information whenever someone changed or deleted information. We didn't need to be concerned about adding information, because our application already recorded the name and timestamp for new records, so we didn't need any special auditing for insert activity. And, our forms did not allow users to actually delete records from these tables, but they could change some columns to blank values, so we wanted to detect this.
We created a history table for each of our key tables with almost the same column structure as the base table, but with all of the columns, except the primary key column(s) and the audit columns, allowed to be null. Our base tables already included audit columns for "modified_by" to capture the user login name, and "date_modified" to capture the time stamp. We then created an "after update for each row" database trigger that created a "sparse" history record whenever a record was changed. This trigger copied the primary key value(s) and the :old.audit_column values (modified by and date_modified) plus the :old.column value only for those columns that were actually changed. We used a "decode" on each column to determine this. I'll post a sample of one of those triggers in an attached file here. This gave us concise, and accurate history records of any/all changes without wasting space to store values that did not change.
ITEM-MASTER-AU-ROW.sql
We used a different technique here to capture audit data for some key tables that we needed audit information kept on. We were concerned about capturing audit information whenever someone changed or deleted information. We didn't need to be concerned about adding information, because our application already recorded the name and timestamp for new records, so we didn't need any special auditing for insert activity. And, our forms did not allow users to actually delete records from these tables, but they could change some columns to blank values, so we wanted to detect this.
We created a history table for each of our key tables with almost the same column structure as the base table, but with all of the columns, except the primary key column(s) and the audit columns, allowed to be null. Our base tables already included audit columns for "modified_by" to capture the user login name, and "date_modified" to capture the time stamp. We then created an "after update for each row" database trigger that created a "sparse" history record whenever a record was changed. This trigger copied the primary key value(s) and the :old.audit_column values (modified by and date_modified) plus the :old.column value only for those columns that were actually changed. We used a "decode" on each column to determine this. I'll post a sample of one of those triggers in an attached file here. This gave us concise, and accurate history records of any/all changes without wasting space to store values that did not change.
ITEM-MASTER-AU-ROW.sql
ASKER
It is useful thanks.
Also, is this a new application that you can set audit options for, or is this an existing application that you are hoping to be able to provide audit reports on from activity in the past?