Re: sys_context , view and session

On 6 Jan 2005 13:48:02 -0800, francois.bourdages_at_harfan.com (Fran?ois Bourdages) wrote:

>Hi
>What i need is for the user of the application to see only part of the
>record in a table. so view will be use. but i need to context my view
>so a user X
>see data tha only him can see. (he cannot see any others record)
>
>here is what i have done :
>some info :
> SQL*Plus: Release 8.1.7.0.0 - Production on Thu Jan 6 16:44:54 2005
> (c) Copyright 2000 Oracle Corporation. All rights reserved.
> Connected to:
> Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
> JServer Release 8.1.7.4.1 - Production
> client and serveur on same machine : windows 2000
>--------------------------------------------------------
>SQL> desc call;
> Name
> -------------------------------------------
>CALL_ID
> CALL_HISTOFLAG
> CALL_REFCALLID
> CALL_CLIENT_ID
> CALL_LST_QUEUE
> CALL_LST_CALLCAT
> ...
>SQL> select CALL_ID from call where CALL_CLIENT_ID = 0000097;
>
> CALL_ID
>----------
> 188
>
>--------------------------------------------------------
>1) i create this package :
>CREATE OR REPLACE PACKAGE PKG_CTX_FILE
>authid current_user
>AS
>PROCEDURE SET_CTX( P_NAME IN VARCHAR2, P_VAL IN VARCHAR2 );
>FUNCTION GET_CTX( P_NAME IN VARCHAR2 ) RETURN VARCHAR2;
>END PKG_CTX_FILE;
>/
>
>
>CREATE OR REPLACE PACKAGE BODY PKG_CTX_FILE
>AS
>L_CTX_NAME VARCHAR2(30) DEFAULT 'MY_CTX_' || SYS_CONTEXT( 'USERENV',
>'CURRENT_SCHEMAID');
>
>PROCEDURE SET_CTX( P_NAME IN VARCHAR2, P_VAL IN VARCHAR2 )
>IS
>BEGIN
> DBMS_SESSION.SET_CONTEXT( L_CTX_NAME, P_NAME, P_VAL );
>END;
>FUNCTION GET_CTX( P_NAME IN VARCHAR2 ) RETURN VARCHAR2
>IS
>BEGIN
> RETURN 'SYS_CONTEXT( ''' || L_CTX_NAME || ''', ''' || P_NAME || '''
>)';
>END;
>END PKG_CTX_FILE;
>/
>
>2) i created this view :
>CREATE OR REPLACE VIEW V_CALL
>AS
>SELECT CALL_ID
> ,CALL_HISTOFLAG
> ,CALL_REFCALLID
> ,CALL_CLIENT_ID
> ,CALL_LST_QUEUE
> ,CALL_LST_CALLCAT
> FROM CALL
> WHERE CALL_CLIENT_ID = PKG_CTX_FILE.GET_CTX('CLIENT_ID');
>3) i created this context :
>declare
> userId number;
>begin
> userId:= SYS_CONTEXT( 'USERENV', 'CURRENT_SCHEMAID');
> execute immediate 'CREATE OR REPLACE CONTEXT my_ctx_'||userId||'
>USING PKG_CTX_FILE';
>end;
>/
>
>4) i execute this OK :
>SQL> EXEC PKG_CTX_FILE.SET_CTX( 'CLIENT_ID', '0000097' );
>
>PL/SQL procedure successfully completed.
>
>5) when i do this test , return data OK :
>SET SERVEROUTPUT ON;
>DECLARE
> clientID VARCHAR2(100);
>BEGIN
> clientID:=PKG_CTX_FILE.GET_CTX('CLIENT_ID');
> DBMS_OUTPUT.PUT_LINE('clientID='||clientID);
>END;
>/
>-- data return :
>clientID=SYS_CONTEXT( 'MY_CTX_24', 'CLIENT_ID' )
>
>PL/SQL procedure successfully completed.
>
>6) but when i select the view : no data :
>SQL> SELECT CALL_ID FROM V_CALL;
>
>no rows selected
>
>==> can you explain me why ?
>==> where is the error ?

It looks very much like you are trying to re-invent Fine Grained Access Control, already present in the Enterprise edition.

Also I'm not sure why you are trying to create a new context. You are aware 'userenv' actually already is a context? IMO, you could drop the package and directly use sys_context('userenv', ....) in the where clause of your view.

Hth

--
Sybrand Bakker, Senior Oracle DBA