98 Configuring a Database Login

98.2.1 How to Configure a Relational Database Platform at the Session Level Using TopLink Workbench

To specify the database platform options for a relational server (or database) session login, use this procedure:

1. Select a relational server (or database) session in the Navigator. Its properties appear in the Editor.

2. Click the Login tab. The Login tab appears.

3. Click the Connection subtab. The Connection subtab appears.

4. Figure 98-1 Login Tab, Connection Subtab, Database Platform Option

   
   Description of "Figure 98-1 Login Tab, Connection Subtab, Database Platform Option"
   
   

Select the database platform from the menu of options. This menu includes all instances of DatabasePlatform in the TopLink classpath.

98.3.1 How to Configure Database Login Connection Options Using TopLink Workbench

To specify the connection options for a relational server (or database) session login, use this procedure:

1. Select a relational server (or database) session in the Navigator. Its properties appear in the Editor.

2. Click the Login tab. The Login tab appears.

3. Click the Connection subtab. The Connection subtab appears.

   Figure 98-2 Login Tab, Connection Subtab, Database Driver

   
   Description of "Figure 98-2 Login Tab, Connection Subtab, Database Driver"
   
   

   Figure 98-3 Login Tab, Connection Subtab

   
   Description of "Figure 98-3 Login Tab, Connection Subtab"
   
   

Use the following information to enter data in the driver fields on the tab:

^Footnote 1 Applicable only when Database Driver is set to Driver Manager.

^Footnote 2 Applicable only when Database Driver is set to J2EE Datasource.

98.4.1 How to Configure Sequencing at the Session Level Using TopLink Workbench

To specify the sequencing information for a relational server (or database) session, use this procedure:

1. Select the session object in the Navigator.

2. Click the Login tab in the Editor.

3. Click the Sequencing subtab. The Sequencing subtab appears.

   Figure 98-4 Login Tab, Sequencing Subtab

   
   Description of "Figure 98-4 Login Tab, Sequencing Subtab"
   
   

Use the following information to enter data in each field of the Sequencing subtab to configure the persistence type:

98.4.2 How to Configure Sequencing at the Session Level Using Java

Using Java, you can perform the following sequence configurations:

• Using the Platform Default Sequence

• Configuring Multiple Sequences

• Configuring Query Sequencing

// assume that dbLogin owns a DatabasePlatform
TableSequence tableSeq2 = ((TableSequence)dbLogin.getDefaultSequence()).clone();
tableSeq2.setName("EMP_SEQ");
tableSeq2.setPreallocationSize(75);
dbLogin.addSequence(tableSeq2);

login.addSequence(
    new DefaultSequence("EMP_SEQ", 75)
);

// assume that dbLogin owns a DatabasePlatform
Sequence unaryTableSequence = new UnaryTableSequence();
unaryTableSequence.setPreallocationSize(40);
dbLogin.setDefaultSequence(unaryTableSequence);
dbLogin.addSequence(
    new DefaultSequence("EMP_SEQ", 75) // UnaryTableSequence
);

login.addSequence(new TableSequence("EMP_SEQ", 25));
login.addSequence(new DefaultSequence("PHONE_SEQ", 30));
login.addSequence(new UnaryTableSequence("ADD_SEQ", 55));
login.addSequence(new NativeSequence("NAT_SEQ", 10));

login.addSequence(new DefaultSequence("EMP_SEQ", 25));
login.addSequence(new DefaultSequence("PHONE_SEQ", 30));
login.addSequence(new UnaryTableSequence("ADD_SEQ", 55));
login.addSequence(new NativeSequence("NAT_SEQ", 10));

ValueReadQuery seqReadQuery = new ValueReadQuery();
StoredProcedureCall spCall = new StoredProcedureCall();
spCall.setProcedureName("UPDATE_SEQ");
seqReadQuery.addNamedArgument("SEQ_NAME");
seqReadQuery.addNamedOutputArgument("SEQ_COUNT");
seqReadQuery.setCall(spCall);
((QuerySequence)login.getDefaultSequence()).setSelectQuery(seqReadQuery);

DataModifyQuery seqUpdateQuery = new DataModifyQuery();
StoredProcedureCall spCall = new StoredProcedureCall();
spCall.setProcedureName("UPDATE_SEQ");
seqUpdateQuery.addNamedArgument("SEQ_NAME");
seqUpdateQuery.setCall(spCall);
((QuerySequence)login.getDefaultSequence()).setUpdateQuery(seqUpdateQuery);

ValueReadQuery seqReadQuery = new ValueReadQuery();
StoredProcedureCall spCall = new StoredProcedureCall();
spCall.setProcedureName("SELECT_SEQ");
seqReadQuery.addArgument("SEQ_NAME");
seqReadQuery.setCall(spCall);
login.((QuerySequence)getDefaultSequence()).setSelectQuery(seqReadQuery)

// Use the two-argument constructor: pass in sequence name and preallocation size.
// Alternatively, you can use zero- or one-argument (sequence name) constructor
login.addSequence(new QuerySequence("SEQ1", 75));

