Skip to Main Content
  • Questions
  • Application contexts in a web environment

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, Daryl.

Asked: September 28, 2002 - 12:21 am UTC

Answered by: Tom Kyte - Last updated: March 29, 2012 - 7:00 am UTC

Category: Database - Version: 9.2.0

Viewed 10K+ times! This question is

You Asked

We have a web application that performs all its database accesses via stored procedures. Like many applications, we're using an application server that manages its own pool of connections, each of which is logged in using the same username. We want to use application contexts to support Fine Grained Access Control as well as things like triggers that do auditing.

Most books (including yours) show the following flow:
1. begin page generation/processing in application server
2. set context for database based on actual user (via stored procedure
call)
3. do database stuff via normal SELECTs and DML

In our case, is there any problem with doing something like this:
1. begin page generation
2. invoke stored procedures needed
2a. each stored procedure sets context based on incoming user id

The second method saves multiple stored procedure calls (and associated network overhead). However, the latter method also potentially sets the same context multiple times. In both cases (again assuming that in the second case, the app server does no database access apart from the stored procedures), it seems that contexts would be set properly for auditing triggers as well as FGAC.

For access apart from our application, I've considered maintaining real Oracle users to match the "virtual" users in the application. If the real users then connect directly (using something like Access or whatever), we could use a logon trigger to set their context for the entire session, still protecting the data via FGAC.

Are there any potential problems with the second approach (doing context settings in the procedures)? The second approach requires less re-work on the client side (no context calls), so clearly we'd like to know this will work.

Thanks for any comments you can provide.


and we said...

Or, how about a GLOBAL APPLICATION context, new with 9i.


They are based on session ids. If, upon logging into your application, you assign a session id to each "session" (each login session) -- we can

a) set the context once (much better for performance, especially if it takes a bit of work to set the context)

b) rejoin this context from any other DATABASE session that user account uses by providing the session id


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.

Here is a quick and dirty SQLPlus example showing how this works:

ops$tkyte@ORA920.US.ORACLE.COM> @test
=================================================================


we'll start by creating a "global" context - using the ACCESSED
globally clause


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

Context created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> 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.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> 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.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> 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.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> 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.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> 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.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> exec my_pkg.set_session_id( 1234 );

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> 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.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> set echo off
Enter to continue

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


Now we'll log out/log in

At first, out context is empty - but we rejoin our session and there it is


ops$tkyte@ORA920.US.ORACLE.COM> disconnect
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
ops$tkyte@ORA920.US.ORACLE.COM> connect /
Connected.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select sys_context( 'app_ctx', 'var1' ) var1,
2 sys_context( 'app_ctx', 'var2' ) var2
3 from dual
4 /

VAR1 VAR2
---------- ----------


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

PL/SQL procedure successfully completed.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> 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.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> set echo off
Enter to continue

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


Now we'll show that this context is tied to our user (we specified
USER above, if we used null anyone can join this session).

this means only our "middle tier" user can rejoin this session


ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> grant execute on my_pkg to scott;

Grant succeeded.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> connect scott/tiger
Connected.
ops$tkyte@ORA920.US.ORACLE.COM> set echo off
scott@ORA920.US.ORACLE.COM>
scott@ORA920.US.ORACLE.COM> exec ops$tkyte.my_pkg.set_session_id( 1234 );

PL/SQL procedure successfully completed.

scott@ORA920.US.ORACLE.COM>
scott@ORA920.US.ORACLE.COM> select sys_context( 'app_ctx', 'var1' ) var1,
2 sys_context( 'app_ctx', 'var2' ) var2
3 from dual
4 /

VAR1 VAR2
---------- ----------


scott@ORA920.US.ORACLE.COM>
scott@ORA920.US.ORACLE.COM>
scott@ORA920.US.ORACLE.COM> set echo off
Enter to continue

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


Now we'll come back and see our context again and then wipe it out


