Thursday, January 1, 2009

Capturing machine name for users in oracle

Create the fields

CREAT_DATE DATE;
CREAT_USER VARCHAR2(25);
MODI_DATE DATE;
MODI_USER VARCHAR2(25);

in tables.

Use this follwing code in every table row level
trigger.

DECLARE
VDATE DATE;
vUSER VARCHAR2(20);
BEGIN
SELECT SYSDATE , audit_user(USERENV('SESSIONID'))
INTO VDATE, VUSER FROM DUAL;
IF INSERTING THEN
:NEW.CREAT_DATE :=VDATE;
:NEW.CREAT_USER :=VUSER;
:NEW.MODI_DATE :=VDATE;
:NEW.MODI_USER :=W_USER;
END IF;

IF UPDATING THEN
:NEW.MODI_DATE:=VDATE;
:NEW.MODI_USER:=VUSER;
END IF;

END;

Then create the function in a common tablespace. and
grant the rights to execute this function to all
users.

FUNCTION AUDIT_USER(SESS_ID IN NUMBER ) RETURN CHAR IS

MACHINE_NAME CHAR(2);
USER_INFO varchar2(24);

BEGIN
SELECT SUBSTR(MACHINE,1,10)
INTO MACHINE_NAME
FROM AUDIT_SESSION
WHERE AUDSID = SESS_ID;

SELECT MACHINE_NAME ||'-' ||TO_CHAR(USER#)
||SUBSTR(OSUSER,1,5)||'-'||TO_CHAR(SYSDATE,'HH24MI')
INTO USER_INFO
FROM AUDIT_SESSION
WHERE AUDSID = SESS_ID;
/* FROM STRING COMPRISES OF
========================
1. FIRST MACHINE NAME REF.
2. FROM DIGIT ELEVANE TO THIRTEEN - USER NO REF.
DBA_USERS
3. FROM FORTEEN TO EIGHTEEN - OPERATING SYSTEM USER
NAME
4. FROM NINTEEN TO TWENTY TWO - TIME IN HH24MI FORMAT
*/
RETURN(USER_INFO,1,24);
END;

No comments:

Post a Comment