Home » RDBMS Server » Server Administration » Trigger to get User (Actual) details
| Trigger to get User (Actual) details [message #163092] |
Tue, 14 March 2006 21:55  |
balasmg
Messages: 31
Registered: June 2005
Location: bangalore
|
Member |
|
|
Hi,
Our application behave such that, we have to mention schema username and password before build the application, hence we are having a common schema for all developer.
pl. note this constraint
(EXAMPLE schema detail as : USERNAME / PASSWORD : DEV/DEV)
I need to track who is doing modification as INSERT/UPDATE/DELETE in some important tables.
I have created column in each such table with created_user, created_date, modified_user, modified_date.
i have written trigger as
CREATE OR REPLACE TRIGGER bi_bu_emp
BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW
BEGIN
IF INSERTING THEN
SELECT USER, SYSDATE INTO :NEW.created_user, :NEW.created_time FROM DUAL;
ELSIF UPDATING THEN
SELECT USER, SYSDATE INTO :NEW.modified_user, :NEW.modified_time FROM DUAL;
END IF;
END;
I AM NOT ABLE TO TRACE WHOSE HAS PARTICULARY DONE THE MODIFICATION, AS ALL THE DEVLOPER ARE USING
SAME SCHEMA, AND "USER" FUNCTION WILL RETURN RESULT AS DEV.
I NEED TO KNOW WHICH USER HAS DONE THIS. IS THER ANY WAY TO GET THE USER INFORMATION.
IN OTHER SITUVATION. TO KILL PARTICULAR SESSION WHICH HANGS UP, I AM USING V$SESSION TABLE, WHICH HAS COLUMN "MACHINE" WHICH IDENTIFIES, WHICH USER SESSION NEED TO BE KILLED. IS THERE ANYWAY TO USE THIS OR ANY OTHER OPTION TO TRACK THE USER.
PL. LET ME KNOW.
Our application details:
Database details: oracle 9i
front end : java.
|
|
|
|
| Re: Trigger to get User (Actual) details [message #163215 is a reply to message #163092] |
Wed, 15 March 2006 09:39  |
wpaillan
Messages: 35
Registered: March 2005
Location: Santiago - Chile
|
Member |
|
|
hi
1).- First of all, I have observations in relation to your Trigger :
CREATE OR REPLACE TRIGGER bi_bu_emp
BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW
BEGIN
/* BECAUSE WORKS ONLY INSERT AND UPDATE */
:NEW.created_user := USER;
:NEW.created_time := SYSDATE;
END;
2).- YOU SHOULD CREATE ONE table FOR RECORDS OF LOG auditory
a.- identical tablae to the Original, + Comentario's Field Auditory
b.- adding a Trigger to the Original table, that you audit ( Insert, Updating, Delete ), that next the record insert the new Audited table
***************
is only example
It is necessary to make your case suitable
---------------------------------------------
CREATE OR REPLACE TRIGGER M4TG_log_table_name
BEFORE INSERT OR UPDATE OR DELETE
ON M4T_table_name
FOR EACH ROW
DECLARE
VCOMENT VARCHAR(1000):= NULL;
BEGIN
/* Solo Cuendo se Inserta Registro */
IF INSERTING THEN
INSERT INTO M4T_tabla_name_LOG (field's,FEC_INICIO,FEC_FIN,TIPO_LOG,COMENT_LOG)
VALUES (:NEW.field's,:NEW.FEC_INICIO,:NEW.FEC_FIN,'Insert','Digitacion');
END IF;
/* Solo Cuendo se Actualiza un dato */
IF UPDATING AND (:OLD.FEC_INICIO != :NEW.FEC_INICIO
or :OLD.FEC_FIN != :NEW.FEC_FIN ) THEN
VCOMENT := NULL;
IF :OLD.FEC_INICIO != :NEW.FEC_INICIO THEN
VCOMENT := VCOMENT || ' -Se Movio ** Fecha Inicio ** '||to_char(:old.FEC_INICIO,'dd/mm/yyyy')||' to '||to_char(:new.FEC_INICIO,'dd/mm/yyyy');
END IF;
IF :OLD.FEC_FIN != :NEW.FEC_FIN THEN
VCOMENT := VCOMENT || ' -Se Movio ** Fecha FIN ** '||to_char(:old.FEC_FIN,'dd/mm/yyyy')||' to '||to_char(:new.FEC_FIN,'dd/mm/yyyy');
END IF;
INSERT INTO M4T_table_name_LOG (field's,FEC_INICIO,FEC_FIN,TIPO_LOG,COMENT_LOG)
VALUES (:NEW.field's,:NEW.FEC_INICIO,:NEW.FEC_FIN,'delete',vcoment);
END IF;
/* Solo cuando se Borra el Registro */
IF DELETING THEN
INSERT INTO M4T_table_name_LOG (field's,FEC_INICIO,FEC_FIN,TIPO_LOG,COMENT_LOG)
VALUES (:old.field's,:old.FEC_INICIO,:old.FEC_FIN,'delete','delete');
END IF;
END;
/
fin Attached example
---------------
|
|
|
|
| Re: Trigger to get User (Actual) details [message #163284 is a reply to message #163215] |
Wed, 15 March 2006 23:26 |
balasmg
Messages: 31
Registered: June 2005
Location: bangalore
|
Member |
|
|
tks for response.
what about by using session details as like below
CREATE OR REPLACE TRIGGER bi_bu_emp
BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW
BEGIN
IF INSERTING THEN
SELECT SYSDATE INTO :NEW.created_date FROM DUAL;
SELECT substR(machine,1,40) INTO :NEW.created_user FROM v$session WHERE sid = (SELECT sid FROM v$mystat WHERE rownum = 1);
ELSIF UPDATING THEN
SELECT SYSDATE INTO :NEW.modified_date FROM DUAL;
SELECT machine INTO :NEW.modified_user FROM v$session WHERE sid = (SELECT sid FROM v$mystat WHERE rownum = 1);
END IF;
END;
i granted the dba role to the dev schema.
I am getting result when i run the statement in sqlplus,
SELECT substR(machine,1,40) FROM v$session WHERE sid = (SELECT sid FROM v$mystat WHERE rownum = 1);
but when i run the same in trigger it give error as
-------- ---------------------------------------------------------------
3/1 PL/SQL: SQL Statement ignored
3/94 PL/SQL: ORA-00942: table or view does not exist
pl. let me know what need to be done
but i am getting error
|
|
|
|
| Re: Trigger to get User (Actual) details [message #163362 is a reply to message #163284] |
Thu, 16 March 2006 07:25 |
wpaillan
Messages: 35
Registered: March 2005
Location: Santiago - Chile
|
Member |
|
|
HI
OBJECTS v$session, v$mystat IS PUBLIC SYNONYM
THE OWNER SYS,
USER SYS, AUTHORIZE TO READ (gRANT SELECT) FOR THE Public synonym,
To A ROLE FOR THE USER CONNECTION
BUT A THEME COMES BASICALLY from ROLES And PERMISSIONS
|
|
|
|
|
|
| Re: Trigger to get User (Actual) details [message #163479 is a reply to message #163370] |
Thu, 16 March 2006 20:43 |
balasmg
Messages: 31
Registered: June 2005
Location: bangalore
|
Member |
|
|
Its working fine in sql* plus in my machine. (os : windows installed with Oracle Client software )
if i connected th' using telnet to the Db server and using sqlplus i am not able to get the ip_address and even
sys_context('userenv','os_user') will also only give login of osuser. but i need the machine or even from ip address from where he access. some thing similar to getting machine name in session table.
pl. let me know is there any way to acheive this task
|
|
|
|
Goto Forum:
Current Time: Fri Sep 20 13:41:10 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
