23 Advanced Topics

OCI Driver Support for Oracle Advanced Security

If you are using one of the JDBC OCI drivers, which presumes you are running from a thick-client machine with an Oracle client installation, then support for Oracle Advanced Security and incorporated third-party features is, for the most part, no different from any Oracle thick-client situation. Your use of Advanced Security features is determined by related settings in the SQLNET.ORA file on the client machine, as discussed in the Oracle Advanced Security Administrator's Guide. Refer to that manual for information.

Thin Driver Support for Oracle Advanced Security

Because the Thin driver was designed to be downloadable with applets, one obviously cannot assume that there is an Oracle client installation and a SQLNET.ORA file where the Thin driver is used. This necessitated the design of a new, 100% Java approach to Oracle Advanced Security support.

Java classes that implement Oracle Advanced Security are included in your JDBC classes12.jar file. Security parameters for encryption and integrity, normally set in SQLNET.ORA, are set in a Java properties file instead.

For information about parameter settings, see "Thin Driver Support for Encryption and Integrity".

OCI Driver Support for Encryption and Integrity

If you are using one of the Oracle JDBC OCI drivers, which presumes a thick-client setting with an Oracle client installation, you can enable or disable data encryption or integrity and set related parameters as you would in any Oracle client situation, through settings in the SQLNET.ORA file on the client machine.

To summarize, the client parameters are shown in Table 23-2:

These settings, and corresponding settings in the server, are further discussed in Appendix A of the Oracle Advanced Security Administrator's Guide.

Thin Driver Support for Encryption and Integrity

Thin driver support for data encryption and integrity parameter settings parallels the thick-client support discussed in the preceding section. Corresponding parameters exist under the oracle.net package and can be set through a Java properties object that you would then use in opening your database connection.

If you replace "SQLNET" in the parameter names in Table 23-2 with "oracle.net", you will get the parameter names supported by the Thin driver (but note that in Java, the parameter names are all-lowercase).

Table 23-3 lists the parameter information for the Thin driver. See the next section for examples of how to set these parameters in Java.

Setting Encryption and Integrity Parameters in Java

Use a Java properties object (java.util.Properties) to set the data encryption and integrity parameters supported by the Oracle JDBC Thin driver.

The following example instantiates a Java properties object, uses it to set each of the parameters in Table 23-3, and then uses the properties object in opening a connection to the database:

...
Properties prop = new Properties();
prop.put("oracle.net.encryption_client", "REQUIRED");
prop.put("oracle.net.encryption_types_client", "( DES40 )");
prop.put("oracle.net.crypto_checksum_client", "REQUESTED");
prop.put("oracle.net.crypto_checksum_types_client", "( MD5 )");

OracleDataSource ods = new OracleDataSource();ods.setProperties(prop);ods.setURL("jdbc:oracle:thin:@localhost:1521:main");Connection conn = ods.getConnection();
...

The parentheses around the parameter values in the encryption_types_client and crypto_checksum_types_client settings allow for lists of values. Currently, the Thin driver supports only one possible value in each case; however, in the future, when multiple values are supported, specifying a list will result in a negotiation between the server and the client that determines which value is actually used.

SQLNET.ENCRYPTION_SERVER = ACCEPTED 
  SQLNET.CRYPTO_CHECKSUM_SERVER = ACCEPTED 
  SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER= (MD5, SHA1) 
  SQLNET.ENCRYPTION_TYPES_SERVER= (DES40, DES, 3DES112, 3DES168)
  SQLNET.CRYPTO_SEED = 12345678901234567890

import java.sql.*;
import java.sql.*;
import java.io.*;
import java.util.*;
import oracle.net.ns.*;
import oracle.net.ano.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;

