sys_context , view and session

From: Fran?ois Bourdages <francois.bourdages_at_harfan.com>
Date: 6 Jan 2005 13:48:02 -0800
Message-ID: <92e05f1e.0501061348.46097bdb@posting.google.com>

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 ? Received on Thu Jan 06 2005 - 15:48:02 CST