In OLTP applications, data gets created and updated. An audit trail is often required to know who created a record and who updated it. A "SELECT NVL(user, SYS.login_user) FROM DUAL" query shows the database login account. The function below is helpful when the database login is not the same as the network/OS login, which may occur in applications that make JDBC/ODBC calls to the Oracle database. This function returns the network/OS login account username. It should work on Oracle 7.xx and higher and has been tested on version 188.8.131.52.
CREATE OR REPLACE FUNCTION nw_os_username RETURN VARCHAR AS v_user VARCHAR2(50); BEGIN SELECT UPPER(osuser) INTO v_user FROM v$session WHERE audsid = USERENV('sessionid'); RETURN(v_user); END nw_os_username; CREATE TABLE tmp_test (col1 NUMBER PRIMARY KEY, col3 NUMBER, created_by VARCHAR2(50), created_date DATE DEFAULT SYSDATE, updated_by VARCHAR2(50), updated_date DATE ); CREATE OR REPLACE TRIGGER trig_created_by BEFORE INSERT ON tmp_test FOR EACH ROW BEGIN :new.created_by := nw_os_username; END; CREATE OR REPLACE TRIGGER trig_updated_by BEFORE UPDATE ON tmp_test FOR EACH ROW BEGIN :new.updated_by := nw_os_username; :new.updated_date := SYSDATE; END; INSERT INTO tmp_test (col1) VALUES(1); COMMIT; UPDATE tmp_test SET col1 = 1; COMMIT;
Here is an example result:
SQL> SELECT * FROM tmp_test; COL1 CREATED_D CREATED_BY UPDATED_D UPDATED_BY ---------- --------- ----------- --------- ---------- 1 20-OCT-02 ABCDEF01 20-OCT-02 GHIJKL01
Melville P. writes: When I execute the following statement against an Oracle workgroup server (v.7.3.4):
select sid, osuser, user#, username, status, machine, terminal from v$session
the output returned for "osuser" is "OraUser", not the actual Novell Netware (v.6.0) login name.
Also, the "terminal" just returns "Windows PC" for all Win95 and Win98 workstations; and the "machine" for most Win95 and Win98 workstations is blank. Any more suggestions would be appreciated.
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free Oracle tips and scripts.
- Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
- Best Web Links: Oracle tips, tutorials, and scripts from around the Web.