Great example, as always, but I'm curious about one thing. How would you close the session for those users that don't logout but simply close the browser or go to another site? Perhaps a batch job that periodically closes the session of users who haven't been active in x hours?

After creating the "global" context and storing some variables in it, I would like to list all the variables stored in it.

I use the following code to do that:

set serveroutput on
declare
list dbms_session.AppCtxTabTyp; -- type defined in dbms_session pkg
cnt number;
begin
dbms_session.list_context (list, cnt);

dbms_output.put_line('Number of contexts: ' || cnt);

for i in 1..cnt loop
dbms_output.put_line( list(i).namespace || ' ' ||
list(i).attribute || ' = '|| list(i).value );
end loop;
end;
/

If my context is created ACCESSED GLOBALLY, my PL/SQL block will show me nothing.

If my context is created without ACCESSED GLOBALLY, my PL/SQL block will show me the variables stored in my context.

I'm sure there is a reasonable reason for that. Can you explain that to me ?

cannot "explain it", but can show what to do about it.

use the views session_context and global_context.  union all them together if you like.

consider:



ops$tkyte@ORA920> create or replace context App_Ctx using My_pkg
  2  ACCESSED GLOBALLY
  3  /

Context created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> set echo off
Enter to continue

=================================================================


now we'll create the package to use it.  This would have our
application logic to ensure we are who we say we are...

the session id could be anything your middle tier assigns to it.
the session id associated with your servlet instance for example.


ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace package my_pkg
  2  as
  3          procedure set_session_id( p_session_id in number );
  4
  5          procedure set_ctx( p_name       in varchar2,
  6                                             p_value      in varchar2 );
  7
  8          procedure close_session( p_session_id in number );
  9  end;
 10  /

Package created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> set echo off
Enter to continue

=================================================================


and now the package body.  The set session id calls set_identifer,
set_ctx sets the context value *for our session id and user only*,
close session deallocates the context


ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace package body my_pkg
  2  as
  3          g_session_id number;
  4
  5          procedure set_session_id( p_session_id in number )
  6          is
  7          begin
  8                  g_session_id := p_session_id;
  9                  dbms_session.set_identifier( p_session_id );
 10          end;
 11
 12          procedure set_ctx( p_name       in varchar2,
 13                                             p_value      in varchar2 )
 14          as
 15          begin
 16                  dbms_session.set_context( 'App_Ctx', p_name, p_value, USER, g_session_id );
 17          end;
 18
 19          procedure close_session( p_session_id in number )
 20          is
 21          begin
 22                  dbms_session.set_identifier( p_session_id );
 23                  dbms_session.clear_identifier;
 24          end;
 25  end;
 26  /

Package body created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> set echo off
Enter to continue

=================================================================


Now we'll test it out.  We'll create a session 1234 and put some stuff
in it


ops$tkyte@ORA920>
ops$tkyte@ORA920> exec my_pkg.set_session_id( 1234 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>
ops$tkyte@ORA920> exec my_pkg.set_ctx( 'Var1', 'Val1' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec my_pkg.set_ctx( 'Var2', 'Val2' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select sys_context( 'app_ctx', 'var1' ) var1,
  2         sys_context( 'app_ctx', 'var2' ) var2
  3    from dual
  4  /

VAR1       VAR2
---------- ----------
Val1       Val2

ops$tkyte@ORA920>
ops$tkyte@ORA920> select * from global_context;

NAMESPACE                       ATTRIBUTE
------------------------------- -------------------------------
VALUE
-----------------------------------------------------------------------------------------------------------------------------------
USERNAME                        CLIENT_IDENTIFIER
------------------------------- -----------------------------------------------------------------
APP_CTX                         VAR1
Val1
OPS$TKYTE                       1234

APP_CTX                         VAR2
Val2
OPS$TKYTE                       1234


ops$tkyte@ORA920> select * from session_context;

NAMESPACE                      ATTRIBUTE
------------------------------ ------------------------------
VALUE
-----------------------------------------------------------------------------------------------------------------------------------
LBAC$LABELS                    LBAC$LASTSEQ



ops$tkyte@ORA920> pause
 

If the context is set by ONEBIGUSER
How can a dbms_job get rid of the context entries, if no log out(Clear_identifier) occurs?

Does ORACLE automagically clean these out ever?

thanks for your help

Hi Tom,

this is something i've wondered about as well. How to "clear down" the values in a global app. context? This may be useful if you, say, reuse session_ids, and the data changes quickly enough to justify re-calculating the values at the beginning of a "session".

Thanks in advance.

Hi Tom,

ignore my previous comment, just realised how to do it. DBMS_JOB which loops through the distinct "client_identifier" and issues a clear_identifier ? Is this the correct approach, if not, how would you do it?

Also, how often would you suggest the DBMS_JOB ran in order to clear them down?

Thanks in advance

Tom,

If using an application context to do FGAC in a web environment, is the overhead in setting up the context itself [ie not any user defined select statements inside it] once per page significant, or will this not be a noticeable performance drain?

in response to the followup "Context is a session thing"

Who can issue clear_identifier beyond the USER that set the context variables?

My understanding of the accessed globally was for web app connection pooled session to reaquire a session state. If 10 users create context vars for their client id sessions and never clear the identifier they created context vars for, what happens to them?
The DB probably doesnt assume they will not revisit within lets say one hour , and try to re-establish their session/context. There for it will keep the context information until ?

Tia
Ray

OK.
1. If the contexts are created by 'ONEBIGUSER' , then ONEBIGUSER should be able to set_identifier(N), clear_identifier for any existing Context entries correct?

2. Ntier where N > 2 I suppose. We just switched from 8.1.7 to 9.2. No middle tier. The app was using a session_table which held the app context data by internal session id. As part of the switch my intention was to use the accessed globally option of the create context. I thought this would allow me to avoid the app session table updates. If I do a select from v$globalcontext I can see trouble brewing if I can't clean these out some how. With my own table I can just use a dbms_job to remove Old/inactive sessions. I would like to do the same with the Context entries.
Can this be done? can you please give me a clue how?
WOuld you advise using context accessed globally for browser - http(mod_plsql) db server only solutions
thanks for your help
Ray

why wouldn't I clear them off in a log off trigger?

This is a web app. Log in to the database is via ONEBIGAPPUSER using mod_plsql. The user signs into the application. Application context is set. The user closes the browser. No logout occurs. The context remains.

The app does have a logout which does the set/clear but no guarantee the user will use it.

I am having trouble getting rid of application context entries.

I can set the identifier, and see the context variables. but the clear_identifier isn't deleting the context entry.
Is a commit required?

tia
Ray

Sorry for the confusion, it apparently comes from me, because I am really confused on this especially.

This is what I am trying to do.

1. create context accessed globally.
2. mod_plsql auto logs into database as ONEBIGUSEr
3. Each user invoking the web app logs into the app not the database.
4. The app gens a session id and timestamp encrypts and writes a cookie
5. the app sets context variables with info about the user using the session id as the client identifier.
6. For each subsequent page the app
-reads the cookie.
-sets the identifier to the session id from the cookie reestablishing the context... no problem.
- resets the cookie with new timestamp .....
7. the user then closes the browser ending his session.
the application context remains.
select * from v$globalcontext
shows many context vars for many sessions
8. Logging in as ONEBIGUSER to sqlplus
I execute
begin
for i in 2250..2600 loop
dbms_session.set_identifier(i);
dbms_output.put_line (to_char(i) || ':' || sys_context('MYCONTEXT','MYVAR');
dbms_session.clear_identifier;
end loop;
end;

I can see the context var, but the clear_identifier
does not remove the context.

no errors ...

tia

Ray

Ok I got rid of them by using clear_context on each of the
session id, attribute name pairs individually.
I followed this up with a clear_identifier
Clear_identifier alone did not remove the context entries for that identifier. Still not sure if it's supposed to or not.
After this I RTFM and it said that clear_context without specifying an attribute name, will clear all the attributes for the specified session.
hth
Ray

Not sure if you want toi investigate this or not.

In my effort to clear the context entries. I believe that I founf that USER A can clear USER B's context entries as long as USER A had execute rights on the trusted package. I did not retest this.

hth
Ray

Tom,
Was ever so happy to find your site. I am a newbie to Oracle db management, and have been researching use of app. context w/FGAC in a web environment. Looking for pitfalls to avoid, I have been scanning metalink forums, etc. Came across this post and was curious if anything ever came out of your last followup msg. Thanks so much for sharing your vast store of Oracle knowledge with the rest of us!

Dear Tom,

I have a question concerning the package used in the create context : do the specification of the package have to be always the same :
procedure set_session_id( p_session_id in number );
procedure set_ctx( p_name in varchar2, p_value in varchar2 );
procedure close_session( p_session_id in number );

I cannot understand otherwise how Oracle knows how to use the provided package when activating the context. However, i cannot find any documentation related to this point.

Thanks in advance,

Laly

Hi TOM,

Pls help me out with my problem.
So much stuff out there in various posts and so much confusion for me.

My situation:

We have a PSP page for call center (Intranet only).
Before users can access it they are asked for userid and password.Userid is same as his oracle userid.
By clicks on page user can make transactions in Main Frame system(through an intermediate system) where user has an account and the password they supply is for this account. For every click user's password is to be sent to intermediate system which inturn accesses main frame.

My problem is where to hold the password for the user session.
Writing into cookie is one option. But management wants to avoid it if possible.

So i am wondering if i can use "GLOBAL APPLICATION context" explained in here! ( ours is 9i). I mean every click will retrieve passwd by sys_context and sends across.

I dont know if my understanding is making any sense.
Am first time looking at security issues.

1) Pls suggest me a solution for my situation.
2)If "GLOBAL APPLICATION context" is a choice then can userid be used as sessionid ? Do i have to encrypt the password !!

3) One of the followups says:
<Followup>
Followup:

just setup a job (dbms_job) to periodically clean up "expired" sessions (keeping
a table of session ids in the database is useful for this -- just have a session
state variable "expires" and close out any session that expired. Have the app
of course update the "expires" state variable on each page access
</Followup>

Can you elaborate this "have a session state variable " ?

4) In the context of above quoted followup, the purpose of it is to exipre inactive sessions after a while ?
If dont want this expiry thing ,when browser is closed its the end of the session.. and no more clearing ?

TIA

Thanks a ton TOM.

Just a couple of more doubts bugging me.

<With respect to my earliers post's Followup:>
1) Using cookie is alternative to "global application context " and it does not leave pwd open to DBA. Pls correct me if wrong. By not going for Cookie am i missing any thing here ?
4)User is authenticated through DAD before getting to the page. Doesnt it mean he is opening a session with database and this session will expire when browser is closed !! If its not a session in real sense then cleaning expired sessions is to be done by having a table with expiration time for each session ?
</With respect to my earliers post's Followup:>

