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

[IPower]

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.

[Sybrand Bakker]

Post by IPower
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
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

[srivenu]

You can select sid from V$MYSTAT where rownum=1 to get the SID.
regards
Srivenu