Difference between revisions of "Configuring a Database Login (ELUG)"

Latest revision as of 07:32, 20 August 2008

In a relational database project, EclipseLink retrieves the table information from the database, for each descriptor. Each Workbench project contains an associated database. You can create multiple logins for each database.

The following table lists the configurable options for a database login.

Configuring a Relational Database Platform at the Session Level

For each database session, you must specify the database platform (such as Oracle9i Database Server). This platform configuration overrides the platform at the project level, if configured.

For more information, see the following:

• Configuring Relational Database Platform at the Project Level
• Data Source Platform Types

How to Configure a Relational Database Platform at the Session Level Using 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.
   Login Tab, Connection Subtab, Database Platform Option
   
4. Select the database platform from the menu of options. This menu includes all instances of DatabasePlatform in the EclipseLink classpath.

Configuring Database Login Connection Options

You configure connection information at the session level for your EclipseLink application. This information is stored in the sessions.xml file. The EclipseLink runtime uses this information whenever you perform a persistence operation using the session in your application.

This connection configuration overrides the connection information at the project level, if configured. For more information about project-level configuration, see Configuring Development and Deployment Logins.

This connection configuration is overridden by the connection information at the connection pool level. For more information, see Configuring Connection Pool Connection Options.

How to Configure Database Login Connection Options Using 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.
   Login Tab, Connection Subtab, Database Driver
   
   Login Tab, Connection Subtab
   
4. Complete each field on the Connection subtab.

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

¹ Applicable only when Database Driver is set to Driver Manager.
² Applicable only when Database Driver is set to J2EE Datasource.

Configuring Sequencing at the Session Level

You configure EclipseLink sequencing at the session or project level to tell EclipseLink how to obtain sequence values: that is, what type of sequences to use.

You can configure a session directly by using a session-level sequence configuration to override project-level sequence configuration, on a session-by-session basis, if required.

Using Workbench (see How to Configure Sequencing at the Session Level Using Workbench), you can configure table sequencing (see Table Sequencing) and native sequencing (Native Sequencing with an Oracle Database Platform and Native Sequencing with a Non-Oracle Database Platform), and you can configure a preallocation size that applies to all sequences (see Sequencing and Preallocation Size).

Using Java (see How to Configure Sequencing at the Session Level Using Java), you can configure any sequence type that EclipseLink supports ( Sequencing Types). You can create any number and combination of sequences. You can create a sequence object explicitly or use the default sequence that the platform creates. You can associate the same sequence with more than one descriptor and you can configure a separate preallocation size for each descriptor's sequence.

If you are migrating a WebLogic CMP application to OC4J and EclipseLink persistence, after migration, you must manually configure your project to use EclipseLink unary sequence tables if your application originally used single-column sequence tables in WebLogic.

After configuring the sequence type at the session (or project) level, to enable sequencing, you must configure a descriptor with a sequence field and a sequence name (see Configuring Sequencing at the Descriptor Level).

For more information about sequencing, see Sequencing in Relational Projects.

How to Configure Sequencing at the Session Level Using 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.
   Login Tab, Sequencing Subtab
   
4. Complete each field on Login–Sequencing subtab.

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

How to Configure Sequencing at the Session Level Using Java

Using Java, you can perform the following sequence configurations:

• Platform Default Sequence
• Multiple Sequences
• Query Sequencing

Using the Platform Default Sequence

After you configure your login with a platform (see Configuring a Relational Database Platform at the Session Level), you can use the default sequence that the platform provides.

If you associate a descriptor with an unspecified sequence, the EclipseLink runtime will create an instance of DefaultSequence to provide sequencing for that descriptor. For more information, see Configuring the Platform Default Sequence.

You can access the default platform sequence directly as the Accessing the Platform Default Sequence example shows. For example, by default, a DatabasePlatform creates a table sequence using the default table and column names (see Table Sequencing).

Accessing the Platform Default Sequence

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

To avoid having to clone the platform default sequence, you can use the DefaultSequence class– a wrapper for the platform default sequence–-as the following example shows. The new sequence named EMP_SEQ will be of the same type as the platform default sequence.

Using the DefaultSequence Class

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

You can override the default platform sequence, as the Overriding the Platform Default Sequence example shows. In this example, dbLogin owns a DatabasePlatform that provides a default sequence of type TableSequence. After setting the default sequence to type UnaryTableSequence, when you use the DefaultSequence class, it will access the new default sequence type. In this example, the sequence named EMP_SEQ will be of type UnaryTableSequence and have a preallocation size of 75.

Overriding the Platform Default Sequence

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

Configuring Multiple Sequences