<new Doubt>
May be it is silly to ask at this point.. but now i think i dont need to do anything much.
Is it not OK if i set global variables for userid and password !! Only one package handles the entire interface and global variable would be available to all procedures/functions . Pls correct me if i am thinking wrong.
</new Doubt>

TOM,

My dilemma in the last part of my last post is not addressed . Probably its very lame ... i cant help it because i am under time pressure and not able to imagine how this is all going to work for me.
Pls say somthing about "using global variables for userid/password".
As i said entire interface is managed by same package and there will not be any need to pass them as arguments.

TIA

Dear Tom,
Can a table's data be set as a context entry and access that entry using a packaged procedure or function?Is this functionality possible?
Please do reply.

Hello Tom,
I meant "Can we set the rows of a Table as a context entry
and access that values using a packaged procedure or function"?
I hope this is clear to you.
Bye

Hi, Tom!

Everything looks fine in global context arrangements, but there is an optional attribute "username" in dbms_session.set_context() function.

I can't understand what it means or rather how it could be used.
Could you show me its usability?

Thanx.

Correct. After logging in by USR2 and setting the same identifer as USR1 I can't see anything that was set by USR1 using SYS_CONTEXT command.
But I still able to see everything using SELECT * FROM GLOBAL_CONTEXT

Tom,

I am new and started learning the Application context because there is a need to build FGAC in one of our new application. We have tables like follows:

Regions have Counties (one to many )
Counties have compartments (one to many)
Compartments have Forest (one to many), In Forest table we store forest inventory data. In this application, our application context could be either region_id, county_id and a group_id. Group_id is an attribute stored in user's entry in Oracle Directory. Every user belongs to a group.

We want to implement FGAC based on any of these combination. Here are my question about How to setup my context:

1) Sometime I would like to retreive rows from the forest table based on Region_id and since our table is normalised there is no region_id in the Forest table.
2) Secondly our context can be based on County_id or combination of Region_id and County_id. How would I set context based on two attributes?
3) How would I set the context based on group_id stored in LDAP server(OID)?
4) Application are being developed by a consulting companies and they are writing code directly on the tables. How would application context work when in the front end, code is based on tables and not a view. Do we have to create views based on Region, County, Compartment and Forest table to build the application?


Thanks a lot.

Thanks for the explaination. Your answer #4 ckears my doubt. Thanks for the recommendation about th books. I do have book written by Knox and yoour( Effective Oracle by Design).

Tom,

I have a context value in the table which I want to set when Enterprise user logon to database. The problem what I am facing is that the enterprise users are mapped to a shared schema and they are connected to database using the shared schema. To get the context from a table I basically use in my package 

          SELECT office_id
          INTO l_office_id
          FROM lookup_office
          WHERE UPPER(username)=UPPER(sys_context('userenv','session_user'));
and then set the context 

dbms_session.set_context(
                                   namespace  => Ctx_Trusted_Sfid.namespace ,
                                   attribute  => Ctx_Trusted_Sfid.office_id,
                                   value      => l_office_id);


I store the user's CN to username column in the lookup_office table. Since, OID's user are connected through a mapped schem, my SELECT does not retreive any row from the table and it does not set any context.

SQL> conn ssahmad/welcome1@apptest
Connected.
SQL> show user
USER is "DB_SUBTREE"

username column does not hold any record for "DB_SUBTREE" which is a global schema. It holds data for "ssahmad" which is a OID user.

I cannot retreive the value from context from the LDAP because its only available in the table. Is there a way to solve this problem? I donot see any example to solve these kind of problem in David Knox's book.

Thanks a lot.  

Tom,

Infact I got the answer from your site itself. David Knox's reply on this thread 

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:8741291999989,

helped me . I have written following function
FUNCTION Application_Username (p_external_name IN VARCHAR2
DEFAULT SYS_CONTEXT('USERENV','EXTERNAL_NAME'))  RETURN VARCHAR2
AS
BEGIN
    RETURN UPPER(SUBSTR(p_external_name,4,INSTR(p_external_name,',')-4));
END;

and now I am calling this fuhctionin my WHERE calause ie 

SELECT office_id
          INTO l_office_id
          FROM lookup_office
          WHERE UPPER(username)=UPPER(Application_Username) ;

And , now here is the result:

SQL> conn ssahmad/welcome1@apptest
Connected.
SQL> select SYS_CONTEXT('USERENV','EXTERNAL_NAME') from dual;

SYS_CONTEXT('USERENV','EXTERNAL_NAME')
--------------------------------------------------------------------------------
cn=ssahmad,cn=Users,o=dec

SQL>  select sys_context('ctx_sfid','OFFICE_ID') Office from dual;

OFFICE
----------
2

Thanks. 

Database: 9.2.0.5
OS: HP-UX

Tom,
There are a few variables..2 or 3 (inside a loop..so always changing values) that
I want to access globally.
I need to use them in some other procedures (different package) to update success or failure.
So, these variables won't have any thing to do with the processing of other procedures...and this is why I don't want
to add extra parameters to bunch of procedures...but find a way to set the values globally..and access it where I need to.
I have read it on your site that global variables are not
a preferred way to go.
So, can this be accomplished by global application contexts?
If yes...can you provide an example..please..

Thanks.

Tom, 
My question is related to your original reply. I have two separte databases instances OID and TEST on seperate machines. I have created my trusted package to be used to create application context in a schema called sec_admin in OID database. This package set and reset the application context.

I have a public database link ( oid_security)  on my test database and it works OK , ie I can describes the package which exist in OID database from a db user on test ie

SQL>test100/welcome1@test
SQL> desc sec_admin.ctx_trusted_sfid@oid_security;
PROCEDURE RESET_OFFICE
PROCEDURE SET_OFFICE

However, I cannot create an application context on test database using the package which exist on OID database. ie;

SQL>test100/welcome1@test
Connected.
SQL> create or replace context ctx_sfid using sec_admin.ctx_trusted_sfid@oid_security;
create or replace context ctx_sfid using sec_admin.ctx_trusted_sfid@oid_security
                                                                   *
ERROR at line 1:
ORA-00933: SQL command not properly ended



Why am I getting this error? I have appropriate CREATE ANY CONTEXT privilege assigned to user "test100". Also sec_admin on OID instance  has EXECUTE on DBMS_SESSION.

Is this a right design?

Thanks 

dear tom,

i read several times that an application context will enhance security. i don't understand this?

let's assume:
create context my_ctx using my_pkg;
grant execute on my_pkg to scott;

scott is allowed to manipulate the context via package my_pkg now. if someone breaks into scott's account he can also manipulate the context using this package. so, all depends on the username + password authentication. i can't see enhanced security here?

pls, clarify.

you are right, of course. so, the application context is only there for setting user session context variables in a more secure way (as we also can create some check routines within it). no more, no less. right?

>> "global" variables are not a preferred PROGRAMMING technique -- it is dangerous as a programming technique. globals, global application contexts, whatever --
globals are dangerous.

I'd second this. I think application contexts are great, but I'd be very wary of unleashing globals on the system without thinking very carefully. Take a look at the following output which shows what can happen to running queries when they access a global application context that is being "messed with". Interesting, but scary.

*******************************************************************
********** USER1 sets up a global context and procedure ***********
*******************************************************************
USER1> create context global_ctx using global_prc accessed globally;

Context created.

USER1>
USER1> create procedure global_prc ( p in varchar2 ) as
2 begin
3 dbms_session.set_context( 'global_ctx', 'attr', p );
4 end;
5 /

Procedure created.


********************************************************************
********** USER2 sets up a table and couple of functions ***********
********************************************************************
USER2> create table t
2 as
3 select case when rownum <= 5 then 'USER1' else 'USER2' end as owner
4 from all_source
5 where rownum <= 10;

Table created.