scott@ORA920.US.ORACLE.COM>
scott@ORA920.US.ORACLE.COM> connect /
Connected.
scott@ORA920.US.ORACLE.COM> set echo off
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> exec my_pkg.set_session_id( 1234 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> 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.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> exec my_pkg.close_session( 1234 );

PL/SQL procedure successfully completed.

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

VAR1 VAR2
---------- ----------


ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> set echo off



But -- that doesn't answer the "original" question. What I would do is use the global context above (for the web apps) and the one procedure you have that sets it would be called by the other applications -- your procedure would look at the USER psuedo column and if the USER = application user, it would set the context using one set of logic, else it would set the application context using a different code path (based on the "real" username).



and you rated our response

  (87 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Closing sessions

December 08, 2002 - 10:23 am UTC

Reviewer: Mishka McCowan from Alexandria, VA USA

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?

Tom Kyte

Followup  

December 08, 2002 - 11:36 am UTC

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

LIST_CONTEXT

March 07, 2003 - 11:18 am UTC

Reviewer: Yves Bergeron from Québec

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 ?

Tom Kyte

Followup  

March 07, 2003 - 1:21 pm UTC

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
 

9.2 App Contexts

April 07, 2003 - 5:39 pm UTC

Reviewer: Ray from Ottawa,ON,CA

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

Tom Kyte

Followup  

April 07, 2003 - 6:32 pm UTC

by default a context is a "session thing". there is nothing to clear out.

Clearing down GACs

April 08, 2003 - 2:57 am UTC

Reviewer: Martin from UK

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.


Tom Kyte

Followup  

April 08, 2003 - 7:49 am UTC

in the case of the data changing -- you would not be wanting to use a global application context then.


in the case of reuse, just clear the identifier.

Aaaahh!

April 08, 2003 - 3:08 am UTC

Reviewer: Martin from UK

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 Kyte

Followup  

April 08, 2003 - 7:50 am UTC

only you can answer the last question ;)

perhaps you have a table client_identifier, expire_time and your job just loops over that expiring sessions as they expire.

How much work is setting an application context

April 09, 2003 - 7:11 am UTC

Reviewer: Tom

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?

Tom Kyte

Followup  

April 09, 2003 - 10:26 am UTC

setting a context takes virtually no time.

it is when the values you want to set in the context take a long time to gather that you want to consider the "global" context.

9.2 app context clear identifier

April 09, 2003 - 9:01 am UTC

Reviewer: Ray from Ottawa,ON,CA

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





Tom Kyte

Followup  

April 09, 2003 - 10:42 am UTC

The context's remain.

Typically this is used in Ntier situations -- so the contexts are "owned" by that single magic user (this is where they make most sense). That single magic user however can clear them out as they "expire"

9.2 app context clear identifier

April 09, 2003 - 12:24 pm UTC

Reviewer: RAY from Ottawa,ON,CA

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


Tom Kyte

Followup  

April 09, 2003 - 2:35 pm UTC

1) yes.

2) no middle tier implies client server -- why wouldn't you just clear it on exit? (or in an on logoff trigger)

9.2 app context clear identifier

April 10, 2003 - 8:54 am UTC

Reviewer: 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



Tom Kyte

Followup  

April 10, 2003 - 9:27 am UTC

Now you are confusing me.

1) said i'm one big user

2) said i'm a client server application

I thought we were talking about two things.

for case #1, no worries, you are onebiguser and onebiguser can clear them at will. Onebiguser can clear out expired sessions whenever onebiguser wants to.



9.2 app context clear identifier

April 10, 2003 - 10:26 am UTC

Reviewer: A reader

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



Tom Kyte

Followup  

April 10, 2003 - 7:21 pm UTC

apparently, I have some "product issues" to file.... it doesn't clear them out from the sga apparently. bummer -- thanks for pushing the issue, i'll look into it.

Clearing context entries

April 10, 2003 - 6:11 pm UTC

Reviewer: Ray from ottawa,on,ca

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




Clearing Global Contexts

April 11, 2003 - 8:04 am UTC

Reviewer: Ray from ottawa,on,ca

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

Anything new yet on clearing identifiers from SGA?

December 23, 2003 - 11:21 am UTC

Reviewer: Bill Schwartz from Montpelier, Vermont

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!

Tom Kyte

Followup  

December 23, 2003 - 12:07 pm UTC

the two messages below (the prior two to these) discuss how to clear them.

package used in application context

December 31, 2003 - 9:16 am UTC

Reviewer: Laly from France

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

Tom Kyte

Followup  

December 31, 2003 - 10:11 am UTC

YOU "activate" the context

your procedure/function names can be whatever please you.

the package could be:

create package demo_pkg
as
procedure foo;
end;


for all we care. if you:

create context my_ctx using demo_pkg;

that just means that any procedure/function in demo_pkg will be successful in using dbms_session.set_context to 'activate' it.

Unless you set it, it isn't "there", this just controls the name of the schema object that is permitted to set it.

keeping password in somewhere for a session

April 18, 2004 - 5:19 pm UTC

Reviewer: rd from Swedden

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

Tom Kyte

Followup  

April 18, 2004 - 7:06 pm UTC

1) a global application context would work, if you are willing to trust the DBA's with possible access to the passwords (well, they have that access now really too, you have to trust someone -- you can go as far as you want to make it hard)

2) userid can be used so that ONLY that user can access their context. the context may either be secured by USER (eg: when setting the context, send the user in and only that USER account may access it), by some sessionid you make up and take care of, or by both.

do you have to encrypt the password? only if you want it stored in the SGA in a manner that would not make it visible to people who can dump the SGA.

3) a session state variable is exactly what you are trying to get -- your password is a session state variable.

4) unless you put a "logout" button in your app AND the user actually uses it you have NO CLUE when the browser executes (the web is stateless, you cannot tell when the browser exits). Hence you usually need to clean up expired sessions.

keeping password in somewhere for a session

April 19, 2004 - 5:59 am UTC

Reviewer: rd from Sweden

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 Kyte

Followup  

April 19, 2004 - 7:01 am UTC