In addition to using the platform default sequence (see Using the Platform Default Sequence), you can explicitly create sequence instances and configure a Login with any combination of sequence types, each with their own preallocation size, as the Configuring Multiple Sequences Explicitly example shows. In this example, the sequence named EMP_SEQ will provide sequence values exclusively for instances of the Employee class and ADD_SEQ will provide sequence values exclusively for instances of the Address class. The sequence named PHONE_SEQ will use the platform default sequence with a preallocation size of 30 to provide sequence values for the Phone class.

Configuring Multiple Sequences Explicitly

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

If login owned a DatabasePlatform (whose default sequence type is TableSequence), you could configure your sequences using the platform default sequence type, as the Configuring Multiple Sequences Using the Default Sequence Type example shows. In this example, sequences EMP_SEQ and PHONE_SEQ share the same TableSequence table: EMP_SEQ and PHONE_SEQ represent rows in this table.

Configuring Multiple Sequences Using the Default Sequence Type

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

Configuring Query Sequencing

You can configure the query that EclipseLink uses to update or read a sequence value for any sequence type that extends QuerySequence.

In most applications, the queries that EclipseLink automatically uses are sufficient. However, if your application has special sequencing needs–-for example, if you want to use stored procedures for sequencing–-then you can configure the update and read queries that the EclipseLink sequence uses.

The following example illustrates how to specify a stored procedure that updates a sequence and returns the new sequence value with a single SQL select query. In this example, the stored procedure is named UPDATE_SEQ. It contains one input argument–-the name of the sequence to update (SEQ_NAME), and one output argument–-the value of the sequence after the updated (SEQ_COUNT). The stored procedure increments the sequence value associated with the sequence named SEQ_NAME and returns the new sequence value in the output argument named SEQ_COUNT.

Using a Stored Procedure for both Sequence Update and Select

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

Using a Stored Procedure for Sequence Updates Only and Using a Stored Procedure for Sequence Selects Only examples illustrate how to specify separate stored procedures for sequence update and select actions.

In the Using a Stored Procedure for Sequence Updates Only example, the stored procedure is named UPDATE_SEQ, and it contains one input argument--the name of the sequence to update (SEQ_NAME). The stored procedure increments the sequence value associated with the sequence named SEQ_NAME.

Using a Stored Procedure for Sequence Updates Only

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

In the Using a Stored Procedure for Sequence Selects Only example, the stored procedure is named SELECT_SEQ and it takes one argument: the name of the sequence to select from (SEQ_NAME). The stored procedure reads one data value: the current sequence value associated with the sequence name SEQ_NAME.

Using a Stored Procedure for Sequence Selects Only

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

You can also create a QuerySequence directly and add it to your login, as this example shows.

Using the QuerySequence Class

// 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));

Configuring a Table Qualifier

Some databases (such as Oracle Database and DB2) require that all tables be qualified by an identifier. This can be the creator of the table or database name on which the table exists. When you specify a table qualifier, EclipseLink uses this qualifier for all of the tables it references. Specify a table qualifier only if required and only if all of the tables have the same qualifier.

How to Configure a Table Qualifier Using 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.
   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.

How to Configure a Table Qualifier Using Workbench

To set the default qualifier for all tables, use the org.eclipse.persistence.sessions.DatabaseLogin method setTableQualifier.

Configuring JDBC Options

Most JDBC drivers support the run-time configuration of various options to customize driver operation to meet user needs. EclipseLink provides direct support (in API and the Workbench) for many of the most important options, as this section describes, as well as more advanced options (see Configuring Advanced Options).

You can also configure additional options by specifying properties (see Configuring Properties).

How to Configure JDBC Options Using 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.
   Login Tab, Options Subtab, JDBC Options
   
4. Complete the JDBC fields on the tab.

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

¹ For more information, see How to Use Parameterized SQL (Parameter Binding) and Prepared Statement Caching for Optimization.
² If you are using the MySQLPlatform database platform (see Data Source Platform Types), use JDBC batch writing (do not use EclipseLink batch writing). For more information, see How to Use Batch Writing for Optimization.

How to Configure JDBC Options Using Java

To enable prepared statement caching for all queries, configure at the Login level, as the following example shows. For more information, see How to Use Parameterized SQL (Parameter Binding) and Prepared Statement Caching for Optimization.

Prepared Statement Caching at the Login Level

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

Parameter binding is enabled by default in EclipseLink. To disable binding, configure at the Login level, as the following example shows. For more information, see How to Use Parameterized SQL (Parameter Binding) and Prepared Statement Caching for Optimization.

Disabling Parameter Binding at the Login Level

databaseLogin.dontBindAllParameters();

To enable JDBC batch writing, use Login method useBatchWriting, as this example shows:

Using JDBC Batch Writing

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

Configuring Advanced Options

Most JDBC drivers support the run-time configuration of various options to customize driver operation to meet user needs. EclipseLink provides direct support (in API and Workbench) for many of the most important options (see Configuring JDBC Options), as well as more advanced options, as this section describes.

You can also configure additional options by specifying properties (see Configuring Properties).

How to Configure Advanced Options Using 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.
   Login Tab, Options Subtab, Advanced Options
   
4. Complete the Advanced Options fields on the tab.

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

How to Configure Advanced Options Using Java

Use the following methods of org.eclipse.persistence.sessions.DatabaseLogin to configure advanced options:

• setShouldForceFieldNamesToUpperCase–By default, EclipseLink 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, EclipseLink 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, EclipseLink 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 Configuring Properties).

Configuring Oracle Database Proxy Authentication

You can configure a session to use Oracle Database proxy authentication with an Oracle Database platform in JSE applications and JEE applications using Oracle JDBC driver release 10.1.0.2.0 or later. If an external connection pool is used, then it should either contain Oracle connections directly (OracleDataSource), or the application server should allow access to the underlying vendor-specific connection in the managed connection. To achieve the latter, the server platform class in Eclipselink needs to implement the java.sql.Connection method unwrapConnection(java.sql.Connection connection).

There is no Workbench support for this feature. To configure EclipseLink to use Oracle Database proxy authentication, you must use Java (see How to Configure Oracle Database Proxy Authentication Using Java).

For more information, see Oracle Database Proxy Authentication.

For information on proxy authentication in JPA applications, see Configuring Oracle Database Proxy Authentication for a JPA Application.

You can use EclipseLink support for Oracle Database proxy authentication by doing the following:

• Providing Authenticated Reads and Writes of Secured Data Through the Use of an Exclusive Isolated Client Session
• Providing Authenticated Writes for Database Auditing Purposes with a Client Session
• Providing Authenticated Reads and Writes with a Database Session

Providing Authenticated Reads and Writes of Secured Data Through the Use of an Exclusive Isolated Client Session

In this configuration, the client session is an isolated client session (see Isolated Client Sessions) that uses an exclusive proxy connection. You must acquire the client session using properties that specify proxy authentication user credentials. Reads and writes of secured data are performed through the proxy-authenticated connection. Reads of nonsecured data occur through nonproxy-authenticated connections.

If you are using Oracle Private Virtual Database (VPD) (see Isolated Client Sessions and Oracle Virtual Private Database (VPD)), use this configuration to set up VPD support entirely in the database. That is, rather than making the isolated client session execute SQL (see Using PostAcquireExclusiveConnection Event Handler and Using PreReleaseExclusiveConnection Event Handler), the database performs the required set up in an after login trigger using the proxy session_user.

Providing Authenticated Writes for Database Auditing Purposes with a Client Session

In this configuration, isolated data or exclusive connections are not required. You must acquire the client session using properties that specify the proxy authentication user credentials.

Writes are performed through the proxy-authenticated connection. Reads occur through nonproxy-authenticated connections. This enables the database auditing process to access the user that performed the write operations.

Providing Authenticated Reads and Writes with a Database Session

In this configuration, you use a DatabaseSession with proxy properties.

How to Configure Oracle Database Proxy Authentication Using Java

To configure EclipseLink 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 the 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 this table.

      Proxy Properties for User Name Authentication

      Property Name	Property Value
      "eclipselink.oracle.proxy-type"	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 the 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 this table.

      Proxy Properties for User Name and Password Authentication

      Property Name	Property Value
      "eclipselink.oracle.proxy-type"	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 the 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 this table.

      Proxy Properties for Distinguished Name Authentication

      Property Name	Property Value
      "eclipselink.oracle.proxy-type"	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 the 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 this table.

      Proxy Properties for User Name Authentication

      Property Name	Property Value
      "eclipselink.oracle.proxy-type"	PROXYTYPE_CERTIFICATE
      PROXY_CERTIFICATE	byte[] {EncryptedCertificate}
      PROXY_ROLES	String[] {"role1", "role2", ...}

2. Configure your session login using Java code. Do this through a SessionCustomizer.

   // If using Oracle VPD support,set the connection policy to exclusive 
   policy.setShouldUseExclusiveConnection(true);
   

3. Acquire a proxy-authenticated session using properties that specify the user credentials:

   Session session = server.acquireClientSession();
   session.setProperty("eclipselink.oracle.proxy-type", OracleConnection.PROXYTYPE_USER_NAME);
   session.setProperty(oracle.jdbc.OracleConnection.PROXY_USER_NAME, "sarah");
   

Copyright Statement