USER2>
USER2> create function time return varchar2 as
2 begin
3 return to_char(sysdate, 'hh24:mi:ss');
4 end time;
5 /

Function created.

USER2>
USER2> create function fff return varchar2 is
2 begin
3 dbms_lock.sleep( 2 );
4 justa.counter := justa.counter + 1;
5 return sys_context( 'global_ctx', 'attr' );
6 end fff;
7 /

Function created.


******************************************************************************
********** USER1 sets a value for the global context, pauses ***********
********** while USER2 kicks off a query and then sets a new value ***********
******************************************************************************
USER1>
USER1> set time on
09:38:59 USER1>
09:38:59 USER1> exec global_prc( 'USER1' );


PL/SQL procedure successfully completed.

09:38:59 USER1>
09:38:59 USER1> pause

09:39:13 USER1>
09:39:13 USER1> exec global_prc( 'USER2' ); <======== NOTE THE TIME

PL/SQL procedure successfully completed.


******************************************************************
********** USER2 kicks off a query that evaluates against ********
********** the global value. ********
******************************************************************
USER2>
USER2> set time on arrays 1
09:39:06 USER2> select sys_context( 'global_ctx', 'attr' ) from dual;

SYS_CONTEXT('GLOBAL_CTX','ATTR')
--------------------------------------------------------------------------------
USER1

09:39:06 USER2>
09:39:06 USER2> select owner, fff, time
09:39:06 2 from t
09:39:06 3 where owner = fff;

OWNER FFF TIME
---------- ---------- ----------
USER1 USER1 09:39:10
USER1 USER2 09:39:14 <========= LOOK WHAT HAPPENS HERE
USER2 USER2 09:39:24
USER2 USER2 09:39:28
USER2 USER2 09:39:33
USER2 USER2 09:39:37
USER2 USER2 09:39:41

7 rows selected.

09:39:41 USER2>
09:39:41 USER2> pause


*******************************************************************
********** Back to USER1 - we are going to repeat the test ********
********** but against a dictionary view. ********
*******************************************************************

09:39:13 USER1>
09:39:13 USER1> pause

09:39:47 USER1>
09:39:47 USER1> exec global_prc( 'USER1' );

PL/SQL procedure successfully completed.

09:39:47 USER1>
09:39:47 USER1> pause

09:39:55 USER1>
09:39:55 USER1> exec global_prc( 'USER2' ); <=========== NOTE THE TIME

PL/SQL procedure successfully completed.


*******************************************************************
********** USER2 again trying to run a query while USER1 **********
********** messes with the global application context. **********
*******************************************************************
09:39:50 USER2>
09:39:50 USER2> select owner, fff, time
09:39:50 2 from all_source
09:39:50 3 where owner = fff;

OWNER FFF TIME
---------- ---------- ----------
USER1 USER1 09:39:54
USER1 USER2 09:39:56 <====== QUERY NOT "RE-EVALUATED" THIS TIME
USER1 USER2 09:39:58
USER1 USER2 09:40:00

I can think of a good reason to use global appctxs, such as to hold "debug/don't debug" flags for packages in a connection-pool application etc, but never where they could influence a query.

Regards
Adrian

...I think application contexts are great and I'd have no qualms about using them in queries. In fact, I have used them a lot: to parameterise views, control "env" information for aplications etc. I just thought that a "sessionid-less" global appctx was a bit dangerous, that's all. As demonstrated in my example where they can make it appear like read consistency is being messed with.

I think we are agreeing on this...

Regards
Adrian

Tom,
I have a package that uses application context (accessed locally). From PL/SQL i can execute this package without any problems. But when i try to call this package form Client (.NET front end), i get "Insufficient Privileges" Error on this context.
Did you ever see problem like this before? Where could be the problem?
I am not sure if you need more information to explain me what's going on. please let me know. It's killing me , as i could prove there is nothing wrong from the package, but client when calls, gets error????

Thanks,

Tom,
I have created context from sqlplus "Create context context_name using my_pkg";

I'm using this context in "my_pkg". And like i mentioned , from sqlplus, i could run the package . But when client calls the same package, it gives Insufficient privileges error?
I'm not sure what's happening behind the scenes

Thanks,

BTW, I am calling package directly, from client and not dbms_session.set_context (it's set in package)

Thanks,

Tom, After much research over the weekend, we found that, this particular client was using a different schema , and corrected that. But how do i get this working if they want to use different schema? Our packages are written in "Dev" Schema and clients might use "Test" Schema in future. Do i have to create another set of context's for this test schema to be able to execute those procedures (written in "dev" schema and given execute permissions to "test" schema)?

Thanks for all your help

Sorry for not being clear enough. I had above problem where client was not able to access or execute packages with context in it. (they were getting insufficient privileges, and we thought client was using same username as i am doing, using sqlplus) But client was logging in as a different user. So that part was solved.
Now the problem comes when we use different schema to access this context.
I 've created context locally,
1)so do i have to change them "Globally Accessed" to make it work?
2)if that's the case , if we set a value in one schema, and set another value in another schema for this context, will it affect other schema values or values are for a particular schema?
--> Please see above 3 or 4 questions (from "Client gets Insufficient Privileges" ) for more information on how i have created these contexts

Thanks,

Tom, But when i looked in dba_context table, it has a schema associated with context i've created and only that schema is letting me execute the packages. If i change the schema (still in same database), i get insufficient privileges ..This is in Oracle10g.

When i changed context to "Accessed Globally" it let me execute package from other schema.


Thanks,
Sree

the schema you are seeing in there is the schema associated with the PACKAGE that is allowed to set it, NOT the schema that "owns the context"

You'll have to demonstrate what you mean about the global context, for the simple reason that they are "global" too -- what you say is not computing.


ops$tkyte@ORA10G> grant dba to a identified by a;
 
Grant succeeded.
 
ops$tkyte@ORA10G> grant dba to b identified by b;
 
Grant succeeded.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> @connect a/a
ops$tkyte@ORA10G> set termout off
a@ORA10G> @login
a@ORA10G> set termout off
a@ORA10G> set termout on
a@ORA10G> create or replace procedure the_procedure
  2  as
  3  begin
  4          dbms_session.set_context( 'the_context', 'test', 'hello' );
  5  end;
  6  /
 
Procedure created.
 
a@ORA10G> create or replace context the_context using the_procedure accessed globally
  2  /
 
Context created.
 
a@ORA10G> exec the_procedure;
 
PL/SQL procedure successfully completed.
 
a@ORA10G>
a@ORA10G> select * from dba_context where namespace = 'THE_CONTEXT';
 
NAMESPACE                      SCHEMA
------------------------------ ------------------------------
PACKAGE                        TYPE
------------------------------ ----------------------
THE_CONTEXT                    A
THE_PROCEDURE                  ACCESSED GLOBALLY
 
 
a@ORA10G>
a@ORA10G>
a@ORA10G> @connect b/b
a@ORA10G> set termout off
b@ORA10G> @login
b@ORA10G> set termout off
b@ORA10G> set termout on
b@ORA10G> create or replace procedure the_procedure
  2  as
  3  begin
  4          dbms_session.set_context( 'the_context', 'test', 'hello' );
  5  end;
  6  /
 
Procedure created.
 
b@ORA10G> create or replace context the_context using the_procedure
  2  /
 
Context created.
 
b@ORA10G> exec the_procedure;
 
PL/SQL procedure successfully completed.
 
b@ORA10G>
b@ORA10G> select * from dba_context where namespace = 'THE_CONTEXT';
 
NAMESPACE                      SCHEMA
------------------------------ ------------------------------
PACKAGE                        TYPE
------------------------------ ----------------------
THE_CONTEXT                    B
THE_PROCEDURE                  ACCESSED LOCALLY


There can only be one "the_context" and the schema is the OWNER of the procedure/package that can set it, not the owner of the context. 

From your answer:
<quote>
We would of course want to encrypt the session id, perhaps using dbms_obfuscation_toolkit and set it as a cookie to it is available in all of the subsequent pages after logon.
</quote>

Why you want to encrypt the session id?

continue above question...

If you know my cookie (i.e. the _encrypted_ session id), you can fool the web application and hijack my session too..

https is the solution, not dbms_obfuscation_toolkit.

Tom,
What is the max. length of the Context Name i can use[in Oracle 10g]? Sometimes even for 30 characters, i get identifier too long error.

I couldn't find anywhere , what's the limit on context name.

Thanks,

Tom, This is the one i'm trying to create and it's mroe than 30 characters (33) and not 30 as i mentioned before.

test@testDB10g> create or replace context cms_AssignedBudgetunit_Search_ctx using budgetUnit;
create or replace context cms_AssignedBudgetunit_Search_ctx using budgetUnit
*
ERROR at line 1:
ORA-00972: identifier is too long


test@testDB10g>
test@testDB10g> create or replace context AssignedBudgetunit_Search_ctx using budgetUnit;

Context created.

So, I can use only 30 characters and not more than that.
Is that right?

Thanks for your help

Tom,
We are using Application Context (accessed locally) in our procedures. These procedures /packages are being called from .NET front end (Web App).
Users are connected to Oracle database using common login id and password and we are using Connection Pooling.
So, my question is when 10 users are trying to run the same package with different parameter values, how does set_context handles them?
eg: we have set a context for empno to get all empno details.
user 1 passed in empno=1234;
and procedure has a set_context that sets empno=1234;