class Employee
{
  public static void main (String args [])
    throws Exception
  {

    Properties props = new Properties();

    try {
      FileInputStream defaultStream = new FileInputStream(args[0]);
      props.load(defaultStream);


      int level = AnoServices.REQUIRED;
      props.put("oracle.net.encryption_client", Service.getLevelString(level));
      props.put("oracle.net.encryption_types_client", "( 3DES168 )");
      props.put("oracle.net.crypto_checksum_client",
                 Service.getLevelString(level));
      props.put("oracle.net.crypto_checksum_types_client", "( MD5 )");
    } catch (Exception e) { e.printStackTrace(); }


    // You can put a database name after the @ sign in the connection URL.
    OracleDataSource ods = new OracleDataSource();
    ods.setURL("jdbc:oracle:thin:@host.example.com:1521:main");
    ods.setConnectionProperties(props);
    Connection conn = ods.getConnection();
    

    // Create a Statement
    Statement stmt = conn.createStatement ();

    // Select the ENAME column from the EMP table
    ResultSet rset = stmt.executeQuery ("select ENAME from EMP");

    // Iterate through the result and print the employee names
    while (rset.next ())
       System.out.println (rset.getString (1));

    conn.close();
  }

}

Using the Oracle Connection Manager

The Oracle Connection Manager is a lightweight, highly-scalable program that can receive Oracle Net packets and re-transmit them to a different server. To a client running Oracle Net, the Connection Manager looks exactly like a database server. An applet that uses the JDBC Thin driver can connect to a Connection Manager running on the Web server host and have the Connection Manager redirect the Oracle Net packets to an Oracle server running on a different host.

Figure 23-1 illustrates the relationship between the applet, the Oracle Connection Manager, and the database.

Figure 23-1 Applet, Connection Manager, and Database Relationship

Description of the illustration applcon.gif

Using the Oracle Connection Manager requires two steps:

• Install and run the Connection Manager.

• Write the connection string that targets the Connection Manager.

There is also discussion of how to connect using multiple connection managers.

cman = (ADDRESS_LIST = 
       (ADDRESS = (PROTOCOL=TCP) 
       (HOST=web-server-host)
       (PORT=1610)))

cman_profile = (parameter_list = 
       (MAXIMUM_RELAYS=512) 
       (LOG_LEVEL=1) 
   (TRACING=YES) 
       (RELAY_STATISTICS=YES) 
       (SHOW_TNS_INFO=YES) 
       (USE_ASYNC_CALL=YES) 
       (AUTHENTICATION_LEVEL=0)
       )

cmctl start

String myURL = 
   "jdbc:oracle:thin:@(description=(address_list=
   (address=(protocol=tcp)(port=1610)(host=webHost))
   (address=(protocol=tcp)(port=1521)(host=oraHost)))
   (connect_data=(INSTANCE_NAME=orcl))
   (source_route=yes))";
   OracleDataSource ods = new OracleDataSource();   ods.setURL(myURL);   ods.setUser("scott");   ods.setPassword("tiger");   Connection conn = ods.getConnection();

Using Signed Applets

In a JDK 1.2.x-based or higher browser, an applet can request socket connection privileges and connect to a database running on a different host than the Web server host. In Netscape 4.0, you perform this by signing your applet (that is, writing a signed applet). You must follow these steps:

1. Sign the applet. For information on the steps you must follow to sign an applet, see Sun Microsystem's Signed Applet Example at:

   http://java.sun.com/security/signExample/index.html 
   
   

2. Include applet code that asks for appropriate permission before opening a socket.

   If you are using Netscape, then your code would include a statement like this:

   netscape.security.PrivilegeManager.enablePrivilege("UniversalConnect"); 
   OracleDataSource ods = new OracleDataSource();
   ods.setURL("jdbc:oracle:thin:scott/tiger@dlsun511:1721:orcl");
   Connection conn = ods.getConnection();
   
   

3. You must obtain an object-signing certificate. See Netscape's Object-Signing Resources page at:

   http://developer.netscape.com/software/signedobj/index.html
   

This site provides information on obtaining and installing a certificate.

For more information on writing applet code that asks for permissions, see Netscape's Introduction to Capabilities Classes at:

http://developer.netscape.com/docs/manuals/signedobj/capabilities/contents.htm

For information about the Java Security API, including signed applet examples, see the following Sun Microsystems site:

http://java.sun.com/security

Configuring a Firewall for Applets that use the JDBC Thin Driver

The instructions in this section assume that you are running an Oracle Net-compliant firewall.

Java applets do not have access to the local system—that is, they cannot get the hostname or environment variables locally—because of security limitations. As a result, the JDBC Thin driver cannot access the hostname on which it is running. The firewall cannot be provided with the hostname. To allow requests from JDBC Thin clients to go through the firewall, you must do the following two things to the firewall's list of rules:

• Add the IP address (not the hostname) of the host on which the JDBC applet is running.