1) well, it leaves it open for the SA's then (and the DBA's if you set the cookie using plsql -- a smart dba can see whatever they want in the database).

You would have to use SSL to encrypt the entire conversation -- else someone can sniff the headers, get the encrypted password and headers -- which is all they need to spoof you. the encrypted password would be as good as the real one with that info. SSL would protect you there.


4) no, it means with each page request they are reauthenticated to the database. there is NO session by default (there is a mode where there could be but i highly recommend against using it --- it reduces the best feature of the web, scalability, back to that of client server)

keeping password in somewhere for a session

April 19, 2004 - 8:05 am UTC

Reviewer: rd from Sweden

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

Tom Kyte

Followup  

April 19, 2004 - 9:35 am UTC

my response is -- by default the mod_plsql stuff is 100% stateless. Your package variables do not survive from page to page AND you might well have more than one session going for a single user! (think "frames", think "open link in new window" done twice -- you may have dozens of sessions for a single user)

OK

April 21, 2004 - 1:06 am UTC

Reviewer: Anne from Oregon.USA

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.

Tom Kyte

Followup  

April 21, 2004 - 7:48 pm UTC

can you elaborate on that a bit more, not sure what you mean?

OK

April 21, 2004 - 11:22 pm UTC

Reviewer: Anne from Oregon,USA

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

Tom Kyte

Followup  

April 22, 2004 - 7:28 am UTC

the entire set of rows?

no, a context entry is a single, scalar, varchar2(4000)



accessed globally context

August 17, 2004 - 9:16 am UTC

Reviewer: Alexander Semenov from Ukraine

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.

Tom Kyte

Followup  

August 17, 2004 - 9:59 am UTC

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



meaning only that user logged in with that username can access it -- even if you know my sessionid, you still cannot see my values -- unless you have my username associated with your session.

accessed globally context

August 18, 2004 - 3:52 am UTC

Reviewer: Alexander Semenov from Ukraine

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 Kyte

Followup  

August 18, 2004 - 8:17 am UTC

you only see context values that either

a) do not have a session id (client_identifier) set on them
b) have your client_identifier set (your session id)



Application Context Design question

October 16, 2004 - 7:54 am UTC

Reviewer: Suhail from Albany, NY

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.

Tom Kyte

Followup  

October 16, 2004 - 10:54 am UTC

1) you'll be returing a subquery in the predicate.

write a query that given a region as input, returns a list of "forests".

that is your subquery.


2) say you were writing a report based on the same -- what queries would you write? those are your subqueries used to filter forest then.

3) you would make a call out to ldap using whatever technique you wanted (eg: middle tier could do it, dbms_ldap could do it) to query that repository and set values.

4) You have to get the context set up -- application can call a procedure, a login trigger can do it, whatever. After that, any query hitting a table (or view) will have a predicate silently added to it.



You might be interested in Effective Oracle Security by Design (oraclepress, david knox), or I have a lengthly chapter on it in Expert one on one Oracle, or
</code> http://asktom.oracle.com/~tkyte/article2/index.html <code>

thanks

October 16, 2004 - 1:43 pm UTC

Reviewer: A reader

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).

How to set a context for OID user

October 26, 2004 - 4:11 pm UTC

Reviewer: Suhail from Albany, NY

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.  

I got the answer

October 26, 2004 - 4:36 pm UTC

Reviewer: Suhail from Albany, NY

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. 

Globally accessible variables

December 02, 2004 - 12:14 pm UTC

Reviewer: mohini from Germany

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 Kyte

Followup  

December 02, 2004 - 12:29 pm UTC

"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 look long and hard before sticking globals in to communicate important information. It is a decision you'll have to live with for a long long long time.

you don't need a global application context, you just need be very judicious in your use of globals -- as a programmer, in any language.

application context

December 15, 2004 - 9:21 am UTC

Reviewer: Suhail from NY

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 

Tom Kyte

Followup  

December 15, 2004 - 2:17 pm UTC

anything that uses a database link in an operational system (eg: database link will be used for more than moving data from A to B periodically) is in my opion "a not-right design"

it would mean both systems must both be up in order for either system to be up.

it means both systems probably should have been one system from day 1

but in any case, you'll find application contexts (pga things, process memory things) not to be very dblink friendly....


so, no, do not do this.

enhanced security of application context

December 17, 2004 - 1:43 pm UTC

Reviewer: A reader

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.

Tom Kyte

Followup  

December 17, 2004 - 3:14 pm UTC

if someone breaks into scott's account


if someone does that -- your problem lies way back before application context doesn't it.


lets put it this way:

"if someone breaks into root"


Ok, so in light of that, why have passwords on anyones accounts? cause if they break into root, they can just su? how do passwords enhance security?

or, why have permissions on files? cause if they break into root they can just change the owner/perms at will?

or why have sysdba for oracle, cause if they break into root, they can just su to oracle and be in, so how does sysdba ehance security?


sense of application context

December 20, 2004 - 7:05 am UTC

Reviewer: A reader

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?