user 2 passed in empno =2345;
and procedure has a set_context that sets empno=1234;

and so on..

So does empno gets effected from each setting or it considers user1 and user2 as seperate sessions(eventhough they are connected using same database login)?

Hope i am not confusing you :-)
Thanks,

Tom, When you say SESSION, you mean All users connecting to database with same login are in same SESSION? If so, how can contexts handle different input values for same parameters?

From my example above, if empno=1234 by one user and empno=2345 by another user at the same time, does that mean,empno no is reset to new value and first user will see out put for 2345 insetead of 1234?

I am not handling contexts by any session id's. and I'm using context's accessed Locally


Thanks for your reply.
Mary

Tom,

Great discussion, as always.

One of the applications I support uses global application contexts. From time to time they complain that they are not seeing a value in the app context, which has been set earlier. When they disconnect and reconnect, they see that context.

Question - is there any way I can see the global context values set in "other" sessions, not my own? Being global, I assume they are in SGA and are somehow exposed. Ideally when a global context is set once, any session which issues SELECT SYS_CONTEXT('CTXNAME','ATTRIB') FROM DUAL should see the value set, always. But in some cases they are not and that's where I want to catch them.

Also, is there any case where global app contexts are not visible, i.e. set to NULL? I have a feeling the app is doing a clear_context; but I can't really prove that.

I've been toying with a parameterised view - ie: a view whose filter conditions can be modified according to the value of a paramter stored in an application context.

I was curious as to what the parsing cost of this might be. As far as I can tell, each successive query carried out without changing the context requires two soft parses. THat is the session stats for "parse count (total)" increase by a count of two and "parse count (hard)" remains the same.

Is there any way to reduce the parse cost further?

Best Regards,

I don't know what you mean, can you demonstrate, the following shows ZERO extra parses - parse once, execute over and over and over....


ops$tkyte@ORA9IR2> create or replace context myctx using p
  2  /

Context created.

ops$tkyte@ORA9IR2> create or replace procedure p( p_name in varchar2, p_val in varchar2 )
  2  as
  3  begin
  4          dbms_session.set_context( 'myctx', p_name, p_val );
  5  end;
  6  /

Procedure created.

ops$tkyte@ORA9IR2> create or replace view v
  2  as
  3  select * from dual where dummy = sys_context( 'myctx', 'x' );

View created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace procedure proc
  2  as
  3  begin
  4          for x in ( select * from v )
  5          loop
  6                  null;
  7          end loop;
  8  end;
  9  /

Procedure created.

ops$tkyte@ORA9IR2> alter session set sql_trace=true;

Session altered.

ops$tkyte@ORA9IR2> exec proc

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> exec proc

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> exec proc

PL/SQL procedure successfully completed.



SELECT *
FROM
 V


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        3      0.00       0.00          0          9          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.00       0.00          0          9          0           0
 

I agree with your test. I re-ran and tied the v$mystat entries to the raw trace file for the test below. <b>There is a question below (IN BOLD)</b>

The trace shows the same behaviour you demonstrated. I was doing a quick and dirty test and looking at snapshots of v$mystat (included these below). I wasn't properly accounting for recursive SQL.

Looking at v$mystat there appear to be two hard parses of some statements in the test (reprodicible in multiple runs). These appear to be attributed to the the first time the following statements are run in a session:

* SELECT * FROM V 
* BEGIN proc; END;

<b>Question: Why are these always hard parsed? Must they always be re-parsed for each new session?</b>

There also appear to be a total of 7 additional parses between the snapshots. 

* (the first time this is executed only)
  select a.name, b.value
   from v$statname a, v$mystat b
     where a.statistic# = b.statistic#
    and lower(a.name) like '%' || lower('parse')||'%'
* BEGIN proc; END;
* select user# from sys.user$ where name = 'OUTLN'
* select text from view$ where rowid=:1
* SELECT * FROM V 
* BEGIN proc; END;
* BEGIN proc; END;

Wasn't it JL who said that when you get the same answer from more than one direction you can have some confidence that your results are correct? I should have verified this with a trace originally.


Here is the test --->

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

SQL> create or replace context myctx using p
  2  /

Context created.

SQL>
SQL>
SQL>
SQL> create or replace procedure p( p_name in varchar2, p_val in varchar2 )
  2    as
  3    begin
  4            dbms_session.set_context( 'myctx', p_name, p_val );
  5    end;
  6  /

Procedure created.

SQL>
SQL>
SQL>
SQL> create or replace view v
  2    as
  3    select * from dual where dummy = sys_context( 'myctx', 'x' );

View created.

SQL>
SQL>
SQL>
SQL>
SQL> create or replace procedure proc
  2    as
  3    begin
  4            for x in ( select * from v )
  5            loop
  6                    null;
  7            end loop;
  8    end;
  9  /

Procedure created.

SQL>
SQL>
SQL> alter session set sql_trace=true;

Session altered.

SQL>
SQL> select a.name, b.value
  2     from v$statname a, v$mystat b
  3       where a.statistic# = b.statistic#
  4      and lower(a.name) like '%' || lower('parse')||'%'
  5  /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                           10
parse time elapsed                                                      138
parse count (total)                                                     190
parse count (hard)                                                       18
parse count (failures)                                                    0

SQL>
SQL> exec proc

PL/SQL procedure successfully completed.

SQL>
SQL> exec proc

PL/SQL procedure successfully completed.

SQL>
SQL> exec proc

PL/SQL procedure successfully completed.

SQL>
SQL> select a.name, b.value
  2     from v$statname a, v$mystat b
  3       where a.statistic# = b.statistic#
  4      and lower(a.name) like '%' || lower('parse')||'%'
  5  /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                           10
parse time elapsed                                                      138
parse count (total)                                                     197
parse count (hard)                                                       20
parse count (failures)                                                    0

SELECT * 
FROM
 V 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        3      0.00       0.00          0          9          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.00       0.00          0          9          0           0

 

To clarify:

* I agree - the query with the sys_context is parsed once only.

As a second, seperate but related followup question;

I ran this test a few times (within a new session each time) and for each test I analysed the raw trace to confirm that the output of the v$ views tallies with the trace output. The parse counts do indeed tally. However, in each test I always see a single hard parse for two statements, the first time these are used. These are;

1) SELECT * FROM V
2) BEGIN proc; END;

My question is, why does Oracle need to hard parse these queries for each test run ?

My expectation was that these statements would be in the library cache from the previous test run. Indeed I don't see any reason why they might not be.

Here is one of the tkprof reports:

TKPROF: Release 9.2.0.5.0 - Production on Wed Oct 5 09:37:10 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Trace file: mig1_ora_2136.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

alter session set sql_trace=true


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 90
********************************************************************************

select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('parse')||'%'

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 0 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.00 0.00 0 0 0 10

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 90

Rows Row Source Operation
------- ---------------------------------------------------
5 FILTER
5 MERGE JOIN
280 SORT JOIN
280 FIXED TABLE FULL X$KSUMYSTA
5 SORT JOIN
5 FIXED TABLE FULL X$KSUSD
1 FIXED TABLE FULL X$KSUSGIF

********************************************************************************

BEGIN proc; END;


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 3 0.00 0.12 0 0 0 3
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.13 0 0 0 3

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 90
********************************************************************************

select user#
from
sys.user$ where name = 'OUTLN'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID USER$
1 INDEX UNIQUE SCAN I_USER1 (object id 44)

********************************************************************************

select text
from
view$ where rowid=:1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY USER ROWID VIEW$

********************************************************************************

SELECT *
FROM
V


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.18 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 9 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.01 0.18 0 9 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 90 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL DUAL




********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 6 0.00 0.12 0 0 0 3
Fetch 4 0.00 0.00 0 0 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15 0.00 0.13 0 0 0 13

Misses in library cache during parse: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.01 0.18 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 5 0.00 0.00 0 13 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13 0.01 0.18 0 13 0 2

Misses in library cache during parse: 1

7 user SQL statements in session.
2 internal SQL statements in session.
9 SQL statements in session.
********************************************************************************
Trace file: mig1_ora_2136.trc
Trace file compatibility: 9.02.00
Sort options: default

1 session in tracefile.
7 user SQL statements in trace file.
2 internal SQL statements in trace file.
9 SQL statements in trace file.
6 unique SQL statements in trace file.
109 lines in trace file.

I see. I think the problem lies between the keyboard and chair :o).

Hello Tom,