98.5.1 How to Configure a Table Qualifier Using TopLink Workbench

To specify a table qualifier, use this procedure:

1. Select a relational server (or database) session in the Navigator. Its properties appear in the Editor.

2. Click the Login tab. The Login tab appears.

3. Click the Options subtab. The Options subtab appears.

   Figure 98-5 Login Tab, Options Subtab, Table Qualifier Field

   
   Description of "Figure 98-5 Login Tab, Options Subtab, Table Qualifier Field"
   
   

In the Table Qualifier field enter the identifier used to qualify references to all tables in this database.

98.5.2 How to Configure a Table Qualifier Using Java

To set the default qualifier for all tables, use the DatabaseLogin method setTableQualifier.

98.6.1 How to Configure JDBC Options Using TopLink Workbench

To specify the JDBC options for a relational server (or database) session login, use this procedure:

1. Select a relational server (or database) session in the Navigator. Its properties appear in the Editor.

2. Click the Login tab. The Login tab appears.

3. Click the Options subtab. The Options subtab appears.

   Figure 98-6 Login Tab, Options Subtab, JDBC Options

   
   Description of "Figure 98-6 Login Tab, Options Subtab, JDBC Options"
   
   

Use this table to enter data in the fields on the Options subtab to select the JDBC options to use with this session login:

^Footnote 1 For more information, see Section 12.11.5, "How to Use Parameterized SQL (Parameter Binding) and Prepared Statement Caching for Optimization".

^Footnote 2 If you are using the MySQL4 database platform (see Section 96.1.3, "Data Source Platform Types"), use JDBC batch writing (do not use TopLink batch writing). For more information, see Section 12.11.3, "How to Use Batch Writing for Optimization".

98.6.2 How to Configure JDBC Options Using Java

To enable prepared statement caching for all queries, configure at the Login level, as Example 98-10 shows. For more information, see Section 12.11.5, "How to Use Parameterized SQL (Parameter Binding) and Prepared Statement Caching for Optimization".

databaseLogin.cacheAllStatements();
databaseLogin.setStatementCacheSize(100);

Parameter binding is enabled by default in TopLink. To disable binding, configure at the Login level, as Example 98-11 shows. For more information, see Section 12.11.5, "How to Use Parameterized SQL (Parameter Binding) and Prepared Statement Caching for Optimization".

databaseLogin.dontBindAllParameters();

To enable JDBC batch writing, use Login method useBatchWriting, as Example 98-12 shows:

project.getLogin().useBatchWriting();
project.getLogin().setMaxBatchWritingSize(100);

98.7.1 How to Configure Advanced Options Using TopLink Workbench

To specify the advanced options for a relational server (or database) session login, use this procedure:

1. Select a relational server (or database) session in the Navigator. Its properties appear in the Editor.

2. Click the Login tab. The Login tab appears.

3. Click the Options subtab. The Options subtab appears.

   Figure 98-7 Login Tab, Options Subtab, Advanced Options

   
   Description of "Figure 98-7 Login Tab, Options Subtab, Advanced Options"
   
   

Use this table to enter data in the fields on the Options subtab to select the advanced options to use with this session login:

98.7.2 How to Configure Advanced Options Using Java

Use the following methods of DatabaseLogin to configure advanced options:

• setShouldForceFieldNamesToUpperCase–By default, TopLink uses the case of field names as returned by the database. If your application expects field names to be uppercase but the database does not return consistent case (for example, if you accessing different databases), use this method.

• setShouldOptimizeDataConversion–By default, TopLink optimizes data access by accessing the data from JDBC in the format the application requires. If you are using an older JDBC driver that does not perform data conversion correctly and conflicts with this optimization, set this to false.

• setShouldTrimStrings–By default, TopLink discards the trailing blanks from CHAR field values. To read and write CHAR field values literally (including any trailing blanks), set this to false.

• setProperties–Set this to true to enable the use of properties for this DatabaseLogin (see Section 97.5, "Configuring Properties").

98.8.1 How to Configure Oracle Database Proxy Authentication Using Java

You configure Oracle Database proxy authentication by customizing your session in your Java code, such as through a SessionCustomizer when using the sessions.xml file. You can wrap a configured TopLink DatasourceLogin JNDIConnector with a TopLink proxy connector instance (from oracle.toplink.platform.database.oracle) appropriate for your JDBC driver and to configure proxy authentication properties.

Regardless of whether you are using the Oracle JDBC Thin driver or OCI driver, use the OracleJDBC10_1_0_2ProxyConnector and the property constants defined in oracle.jdbc.OracleConnection.

The properties to set are shown in Tables a through d.

login.setProperty(
    "proxytype", Integer.toString(OracleConnection.PROXYTYPE_USER_NAME));

To configure TopLink to use Oracle Database proxy authentication, do the following:

