OS Username from Logon Trigger

[cardy]

How can I capture the Operating System username from an AFTER LOGON ON DATABASE trigger in 8i (8.1.5+)?
I've tried select OSUSER from V$SESSION but it appears not to be populated at the point the trigger is fired.
What methodologies do you DBAs implement for auditing who signs on to the database ?

TIA

Cardy

 

[karluk]

I don't have any practical experience in this type of auditing, but the Oracle documentation says that you should be able to get this information from v$session. The following query should work. Is it similar to what you are using in your trigger?

SELECT osuser, machine
FROM v$session WHERE audsid = userenv( 'sessionid' );

 

[cardy]

This is exactly what I had in my trigger but as I said earlier, the osuser column does not appear to be populated at the point the trigger is fired. This is what I have :-

CREATE OR REPLACE TRIGGER logonauditing
AFTER LOGON ON database
DECLARE
machinename VARCHAR2(64);
osuserid VARCHAR2(30);
CURSOR c1 IS
SELECT osuser, machine
FROM v$session
WHERE audsid = userenv( 'sessionid' );
BEGIN
OPEN c1;
FETCH c1 INTO osuserid, machinename;
INSERT INTO ibo VALUES ( 999 , sysdate , 'LOGON' || '-' || user || '-' || osuserid || '-' || machinename );
CLOSE c1;
COMMIT;
END;
/

The "osuser" comes out as NULL.
Anybody out there manage to capture the osuser with this type of trigger ?

TIA

Cardy

 

[karluk]

That is certainly inconvenient. It looks as if this selection logic is returning information about the background process that is running the trigger, rather than the user who just logged in.

I got your trigger to return an osuser with the following modification to the cursor. Please see if it works for you. Hopefully it's a step in the right direction.

CURSOR c1 IS
SELECT osuser, machine
FROM v$session
WHERE sid=(select distinct sid from v$mystat);