we're creating a web application where a user can log in using his phone number. When the customer is authenticated to the application, we get unique customer ID and contract ID associated with his phone from the database and put these values into context variables. After that we're using this variables in all the stored procedures we've created to get the data we need. The reason for doing that is simplicity - we don't need to provide any parameters to the procedures and security - it is impossible to change context after the authentication. What is your opinion on this approach in general? Are we using context in the right way? I noticed once on your site (don't remember exactly where) you mentioned that application context was good in SQL not in PL/SQL. Will it have any impact on performance?

And one more question on performance. We didn't took into consideration that all the context variables are in fact VARCHAR2(4000). While we were using this procedures in our Oracle 9i, performance was excellent. But as soon as we started getting data over the link from the same tables in Oracle 8i, our application just crashed. Oracle was using FS all the time. No indexes at all. After 6 hours of trying to find out the reason we've changed the following code:

SELECT custcode
INTO v_custcode
FROM customer_all@db1
WHERE customer_id = SYS_CONTEXT( 'our_context', 'customer_id' )

where customer_id is NUMBER and PK for CUSTOMER_ALL

into

DECLARE
v_customer_id NUMBER;
BEGIN

SELECT SYS_CONTEXT( 'our_context', 'customer_id' )
INTO v_customer_id
FROM DUAL;

SELECT custcode
INTO v_custcode
FROM customer_all@db1
WHERE customer_id = v_customer_id

and after that the plan became correct.

Is this a normal behaviour and what we should do to avoid probems like this in the future? Do we need to always convert context variables into the datatype they actually represent? Why was it working normally in a single database and crashed (performance-wise) over dblink?

Thanks in advance.

Hello Tom,

Thanks for the answer. But as I understand, if we're using package variables, we lose them when we log in/log out. And this is our case as we have connection pool and sessions will be re-used. That's why we're going to use session identifiers which will prevent us from losing data in variables even if the session was terminated. For sure, we can store user's password somewhere in middle tier and re-authenticate him each time we need to re-connect the session. But it looks quite, I'd say, ugly. What is your opinion on that?

Thanks in advance.

Sorry for the mess. Of course, it is a global context.

Thanks. That's exactly the solution we finally came to. There is one more issue I'd like to ask you about context:

STROM@JOHN> select sys_context('csc_context', 'customer_id' ) from dual;

SYS_CONTEXT('CSC_CONTEXT','CUSTOMER_ID')
------------------------------------------
497678

STROM@JOHN> select customer_id from customer_all@bscs where customer_id = sys_context('csc_context', 'customer_id');

STROM@JOHN> select customer_id from customer_all@bscs where customer_id = to_number(sys_context('csc_context', 'customer_id'));

STROM@JOHN> select customer_id from customer_all@bscs where customer_id = (select sys_context('csc_context', 'customer_id') from dual);

CUSTOMER_ID
-----------
497678

Is it a bug or what?

We have

Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

Database on the opposite end of the link is Oracle 8.1.7

Thanks. So if I understand you right, entire query is parsed on the opposite side of the DB link (in our case - 'bscs' database) and context is supposed to be queried in the same database (again 'bscs') - not in the calling database. Right?

Hello Tom,

I'm back with my questions :). I've read the entire thread and anyway I cannot understand quite clear how long the context will survive. If we never clean it up, it can happen that in some time we can have Oracle memory filled up with all these things form the context. Or it can't? What should we do to get rid of all the context variables in the memory except creating a job to check for expiration date in the context? Is there any built-in utility to do that?

Thanks in advance

Hello Tom,

Sorry for bothering you, but I still can't get what's going to happen if we never clean the context up. Will it survive, say, for a year if the database is up and running this entire year? If not - what exact application will remove it from the database and under which circumstances?

P.S. I'd like to thank you for all what you're doing and wish you Happy New Year :)

Hi Serge ,
may be this could be of some Help:


SQL> create or replace package p
  2  as
  3  procedure p( p_name in varchar2, p_val in varchar2 ) ;
  4  procedure clear_ctx ;
  5  procedure clear_ctx_param (p_param varchar2) ;
  6  end ;
  7  /

Paket wurde erstellt.

SQL> create or replace package body p
  2  as
  3  procedure p( p_name in varchar2, p_val in varchar2 )
  4  as
  5  begin
  6  dbms_session.set_context( 'MY_G_CTX', p_name, p_val );
  7  end;
  8
  9  procedure clear_ctx
 10  is
 11  begin
 12  dbms_session.clear_all_context( 'MY_G_CTX') ;
 13  end ;
 14
 15  procedure clear_ctx_param (p_param varchar2 )
 16  is
 17  begin
 18  dbms_session.clear_context(namespace =>'MY_G_CTX', attribute => p_param ) ;
 19  end ;
 20
 21  end p ;
 22  /

Paketrumpf wurde erstellt.


SQL>  exec p.p('param1','val1') ;

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.



SQL>  exec p.p('param2','val2') ;

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.



SQL> select namespace , attribute , value from global_context ;

NAMESPACE                       ATTRIBUTE
------------------------------- ------------------------------
VALUE
--------------------------------------------------------------
MY_G_CTX                        PARAM2
val2

MY_G_CTX                        PARAM1
val1



SQL> exec p.clear_ctx_param('PARAM1') ;

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.


SQL> select namespace , attribute , value from global_context ;

NAMESPACE                       ATTRIBUTE
------------------------------- -------------------------------
VALUE
---------------------------------------------------------------
MY_G_CTX                        PARAM2
val2


SQL> exec p.clear_ctx ;

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

SQL> select namespace , attribute , value from global_context ;

Es wurden keine Zeilen ausgewõhlt
 

Hi Tom,

I am trying to use Application Context to store some session parameters and
I am getting the below error(At the end of the script).

Here is what I do,

--Logon as sys and run
grant execute on dbms_session to scott;
grant create any context to scott;

--Create the procedure as scott
create or replace procedure set_bom_ctx(component_level_ in number, bom_id_ in number)
is
begin
dbms_session.set_context('BOM_ID', component_level_, bom_id_);
end;
/

--Logon as scott and run
create context bom_ctx using set_bom_ctx accessed globally;

When I try to set the context using,(Still Logged on as SCOTT)

exec set_bom_ctx(0, 500);

I get this error..

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 90
ORA-06512: at "SCOTT.SET_BOM_CTX", line 4
ORA-06512: at line 1

What else I am missing here??

Thanks and Appreciate your help..

Kandy_Train

Thanks a lot...

No words to appreciate your help...

Hi Tom,

in my typical 3-tier web app i want to audit the user logged on. I have decided to go against general convention and follow your advice. I have REAL DB ACCCOUNTS!! This is so i can take advantage of vpd later.

My question is that i need to get the ip address of the browser. By default, SYS_CONTEXT('userenv','ip_address') is only going to give me the ip address of the java middle tier machine. Fortunately, I have access to the browser ip in java and was trying to work out how i can pass it to a db procedure that reads it and sets the application context with it.

Also, not sure if i should create my own application context namespace or use userenv.

DO you have an example showing how i can pass it in securely? I am using 9i.

Thanks

Rakesh

Let me give you some more info on my current design:

1. Java web app asks for username/password.
2. These credentials are used to create a database connection and (if successful) a stored proc is called returning a list of roles this user has.
3. The connection is then closed (but i use OCI connection pooling so its not an issue perfromance wise).
4. The java client uses the list of roles to create an authentication object which can be used by the java layer for security control.
5. When the user (say) calls 'Add Account' the authentication object is queried for the username/password, a connection to the db is established and the relevent proc called. On successful completion, the connection is closed.

I think this model is a good compromise between java and db. No more 'one big app user' and reinvention of security wheels.

Just need to be able to pass the users ip address to the db (other than by adding it to every proc call) becuase i have some hairy security policies to implement based on who and where (more on that later!).

All the examples, I have seen using an app context to set values use a proc with no parameters. I guess thats becuase the proc is called for you so you don't get the chance to pass in values.

How can i set values in the app context (perhaps i can do this in my authentication proc?) with/without proxy authentication?

Examples were a bit thin on the ground....

Thanks

RP

Hi TOM, in our application we have certain information that is private to the middle-tier user (client-identifier) but we also have information that needs to be available to all middle-tier users. However we do not want this really global information to be available on full backups or after bouncing the DB, so regular tables are not really an option.

I am confuse whether it is possible to use a client-identifier value of NULL when setting a value in a global context to make the value visible irrespective of the client-identifier when calling set_context().

I have also seen code that queries directly the globalContext view in SYS. Does this violates in any way the CONTEXT visibility rules?

Are there any differences in behaviour between 9i and 10g on this respect?

Can you compare/contrast this approach with the 10g end-to-end metrics support referenced in http://www.slaviks-blog.com/2007/05/22/propagating-middle-tier-and-application-users-to-the-dbms-part-1-of-3
Specifically, 1) what are the security considerations of USERENV vs. a context that can only be accessed through a particular package; and 2) what are the relative overheads (round-trips and/or other)?

Tom, thanks a lot for this site.

My issue is that the context (not global) I created is getting dropped/disables internittantly. Every now and then I have to recreate this context using the CREATE or REPLACE statement.

Could you please tell me when this might happen.

In our applications, we have a 1 row table containing various parameters. Some of them are widely used (millions of times a day) so there is contention on that table. These parameters are only updated 3 times a year. I am thinking of setting a globally accessed context (accessible to all users) that would be initialized at database startup and updated in a trigger on the table. That way, users would "select sys_context ..." instead of "select x, y from param_table".

Do you think it would be a good use of context ?

Hi Tom,

Do I understand well if I say that:
- it costs less to access a context than to access a
table (more scalable)
- it's even better to access a package variable

With that in mind, I have to tell you that the application is a web application (mod_plsql with AS10g). Each time a user access a page (100000s of times a day), I have to make sure that we have the current value of the parameter. So the scenario could be:
- In AS, there's a "before execute procedure" defined in the DAD. In that procedure, I would access the context to set a package variable with the current value of the parameter. Then, the application would access the package variable.

Does it make sense ?

