Oracle Fusion Middleware Administration Guide for Oracle Directory Server Enterprise Edition

Configuring and Testing the JDBC Data View

The following tasks assume that a MySQL database is installed, running and populated with data, and that the MySQL database has the following characteristics:

The following table describes the tables in the database, and their composite fields. You need this information to set up the JDBC data view.

MySQL Table 








ProcedureTo Configure the JDBC Data View

  1. Create a JDBC data source named mysql1 for the SQL database.

    % dpconf create-jdbc-data-source -b sample_sql \
     -B jdbc:mysql:// \
     -J file:/net/host2.example/local/mysql/lib/jdbc.jar \
     -S com.mysql.jdbc.Driver mysql1

    Note –

    You can set the number of connections for JDBC data source using the num-connection-incr(5dpconf), num-connection-init(5dpconf), and num-connection-limit(5dpconf) JDBC data source properties.

  2. Specify the user name and password file for the SQL database.

    % dpconf set-jdbc-data-source-prop mysql1 db-pwd:sqlpwd db-user:rootUser

    The sqlpwd and rootUser are the password and username of the authenticating user and these credentials are stored in the database. You must configure the proxy server with username and password when configuring a JDBC data source.

    The db-vendor property of the JDBC data source should be set using set-jdbc-data-source-prop if a third party JDBC driver, which is other than the one provided by DB Vendor, is used to connect to the RDBMS back-end. This data is used to construct vendor specific SQL statements, whenever possible, to improve performance. For more information, see db-vendor(5dpconf).

  3. Restart the proxy server.

    % dpadm restart /local/dps
  4. Enable the data source, and allow write operations to the data source.

    % dpconf set-jdbc-data-source-prop mysql1 is-enabled:true is-read-only:false
  5. (Optional) Set monitoring-inactivity-timeout, monitoring-interval, and monitoring-mode for better monitoring of the open connections and data sources. .

    For more information, see monitoring-inactivity-timeout(5dpconf), monitoring-interval(5dpconf), and monitoring-mode(5dpconf)

  6. Create a JDBC data source pool named mysql1–pool.

    % dpconf create-jdbc-data-source-pool mysql1-pool
  7. Attach the JDBC data source to the data source pool.

    % dpconf attach-jdbc-data-source mysql1-pool mysql1
  8. Create a JDBC data view for the data source pool, named myjdbc1–view, with a base DN of o=sql.

    % dpconf create-jdbc-data-view mysql1-view mysql1-pool o=sql
  9. Create a JDBC table for each table in the MySQL database.

    % dpconf create-jdbc-table employee1 EMPLOYEE
    % dpconf create-jdbc-table country1 COUNTRY
    % dpconf create-jdbc-table phone1 PHONE

    The name of the table in the SQL database is case sensitive. Make sure that you use the same case that is used in the SQL database.

  10. Create a JDBC attribute for each column in each table.

    Creating a JDBC attribute maps the MySQL column to an LDAP attribute.

    % dpconf add-jdbc-attr employee1 uid ID
    % dpconf add-jdbc-attr employee1 sn SURNAME
    % dpconf add-jdbc-attr employee1 userPassword PASSWORD
    % dpconf add-jdbc-attr employee1 roomNumber ROOM
    % dpconf add-jdbc-attr phone1 telephoneNumber NUMBER
    % dpconf add-jdbc-attr country1 countryName NAME

    It is not necessary to create JDBC attributes for the phone1 user_id and country1 id columns, because these columns only contain the values that are present in EMPLOYEE.ID for which an LDAP attribute uid is already created.

  11. Create a JDBC object class for the LDAP person object class.

    In this step, the employee1 table is identified as the primary table, and the country1 and phone1 tables are identified as secondary tables. The creation of a JDBC object class also requires a DN. In this example, the DN is constructed from the uid attribute and the base DN of the data view.

    % dpconf create-jdbc-object-class mysql1-view person employee1 country1 phone1 uid
  12. Define the join rules between the primary table and the secondary tables.

    A join rule is defined on the secondary table and determines how data from that table is linked to data from the primary table.

    % dpconf set-jdbc-table-prop country1 filter-join-rule:ID=\${EMPLOYEE.COUNTRY_ID}
    % dpconf set-jdbc-table-prop phone1 filter-join-rule:USER_ID=\${EMPLOYEE.ID}
  13. Specify the super class for the JDBC object class.

    The super class indicates the LDAP object class from which the JDBC object class inherits attributes.

    % dpconf set-jdbc-object-class-prop mysql1-view person super-class:top

ProcedureTo Create the Required ACIs

Before you can test the JDBC data view, you must enable write access to the data view by configuring ACIs. By default, write access to non-LDAP data views is denied. For the purposes of this example, it is sufficient to add one global ACI that allows users to modify their own passwords.

  1. As the Proxy Manager, add a pool of ACIs to the JDBC data source and add a global ACI that allows users to modify their own entries.

    % ldapmodify -p 1389 -D "cn=proxy manager" -w password
    dn: cn=mysql1,cn=virtual access controls
    changetype: add
    objectclass: acisource
    dpsaci: (targetattr="*") (target = "ldap:///o=sql") 
    (version 3.0; acl "enable all access for all users "; allow(all) 
    cn: mysql1
  2. Create a connection handler to handle connections to the o=sql domain.

    % dpconf create-connection-handler mysql1-handler
  3. Enable the connection handler and configure it to handle all binds from users in the o=sql domain.

    % dpconf set-connection-handler-prop mysql1-handler is-enabled:true \
  4. Configure the connection handler to use the pool of ACIs added previously.

    % dpconf set-connection-handler-prop mysql1-handler aci-source:mysql1

ProcedureTo Test the JDBC Data View

  1. As a user under o=sql, search the JDBC data source to verify that you can read from the data view.

    % ldapsearch -p 1389 -D "uid=kvaughan,o=sql" -w mypwd -b o=sql "objectclass=*"

    Note –

    You must use the credentials of a user under o=sql.

  2. As a user under o=sql, modify the userPassword attribute to verify that you can write to the data view.

    % ldapmodify -p 1389 -D "uid=kvaughan,o=sql" -w mypwd
    dn: uid=kvaughan,o=sql
    changetype: modify
    replace: userPassword
    userPassword: myNewpwd