• Ensure that the hostname "__jdbc__" never appears in the firewall's rules. This hostname has been hard-coded as a false hostname inside the driver to force an IP address lookup. If you do enter this hostname in the list of rules, then every applet using Oracle's JDBC Thin driver will be able to go through your firewall.

By not including the Thin driver's hostname, the firewall is forced to do an IP address lookup and base its access decision on the IP address, instead of the hostname.

Writing a URL to Connect through a Firewall

To write a URL that allows you to connect through a firewall, you must specify the name of the firewall host and the name of the database host to which you want to connect.

For example, if you want to connect to a database on host oraHost, listening on port 1521, with SID ORCL, and you are going though a firewall on host fireWallHost, listening on port 1610, then use the following URL:

OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:" +
      "@(description=(address_list=" +
      (address=(protocol=tcp)(host=<firewall-host>)(port=1610))" +
      "(address=(protocol=tcp)(host=oraHost)(port=1521)))" +
      "(source_route=yes)" +
      "(connect_data=(INSTANCE_NAME=orcl)))");
);
ods.setUser("scott");
ods.setPassword("tiger");
Connection conn = ods.getConnection();

String connString =
"jdbc:oracle:thin:@ixta.us.oracle.com:1521:orcl";

OracleDataSource ods = new OracleDataSource();
ods.setURL(connString);
ods.setUser("scott");
ods.setPassword("tiger");
Connection conn = ods.getConnection();

The first element in the address_list represents the connection to the firewall. The second element represents the database to which you want to connect. Note that the order in which you specify the addresses is important.

Notice that you can also write the preceding URL in this format:

String connString = 
      "jdbc:oracle:thin:@(description=(address_list=
      (address=(protocol=tcp)(port=1600)(host=fireWallHost))
      (address=(protocol=tcp)(port=1521)(host=oraHost)))
      (connect_data=(INSTANCE_NAME=orcl))
      (source_route=yes))";
OracleDataSource ods = new OracleDataSource();
ods.setURL(connString);
ods.setUser("scott");
ods.setPassword("tiger");
Connection conn = ods.getConnection();

When your applet uses a URL similar to the one above, it will behave as if it were connected to the database on host oraHost.

For more information on the parameters used in the above example, see the Oracle Net Services Administrator's Guide. For more information on how to configure a firewall, please see your firewall's documentation or contact your firewall vendor.

CODE, HEIGHT, and WIDTH

The HTML page that runs the applet must have an APPLET tag with an initial width and height to specify the size of the applet display area. You use the HEIGHT and WIDTH parameters to specify the size, measured in pixels. This size should not count any windows or dialogs that the applet opens.

The APPLET tag must also specify the name of the file that contains the applet's compiled Applet subclass—specify the file name with the CODE parameter. Any path must be relative to the base URL of the applet—the path cannot be absolute.

In the following example, JdbcApplet.class is the name of the applet's compiled applet subclass:

<APPLET CODE="JdbcApplet" WIDTH=500 HEIGHT=200> 
</APPLET>

If you use this form of the CODE tag, then the classes for the applet and the classes for the JDBC Thin driver must be in the same directory as the HTML page.

Notice that in the CODE specification, you do not include the file name extension ".class".

CODEBASE

The CODEBASE parameter is optional and specifies the base URL of the applet; that is, the name of the directory that contains the applet's code. If it is not specified, then the document's URL is used. This means that the classes for the applet and the JDBC Thin driver must be in the same directory as the HTML page. For example, if the current directory is my_Dir:

<APPLET WIDTH=500 HEIGHT=200 CODE=JdbcApplet CODEBASE="."
</APPLET>

The entry CODEBASE="." indicates that the applet resides in the current directory (my_Dir). If the value of codebase was set to Applet_Samples, for example:

CODEBASE="Applet_Samples"

This would indicate that the applet resides in the my_Dir/Applet_Samples directory.

ARCHIVE

The ARCHIVE parameter is optional and specifies the name of the archive file (either a .zip or .jar file), if applicable, that contains the applet classes and resources the applet needs. Oracle recommends using a .zip file or .jar file, which saves many extra roundtrips to the server.

The .zip (or .jar) file will be preloaded. If you have more than one archive in the list, separate them with commas. In the following example, the class files are stored in the archive file JdbcApplet.zip:

<APPLET CODE="JdbcApplet" ARCHIVE="JdbcApplet.zip" WIDTH=500 HEIGHT=200>
</APPLET>

Connecting with the OracleDriver Class defaultConnection() Method

The oracle.jdbc.OracleDriver class defaultConnection() method is an Oracle extension and always returns the same connection object. Even if you invoke this method multiple times, assigning the resulting connection object to different variable names, just a single connection object is reused.

You do not need to include a connect string in the defaultConnection() call. For example:

import java.sql.*; 
import oracle.jdbc.*; 
  
class JDBCConnection 
{ 
  public static Connection connect() throws SQLException 
  { 
    Connection conn = null; 
    try {  
      // connect with the server-side internal driver
         OracleDriver ora = new OracleDriver(); 
         conn = ora.defaultConnection(); 
      } 
 
    } catch (SQLException e) {...}
    return conn; 
  } 
}

Note that there is no conn.close() call in the example. When JDBC code is running inside the target server, the connection is an implicit data channel, not an explicit connection instance as from a client. It should typically not be closed.

If you do call the close() method, be aware of the following:

• All connection instances obtained through the defaultConnection() method, which actually all reference the same connection object, will be closed and unavailable for further use, with state and resource cleanup as appropriate. Executing defaultConnection() afterward would result in a new connection object.

• Even though the connection object is closed, the implicit connection to the database will not be closed.

Connecting with the OracleDataSource.getConnection() Method

To connect to the internal server connection from code that is running within the target server, you can use the OracleDataSource.getConnection() method with either of the following URLs:

OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:kprb:");
Connection conn = ods.getConnection();

or:

OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:default:connection:");
Connection conn = ods.getConnection();

Any user name or password you include in the URL is ignored in connecting to the server default connection.

The OracleDataSource.getConnection() method returns a new Java Connection object every time you call it. Note that although the method is not creating a new physical connection (only a single implicit connection is used), it is returning a new object.

The fact that OracleDataSource.getConnection() returns a new connection object every time you call it is significant if you are working with object maps (or "type maps"). A type map is associated with a specific Connection object and with any state that is part of the object. If you want to use multiple type maps as part of your program, then you can call getConnection() to create a new Connection object for each type map.

Example

Following is an example of server-side error processing:

try 
{
   // should get "ORA-942: table or view does not exist"
   stmt.execute("drop table no_such_table");
}
catch (OracleSQLException e)
{
   System.out.println(e.getMessage());
   // prints "ORA-942: table or view does not exist"

   System.out.println(e.getNumParameters());
   // prints "1"

   Object[] params = e.getParameters();
   System.out.println(params[0]);
   // prints "NO_SUCH_TABLE"
}

Loading Class Files into the Server

Consider a case where you have three class files in your application: Foo1.class, Foo2.class, and Foo3.class. The following three examples demonstrate: 1) specifying the individual class file names; 2) specifying the class file names using a wildcard; and 3) specifying a JAR file that contains the class files.

Each class is written into its own class schema object in the server.

These three examples use the default OCI driver in loading the files:

loadjava -user scott/tiger Foo1.class Foo2.class Foo3.class

or:

loadjava -user scott/tiger Foo*.class

or:

loadjava -user scott/tiger Foo.jar

Or use the following command to load with the Thin driver (specifying the -thin option and an appropriate URL):

loadjava -thin -user scott/tiger@localhost:1521:ORCL Foo.jar

(Whether to use an OCI driver or the Thin driver to load classes depends on your particular environment and which performs better for you.)

Loading Source Files into the Server

If you enable the loadjava -resolve option in loading a .java source file, then the server-side compiler will compile your application as it is loaded, resulting in both a source schema object for the original source code, and one or more class schema objects for the compiled output.

If you do not specify -resolve, then the source is loaded into a source schema object without any compilation. In this case, however, the source is implicitly compiled the first time an attempt is made to use a class defined in the source.

For example, run loadjava as follows to load and compile Foo.java, using the default OCI driver:

loadjava -user scott/tiger -resolve Foo.java

Or use the following command to load with the Thin driver (specifying the -thin option and an appropriate URL):

loadjava -thin -user scott/tiger@localhost:1521:ORCL -resolve Foo.java

Either of these will result in appropriate class schema objects being created in addition to the source schema object.