Hi Tom,
First of all, thanks for your efforts.
I'm using your approach but I have a doubt, I want to get all the client ids in my context in order to clear some of them.
I can do that by querying v$globalcontext, but I can´t do it from a stored procedure, it doesn't compile.
What can I do to remove the contents for some client ids?
Thanks tom
David

Tom:

This is my only question this week.

Can this problem be resolved by using an application context or not?
I have an after update or delete trigger for auditing several changes in several production tables.

Audit data is saved into this table.

audit_tbl
( seq number(10),
aud_date date,
aud_time varchar2(12),
who varchar2(30),
ip_address varchar2(30),
terminal varchar2(30),
op varchar(6),
pkey varchar2(30),
tname varchar2(30),
cname varchar2(30),
old varchar2(2000),
new varchar2(2000)
)


The tables that are audited are begin updated/deleted in 3 ways:

1. Using web application (mod_plsq) using aplication user accounts
2. using TOAD, WINSQL, SQL NAVIGATOR, etc. using a database account
3. Using a power builder client/serve application that uses application user accounts


My question to you is how to best capture the "WHO" or "userid" for person who did the DML in the three cases.


Some table do have "last_modified_by" and some do not. Trigger can only access the OLD and NEW values of the ROW. If the column is not getting updated or table does not have it i can't get it. Also on deletes the "OLD.USERID" would not have the userid that deleted the record.


I was looking into DBMS_APPLICATION_INFO but that saves the client identifier in V$SESSION. IT will only work for client server application where SESSION ID does not change.

When i tested the Stored procedure on the web, every call was giving me a different session id so if i am correct I cant set it after user log in and read it back when the update (diff stored procedure) occurs.

For ip address tracking i did this. Do you think i can capture the application userid in similar fashion.

IF owa.num_cgi_vars > 0 THEN
lvIPAddress := owa_util.get_cgi_env(param_name => 'REMOTE_ADDR');
ELSE
lvIPAddress := SYS_CONTEXT('USERENV','IP_ADDRESS');
END IF;

IF your application does not identify the client to the database
THEN
   your database will have no clue whatsoever whom the client is
END /* the truth, nothing but the truth */

tom:

Welcome back, I hope you enjoyed your vacation last week.

Yes i know that REMOTE_ADDR is the gateway/proxy server that was sent with the http header.
And yes i know you can change that. However, the average user does not do that. 
You can capture it -- using oracle environment setting but it does not mean it is 100% reliable.

also, you can tamper with cookies. But why use cookies (store a text file on client with user info) instead of using application context (data for session./user stored in oracle memory).

to me these 3 things sound to do the same function:

1) application context (setting for user info in memory)
2) cookies (text file on client for user info)
3) database table (table to store user info)


My question is on HOW best to have the trigger capture the userid that deleted or updated the record?
(application context, session context, pass variable to trigger, etc.)

I did manage to pass the userid to the UPDATE_EMP procedure and then passed the (p_userid) to a trigger using this concept. is there an issue with this

CREATE OR REPLACE PACKAGE tmp_pkg
    AS
            TYPE tmpArray IS TABLE OF VARCHAR2(32) INDEX BY BINARY_INTEGER;
            newRows tmpArray;
            empty   tmpArray;
    END;
/
This creates the package tmp_pk with TYPE table which stores the variable value that will be retrive from the trigger 

CREATE TABLE emp
  (
        emp_id          NUMBER(6),
        emp_name        VARCHAR2(32),
        salary          NUMBER(8),
        CONSTRAINT emp_id_pk PRIMARY KEY (emp_id)
  );

INSERT INTO emp VALUES (1, 'JOHN', '15000');
INSERT INTO emp VALUES (2, 'JAMES', '12000');
INSERT INTO emp VALUES (3, 'KING', '14000');
INSERT INTO emp VALUES (4, 'KATE', '18000');

COMMIT;


CREATE TABLE LOG 
  (
    log_id              NUMBER(6),
    updated_date        DATE,
    previous_value      VARCHAR2(32),
    new_value           VARCHAR2(32),
    updated_by          VARCHAR2(32),
    CONSTRAINT log_id_pk PRIMARY KEY (log_id)
  );    
CREATE OR REPLACE TRIGGER UPDATE_EMP_TR
BEFORE UPDATE ON emp
FOR EACH ROW
DECLARE
        v_log_id        NUMBER(6);
BEGIN

        SELECT NVL(MAX(log_id), 0)+1 
          INTO v_log_id
          FROM LOG;

        INSERT INTO LOG 
        VALUES (v_log_id, SYSDATE, :OLD.salary, :NEW.salary, tmp_pkg.newRows(1));

END;
/
CREATE OR REPLACE PROCEDURE UPDATE_EMP(eid IN VARCHAR2, sal IN NUMBER, updated_by IN VARCHAR2)
IS

BEGIN

       tmp_pkg.newrows          := tmp_pkg.empty;
       tmp_pkg.newRows(1)       := updated_by;

   UPDATE emp 
          SET salary = sal
        WHERE emp_id = eid;

   COMMIT; 

END;
/
SQL> EXEC update_emp(1, 18000, 'SMITH');

PL/SQL PROCEDURE successfully completed.

    LOG_ID UPDATED_D PREVIOUS_VALUE                   NEW_VALUE                        UPDATED_BY
---------- --------- -------------------------------- -------------------------------- ------------
         1 03-APR-07 15000                            18000                            SMITH

Tom:

This is an old application that i cant just rewrite into apex (alo do not have it yet).

I do both what you said.

Create a session id (unique identifier) and pass it from page to page for session management.

But still this does not push it to the trigger since you cant pass variables. I used the method above using temporary array. Is this acceptable?

Also why do you say tha session cookies cant be used to identify the user?

Session management can be done using either method:

1. db table
2. application context
3. session cookie
4. passing session id from page to page.



After a user logs in you create a session cookie (sessionid, username, etc) and everytime a user submits a page it is read instead of passing his name from page to page.

I assume the application context = session cookie. You set an application context for session id and username and read the information from it on other pages using session id.

Tom:

sorry, I think you are missing my point.

This is where I interpreted you saying a cookie cant be used to identify a user.

<<first, a cookie is not at all what you describe - could it be stored permanently? sure. Would you even consider using that sort of cookie to identify a session? Never, nope, not a chance. >>


What i meant to say is that these 3 different implementations can be used to identify the user:

1) session cookie (session id, user name, etc)
2) application context (session id, user name ,etc)
3) database table (Session id, user name ,etc)

I did not mean to say that application context is same as session cookie. One uses a text file to store data and another uses an area in oracle memory.

Tom, 
  I was trying out your example and I seem to have hit a problem.  Please see below 

SQL> connect chand/chand
Connected.
SQL>  create or replace package mypkg
  2  as
  3    procedure set_session_id(p_session_id in number);
  4    procedure set_context(p_name in varchar2, p_value in varchar2);
  5    procedure close_session(p_session_id in number);
  6  end;
  7  /

Package created.

SQL>
SQL>  create or replace package body mypkg
  2   as
  3      g_session_id number;
  4
  5      procedure set_session_id(p_session_id in number)
  6      is
  7       begin
  8          g_session_id := p_session_id;
  9          dbms_session.set_identifier(p_session_id);
 10       end;
 11
 12      procedure set_context(p_name in varchar2, p_value in varchar2)
 13      is
 14        begin
 15         dbms_session.set_context('myctx', p_name, p_value);
 16      end;
 17
 18
 19      procedure close_session(p_session_id in number)
 20      is
 21      begin
 22        dbms_session.set_identifier(p_session_id);
 23        dbms_session.clear_identifier;
 24      end ;
 25   end;
 26  /

Package body created.

SQL>
SQL>
SQL> create context myctx using mypkg accessed globally;

Context created.

SQL>
SQL>
SQL> exec mypkg.set_session_id(1234);

PL/SQL procedure successfully completed.

SQL> exec mypkg.set_context('var1', 'session1');

PL/SQL procedure successfully completed.

SQL> exec mypkg.set_context('var2', 'session1');

PL/SQL procedure successfully completed.

SQL> column namespace format a10
SQL> column attribute format a10
SQL> column value format a10
SQL> select substr(namespace, 1, 6) namespace,
  2         substr(attribute, 1, 6) attribute,
  3         substr(value,1, 10) value
  4  from global_context;



NAMESPACE  ATTRIBUTE  VALUE
---------- ---------- ----------
MYCTX      VAR1       session1
MYCTX      VAR2       session1

SQL> select sys_context('myctx', 'var1') from dual;

SYS_CONTEXT('MYCTX','VAR1')
-------------------------------------------------------------------------------



SQL>
SQL> disconnect
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Produ
ction
SQL> connect chand/chand
Connected.
SQL> l
  1  select substr(namespace, 1, 6) namespace,
  2         substr(attribute, 1, 6) attribute,
  3         substr(value,1, 10) value
  4* from global_context
SQL> /

NAMESPACE  ATTRIBUTE  VALUE
---------- ---------- ----------
MYCTX      VAR1       session1
MYCTX      VAR2       session1

SQL>
SQL> select sys_context('myctx', 'var1') from dual;

SYS_CONTEXT('MYCTX','VAR1')
--------------------------------------------------------------------------------

session1

SQL>

The user who initially created the session can't see the values on calling sys_context. But when I reconnect I can then see the value.  Do you know what could be happening here?  

thank you ,
chandini