Tom Kyte

Followup  

December 20, 2004 - 8:35 am UTC

correct.

Proceed with caution....

January 27, 2005 - 5:09 am UTC

Reviewer: Adrian Billington from UK

>> "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

Tom Kyte

Followup  

January 27, 2005 - 9:06 am UTC

well, they can be used for a query (they are designed to be used that way) -- but you must be careful how you use them.

Their designed, intended use goes like this:

a) user logs into application server, gets a sessionid
b) during this login, something that takes a "long time" (long being relative) takes place -- like a LDAP lookup to get information
c) rather than lookup this data over and over -- you put it into a global context identified by that sessionid (which should be unique to that user)
d) it remains "read only" for the rest of the session
e) when the session expires, you clean it out

used that way, they are safe.

Like I said...

January 28, 2005 - 3:19 am UTC

Reviewer: Adrian from UK

...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

Client gets "Insufficient Privileges" error

July 08, 2005 - 9:04 am UTC

Reviewer: A reader from USA

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 Kyte

Followup  

July 08, 2005 - 9:55 am UTC

insufficient details.

I don't see how you created the context or anything.


basically, it sounds like you are trying to set the context directly from the client, but a context is bound to a package or procedure and can only be modified by calling that package or procedure.


so, are you calling dbms_session.set_context from your client OR are you calling the only procedure/package that is allowed to set the context.

Insufficient privileges

July 08, 2005 - 10:09 am UTC

Reviewer: A reader

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,


Tom Kyte

Followup  

July 08, 2005 - 12:49 pm UTC

is the "client" logged in as the same user you are in sqlplus?

is the problem running MY_PKG or the context call.

we cannot tell the difference between "sqlplus" and "client x", they are all clients.

Make sure

o same user
o same database
o be more clear as to exact error, what is the entire error stack

More info..

July 08, 2005 - 10:11 am UTC

Reviewer: A reader

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

Thanks,

Different User

July 11, 2005 - 9:59 am UTC

Reviewer: A reader

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


Tom Kyte

Followup  

July 11, 2005 - 11:37 am UTC

insufficient data here.

but an application context is GLOBAL in nature, they do not have schemas.

Same Reader from above

July 11, 2005 - 12:01 pm UTC

Reviewer: A reader

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 Kyte

Followup  

July 11, 2005 - 12:38 pm UTC

a context is GLOBAL in scope in the database.

a context is always owned by PUBLIC -- they have no schema.

you can have ONE context of a given name and it will be associated with ONE package in a database.

globally accessed just says whether the same context values can be accessed by multiple sessions.


You will have ONE schema that may set this single context in a given database.

dba_context shows context has schema

July 11, 2005 - 12:45 pm UTC

Reviewer: A reader

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

Tom Kyte

Followup  

July 11, 2005 - 2:48 pm UTC

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. 

Why encryptions?

July 11, 2005 - 2:29 pm UTC

Reviewer: A reader

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?

Tom Kyte

Followup  

July 11, 2005 - 2:57 pm UTC

if I know your session id, I can likely attach to your context.

...
b) rejoin this context from any other DATABASE session that user account uses by
providing the session id
....

encrypting session id..

July 14, 2005 - 2:10 pm UTC

Reviewer: A reader

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 Kyte

Followup  

July 14, 2005 - 2:55 pm UTC

