CODEWITHSUNDEEP
sqloracle-databaseplsql
Kishore Jv
Kishore Jv

Reputation: 159

auditing the user logging information in oracle using trigger

I have created the following trigger, but I would need to get the username and the dml operation he performed that has logged into application instead of the user that is currently logged in the DB

CREATE OR REPLACE TRIGGER orders_after_delete
AFTER update or delete
ON orders

FOR EACH ROW
DECLARE

v_username varchar2(10);

BEGIN

SELECT user INTO v_username
FROM dual;

INSERT INTO orders_audit
VALUES
( :old.order_id,
:old.quantity,
:old.cost_per_item,
:old.total_cost,
sysdate,
v_username,
:new.total_cost );

END;

With this questy, I get the username of the user logged in the DB. I would like to have the user logged in my user application instead.

Upvotes: 0

Views: 803

Answers (2)

Cyryl1972
Cyryl1972

Reputation: 624

To audit users, use the proper Oracle audit feature:

https://docs.oracle.com/cd/E11882_01/server.112/e10575/tdpsg_auditing.htm#TDPSG50000

Much better than triggers.

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

Before the INSERT statement, put a check on the username using an IF-ELSE construct. One way to differentiate application user from a schema owner is to validate the roles granted to the user.

You can query the username, process, program from v$session to check whether the user is logged in through application.

Upvotes: 0

Related Questions