My bad. Sorry for wasting your precious time. Jeez, next time I review what I do properly, before I jump in!

thank you,
Chandini

 
Hi, 
I am writing a web application. One of the features is that the 
transactions of one user can cause an alert to be raised to another connected 
user. 
This other user's session is polling the database every n seconds to 
see whether or not there is an alert for them. 
The application makes use of global application contexts to store 
information relevent to a users session. 
Included in this context is a flag which denotes whether or not there 
is an alert for that user (which makes the polling calls extremely 
light weight) 
In order to set that flag the user causing the alert spoofs into the 
other users context and sets the flag. This works a treat. 
The problem I have comes with the polling routine clearing the alert 
flag once it has been set and read as such. 
The check alert function looks like this... 
        FUNCTION check_alert return varchar2 
        IS 
                i_am constant varchar2(128) := 'fe.check_alert'; 
            ret_val varchar2(1); 
        BEGIN 
                ret_val := nvl(sysadmin_ctx.ctx_pkg.get_ctx(p_attr => 'alert_flag'),'N'); 
                if ret_val = 'Y' then 
                        -- clear alert flag 
                        sys_utl.log(p_level => 'DEBUG', p_module => i_am, p_message => 'Alert flag was Y. Clearing flag.'); 
                        sysadmin_ctx.ctx_pkg.set_ctx(p_attr => 'alert_flag',  p_value => 'N'); 
                        sys_utl.log(p_level => 'DEBUG', p_module => i_am, p_message => 
'Cleared flag. value now :'||nvl(sysadmin_ctx.ctx_pkg.get_ctx(p_attr 
=> 'alert_flag'),'N')); 
                end if; 
                return ret_val; 
        EXCEPTION 
                        when OTHERS then 
                                sys_utl.err_and_stop (p_module => i_am ,p_sqlcode => sqlcode, 
p_errstack => dbms_utility.format_error_stack, p_backtrace => 
dbms_utility.format_error_backtrace); 
        END check_alert;
 
The relevent parts of the ctx_pkg are standard and look like this.
 
                PROCEDURE set_ctx(p_attr IN VARCHAR2, p_value IN VARCHAR2) 
                  AS 
                  BEGIN 
                   DBMS_SESSION.SET_CONTEXT( 
                    namespace  => 'custom_ctx', 
                    attribute  => p_attr, 
                    value      => p_value, 
                    username   => USER, -- Retrieves the session user, will always 
be same for web users 
                    client_id  => session_id_global); 
                  END set_ctx; 
                  FUNCTION get_ctx (p_attr varchar2) return varchar2 
                  is 
                  begin 
                        return(sys_context('custom_ctx',p_attr)); 
                  end get_ctx;
 
Now what is interesting is the resulting debug messages. They look 
like this...most of the time... 
15-SEP-2010 14:02:21 22      fe.check_alert                  DEBUG 
Alert flag was Y. Clearing flag. 
15-SEP-2010 14:02:21 22      fe.check_alert                  DEBUG 
Cleared flag. value now :Y
 
Occasionally it works and they look like this...which is what i'd 
expect.
 
15-SEP-2010 13:37:11 22      fe.check_alert                  DEBUG 
Alert flag was Y. Clearing flag. 
15-SEP-2010 13:37:11 22      fe.check_alert                  DEBUG 
Cleared flag. value now :N

 
Now I am stumped as to what is going on. Though you do mention in one response that context variables were really intended to be set once and read often...and wonder if I am falling foul of this... 
Cheers 
Ralph 

rajesh@10GR2> create table t(x number);

Table created.

Elapsed: 00:00:00.06
rajesh@10GR2> create table t_aud(x number,y varchar2(10));

Table created.

Elapsed: 00:00:00.03
rajesh@10GR2>
rajesh@10GR2> create or replace context cms_context using prc_test;

Context created.

Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2> create or replace procedure prc_test(p_user in varchar2 default user)
  2  as
  3  begin
  4     dbms_session.set_context('cms_context','cms_user',p_user);
  5     insert into t values (1);
  6     commit;
  7     dbms_session.set_context('cms_context','cms_user',NULL);
  8  end;
  9  /

Procedure created.

Elapsed: 00:00:00.17
rajesh@10GR2>
rajesh@10GR2> create or replace trigger t_trig
  2  before insert or update on T
  3  for each row
  4  begin
  5     insert into t_aud(x,y) values (:new.x,sys_context('cms_context','cms_user'));
  6  end;
  7  /

Trigger created.

Elapsed: 00:00:00.07
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> exec prc_test('TEST_USER');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2> select * from t;

         X
----------
         1

Elapsed: 00:00:00.03
rajesh@10GR2> select * from t_aud;

         X Y
---------- ----------
         1 TEST_USER

Elapsed: 00:00:00.01
rajesh@10GR2>
rajesh@10GR2>

Tom:

I have a requirement where the value of a Input parameters from Stored Procedure has to be Placed in a column available in an Audit table, which is inserted by a Trigger. Does the code highlighted above is a Best approach or We have any specific feature in Oracle 10g database to handle this?

hi tom, we are using gc in our app to get the where predicate of a table (through policy) for specific client_id, a row in gv$globalcontext is : GC_NAME, TABLE_NAME, SOME_WHERE_PREDICATE , NULL (username), client_id. when the user select a table we return in the policy function the where that return from sys_context(GC_NAME,TABLE_NAME).
In the middle of the day some predicate for some users could change so we decide that every hour we run a job that would refresh the gc for users that are stale for more than 3 hour (we have attribute of the last refresh in the gc) so the job would select the updated predicate from specific table that has them and then update the gc for the specific client_id.
my problem : i run dbms_session.clear_context for the client_id before i would refresh the gc predicates, so there is a small period when there is nothing in the gc for a specific client_id and when he would the select the table he would see all of it, how would i solve it? (there is no transaction in updating the gc)

hi,
thanks for the answer but you're 2 proposals still don't solve my issue completely because:
proposal 1 - a) we change our policy from dynamic to context_sensitive policy because we have performance problems, so loop would only help in the first time.
b) we have several tables in the gc which should have policy and not all the users have where predicate in all the tables, some can have 1 or 2 table without a where, so comparing to null don't necessarily indicate that a refresh in going on.
proposal 2 - i have to clear the other tables predicates before i calculate the new predicate again because it can be that a user has where predicate for table "emp" and then we decide that from now he will not has a where predicate and can see all the table data.

i have dedicated many time on thinking of how to solve this issue, i would very appreciate your help.
thanks.

just what i needed:)

Just to enrich my knowledge, workspace feature would also be the answer to this problem?
(i could to my changes in specific workspace and then merge the changes)

i'm talking about the problem of the "reader" with the title "consistency of global context", 3 reviews backward from this.

Hi Tom, we have a web application running on a weblogic server which connects to the database using a single user. Front end usernames are real database users which are then proxy authenticated when performing transactions in the database.

We want to build a facility where a user ticks a checkbox on the front end, which will set a debug flag on the database for the specific user. Any subsequent calls to the database by that user (which may all be in different sessions) will then use this flag to determine whether to log debug messages or not.

I tried out this, firstly without proxy connection, for ease of understanding.

Here is the code, I have only included the output from the last few relevant lines, but I am can't get to set application context values specific to a user.

Is my understanding incorrect? Can I not set application contexts by a user across their multiple sessions if I provide the username parameter in dbms_session.set_context? According to David Knox, "In the next example, the username parameter is passed to the SET_CONTEXT procedure. The desired effect is to make the attribute value sharable across all sessions for the same schema."

Any help much appreciated.

--This is the main application user
CONNECT appuser/appuser

CREATE OR REPLACE PROCEDURE set_debug (p_debug VARCHAR2) AS
BEGIN
  DBMS_SESSION.SET_CONTEXT (
    namespace => 'debug_context',
    attribute => 'debug_yn',
    value     => p_debug, --incoming parameter
    username  => USER);
END;
/
 
CREATE CONTEXT debug_context USING set_debug ACCESSED GLOBALLY;

--Creating a real database user
CREATE USER user1 IDENTIFIED BY user1;

GRANT CREATE SESSION TO user1;

GRANT EXECUTE ON set_debug TO user1;
 
--Creating one more real database user
CREATE USER user2 IDENTIFIED BY user2;
 
GRANT CREATE SESSION TO user2;

GRANT EXECUTE ON set_debug TO user2;
 
COL user FORMAT A10
COL debug_yn FORMAT A10
 
CONN user1/user1

BEGIN
  appuser.set_debug('Y');
END;
/
 
--Checking if debug has been set to Y for user1
SELECT USER, SYS_CONTEXT('debug_context','debug_yn') debug_yn FROM DUAL;

USER       DEBUG_YN
---------- ----------
USER1      Y

--It has

--Connecting as user2 and setting debug to N
CONN user2/user2
 
BEGIN
  appuser.set_debug('N');
END;
/
 
--Checking if debug has been set to N for user2
SELECT USER, SYS_CONTEXT('debug_context','debug_yn') debug_yn FROM DUAL;

USER       DEBUG_YN
---------- ----------
USER2      N

--It has

CONN user1/user1
 
--Checking if debug is still Y for user1
SELECT USER, SYS_CONTEXT('debug_context','debug_yn') debug_yn FROM DUAL;

USER       DEBUG_YN
---------- ----------
USER1