depends on what you encrypted the cookie with but yes, agreed, https is very good for this (as we don't have to think about it).

Just having the cookie may not be enough, you might need a certificate, you might need an ip address (yes, that can be gotten around)

Identifier too Long

August 08, 2005 - 3:22 pm UTC

Reviewer: A reader

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 Kyte

Followup  

August 08, 2005 - 8:32 pm UTC

show me a 30 character one failing (and also say if you are using multi-byte when you do so, that could be relevant)

the sql reference guide states "namespace follows rules for schema objects", 30 characters

It's 33 characters

August 09, 2005 - 10:59 am UTC

Reviewer: A reader

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 Kyte

Followup  

August 09, 2005 - 11:19 am UTC

that is correct, 30 characters.

Context Sessions from WEB Application

August 12, 2005 - 2:19 pm UTC

Reviewer: Mary

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 Kyte

Followup  

August 13, 2005 - 9:41 am UTC

the context values are session specific. Each SESSION can only see their context values.

Just like global variables in the packages.

Can you clarify more..

August 13, 2005 - 7:26 pm UTC

Reviewer: A reader

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 Kyte

Followup  

August 13, 2005 - 8:17 pm UTC

when I say session I mean a session, a thing you see in v$session.

every session is separate and distinct and doesn't share anything.

You as an individual may have 0, 1, or N sessions going on.


As long as you grab a connection from the pool...
Set the context....
Use the context....
Then give the connection back to the pool

you will have had your own unique session. Usernames are not sessions, they are used for identification and authorization.

How to find out Global Context Values in Other Sessions

September 26, 2005 - 6:04 pm UTC

Reviewer: Arup Nanda from Danbury, CT, USA

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.

Tom Kyte

Followup  

September 27, 2005 - 10:03 am UTC

You'd need the session identifier associated with it and if needed, the ability to log into that account.


If the session hasn't been "reattached" or you are logged in as the wrong user - it won't be visible - the context will appear null (security there - the global application contexts are bound to a sessionid AND optionally a user account)

Cost of parsing when using sys_context

October 03, 2005 - 2:08 pm UTC

Reviewer: Mathew Butler from United Kingdom

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,

Tom Kyte

Followup  

October 03, 2005 - 8:54 pm UTC

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
 

Parse cost of sys_context

October 04, 2005 - 5:29 am UTC

Reviewer: Mathew Butler from United Kingdom

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

 

Tom Kyte

Followup  

October 04, 2005 - 3:50 pm UTC

but that only shows 2 hard parses -- with 5 statements having been executed???

the tkprof would show you "misses in library cache" - that is, it shows you the total number of times it was hard parsed.

Here the tkprof shows a SINGLE parse -- meaning it could not have been hard parsed 3 times

October 05, 2005 - 4:57 am UTC

Reviewer: Mathew Butler from United Kingdom

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.




Tom Kyte

Followup  

October 05, 2005 - 7:39 am UTC

so? It has to be HARD PARSED at least ONCE in its life.

You created every thing "fresh" and "new", so yes, of course it was hard parsed the first time.


Log out, log in, run test AGAIN without dropping and creating everything and since it was hard parsed before, it'll be soft this time around.

PBKAC

October 06, 2005 - 11:35 am UTC

Reviewer: Mathew Butler from UK.

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

Tom Kyte

Followup  

October 06, 2005 - 12:15 pm UTC

I love that site, laugh out loud at some of the stories there.

pebcak.net

Using application context for data selection

November 02, 2005 - 4:12 pm UTC

Reviewer: Serge Shmygelsky from Ukraine

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.


Tom Kyte

Followup  

November 03, 2005 - 6:46 am UTC

i would do what you did over dblinks - yes, the sys context function is evaluated locally, not remotely.


if you use sys_context in SQL - great.
if you are using it in PLSQL (eg:

....
if sys_context(a,b) = c
......

I would suggest you use a PACKAGE with getter/setter functions instead (just as unchangeable - the setter functions can be private to the package body - the getter functions are visible to all)

Using application context for data selection - ctd

November 03, 2005 - 10:35 am UTC

Reviewer: Serge Shmygelsky from Ukraine

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.


Tom Kyte

Followup  

November 04, 2005 - 2:36 am UTC

I don't get it - because in a connection pool you don't get your same session back - or are you using GLOBAL application contexts?

Using application context for data selection - ctd

November 04, 2005 - 2:46 am UTC

Reviewer: Serge Shmygelsky from Ukraine

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

Tom Kyte

Followup  

November 04, 2005 - 3:40 am UTC

then I would suggest you look to see if you peek at a context value over and over, eg:


for x in ( select ... )
loop
if ( x.col = sys_context( .... ) )
....


and optimize that by setting a local variable equal to the context value and referencing the local variable over and over instead of calling sys_context over and over (eg: remove redundant unnecessary function calls when or if possible)

Using application context for data selection - ctd

November 04, 2005 - 5:46 am UTC

Reviewer: Serge Shmygelsky from Ukraine

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

Tom Kyte

Followup  

November 04, 2005 - 8:48 am UTC

not a bug, it is the resolution of where the sys_context function is being called. I would bind over a database link (you are using the remote context - it is empty)

Using application context for data selection - ctd

November 04, 2005 - 9:15 am UTC

Reviewer: Serge Shmygelsky from Ukraine

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?

Tom Kyte

Followup  

November 04, 2005 - 5:05 pm UTC

since you were referencing all remote tables in that context and the sys_context function is 'over there', yes.

I would use binds in all distributed queries in this case. (any mixed remote/local table or remote table query)

Cleaning up global application context

December 23, 2005 - 8:31 am UTC

Reviewer: Serge Shmygelsky from Ukraine

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

Tom Kyte

Followup  

December 23, 2005 - 12:39 pm UTC

there is no built in utility to do that for you are the only one that knows when the global application context is not "necessary" anymore - we sure don't.

Cleaning the context -ctd

December 27, 2005 - 4:09 am UTC

Reviewer: Serge Shmygelsky from Ukraine

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 :)

Tom Kyte

Followup  

December 27, 2005 - 9:50 am UTC

it'll just stick in there until you restart. Unless and until you do something about it, we are compelled to keep it for you.

clear context with dbms_session

December 27, 2005 - 10:55 am UTC

Reviewer: pasko from Hamburg,Germany

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
 

Context Usage

February 08, 2006 - 3:06 pm UTC

Reviewer: Sujith Wimalasooriya from NY USA

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

Tom Kyte

Followup  

February 09, 2006 - 4:56 am UTC

you created BOM_CTX
you are trying to set BOM_ID

BOM_ID should be BOM_CTX

Context Usage

February 09, 2006 - 11:46 am UTC

Reviewer: Sujith Wimalasooriya from NY USA

Thanks a lot...

No words to appreciate your help...

Putting it together

March 14, 2006 - 1:59 pm UTC

Reviewer: RP from London, UK

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

Tom Kyte

Followup  

March 15, 2006 - 9:23 am UTC

your middle tier is going to have to pass that along.

You could pass it along "securely" using ntier proxy authentication. you might search for that term here to read a bit about that.

March 16, 2006 - 2:52 am UTC

Reviewer: RP from London, UK

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

Tom Kyte

Followup  

March 16, 2006 - 11:38 am UTC

3) how so? you created a connection using their credentials - those credentials were not associated with the connect before.


what examples have you ever seen that use a procedure with "no parameters"? Most all of the ones I've done myself have them - but anyway - you know how to pass parameters from CLIENT (app server) to DATABASE - just do that.



Usage of NULL client identifier

October 26, 2006 - 10:34 am UTC

Reviewer: Jose from London, UK

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?

Tom Kyte

Followup  

October 26, 2006 - 12:14 pm UTC

you would use a global application context and just not set the user (unless all mid tier sessions use the same user)

not sure what you mean by "violated" the context visibility rules? If you are allowed to see the context values, you are allowed, if not, you won't see it (eg: if you think something is being done wrong, by all means set up a test case and show us)

End-to-End Metrics

May 29, 2007 - 4:18 pm UTC

Reviewer: John Bossert from Seattle, WA USA

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 Kyte

Followup  

May 30, 2007 - 11:08 am UTC

1) not sure you can compare apples and toaster ovens. I don't see how to compare these two concepts??? they achieve very different things.

2) depends on how you write the code. anywhere from one total to N round trips.

January 15, 2008 - 11:41 am UTC

Reviewer: Solomon

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.
Tom Kyte

Followup  

January 15, 2008 - 3:35 pm UTC

because someone is dropping it.

Enable auditing

Using global context as application parameters

January 16, 2008 - 10:22 am UTC

Reviewer: Louis from Trois-Rivières, Qc, Canada

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 ?
Tom Kyte

Followup  

January 16, 2008 - 4:01 pm UTC

and hopefully they would select this only once in a long while (eg: package up the parameters into a bit of code, and don't query them out everytime). but yes (heck, before going to the context, just try implementing this idea - wrap em in a nice getter routine that doesn't require constant requery)

note: in 11g, you'll use the client result cache and it'll save the results in the client and not re-query them over and over (eg: solves the problem) and ensure that the current values are used (database notifies client of changes so client re-queries them next time)

Using global context as application parameters

January 17, 2008 - 10:26 am UTC

Reviewer: Louis from Trois-Rivières, Qc, Canada

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 ?
Tom Kyte

Followup  

January 17, 2008 - 11:24 am UTC

a) you'll still be accessing a table - dual or fast dual

b) absolutely NOT, it is entirely different. if you can use a package variable - USE IT. do not use a context in place of that.



I like the last paragraph, that would be a good idea.

Clearing context

June 27, 2008 - 7:56 am UTC

Reviewer: David from Madrid, Spain

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 Kyte

Followup  

June 27, 2008 - 9:24 am UTC

grant select on v_$globalcontext to Your_Schema;

http://asktom.oracle.com/tkyte/Misc/RolesAndProcedures.html

application context

May 15, 2009 - 4:35 pm UTC

Reviewer: A reader

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;
Tom Kyte

Followup  

May 23, 2009 - 10:44 am UTC

do you understand what REMOTE_ADDR is?

do you understand that I can make it be whatever I want?

if you understand that REMOTE_ADDR is easily set by any client, that the user of a proxy server would affect it's value, that anyone with 30 seconds and access to google could figure out how to change it....

then you can certainly use it - just be aware it is not very useful, that is all.



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 */



Can you use cookies or a hidden field you pass from screen to screen to identify YOUR concept of a session? Yes (use SSL or the answer becomes "of course not")

Would you be able to tell WHO was using the session? Of course not, not unless and until the application thinks you are important enough to know that.


context

May 24, 2009 - 7:09 pm UTC

Reviewer: A reader

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 Kyte

Followup  

May 26, 2009 - 8:54 am UTC

vacation? not really. I was giving a seminar and customer visits all week.


... However, the average user does not do that.
....

I am never worried about the average user, why even bother auditing if you only have average users, if you have just average users - why even bother having any semblance of security.

Sam - you ask the same things of me over and over and I keep saying the same things over and over.


... 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).
....


cookies used in this fashion would not be stored on the client - who said to do that? where do you see that? You do know about cookies and how they can work right???

Tell me sam - this magic application context - ummm, how do you associate it with a browser session?????? (hint: cookie, hint 2: hidden data in a form)

environments like APEX automate this for you AND provide the ability to make the cookie/hidden data tamper proof (hint: it takes a little thought, a little design, but - could you not think of a way to encode data such that it is tamper proof - eg: the cookie itself is encrypted for example. If you tamper with it - well - ummm - ok, so what? you cannot send me correctly encrypted data, you do not know my key)



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)


you do not understand them then.

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.

You need to have the web browser send something back and forth between itself and the application server - so that the application server can identify WHO the client is (remote_addr - YOU CANNOT USE IT - PERIOD. I use a proxy server, others use the same proxy server - we'd all look to be the SAME EXACT PERSON)



To answer your question:
My question is on HOW best to have the trigger capture the userid that deleted
or updated the record?


(which I've actually done before, on other pages, saying pretty much the same thing - you've been working on this for like a year now....)


You need to

a) be able to identify the 'session' of the user from page to page reliably in your database code AND have the application initiate this 'session' (so you KNOW who the user is)

b) be able to have the application PUSH the client identity down into the database with each and every page, each and every time. The application would need to therefore do (a) as well.



Sam - I suggest you just use APEX, it automates all of this for you. It'll give you a session state and the ability to reliably identify the user - without you having to guess as to how things might work

applicatin context

May 29, 2009 - 7:12 pm UTC

Reviewer: A reader

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 Kyte

Followup  

June 01, 2009 - 7:41 pm UTC

... But still this does not push it to the trigger since you cant pass variables. I ...

SMK - Sam - whatever you want to call yourself. After a long long time of using Oracle, you still do not "get Oracle"

do me a favor - read your subject, just read the subject of this review. Read it - and say it out loud. Might you - after saying it out loud, figure out AT LEAST one way to get a variable to a trigger? (I can think of a couple of others - but after you read your own subject - might you find at least ONE way???)


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

where did I EVER SAY THAT. I've been saying - well - EXACTLY THE OPPOSITE. Give me a break (please)


turned around to you - you tell US why an encrypted cookie passed over SSL could not be. You tell US. We've told you how to identify a session, you don't believe it - so tell us where the flaw in our plan is.

a) you generate a unique, unguessable id for the session (eg: sys_guid() would do that)

b) you encrypt the id

c) you send it back and forth over SSL to the client via a cookie or hidden field.



... I assume the application context = session cookie. ...

that shows you do not have the knowledge to update the application you are working on - please - get some help where you are.

June 01, 2009 - 10:08 pm UTC

Reviewer: A reader

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 Kyte

Followup  

June 02, 2009 - 7:26 am UTC

first, I'm not missing your point, you are saying things that are not true at all - you have misconceptions about things and how they work - that is your point.

How - HOW could you interpret a factual description of a cookie as meaning "cookies cannot be used to identify people"???!?!?!?!?!?!? How could you ? Give me the logic behind that.


cookies may be either

a) session based, they live for the life of the browser session, they NEVER EVER MAKE IT INTO A FILE on the client
b) persistent, they live until they expire or forever. The browser saves them in a file on the client and reads that file to load them up every time the browser starts.



Now, would you use (b) to identify a session???? NO, you would not, it would not be "smart"

does saying you would not use (b) to identify a session imply, infer - even remotely (I cannot find any way to read that sentence and come to the 'conclusion' you did) that you cannot use a cookie to identify a session???????? NO, it does not.




please - tell us how (2) and (3) in your list above could be used to identify a session.


... 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. ...

then

a) why did you say it?
b) cookies do not have to use files, they can, but they do not have to and you would never use a cookie that persists like that in a file to identify something as transient as a session.

Tell me please - how can an application context by itself (or a database table by itself, or even an application context PLUS a database table together) identify a session

just answer that - don't say anything else - just tell use how an application context can identify a session - no cookies, no hidden fields, just an application context.

Problem with 10g XE and sys_context?

June 10, 2009 - 5:37 am UTC

Reviewer: chandini paterson

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

Tom Kyte

Followup  

June 10, 2009 - 12:54 pm UTC

your code is very very different from my code, please review - you are missing a couple of parameters in the set context call.


Doh!

June 11, 2009 - 1:03 am UTC

Reviewer: 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

Getting inconsistent results using global app ctx

September 17, 2010 - 9:31 am UTC

Reviewer: Ralph from East Sussex,UK

 
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 

Tom Kyte

Followup  

September 17, 2010 - 12:58 pm UTC

... This other user's session is polling the database every n seconds to
see whether or not there is an alert for them.
...

why aren't you use dbms_alert?




You don't seem to be setting your session_id as far as I can see, your sys_context call would use your last setting - where as you dbms_session.set_context would be setting in it SOME SPECIFIC session.


give a FULL example that is TINY - teeny teeny tiny - fits on the screen here. Probably - by doing that - you'll find your issue.

Application Context in Triggers

December 08, 2010 - 2:24 am UTC

Reviewer: Rajeshwaran, Jeyabal

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?
Tom Kyte

Followup  

December 08, 2010 - 9:38 am UTC

(i've mentioned this before, posting a ton of code followed by a question - that doesn't really even need the code - is something you should avoid.)


Your approach works, yes, it is fine.

consistency of global context

November 28, 2011 - 3:59 pm UTC

Reviewer: A reader

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)
Tom Kyte

Followup  

November 29, 2011 - 7:54 am UTC

in your policy function, have it check for a null policy and loop (with a small sleep) until it gets a non-null policy.


or - just don't clear the predicate, just set the new one in there - so there is always a predicate.

reply for the above answer

November 29, 2011 - 11:53 am UTC

Reviewer: A reader

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.
Tom Kyte

Followup  

November 30, 2011 - 7:05 am UTC

both proposals solved the stated problem, if you don't state something....


Sounds like you might have to use dbms_lock to create a critical section.


All of the policy 'getters' would

a) get a named lock in shared mode
b) access the context
c) release the named lock


Any policy setter would

a) get that named lock in exclusive mode
b) set the context
c) release the named lock



thanks!

November 30, 2011 - 4:32 pm UTC

Reviewer: A reader

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)
Tom Kyte

Followup  

December 01, 2011 - 7:47 am UTC

what problem? I'm not sure what you are talking about?

what problem

December 01, 2011 - 12:38 pm UTC

Reviewer: A reader

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

Followup  

December 06, 2011 - 10:44 am UTC

no, it would not apply at all. How did you see it fitting in? they are talking about a global application context being used to set a predicate on a set of queries - not updating any data in any table.


and in the future, state the problem as if it were your own - build the context around the question - make it clear what you are asking. Most of the pages here are "big", with lots of random thoughts.

Can't get it to work with username parameter

March 01, 2012 - 1:53 pm UTC

Reviewer: Dhruva from London, UK

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

Tom Kyte

Followup  

March 01, 2012 - 2:33 pm UTC

the global application context will be only visible to one user at a time using this technique.

here is an approach you can use instead

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


Cool!

March 01, 2012 - 4:22 pm UTC

Reviewer: Dhruva from London, UK

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)!
Tom Kyte

Followup  

March 02, 2012 - 5:32 am UTC

ops$tkyte%ORA11GR2> CREATE OR REPLACE PROCEDURE set_something( p_what in varchar2, p_val in varchar2 ) AS
2 BEGIN
3 DBMS_SESSION.SET_CONTEXT (
4 namespace => 'debug_context',
5 attribute => user || '_' || p_what,
6 value => p_val );
7 END;
8 /

dangerous

March 02, 2012 - 7:21 am UTC

Reviewer: Sokrates

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).

Tom Kyte

Followup  

March 02, 2012 - 8:13 am UTC

good point.

use " - chr(0) would be a bad idea, it can drive some programming languages nuts.

global context and policy context sensitive

March 27, 2012 - 4:45 am UTC

Reviewer: yair

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.
Tom Kyte

Followup  

March 27, 2012 - 8:17 am UTC

look at the other place you posted this same exact thing.

no example
no look

example

March 27, 2012 - 10:24 am UTC

Reviewer: yair

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.
Tom Kyte

Followup  

March 27, 2012 - 11:38 am UTC

where did you change a context value?

set_cid changes the sessions identifier, but I don't see a context being touched?


you call init, changed context
you call set_cid - does NOT touch the context
you call sel - get parsed
you call set_cid - does NOT touch the context
you call sel - no need to reparse, no context has been touched.



maybe you mean for set_cid to modify a context in addition to changing the session id?

change context

March 28, 2012 - 4:48 am UTC

Reviewer: yair

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
Tom Kyte

Followup  

March 28, 2012 - 9:11 am UTC

because plsql is caching cursors open and sqlplus does not. sqlplus always parses.


please, think about this, you asked the policy to be context sensitive and you are wondering why it isn't changing the policy of a cached cursor - when you never change the context????


you have to change the context for a context sensitive policy to invalidate cached cursors that are relying on the context!

how can i change the context?

March 28, 2012 - 9:52 am UTC

Reviewer: yair

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.
Tom Kyte

Followup  

March 28, 2012 - 10:35 am UTC

I think you'll have to make your policy the default - dynamic - you aren't changing the context that your policy is using.


use dynamic and that'll cause it to evaluate your policy function for each executing.

change context

March 28, 2012 - 11:46 am UTC

Reviewer: yair

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...
Tom Kyte

Followup  

March 28, 2012 - 12:13 pm UTC

you didn't change any attributes relevant to the problem. but regardless - you need the policy function to get involved for each execution since the predicate can and will change from execution to execution. the context_sensitive will just cache the where clause returned by the function, but you need to get the function involved.

dynamic is going to be what you need in this case.

or you need to reset your session state upon making your change, that'll unload all plsql packages, session state, close all cursors, etc.


if your policy function was just a "return this string", what sort of overhead are you seeing?

policy

March 29, 2012 - 3:01 am UTC

Reviewer: yair

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.
Tom Kyte

Followup  

March 29, 2012 - 7:00 am UTC

it'll have to be dynamic, as stated.

the way to get the policy function executed again is... to make the statement parse again.