Re: sys_context('USERENV','SESSIONID') always Zero

On 3 Sep 2004 09:52:44 -0700, power_ian_at_hotmail.com (IPower) wrote:

>I am an oracle newbie, I am looking for information on a current
>problem I am coming across. I am trying to implement fga using a
>procedure to populate a user-defined table to track audit information.
>In this procedure I am trying to use
>sys_context('USERENV','SESSIONID'), to retrieve the current session
>id. When I view the data the audit session id is always zero. Why? I
>am using a 10g oracle database. Here is my code:
>begin
> dbms_fga.add_policy(
> object_schema=>'TEST',
> object_name=>'DIARY',
> policy_name=>'DIARY_ACCESS',
> handler_schema => 'TEST',
> handler_module => 'LOGERROR',
> statement_types => 'SELECT, INSERT, DELETE, UPDATE',
> enable => true
>);
>end;
>
>CREATE OR REPLACE PROCEDURE LOGERROR(V_SCHEMA VARCHAR2,
>V_TABLE VARCHAR2,
>V_POLICY VARCHAR2) AS
>
>BEGIN
>--insert into test1 table
>INSERT INTO test1
>(
>audit_session_id,
>timestamp,
>owner,
>object_name,
>policy_name
>)
>VALUES
>(
>sys_context('USERENV','SESSIONID'),
>sysdate,
>v_schema,
>v_table,
>v_policy);
>END;
>
>Went I select * from diary and check my table auditsessionId is
>returned as zero. However when I select
>sys_context('USERENV','SESSIONID') from dual I receive the correct
>sessionId. If possible can someone explain why my audit_session_Id is
>always zero in the test1 table.

Audit session id is always 0 when you are connected as SYS or with SYSDBA privilege.
You seem to have created the function in the SYS schema. You should *NEVER EVER* do that, as the SYS schema is the dictionary, and the dictionary is not exported. This is why Oracle provides there own code in catproc.sql.

Create the function in a different schema (preferably not SYSTEM) and make sure it is has AUTHID CURRENT_USER before the AS

--
Sybrand Bakker, Senior Oracle DBA