Problem solve Get help with specific problems with your technologies, process and projects.

Retrieving OS/network username for audit trail

This tip is helpful in logging situations where the database login is not the same as the network/OS login.

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 8.1.7.3.

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

Reader Feedback

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.

Dig Deeper on Oracle database security

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close