1. Decide on the proxy type you want to use and create appropriate users and roles.

   1. User Name Authentication:

      To authenticate a proxy user sarah by user name only, create the user account on Oracle Database using the following:

      alter user sarah grant connect through dbadminuser
          with roles clerk, reports;
      

      In this case, you will need to set the proxy properties shown in Table 98-2.

      Table 98-2 Proxy Properties for User Name Authentication

      Property Name	Property Value
      "proxytype"	PROXYTYPE_USER_NAME
      PROXY_USER_NAME	"sarah"
      PROXY_ROLES	String[] {"role1", "role2", ...}

      
      

   2. User Name and Password Authentication:

      To authenticate a proxy user sarah by user name and password, create the user account on Oracle Database using the following:

      alter user sarah grant connect through dbadminuser
          authenticated using password
          with roles clerk, reports;
      

      In this case, you will need to set the proxy properties shown in Table 98-3.

      Table 98-3 Proxy Properties for User Name and Password Authentication

      Property Name	Property Value
      "proxytype"	PROXYTYPE_USER_NAME
      PROXY_USER_NAME	"sarah"
      PROXY_PASSWORD	"passwordforsarah"
      PROXY_ROLES	String[] {"role1", "role2", ...}

      
      

   3. Distinguished Name Authentication:

      To authenticate a proxy user sarah by globally unique distinguished name, create the user account on Oracle Database using the following:

      create user sarah identified globally as
          'CN=sarah,OU=americas,O=oracle,L=city,ST=ca,C=us';
      alter user sarah grant connect through dbadminuser
          authenticated using distinguished name
          with roles clerk, reports;
      

      In this case, you will need to set the proxy properties shown in Table 98-4.

      Table 98-4 Proxy Properties for Distinguished Name Authentication

      Property Name	Property Value
      "proxytype"	PROXYTYPE_DISTINGUISHED_NAME
      PROXY_DISTINGUISHED_NAME	"CN=sarah,OU=americas,O=oracle,L=city,ST=ca,C=us"
      PROXY_ROLES	String[] {"role1", "role2", ...}

      
      

   4. Certificate Authentication:

      To authenticate a proxy user sarah by encrypted distinguished name, create the user account on Oracle Database using the following:

      alter user sarah grant connect through dbadminuser
          authenticated using certificate
          with roles clerk, reports;
      

      In this case, you will need to set the proxy properties shown in Table 98-2.

      Table 98-5 Proxy Properties for User Name Authentication

      Property Name	Property Value
      "proxytype"	PROXYTYPE_CERTIFICATE
      PROXY_CERTIFICATE	byte[] {EncryptedCertificate}
      PROXY_ROLES	String[] {"role1", "role2", ...}

      
      

2. Configure your session login using Java code. Do this through a SessionCustomizer when using the sessions.xml file.

   The following example demonstrates how you can wrap the already specified JNDIConnector with the appropriate TopLink proxy authentication connector. You can set the server session's default connection policy to the same proxy authenticated login.

   If you use Oracle VPD (Section 87.5.1, "Isolated Client Sessions and Oracle Virtual Private Database (VPD)"), you should set the connection policy to use exclusive connections, and the descriptor for secured data to isolated (Section 119.13, "Configuring Cache Isolation at the Descriptor Level").

   Login login = server.getDatasourceLogin();
   // Make sure that external connection pooling is used
   login.setUsesExternalConnectionPooling(true);
   // Wrap JNDIConnector with OracleJDBC10_1_0_2ProxyConnector
   login.setConnector(
       new OracleJDBC10_1_0_2ProxyConnector(
           ((JNDIConnector)login.getConnector()).getName()));
   ConnectionPolicy policy = server.getDefaultConnectionPolicy();
   policy.setPoolName(null);
   policy.setLogin(login);
   // If using Oracle VPD support, set the connection policy to exclusive 
   
   policy.setShouldUseExclusiveConnection(true);
   

   Note that you may experience problems when using a data source provided by the application server. In this case, instead of using

   login.setConnector(new OracleJDBC10_1_0_2ProxyConnector
                             (((JNDIConnectorlogin.getConnector()).getName()));
   

   create the data source, as follows:

   oracle.jdbc.pool.OracleDataSource ds = new oracle.jdbc.pool.OracleDataSource();
   ds.setUser("MyMainUser");
   ds.setPassword("MyPassword");
   ds.setUrl("jdbc:oracle:thin:@MyServer:1521:MyDb");
   login.setConnector( new OracleJDBC10_1_0_2ProxyConnector(ds));
   

3. Acquire a proxy-authenticated client session through specifying a ConnectionPolicy with this user's credentials.

   ConnectionPolicy policy = 
       (ConnectionPolicy)server.getDefaultConnectionPolicy().clone();
   Login login = (Login)policy.getLogin().clone;
   // Set proxy properties into connection policy's login
   login.setProperty("proxytype" , OracleOCIConnectionPool.PROXYTYPE_USER_NAME);
   login.setProperty(OracleOCIConnectionPool.PROXY_USER_NAME ,"sarah");
   policy.setLogin(login);
   Session session = server.acquireClientSession(policy);