Re: SYS_CONTEXT('userenv','sessionid') always returns zero

In article <f6cef816.0202221703.71a821e0_at_posting.google.com>, goodday_at_webmail.hmd.co.kr says...
>
>Hi!
>I made 'AFTER ON LOGON DATABASE' trigger extract user's logon program
>in v$session like below,
>
>//------------------
>create or replace TRIGGER after_logon_audit
>after logon on database
>WHEN ( user in ('SCOTT', 'SCOTT2'))
>declare
> err_num number;
> err_msg varchar2(100);
>begin
>
> insert into logon_audit
> select sysdate, sys_context('userenv', 'ip_address'),
>sys_context('userenv', 'session_user'), a.osuser, a.machine,
>a.terminal, a.program,
> sys_context('userenv', 'sessionid'), sys_context('userenv',
>'session_userid')
> from v$session a, dual
> where sys_context('userenv', 'sessionid') = a.audsid and
> sys_context('userenv', 'session_userid') = a.user#;
>
> commit;
>
>Exception
> when Others then
> err_num := SQLCODE;
> err_msg := substr(SQLERRM, 1, 100);
> insert into logon_audit_err values( sysdate, err_num, err_msg );
>end;
>//----------------------------------
>
>Of course, trigger owner is scott and scott has a select privilege on
>v_$session and adminster database trigger privilege.
>Above trigger was compiled without error and is valid now.
>But, nothing was inserted to logon_audit table after logon by user
>scott or scott2.
>After all, the reason was that sys_context('userenv', 'sessionid')
>always returns zero at trigger execution time. But,
>sys_context('userenv', 'sessionid') returns correct value(same value
>as audsid of v$session) in sql*plus after logon.
>So, can't extract user logon program from v$session.
>How can i solve this problem? Please give me your hand...

simplest answer:

o use the native auditing features which will give you all of the information above with the minimal of overhead and zero lines of procedural code. This would be the recommended approach. enable auditing, audit connect.

The harder answer since it implies writing code that must be maintained forever follows.

You don't say what version you are using but using deduction I can guess 815. I know it cannot be before 815 since this trigger was introduced in that release. I know its not 816 or above since the sys_context function there returns the audsid of the user connecting. In 815 it returns 0 which is the audsid of the "database" if you will.

You can use a query against v$session that uses the predicate:

....

     where sid = ( select sid from v$mystat where rownum = 1 );

that'll get your sessions row from v$session.

I would STRONGLY encourage you to upgrade to 817 however -- there are some known issues with regards to logon triggers that can make it pretty hard to log on to the database. If you compile an invalid logon trigger -- you may make it not possible to create a new session (make sure to DROP that trigger right after it fails compilation). These issues are solved in 816 and 817.

Actually -- I guess I would strongly encourage you to not re-invent the already built wheel and just use the auditing facilities within the database.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp