Solved

Example code in JDBC using "application context" in Oracle

Posted on 2004-08-17
9
1,625 Views
Last Modified: 2008-01-09
Hi

I am looking for a sample JDBC code in Oracle 8i to use "application context" to pass certain client information. I could find many examples using SYS_CONTEXT and implementing security policies in PL/SQL, but could not find one using JDBC.

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c26privs.htm#8113
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c26privs.htm#4759
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c26privs.htm#4759
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c26privs.htm#4759

-Srinivas
0
Comment
Question by:sbolisetty
  • 5
  • 4
9 Comments
 
LVL 7

Expert Comment

by:bvanderveen
ID: 11821975
look at the package dbms_application_info

I have frequently used this to set application context values.  Example (from Oracle Applications):

BEGIN
      dbms_application_info.set_client_info(33);
END;


This is just a session-specific value.


0
 

Author Comment

by:sbolisetty
ID: 11822237
From a PL/SQL block that is how it can be done. From a JDBC connection - especially using connection pool with in an application server - how do I go about passing this information from application server's session back to the database?

-Srinivas
0
 
LVL 7

Accepted Solution

by:
bvanderveen earned 315 total points
ID: 11822530
Use a CallableStatement to call dbms_application_info.  Use set_client to set the value, get_client to check it.  With a Connection named conn:
 
   CallableStatement pCall = null;

   try{
         Class.forName("oracle.jdbc.driver.OracleDriver");
          conn = DriverManager.getConnection("jdbc:oracle:thin:@dbserver:1521:ORCL","scott","tiger");
         
          pCall = conn.prepareCall("{call dbms_application_info(?)}");
          pCall.setInt(1,33);  //set the value of the parameter above...
          pCall.execute();
    } catch(SQLException e){
         System.out.println("You have done something wrong");
    }

 
0
 

Author Comment

by:sbolisetty
ID: 11822960
This seem to be just the session-id. Is there any way to pass other information? For example, in my application I need to pass at least two fields - User who is Logged in and Module-Name the user is working on. This is needed to enforce security policies as dictated by the existing oracle structures.

-Srinivas
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:sbolisetty
ID: 11823245
Looked up on the DB_Application_Info a bit. The set_module and set_client_info procedures should be good enough for me to pass this information to the oracle backend. From the looks of it, this needs to be done before every call to the database. Is this the way Applicatio Context is used in Oracle? Is there any better way to do this from Java Application Server point of view? Thanks for your help.

-Srinivas
0
 
LVL 7

Expert Comment

by:bvanderveen
ID: 11823411
These are session-based.  You don't need to set before each call to the db, just per session.  When you open a jdbc connection, you have a session.  Set application-level info there, and it will persist throughout the session.  Next connection, they will need to be re-established.

If you are in an environment (HTML, for instance) where the connection is not persistent, you need to stash the values in the page, and use them each time you connect.  Use the get functions to test the value during development.

One other "gotcha"  if you use connection pooling, users may be sharing these values.  Not good, so test around this, or see if your app can skip the pooling.
0
 

Author Comment

by:sbolisetty
ID: 11823579
Do you know of any easier way to approach this problem in the case of HTML-Application using connection-pooling? We do all this and trying to attack this problem from this one application perspective. Thank you.

-Srinivas
0
 
LVL 7

Expert Comment

by:bvanderveen
ID: 11823698
Simple way: Hold the value you want in a hidden field on your page and reset it each time.  

Are you using JSP, PLSQL server pages, ?  9iAS, WebShere?

You might look at the session object, or use a temporary table to hold values as well.  Don't know your architechture, so don't know.  If using a framework such as JSP, Struts, etc.  look at the documentation on the session object.
0
 

Author Comment

by:sbolisetty
ID: 11824222
It is not the problem with session object. We do keep session object based information and caching on the app server itself. But we have this need to send the user and module names to the oracle backend which will filter out the data based on the security policies set inside the database. The solution above may work if we simply call the dbms_application_info stored procedures before doing any thing with in the connection object. That is, we can get the connection from the pool - then call these two stored procedures and then continue to do what we need to in the application. But this seems a bit cludge and wondering if and how this is solved by others. Thanks.

-Srinivas
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now