> cool solutions home   > cool tools home
Oracle Advanced Driver
An extension of the EMP/Eventlog example provided with the JDBC 1.6 Driver for Oracle.
Reader Rating    from ratings rate this article
View a Printer Friendly Version of this Page Send this page to a friend
vitals
Updated: 4 Feb 2004
Created: 6 Jan 2004
File Size: 25KB
License: Free
Download 1:  oracle_adv_driver.zip
Publisher: Michel Bluteau
E-mail: mbluteauTAKETHISOUT@novell.com
disclaimer
Please read the note from our friends in legal before using this file.
details

This driver is based on the example coming with JDBC 1.6 driver for Oracle, which uses some Oracle side tables(EMP, EVENTLOG, etc). It works with DirXML 2.0.

It is required to obtain classes12.zip and nls_charset12.zip from http://otn.oracle.com as specified in the documentation for the driver.

The example included with the driver comes with some limited capabilities regarding the integration of Oracle security: The Subscriber Channel encapsulates JDBC statements in XSL in order to CREATE/DROP USERS and GRANT CONNECT privileges. However, the example does not integrates ROLES and ROLES memberships. This advanced connector has been designed in order to handle USERS, ROLES, and memberships(USERS to ROLES, ROLES to ROLES).

The use or Oracle Internet Directory(OID) would very likely render this driver useless(the LDAP Driver would do), but since some Oracle customers have dozens of Oracle applications and many more instances, OID enablement of all their Oracle applications instances/applications is still something planned for the future. Meanwhile, this driver would allow a more comprehensive integration of Oracle and eDirectory security, especially interesting for customers with many Oracle developers needing privileges and access on many Oracle instances.

Schema extensions(CASE EXACT STRING, SINGLE VALUE, SYNC IMMEDIATE):
User:
OracleAddRole, OracleDelRole, OracleRole, OracleRoleAD, OracleUserID
Dynamic Group: OracleAddRole, OracleDelRole, OracleMember, OracleMemberAD, OracleRoleA, OracleRoleAD, OracleRoleID, OracleRolePasswordAttr

N.B. Dynamic Group has been selected since it can be nested.

What the connector does.

Subscriber:

  • USER CREATE EVENT: Updates xdnovel.emp and CREATE USER(JDBC in XSL) Required attribute = OracleUserID
  • USER DELETE EVENT: Updates xdnovel.emp and DROP USER(JDBC in XSL)
  • USER Modify EVENT: Updates xdnovel.emp
  • ROLE CREATE EVENT: Updates xdnovel.role and CREATE ROLE(PL/SQL trigger) Required attribute = OracleRoleID, OracleRolePasswordAttr='new'
  • ROLE DELETE EVENT: Updates xdnovel.role and DROP ROLE(PL/SQL trigger)
  • ADD ROLE TO USER EVENT: Updates column ADDROLE for xdnovel.emp and GRANT ROLE(PL/SQL trigger). MUST add Role name to ORACLEADDROLE Attribute manually(e.g. iManager plug-in studio task).
  • DELETE ROLE FROM USER EVENT: Updates column DELROLE for xdnovel.emp and REVOKE ROLE(PL/SQL trigger). MUST add Role name to ORACLEDELROLE Attribute manually.
  • ADD ROLE TO ROLE EVENT: Updates column ADDROLE for xdnovel.role and GRANT ROLE(PL/SQL trigger). MUST add Role name to ORACLEADDROLE Attribute manually.
  • DELETE ROLE FROM ROLE EVENT: Updates column DELROLE for xdnovel.role and REVOKE ROLE(PL/SQL trigger). MUST add Role name to ORACLEDELROLE Attribute manually. It sends the password to Oracle after a modify universal password.

Publisher:

  • CREATE/DROP USER EVENT: DBMS_Job Polls DBA_USERS with Stored Procedure sp_dirxml_er which updates xdnovel.emp(which updates xdnovel.eventlog)
  • CREATE/DROP ROLE EVENT: DBMS_Job Polls DBA_ROLES with Stored Procedure sp_dirxml_er which updates xdnovel.role(which updates xdnovel.eventlog)
  • GRANT/REVOKE ROLE EVENT: DBMS_Job Polls DBA_USERS with Stored Procedure sp_dirxml_aa/sp_dirxml_aa which updates xdnovel.emp and xdnovel.role(which update xdnovel.eventlog)

Installation:

  • Extend the schema for User and Dynamic Group
  • Import the driver into eDirectory, Oracle_AdvDriver_DirXML11a.xml for DirXML 1.1a or Oracle_AdvDriver_DirXML20.xml for DirXML 2.0/Apollo/Nsure Identity Management 2.0. Enter the selected password for dirxml account, IP address for Oracle server, and instance name(e.g. O9ir2).
  • Login as System(default password is manager) against Oracle with SQL+ or iSQL+. Create user xdnovel and dirxml, and grant all privileges:
    • CREATE USER XDNOVEL identified by dirxml;
    • GRANT ALL PRIVILEGES TO XDNOVEL;
    • CREATE USER DIRXML identified by dirxml;
    • GRANT ALL PRIVILEGES TO DIRXML;
  • Login as SYS(Change_on_install is default password) with Oracle Enterprise Manager or DBA Studio, with SYSDBA privileges. Grant privileges SELECT objects DBA_USERS, DBA_ROLES and DBA_ROLE_PRIVS to XDNOVEL. This is mandatory in order for the Publisher Polling process through DBMS_JOBS and Stored Procedures(which require explicit privileges to objects).
  • Login with XDNOVEL with SQL+ or iSQL+ and execute the following SQL scripts:
    • CREATE_EMP.SQL ; Creates the EMP tables and other related objects.
    • CREATE_ROLE.SQL ; Creates the ROLE,MEMBER tables + related objects.
    • SUB_newRole_dropRole_trigger.txt ; Triggers for CREATE/DROP ROLE
    • SUB_addRoleMembership_trigger.txt ; Triggers for GRANT/REVOKE ROLE
    • PUB_Poll_for_EMP_ROLE.sql ; Procedure for polling USERS and ROLES
    • PUB_Poll_membership.txt ; Procedure for polling ROLE memberships
  • DBMS_Job.txt ; Jobs polling DBA_USERS, DBA_ROLES, DBA_ROLE_PRIVS. Execute one at a time, then issue COMMIT;. The script also includes code for removing Jobs from queues, listing jobs in queues, etc. The polling interval is a fraction of 24 hours, so 1/8640 corresponds to 10 seconds. Make sure that Job queues are being monitored by setting the startup parameter JOB_QUEUE_PROCESSES . The code for dynamically activating the Job queues is included in the file.