Hi All,

I have a requirement described below.

Requirement:

---

We want to audit all DML operations done on a table by the application users. There are several application users who do the DML and it is required to track which application user did what DML operation.

When connection is made to the database, the schema to which all the application users connect is the one and the same – i.e. we use SCOTT schema credentials for establishing DB connection for all different application users.

Therefore the detail of which application user is performing the DML is not available for oracle. It is available in the application code. Our primary requirement is that we need to have the detail of the application user doing the transaction to be passed into the database so that we can audit the DML operations done.

To take an example:

Let’s say that the table to be audited is EMP which is in SCOTT schema:

SQL> desc EMP
Name Null? Type

---

---

---

EMPNO NOT NULL NUMBER (4)
ENAME VARCHAR2 (10)
JOB VARCHAR2 (9)
MGR NUMBER (4)
HIREDATE DATE
SAL NUMBER (7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

Let’s say that there are 4 application users; user1, user2, user3 & user4. Now when they connect to the database, the connection is to schema SCOTT. So all the 4 application users will connect to the same db schema ‘Scott’. The app users will do some transactions; they may roll it back etc. And we need to track that. – The tricky part being that we need to track which application user has done the DML.

We are planning to track the Edit Audits through Triggers. Before the actual Data record Insert into the EMP table the trigger should insert the Record into the Audit Table, along with the UserID.

We have distributed transactions (under the COM+ Environment)

I am unable to figure out how to meet this requirement...shall be very thankful for any help/suggestions.

Thanks,
Nirav

Please have a look to proxy authentication and authorization and auditing in a multitier environment .

Hi Nirav,

I have these ready-to-use scripts for DDL auditing:

http://www.dba-oracle.com/art_builder_proper_oracle_design_for_auditing.htm

connect sys/manager;

drop table perfstat.stats$ddl_log;

create table
perfstat.stats$ddl_log

(
user_name varchar2(30),
ddl_date date,
ddl_type varchar2(30),
object_type varchar2(18),
owner varchar2(30),
object_name varchar2(128)

)
tablespace perfstat;

The DDL trigger executes every time a DDL statement is executed, and adds new entries to your new table, as shown below:

connect sys/manager

create or replace trigger
DDLTrigger
AFTER DDL ON DATABASE
BEGIN
insert into
perfstat.stats$ddl_log
(
user_name,
ddl_date,
ddl_type,
object_type,
owner,
object_name
)
VALUES
(
ora_login_user,
sysdate,
ora_sysevent,
ora_dict_obj_type,
ora_dict_obj_owner,
ora_dict_obj_name
);

END;
/

Hope this helps. . .

Don Burleson
Oracle Press author

you can do that by adding to the table you want to audit the pc name and the ip address and the OS user and you can get them through sys_context.