--It is not!

CONN user2/user2
 
--Checking if debug is still N for user2
SELECT USER, SYS_CONTEXT('debug_context','debug_yn') debug_yn FROM DUAL;

USER       DEBUG_YN
---------- ----------
USER2      N

--It is

ops$tkyte%ORA11GR2> CREATE OR REPLACE PROCEDURE set_debug (p_debug VARCHAR2) AS
  2  BEGIN
  3    DBMS_SESSION.SET_CONTEXT (
  4      namespace => 'debug_context',
  5      attribute => user,
  6      value     => p_debug );
  7  END;
  8  /

Procedure created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> CREATE or replace CONTEXT debug_context USING set_debug ACCESSED GLOBALLY;

Context created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> CREATE USER user1 IDENTIFIED BY user1;

User created.

ops$tkyte%ORA11GR2> GRANT CREATE SESSION TO user1;

Grant succeeded.

ops$tkyte%ORA11GR2> GRANT EXECUTE ON set_debug TO user1;

Grant succeeded.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> CREATE USER user2 IDENTIFIED BY user2;

User created.

ops$tkyte%ORA11GR2> GRANT CREATE SESSION TO user2;

Grant succeeded.

ops$tkyte%ORA11GR2> GRANT EXECUTE ON set_debug TO user2;

Grant succeeded.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> CONN user1/user1
Connected.
user1%ORA11GR2> exec ops$tkyte.set_debug('Y');

PL/SQL procedure successfully completed.

user1%ORA11GR2> SELECT USER, SYS_CONTEXT('debug_context',user) debug_yn FROM DUAL;

USER       DEBUG_YN
---------- ----------
USER1      Y

user1%ORA11GR2> 
user1%ORA11GR2> CONN user2/user2
Connected.
user2%ORA11GR2> exec ops$tkyte.set_debug('N');

PL/SQL procedure successfully completed.

user2%ORA11GR2> SELECT USER, SYS_CONTEXT('debug_context',user) debug_yn FROM DUAL;

USER       DEBUG_YN
---------- ----------
USER2      N

user2%ORA11GR2> 
user2%ORA11GR2> 
user2%ORA11GR2> CONN user1/user1
Connected.
user1%ORA11GR2> SELECT USER, SYS_CONTEXT('debug_context',user) debug_yn FROM DUAL;

USER       DEBUG_YN
---------- ----------
USER1      Y

user1%ORA11GR2> 
user1%ORA11GR2> CONN user2/user2
Connected.
user2%ORA11GR2> SELECT USER, SYS_CONTEXT('debug_context',user) debug_yn FROM DUAL;

USER       DEBUG_YN
---------- ----------
USER2      N

Ah, I see what you've done, you have created a unique attribute for each user which only they can set/get.

It works fine with proxy authenticated users as well, but with the caveat that I can only create one attribute.

So if I have to, let's say, switch debug on/off and also set the level of debug (detailed/summary,etc.), then I'll probably need to concatenate the values with a delimiter, unless Mr Tom can come up with a clever idea (as he usually always does)!

when you have users with '_' in there name ( not forbidden )

better than

5 attribute => user || '_' || p_what, 

would probably be

5 attribute => user || chr(0) || p_what, 

or

5 attribute => user || '"' || p_what, 

because it is not allowed that \0 or " is part of a username:

http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements008.htm#sthref744

.. neither quoted nor nonquoted identifiers can contain double quotation marks or the null character (\0).

hi tom, we are using gc in our app to get the where predicate of a table (through policy) for
specific client_id, a row in gv$globalcontext is : GC_NAME, TABLE_NAME, SOME_WHERE_PREDICATE , NULL (username), client_id. when the user select a table we return in the policy function the where that return from sys_context(GC_NAME,TABLE_NAME).
the policy is context sensitive.
My problem:
when a conn is pooled we set the client_id, that way it has specific where predicate(for the curr client_id) of table in
sys_context(GC_NAME,TABLE_NAME).
then the session exec proc_a which has select from tab_a (which has policy predicate on it)
until here everything is ok.
next we change the client_id to null so the session wouldn't has policy's predicate.
the session exec proc_a and the select fails (because the policy didn't invoke),
you know why the policy didn't invoke?
when i change session_cursor_cache to 0 before setting the client_id to null the policy was executed and the problem was solved.
how can i solve this problem in more elegant way?

tell me if this info isn't enough.
thanks.

sorry, you're right
here is my example :

drop user dubi cascade;
CREATE USER dubi IDENTIFIED BY abcd;

grant dba to dubi;

create table dubi.emp (id number);


begin
insert into dubi.emp values(2);
insert into dubi.emp values(1);
commit;
end;
/

CREATE or replace PACKAGE dubi.shubi
AS
PROCEDURE init;
FUNCTION policy_func (object_schema IN VARCHAR2, object_name VARCHAR2) RETURN varchar2;
PROCEDURE set_cid(c varchar2);
END shubi;
/

CREATE OR REPLACE PACKAGE BODY dubi.shubi AS
FUNCTION policy_func (object_schema IN VARCHAR2, object_name VARCHAR2)
RETURN varchar2 IS
BEGIN
return sys_context('G_C','EMP');
END;
PROCEDURE set_cid(c varchar2)
IS
BEGIN
dbms_session.set_identifier(c);
END;
PROCEDURE init
IS
BEGIN
dbms_session.set_context(
namespace => 'G_C',
attribute => 'EMP',
value => 'id = sys_context(''G_C'',''ID1'')',
client_id => '1');
dbms_session.set_context(
namespace => 'G_C',
attribute => 'ID1',
value => '1',
client_id => '1');
END;
END shubi;
/

CREATE PACKAGE dubi.mosh
AS
PROCEDURE sel;
END mosh;
/

CREATE OR REPLACE PACKAGE BODY dubi.mosh AS
PROCEDURE sel

IS
l_id number;
BEGIN
select id
into l_id
from emp
where rownum=1;
END;
END mosh;
/

grant execute on dbms_session to dubi;
drop context g_c;
create context g_c using dubi.shubi accessed globally;

BEGIN
dbms_rls.add_policy(object_schema => 'dubi',
object_name => 'emp',
policy_name => 'emp_policy',
function_schema =>'dubi',
policy_function => 'shubi.policy_func',
policy_type => dbms_rls.CONTEXT_SENSITIVE);
END;
/

conn dubi/abcd

begin
shubi.init;
shubi.set_cid('1');
end;
/

exec mosh.sel;

begin
shubi.set_cid(null);
end;
/

exec mosh.sel;

here i got an error and i shouldn't, because i have no value in "SYS_CONTEXT('G_C','EMP')" for client_id=null
so i need to see all the table.
thanks.

hi tom,
if as yuo say, how do explain that when i run this code
it's working:
conn dubi/abcd

begin
shubi.init;
shubi.set_cid('1');
end;
/

select * from emp;

begin
shubi.set_cid(null);
end;
/

select * from emp;

why in pl\sql it's not working.

and even if change context in set_cid i get the same error,
here is the cahnge in the package body:
CREATE OR REPLACE PACKAGE BODY dubi.shubi AS
FUNCTION policy_func (object_schema IN VARCHAR2, object_name VARCHAR2)
RETURN varchar2 IS
BEGIN
return sys_context('G_C','EMP');
END;
PROCEDURE set_cid(c varchar2)
IS
BEGIN
dbms_session.set_identifier(c);
dbms_session.set_context(
namespace => 'G_C',
attribute => 'TODAY',
value => sysdate,
client_id => c);
END;
PROCEDURE init
IS
BEGIN
dbms_session.set_context(
namespace => 'G_C',
attribute => 'EMP',
value => 'id = sys_context(''G_C'',''ID1'')',
client_id => '1');
dbms_session.set_context(
namespace => 'G_C',
attribute => 'ID1',
value => '1',
client_id => '1');
END;
END shubi;
/



begin
shubi.init;
shubi.set_cid('1');
end;
/

exec mosh.sel;

begin
shubi.set_cid(null);
end;
/

exec mosh.sel;

get the same error..

thanks

i put this code (in the proc set_cid):
dbms_session.set_context(
namespace => 'G_C',
attribute => 'TODAY',
value => sysdate,
client_id => c);
and it still didn't help..
so what should i do to tell oracle that i change the context?
thanks.

hi,
we change it from dynamic to sensitive because we have performance problems.. we don't won't to go back.
why do you say "you aren't changing the context that your policy is using"?

this is my policy and my context:
create context g_c using dubi.shubi accessed globally;
BEGIN
dbms_rls.add_policy(object_schema => 'dubi',
object_name => 'emp',
policy_name => 'emp_policy',
function_schema =>'dubi',
policy_function => 'shubi.policy_func',
policy_type => dbms_rls.CONTEXT_SENSITIVE);
END;

and i exec (in set_cid) :
dbms_session.set_context(
namespace => 'G_C',
attribute => 'TODAY',
value => sysdate,
client_id => c);

and my function policy return:
sys_context('G_C','EMP');

so i am changing the context that my policy is using...

the policy is on 3 main tables in our system.
so when it was dynamic we have 3 million execution
and when it was sensitive we have 1/2 million,
thus the parsing process was longer then with context sensitive.

what attribute should change to tell oracle to exec the policy func again? i have tried everything and didn't find something that will cause the policy func to exec again.
thanks.