Novell Home

Oracle Triggerless/Scalable Driver for IDM

Novell Cool Solutions: AppNote
By Michel Bluteau

Digg This - Slashdot This

Posted: 16 May 2007
 

Oracle Scalable Trigerless driver for User Accounts and Roles leveraging Identity Manager 3.5 and Jobs

Introduction

This AppNote includes a step-by-step approach on how to adapt the Identity Manager JDBC driver example provided on the Oracle media. The result is bi-directional synchronization between Novell eDirectory and Oracle's dictionary (or catalog) for User Accounts and Roles. The instructions work with Identity Manager 2.01, 3.01, 3.5 and future versions, but an alternative to Jobs is required if you don't have at least 3.5 (such as the Clock object referenced below).

The logic described in this article is the result of improvements over earlier articles for Identity Manager 2 and 3:

Improvements were added to the original design described in the above two articles after I experimented with the original logic in production environments for a few of my customers, and after I received some feedback from readers who experimented with the original logic. I believe that this new logic should result in significant improvements in performance and scability.

The AppNote uses simplified SQL scripts to create the objects on the Oracle's side, derived from the direct approach.

Direct Synchronization

Figure 1: Direct Synchronization on the Subscriber Channel

Figure 2: Direct Synchronization on the Publisher Channel

For the Publisher Channel, we will not use triggers on the Oracle side. While triggers are okay for applications, they are a no-no for the dictionary (or Catalog), which contains the system tables for objects like User Accounts, Roles and Privileges. The information we are interested in can be retrieved from three views: DBA_USERS, DBA_ROLES, and DBA_ROLE_PRIVS. The key in the logic described below is how to detect changes through these three views and keep eDirectory up-to-date versus the dictionary, without triggers.

Let's start by creating the objects that we require on the Oracle side. First, let's create the User Account and Schema used by the driver to interface with Oracle. SQL+ or iSQL+ can be used, or any other tool. The SQL scripts we will use are derived from the 1_install.sql script provided on the Identity Manager 3.5 media (check under /media/IDM3_5_Lin_NW_Win/nt/dirxml/drivers/jdbc/tools/sql/oracle/install).

Figure 3: iSQL*Plus web interface (in French)

1. First, we will create the User Account and assign privileges, while connected as SYSTEM:

CREATE USER idmUser IDENTIFIED BY dirxml
default tablespace USERS
quota unlimited on USERS
temporary tablespace TEMP;
GRANT CONNECT TO idmUser;

Figure 4: Creating the driver's User Account/Schema with iSQL*Plus

2. Assign additional privileges as follows:

GRANT CONNECT TO idmUser;
GRANT CREATE SESSION, ALTER SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE PROCEDURE, CREATE VIEW TO idmUser;
Grant create user, alter user, drop user, create role, alter any role, drop any role, grant any role, grant any privilege to idmUser;

Some of the above privileges, like create user, are required if you want the driver to be able to create accounts in Oracle. You can restrict these privileges if, for example, your driver is uni-directional through the Publisher Channel (from Oracle to eDirectory).

3. Allow the driver's account to query the dictionary for Users, Roles and Role Privs. You need to be connected as SYS to grant these privileges:

GRANT SELECT ON sys.dba_users TO idmUser;
GRANT SELECT ON sys.dba_roles TO idmUser;
GRANT SELECT ON sys.dba_role_privs TO idmUser;

4. Create three views for DBA_USERS, DBA_ROLES and DBA_ROLE_PRIVS:

CREATE OR REPLACE VIEW idmUser.view_ORCLUSERS (pk_USERNAME, USER_ID, ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, CREATED, PROFILE, INITIAL_RSRC_CONSUMER_GROUP, EXTERNAL_NAME)
AS
/* VIEW 'DBA_USERS' would need to be schema prefixed if it were located outside */
/* of the driver's schema. */
SELECT USERNAME, USER_ID, ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, CREATED, PROFILE, INITIAL_RSRC_CONSUMER_GROUP, EXTERNAL_NAME FROM SYS.DBA_USERS;

CREATE OR REPLACE VIEW idmUser.view_ORCLROLES (pk_ROLE, PASSWORD_REQUIRED)
AS
/* VIEW 'DBA_ROLES' would need to be schema prefixed if it were located outside */
/* of the driver's schema. */
SELECT ROLE, PASSWORD_REQUIRED FROM SYS.DBA_ROLES;

CREATE OR REPLACE VIEW idmUser.view_ORCLPRIVS (pk_GRANTEE, pk_GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE)
AS
/* VIEW 'DBA_ROLE_PRIVS' would need to be schema prefixed if it were located outside */
/* of the driver's schema. */
SELECT GRANTEE, GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE FROM SYS.DBA_ROLE_PRIVS;

5. Create the Eventlog tables and a sequence:

CREATE SEQUENCE idmUser.seq_log_record_id
 START WITH 1
 INCREMENT BY 1
 NOMINVALUE
 NOMAXVALUE
 CACHE 128
 ORDER;

CREATE TABLE idmUser.direct_process
(
 record_id  NUMBER(32)   NOT NULL,
 table_key  VARCHAR2(64)  NOT NULL,  
 status    CHAR(1)    DEFAULT 'N' NOT NULL,
 event_type  NUMBER(1)   NOT NULL, 
 event_time  DATE      DEFAULT SYSDATE NOT NULL,   
 perpetrator VARCHAR2(32)  DEFAULT USER,
 table_name  VARCHAR2(32)  NOT NULL,
 column_name VARCHAR2(32), 
 old_value  VARCHAR2(64),
 new_value  VARCHAR2(64),
 CONSTRAINT chk_devent_type 
  CHECK(event_type IN (1, 2, 3, 4, 5, 6, 7, 8)),
 CONSTRAINT chk_dstatus
  CHECK (status  IN ('N', 'S', 'W', 'E', 'F'))
);

CREATE TABLE idmUser.direct_processed
(
 record_id  NUMBER(32)   NOT NULL,
 table_key  VARCHAR2(64)  NOT NULL,  
 status    CHAR(1)    NOT NULL,
 event_type  NUMBER(1)   NOT NULL, 
 event_time  DATE      NOT NULL,    
 perpetrator VARCHAR2(32),
 table_name  VARCHAR2(32)  NOT NULL,
 column_name VARCHAR2(32), 
 old_value  VARCHAR2(64),
 new_value  VARCHAR2(64)
);

6. Create indexes on the eventlog tables in order to optimize the Publisher Channel queries:

CREATE UNIQUE INDEX idmUser.idx_directlog_1
 ON idmUser.direct_process(record_id);
ALTER TABLE idmUser.direct_process
 ADD CONSTRAINT uk_dlog_record_id UNIQUE (record_id); 
CREATE INDEX idmUser.idx_directlog_2
 ON idmUser.direct_process(event_time, record_id);
CREATE INDEX idmUser.idx_directlog_3
 ON idmUser.direct_process(status);

7. Create a Procedure that will clean up the eventlog table:

CREATE PROCEDURE idmUser.proc_directlog
AS
  CURSOR c is SELECT * FROM idmUser.direct_process
         WHERE status != 'N' FOR UPDATE;
BEGIN 
  FOR r IN c LOOP
  INSERT INTO idmUser.direct_processed
  VALUES
  (
   r.record_id,
   r.table_key,
   r.status,
   r.event_type, 
   r.event_time,
   r.perpetrator,
   r.table_name,
   r.column_name,
   r.old_value,
   r.new_value
  );
  DELETE FROM idmUser.direct_process
   WHERE CURRENT OF c;
  END LOOP;
  COMMIT;
END proc_directlog;

Now, we have all the objects we need on the Oracle side. We will not create the triggers, because we are working against the dictionary, for which the use of Triggers is forbidden. Instead, we will encapsulate the Triggers within XSLT in the driver later.

Creating a Structure for the Oracle Objects

Before we start configuring the Identity Manager driver, we need to extend the schema and create a structure in eDirectory to host Oracle objects. We will create a scalable structure capable of handling any number of Oracle drivers/systems.

1. Create an Auxiliary Class called OrclRole for Group, which contains the following two attributes:

  • OrclSID, a single-value, case-ignore string. This must contain the Oracle SID name for the Oracle security store (Ora10gR2 in this article).
  • Group Membership, used to Grant a Group (Oracle Role) to another Group

Figure 5: Summary page for the creation of the OrclSID attribute.

Figure 6: Summary page for the OrclRole Auxiliary Class

2. Create an Effective class OrclPriv, for Group Memberships. This is derived from Top and can be contained by domain, Organization, and Organization Unit. It uses CN for naming and contains the following 2 mandatory attributes:

  • OrclGrantee, a single-value, case-insensitive string. This is mapped to the Oracle column GRANTEE (view DBA_ROLE_PRIVS), which contains the USERNAME or Role for which a Role is granted.
  • OrclGrantedRole, a single-value, case-insensitive string. This is mapped to the Oracle column GRANTED_ROLE (view DBA_ROLE_PRIVS), which contains the Role which is granted.

Figure 7: Summary page, OrclGrantee attribute

Figure 8: Summary page, OrclGrantedRole attribute

Figure 9: Summary page, OrclPriv effective class

3. Create the eDirectory structure to receive Oracle objects.

Figure 10: Simple tree structure under o=novl,c=ca. We will use a flat structure for Users

4. Under ou=Oracle,o=novl,c=ca, create a OU/Group pair for each Oracle system/driver. The Group is a control Group for which members have an account on the corresponding Oracle system. The OU is where we will store Roles and Role_Privs objects.

Figure 11: OU/Group pair for each Oracle system/driver

Importing the Driver Template

Now we are ready to import the driver template included on the media for Identity Manager 3.5. For example, look under /media/IDM3_5_Lin_NW_Win/nt/dirxml/drivers/precfg.

Figure 12: Identity Manager 3.5 plug-in for iManager

1. Import the driver configuration file for JDBC

Figure 13: Importing the driver configuration file for JDBC

2. Provide a name for the driver.

3. Select Oracle for the target database and choose Local or Remote.

Figure 14: Setting driver parameters

3. Choose Direct for the Synchronization model.

4. Select Oracle Type 4 for the JDBC implementation.

Figure 15: Data flow and connectivity information for the Oracle system

5. If you chose "local" on the previous page, you will need to obtain the JDBC driver from Oracle and copy it on the Identity Manager server. See the documentation for more details.

Figure 16: Third-party JDBC implementation

6. Select your Users context for both User and Group container DN. Select triggered as the Publication mode. Don't select triggerless, because we will implement our own triggerless alternative method, versus the built-in triggerless method included with Identity Manager 3 and up.

Figure 17: DN and Publication mode for driver

7. Select the Security Equivalence (e.g., Admin) and Exclusions.

Figure 18: Security Equivalence and Exclusions

Figure 19: Summary page for the driver configuration

8. Edit the driver configuration to modify the Authentication ID and set the password (dirxml).

Figure 20: Setting Authentication ID and password

9. Modify the view names to include the three views created in Oracle, view_ORCLUSERS, view_ORCLROLES and view_ORCLPRIVS.

Figure 21: Modifying the view names

10. Change the schema prefix for the eventlog table.

11. Select Show for primary key parameters, and enter view_ORCLUSERS(none) in order to prevent errors for referenced objects that do not exist in Oracle.

12. Change the schema prefix for proc_directlog.

Figure 22: More driver configuration settings

13. Modify the Connected System name to match your Oracle SID.

14. Create 4 GCVs for the Users OU and Oracle objects top OU.

Figure 23: Creating GCVs

Next, we will modify our driver to adapt it to our business logic.

Figure 24: Original filter configuration

We need to change the filter configuration to reflect our logic.

15. Change the Application Name for User to idmUser.view_ORCLUSERS.

16. Add the CN and map it to PK_USERNAME.

17. Map Group Membership to Group Membership.

18. Delete all other User attributes.

Figure 25: Updated filter configuration

19. Modify Group so it points to idmUser.view_ORCLROLES.

20. Add the CN (PK_ROLE), OrclSID (OrclSID) and Group Membership (Group Membership). Don't forget to scroll down at the bottom of the attributes list, and select show all attributes, to see our newly created attributes.

Figure 26: Adding Group attributes

21. Add the OrclPriv class(idmUser.view_ORCLPRIVS), then the two attributes OrclGrantee(PK_GRANTEE) and OrclGrantedRole(PK_GRANTED_ROLE).

Figure 27: Adding OrclPriv class and attributes

Because we are designing our configuration so it can support multiple Oracle systems, we do not want a delete event on User to be sent unchanged on the Publisher Channel(from Oracle). We will use the Control Group (Ora10gR2grp) to determine if the User has an account with a given Oracle system.

22. Transform a Delete User event into a Remove from Group event.

Figure 28: Publisher Event Transform Policy that converts a User Delete Event into a Remove from Control Group Event. Note how we take advantage of GCVs in the policy.

23. Modify the Publisher Matching Policy as above. Again, we take advantage of GCVs.

Figure 29: Publisher Matching Policy

Figure 30: Publisher Create Policy for User, Group and Priv

24. At the Publisher Create Policy level, add attributes for User and Group at creation.

Figure 31: Adding User and Group creation attributes

Figure 32: Publisher Placement Policy

Note that when we create Priv objects in eDirectory, we use OrclGrantee-Priv-OrclGrantedRole for the name. -Priv- has been selected in an arbitrary fashion; we just need to make sure that the separator we select is not included in any of the names for Oracle Users or Roles.

25. Make sure the Subscriber Matching Rule is the same as the one for the Publisher Channel.

Figure 33: Checking the Subscriber Matching Rule

26. Create the Subscriber Create Policy.

27. Check to see whether events on User and Group are for the driver's Oracle connected system by validating the OrclSID for Group, or Group Membership for the Control Group for User.

Figure 34: Checking events on User and Group

28. Add an association for User or Group at the Subscriber Create Policy level.

Figure 35: Adding User or Group association

An XSLT StyleSheet rule is included at the Subscriber Command Transform level; it must be modified for our needs.

Figure 36: Default XSLT StyleSheet rule

This is the XSLT StyleSheet we need to use:

<?xml version="1.0" encoding="UTF-8"?><xsl:transform version="1.0" xmlns:query="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.XdsQueryProcessor" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:param name="destQueryProcessor"/>
	<xsl:param name="srcQueryProcessor"/>
	<!-- identity template -->
	<xsl:template match="node()|@*">
		<xsl:copy>
			<xsl:apply-templates select="node()|@*"/>
		</xsl:copy>
	</xsl:template>
	<!-- Adds a database user/login account -->
	<xsl:template match="input/add[@class-name='User']" xmlns:jdbc="urn:dirxml:jdbc">
		<xsl:variable name="orclacct" select="add-attr[@attr-name='CN']/value[1]"/>
		<xsl:choose>
			<xsl:when test="$orclacct">
				<xsl:call-template name="add-add-sql">
					<xsl:with-param name="orclacct" select="$orclacct"/>
				</xsl:call-template>
			</xsl:when>
			<xsl:otherwise>
				<xsl:message>
					<status level="error" type="password-set-operation">
						<xsl:text>Unable to create user account. </xsl:text>
						<xsl:text>Missing 'orclacct' value.</xsl:text>
					</status>
				</xsl:message>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>
	<!-- Adds SQL to add a database user/login account -->
	<xsl:template name="add-add-sql" xmlns:Mapping="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.jdbc.util.MappingPolicy" xmlns:jdbc="urn:dirxml:jdbc">
		<xsl:param name="orclacct"/>
		<xsl:copy>
			<xsl:variable name="attrs" select="add-attr[@attr-name='CN']"/>
			<xsl:variable name="blah" select="Mapping:FirstPerReplica(., $attrs, $srcQueryProcessor)"/>
			<xsl:attribute name="jdbc:transaction-id">0</xsl:attribute>
			<xsl:attribute name="jdbc:op-id">0</xsl:attribute>
			<xsl:attribute name="jdbc:op-type">password-set-operation</xsl:attribute>
			<xsl:apply-templates select="node() | @*"/>
			<password>
				<xsl:value-of select="$orclacct"/>
			</password>
		</xsl:copy>
		<!-- DDL statements cause transaction commit in Oracle. As such, they should not be part of a manual transaction -->
		<jdbc:statement jdbc:op-id="0">
			<jdbc:sql>
				<xsl:text>CREATE USER </xsl:text>
				<xsl:value-of select="$orclacct"/>
				<!-- 

					because an association value is not yet available, you can 

					reference association fields using XSLT attribute value template

					notation and the driver will replace the variable with it's corresponding 

					value once it becomes available

				-->
				<!-- <xsl:text> IDENTIFIED BY {$$password}</xsl:text> -->
				<xsl:text> IDENTIFIED BY </xsl:text>
				<xsl:value-of select="$orclacct"/>
			</jdbc:sql>
		</jdbc:statement>
		<jdbc:statement jdbc:op-id="0">
			<jdbc:sql>
				<xsl:text>GRANT CREATE SESSION TO </xsl:text>
				<xsl:value-of select="$orclacct"/>
			</jdbc:sql>
		</jdbc:statement>
	</xsl:template>
	<!-- Drops a database user/login account -->
	<xsl:template match="input/delete[@class-name='User']" xmlns:jdbc="urn:dirxml:jdbc" xmlns:query="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.XdsQueryProcessor">
		<xsl:variable name="query">
			<!-- Adding an association to the first command element of each document (other than an add) also 

								makes variable replacement possible -->
			<!-- The Oracle JDBC driver errantly returns the same result set multiple times unless you set the 

								statement type to "query" -->
			<jdbc:statement jdbc:type="query">
				<xsl:copy-of select="association"/>
				<jdbc:sql>SELECT pk_USERNAME FROM view_orclusers WHERE pk_USERNAME = {$pk_USERNAME}</jdbc:sql>
			</jdbc:statement>
		</xsl:variable>
		<xsl:variable name="result" select="query:query($destQueryProcessor,$query)"/>
		<xsl:variable name="username" select="$result//jdbc:result-set[1]/jdbc:row[1]/jdbc:column[@jdbc:name='pk_USERNAME']/jdbc:value"/>
		<xsl:choose>
			<xsl:when test="$username">
				<xsl:call-template name="add-drop-sql">
					<xsl:with-param name="username" select="$username"/>
				</xsl:call-template>
			</xsl:when>
			<xsl:otherwise>
				<xsl:message>
					<status level="error" type="password-set-operation">
						<xsl:text>Unable to drop user account. </xsl:text>
						<xsl:text>Unable to retrieve account information.</xsl:text>
					</status>
				</xsl:message>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>
	<!-- Adds SQL to drop user/login account -->
	<xsl:template name="add-drop-sql" xmlns:jdbc="urn:dirxml:jdbc">
		<xsl:param name="username"/>
		<xsl:copy>
			<xsl:attribute name="jdbc:op-id">0</xsl:attribute>
			<xsl:apply-templates select="node() | @*"/>
		</xsl:copy>
		<!-- DDL statements cause transaction commit in Oracle. As such, they should not be part of a manual transaction -->
		<jdbc:statement jdbc:op-id="0">
			<jdbc:sql>
				<xsl:text>DROP USER </xsl:text>
				<xsl:value-of select="$username"/>
				<xsl:text> CASCADE</xsl:text>
			</jdbc:sql>
		</jdbc:statement>
	</xsl:template>
	<!-- Handles multi-valued to single-valued attribute mappings for modify events. 

						Ensures that the first value on this replica is synced to the database -->
	<xsl:template match="input/modify[@class-name='User']" xmlns:Mapping="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.jdbc.util.MappingPolicy" xmlns:query="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.XdsQueryProcessor">
		<xsl:copy>
			<xsl:variable name="attrs" select="modify-attr[@attr-name='Given Name' or @attr-name='Surname' or @attr-name='Telephone Number' or @attr-name='Facsimile Telephone Number']"/>
			<xsl:variable name="blah" select="Mapping:FirstPerReplica(., $attrs, $srcQueryProcessor)"/>
			<xsl:apply-templates select="node()|@*"/>
		</xsl:copy>
	</xsl:template>
	<!-- 
		Modify password.
	-->
	<xsl:template match="input/modify[@class-name='User']" xmlns:jdbc="urn:dirxml:jdbc">
		<xsl:variable name="nspmDistributionPassword" select="modify-attr[@attr-name='nspmDistributionPassword']"/>
		<xsl:choose>
			<xsl:when test="$nspmDistributionPassword">
				<xsl:message>
					<xsl:text>'nspmDistributionPassword' value available.</xsl:text>
				</xsl:message>
				<xsl:variable name="association" select="association"/>
				<!--	_________________	Query eDirectory for existing attributes __________________ -->
				<xsl:variable name="src-query">
					<query class-name="User" dest-dn="{@src-dn}" scope="entry">
						<read-attr attr-name="CN"/>
					</query>
				</xsl:variable>
				<!--	_________________	Paste exiting attributes into variables __________________ -->
				<xsl:variable name="result" select="query:query($srcQueryProcessor, $src-query)"/>
				<xsl:variable name="oracleu" select="$result/nds/output/instance[1]/attr[@attr-name='CN']/value[1]"/>
				<xsl:call-template name="mod-pwd-sql">
					<xsl:with-param name="oracleuser" select="$oracleu"/>
				</xsl:call-template>
			</xsl:when>
			<xsl:otherwise>
				<xsl:message>
					<xsl:text>Unable to set password. </xsl:text>
					<xsl:text>Missing 'nspmDistributionPassword' value.</xsl:text>
				</xsl:message>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>
	<!--
		Adds SQL set password. 
	-->
	<xsl:template name="mod-pwd-sql" xmlns:Mapping="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.jdbc.util.MappingPolicy" xmlns:jdbc="urn:dirxml:jdbc">
		<xsl:param name="oracleuser"/>
		<xsl:copy>
			<xsl:attribute name="jdbc:transaction-id">0</xsl:attribute>
			<xsl:attribute name="jdbc:op-id">0</xsl:attribute>
			<xsl:attribute name="jdbc:op-type">password-set-operation</xsl:attribute>
			<xsl:apply-templates select="node() | @*"/>
			<password>
				<xsl:value-of select="modify-attr[@attr-name='nspmDistributionPassword']//add-value//value"/>
			</password>
		</xsl:copy>
		<jdbc:statement jdbc:op-id="0">
			<jdbc:sql>
				<xsl:text>ALTER USER </xsl:text>
				<xsl:value-of select="$oracleuser"/>
				<!-- 
					because an associaiton value is not yet available, you can 
					reference association fields using XSLT attribute value template
					notation and the driver will replace the variable with it's corresponding 
					value once it becomes available
				-->
				<xsl:text> IDENTIFIED BY {$$password}</xsl:text>
			</jdbc:sql>
		</jdbc:statement>
	</xsl:template>
</xsl:transform>

The above XSLT StyleSheet rule will take care of Add and Delete events on the User, as well as Password Synchronization.

29. Delete the XSLT rule "Replica Value".

Figure 37: Deleting the Replica Value rule

30. Create an XSLT rule to take care of Group/Role:

<?xml version="1.0" encoding="UTF-8"?><xsl:transform version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:param name="destQueryProcessor"/>
	<xsl:param name="srcQueryProcessor"/>
	<!-- identity template -->
	<xsl:template match="node()|@*">
		<xsl:copy>
			<xsl:apply-templates select="node()|@*"/>
		</xsl:copy>
	</xsl:template>
	<!-- Adds a database role -->
	<xsl:template match="input/add[@class-name='Group']" xmlns:jdbc="urn:dirxml:jdbc">
		<xsl:variable name="role" select="add-attr[@attr-name='CN']/value[1]"/>
		<xsl:variable name="sid" select="add-attr[@attr-name='OrclSID']/value[1]"/>
		<xsl:choose>
			<xsl:when test="$role and string($sid) = '~ConnectedSystemName~'">
				<xsl:call-template name="add-add-sql">
					<xsl:with-param name="role" select="$role"/>
					<xsl:with-param name="sid" select="$sid"/>
				</xsl:call-template>
			</xsl:when>
			<xsl:otherwise>
				<xsl:message>
					<status level="error" type="password-set-operation">
						<xsl:text>Unable to create role. </xsl:text>
						<xsl:text>Missing 'OrclRole' value or 'OrclSID' not equal to '~ConnectedSystemName~'.</xsl:text>
					</status>
				</xsl:message>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>
	<!-- Adds SQL to add a database role -->
	<xsl:template name="add-add-sql" xmlns:Mapping="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.jdbc.util.MappingPolicy" xmlns:jdbc="urn:dirxml:jdbc">
		<xsl:param name="role"/>
		<xsl:param name="sid"/>
		<xsl:copy>
			<xsl:variable name="attrs" select="add-attr[@attr-name='CN' or @attr-name='OrclSID']"/>
			<xsl:variable name="blah" select="Mapping:FirstPerReplica(., $attrs, $srcQueryProcessor)"/>
			<xsl:attribute name="jdbc:transaction-id">0</xsl:attribute>
			<xsl:attribute name="jdbc:op-id">0</xsl:attribute>
			<xsl:attribute name="jdbc:op-type">password-set-operation</xsl:attribute>
			<xsl:apply-templates select="node() | @*"/>
		</xsl:copy>
		<!-- DDL statements cause transaction commit in Oracle. As such, they should not be part of a manual transaction -->
		<jdbc:statement jdbc:op-id="0">
			<jdbc:sql>
				<xsl:text>CREATE ROLE </xsl:text>
				<xsl:value-of select="$role"/>
				<!-- 

					because an association value is not yet available, you can 

					reference association fields using XSLT attribute value template

					notation and the driver will replace the variable with it's corresponding 

					value once it becomes available

				-->
			</jdbc:sql>
		</jdbc:statement>
	</xsl:template>
	<!-- Drops a database role -->
	<xsl:template match="input/delete[@class-name='Group']" xmlns:jdbc="urn:dirxml:jdbc" xmlns:query="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.XdsQueryProcessor">
		<xsl:variable name="role" select="remove-attr[@attr-name='CN']/value[1]"/>
		<xsl:variable name="sid" select="remove-attr[@attr-name='OrclSID']/value[1]"/>
		<xsl:variable name="query">
			<!-- Adding an association to the first command element of each document (other than an add) also 

								makes variable replacement possible -->
			<!-- The Oracle JDBC driver errantly returns the same result set multiple times unless you set the 

								statement type to "query" -->
			<jdbc:statement jdbc:type="query">
				<xsl:copy-of select="association"/>
				<jdbc:sql>SELECT pk_ROLE FROM view_ORCLROLES WHERE pk_ROLE = {$pk_ROLE}</jdbc:sql>
			</jdbc:statement>
		</xsl:variable>
		<xsl:variable name="result" select="query:query($destQueryProcessor,$query)"/>
		<xsl:variable name="rolename" select="$result//jdbc:result-set[1]/jdbc:row[1]/jdbc:column[@jdbc:name='pk_ROLE']/jdbc:value"/>
		<xsl:choose>
			<xsl:when test="$rolename and string($sid) = '~ConnectedSystemName~'">
				<xsl:call-template name="add-drop-sql">
					<xsl:with-param name="role" select="$role"/>
					<xsl:with-param name="sid" select="$sid"/>
				</xsl:call-template>
			</xsl:when>
			<xsl:otherwise>
				<xsl:message>
					<status level="error" type="password-set-operation">
						<xsl:text>Unable to drop role. </xsl:text>
						<xsl:text>Unable to retrieve role info or SID not equal to "~ConnectedSystemName~".</xsl:text>
					</status>
				</xsl:message>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>
	<!-- Adds SQL to drop role -->
	<xsl:template name="add-drop-sql" xmlns:jdbc="urn:dirxml:jdbc">
		<xsl:param name="role"/>
		<xsl:param name="sid"/>
		<xsl:copy>
			<xsl:attribute name="jdbc:op-id">0</xsl:attribute>
			<xsl:apply-templates select="node() | @*"/>
		</xsl:copy>
		<!-- DDL statements cause transaction commit in Oracle. As such, they should not be part of a manual transaction -->
		<jdbc:statement jdbc:op-id="0">
			<jdbc:sql>
				<xsl:text>DROP ROLE </xsl:text>
				<xsl:value-of select="$role"/>
				<xsl:text> CASCADE</xsl:text>
			</jdbc:sql>
		</jdbc:statement>
	</xsl:template>
</xsl:transform>

Then, we also need an additionnal XSLT rule for Privs:
<?xml version="1.0" encoding="UTF-8"?><xsl:transform version="1.0" xmlns:cmd="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.XdsCommandProcessor" xmlns:query="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.XdsQueryProcessor" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:param name="destQueryProcessor"/>
	<xsl:param name="srcQueryProcessor"/>
	<xsl:param name="srcCommandProcessor"/>
	<xsl:param name="destCommandProcessor"/>
	<!-- identity template -->
	<xsl:template match="node()|@*">
		<xsl:copy>
			<xsl:apply-templates select="node()|@*"/>
		</xsl:copy>
	</xsl:template>
	<!-- Adds or Drops a database role membership for User	-->
	<xsl:template match="modify[@class-name='User']" xmlns:jdbc="urn:dirxml:jdbc">
		<xsl:variable name="association" select="association"/>
		<xsl:variable name="addgroupmembership" select="./modify-attr[@attr-name='Group Membership']/add-value/value"/>
		<xsl:message>We are in the Sub-CT stylesheet Role Membership for User</xsl:message>
		<xsl:variable name="delgroupmembership" select="./modify-attr[@attr-name='Group Membership']/remove-value/value"/>
		<xsl:copy>
			<xsl:apply-templates select="node()|@*"/>
		</xsl:copy>
		<xsl:variable name="rolepattern" select="concat('~ConnectedSystemName~','\')"/>
		<xsl:for-each select="$addgroupmembership">
			<xsl:choose>
				<xsl:when test="contains(.,$rolepattern)">
					<xsl:message>Group corresponds to a Oracle role</xsl:message>
					<xsl:call-template name="add-usermembership-sql">
						<xsl:with-param name="oraclemember" select="$association"/>
						<xsl:with-param name="oraclemembership" select="."/>
					</xsl:call-template>
				</xsl:when>
				<xsl:otherwise>
					<xsl:message>Group does not correspond to a Oracle role</xsl:message>
				</xsl:otherwise>
			</xsl:choose>
		</xsl:for-each>
		<xsl:for-each select="$delgroupmembership">
			<xsl:choose>
				<xsl:when test="contains(.,$rolepattern)">
					<xsl:call-template name="del-usermembership-sql">
						<xsl:with-param name="oraclemember" select="$association"/>
						<xsl:with-param name="oraclemembership" select="."/>
					</xsl:call-template>
				</xsl:when>
				<xsl:otherwise>
					<xsl:message>Group does not correspond to a Oracle role</xsl:message>
				</xsl:otherwise>
			</xsl:choose>
		</xsl:for-each>
	</xsl:template>
	<!-- Adds or Drops a database role membership for Group	-->
	<xsl:template match="modify[@class-name='Group']" xmlns:jdbc="urn:dirxml:jdbc">
		<xsl:variable name="association" select="association"/>
		<xsl:variable name="addgroupmembership" select="./modify-attr[@attr-name='Group Membership']/add-value/value"/>
		<xsl:message>We are in the Sub-CT stylesheet Role Membership for Group</xsl:message>
		<xsl:variable name="delgroupmembership" select="./modify-attr[@attr-name='Group Membership']/remove-value/value"/>
		<xsl:copy>
			<xsl:apply-templates select="node()|@*"/>
		</xsl:copy>
		<xsl:variable name="rolepattern" select="concat('~ConnectedSystemName~','\')"/>
		<xsl:for-each select="$addgroupmembership">
			<xsl:choose>
				<xsl:when test="contains(.,$rolepattern)">
					<xsl:message>Group corresponds to a Oracle role</xsl:message>
					<xsl:call-template name="add-grmembership-sql">
						<xsl:with-param name="oraclemember" select="$association"/>
						<xsl:with-param name="oraclemembership" select="."/>
					</xsl:call-template>
				</xsl:when>
				<xsl:otherwise>
					<xsl:message>Group does not correspond to a Oracle role</xsl:message>
				</xsl:otherwise>
			</xsl:choose>
		</xsl:for-each>
		<xsl:for-each select="$delgroupmembership">
			<xsl:choose>
				<xsl:when test="contains(.,$rolepattern)">
					<xsl:message>Group corresponds to a Oracle role</xsl:message>
					<xsl:call-template name="del-grmembership-sql">
						<xsl:with-param name="oraclemember" select="$association"/>
						<xsl:with-param name="oraclemembership" select="."/>
					</xsl:call-template>
				</xsl:when>
				<xsl:otherwise>
					<xsl:message>Group does not correspond to a Oracle role</xsl:message>
				</xsl:otherwise>
			</xsl:choose>
		</xsl:for-each>
	</xsl:template>
	<!-- Adds SQL to add a database role membership for User -->
	<xsl:template name="add-usermembership-sql" xmlns:Mapping="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.jdbc.util.MappingPolicy" xmlns:jdbc="urn:dirxml:jdbc">
		<xsl:param name="oraclemember"/>
		<xsl:param name="oraclemembership"/>
		<xsl:message>Add roles to Database user</xsl:message>
		<xsl:copy>
			<xsl:attribute name="jdbc:transaction-id">0</xsl:attribute>
			<xsl:attribute name="jdbc:op-id">0</xsl:attribute>
			<xsl:attribute name="jdbc:op-type">password-set-operation</xsl:attribute>
			<xsl:apply-templates select="node() | @*"/>
		</xsl:copy>
		<!--	_________________	Query eDirectory for existing attributes __________________ -->
		<xsl:variable name="src-query">
			<query class-name="Group" dest-dn="{$oraclemembership}" scope="entry">
				<read-attr attr-name="CN"/>
				<read-attr attr-name="OrclSID"/>
			</query>
		</xsl:variable>
		<!--	_________________	Paste exiting attributes into variables __________________ -->
		<xsl:variable name="result" select="query:query($srcQueryProcessor, $src-query)"/>
		<xsl:variable name="oraclerole" select="$result/nds/output/instance[1]/attr[@attr-name='CN']/value[1]"/>
		<xsl:variable name="oraclesid" select="$result/nds/output/instance[1]/attr[@attr-name='OrclSID']/value[1]"/>
		<!--	_________________	Query Oracle for existing attributes __________________ -->
		<xsl:variable name="dest-query">
			<query class-name="User" dest-dn="{$oraclemember}" scope="entry">
				<read-attr attr-name="CN"/>
			</query>
		</xsl:variable>
		<!--	_________________	Paste exiting attributes into variables __________________ -->
		<xsl:variable name="result2" select="query:query($destQueryProcessor, $dest-query)"/>
		<xsl:variable name="oracleuser" select="$result2/nds/output/instance[1]/attr[@attr-name='CN']/value[1]"/>
		<xsl:if test="string($oraclesid) = '~ConnectedSystemName~'">
			<!-- These stored procedures must not be called within user-defined (manual) transactions -->
			<jdbc:statement jdbc:op-id="0">
				<jdbc:sql>
					<xsl:text>GRANT </xsl:text>
					<xsl:value-of select="$oraclerole"/>
					<xsl:text> TO </xsl:text>
					<xsl:value-of select="$oracleuser"/>
				</jdbc:sql>
			</jdbc:statement>
		</xsl:if>
	</xsl:template>
	<!-- Adds SQL to remove a Oracle role membership for User -->
	<xsl:template name="del-usermembership-sql" xmlns:Mapping="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.jdbc.util.MappingPolicy" xmlns:jdbc="urn:dirxml:jdbc">
		<xsl:param name="oraclemember"/>
		<xsl:param name="oraclemembership"/>
		<xsl:message>remove a Oracle role membership for User</xsl:message>
		<xsl:copy>
			<xsl:attribute name="jdbc:transaction-id">0</xsl:attribute>
			<xsl:attribute name="jdbc:op-id">0</xsl:attribute>
			<xsl:attribute name="jdbc:op-type">password-set-operation</xsl:attribute>
			<xsl:apply-templates select="node() | @*"/>
		</xsl:copy>
		<!--	_________________	Query eDirectory for existing attributes __________________ -->
		<xsl:variable name="src-query">
			<query class-name="Group" dest-dn="{$oraclemembership}" scope="entry">
				<read-attr attr-name="CN"/>
				<read-attr attr-name="OrclSID"/>
			</query>
		</xsl:variable>
		<!--	_________________	Paste exiting attributes into variables __________________ -->
		<xsl:variable name="result" select="query:query($srcQueryProcessor, $src-query)"/>
		<xsl:variable name="oraclerole" select="$result/nds/output/instance[1]/attr[@attr-name='CN']/value[1]"/>
		<xsl:variable name="oraclesid" select="$result/nds/output/instance[1]/attr[@attr-name='OrclSID']/value[1]"/>
		<!--	_________________	Query Oracle for existing attributes __________________ -->
		<xsl:variable name="dest-query">
			<query class-name="User" dest-dn="{$oraclemember}" scope="entry">
				<read-attr attr-name="CN"/>
			</query>
		</xsl:variable>
		<!--	_________________	Paste exiting attributes into variables __________________ -->
		<xsl:variable name="result2" select="query:query($destQueryProcessor, $dest-query)"/>
		<xsl:variable name="oracleuser" select="$result2/nds/output/instance[1]/attr[@attr-name='CN']/value[1]"/>
		<xsl:if test="string($oraclesid) = '~ConnectedSystemName~'">
			<!-- These stored procedures must not be called within user-defined (manual) transactions -->
			<jdbc:statement jdbc:op-id="0">
				<jdbc:sql>
					<xsl:text>REVOKE </xsl:text>
					<xsl:value-of select="$oraclerole"/>
					<xsl:text> FROM </xsl:text>
					<xsl:value-of select="$oracleuser"/>
				</jdbc:sql>
			</jdbc:statement>
		</xsl:if>
		<xsl:message>Verify if removed Group is MyDBGrp. Removed Group is: <xsl:value-of select="$oraclemembership"/>
		</xsl:message>
		<xsl:if test="string($oraclemembership) = concat('~OracleOU~','\','~ConnectedSystemName~','grp')">
			<xsl:message>User must be removed from Oracle</xsl:message>
			<jdbc:statement jdbc:op-id="0">
				<jdbc:sql>
					<xsl:text>DROP USER </xsl:text>
					<xsl:value-of select="$oracleuser"/>
					<xsl:text> CASCADE</xsl:text>
				</jdbc:sql>
			</jdbc:statement>
		</xsl:if>
	</xsl:template>
	<!-- Adds SQL to add a database role membership for Group -->
	<xsl:template name="add-grmembership-sql" xmlns:Mapping="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.jdbc.util.MappingPolicy" xmlns:jdbc="urn:dirxml:jdbc">
		<xsl:param name="oraclemember"/>
		<xsl:param name="oraclemembership"/>
		<xsl:copy>
			<xsl:attribute name="jdbc:transaction-id">0</xsl:attribute>
			<xsl:attribute name="jdbc:op-id">0</xsl:attribute>
			<xsl:attribute name="jdbc:op-type">password-set-operation</xsl:attribute>
			<xsl:apply-templates select="node() | @*"/>
		</xsl:copy>
		<!--	_________________	Query eDirectory for existing attributes __________________ -->
		<xsl:variable name="src-query">
			<query class-name="Group" dest-dn="{$oraclemembership}" scope="entry">
				<read-attr attr-name="CN"/>
				<read-attr attr-name="OrclSID"/>
			</query>
		</xsl:variable>
		<!--	_________________	Paste exiting attributes into variables __________________ -->
		<xsl:variable name="result" select="query:query($srcQueryProcessor, $src-query)"/>
		<xsl:variable name="oraclerolemembership" select="$result/nds/output/instance[1]/attr[@attr-name='CN']/value[1]"/>
		<xsl:variable name="oraclerolemembershipsid" select="$result/nds/output/instance[1]/attr[@attr-name='OrclSID']/value[1]"/>
		<!--	_________________	Query Oracle for existing attributes __________________ -->
		<xsl:variable name="dest-query">
			<query class-name="Group" dest-dn="{$oraclemember}" scope="entry">
				<read-attr attr-name="CN"/>
			</query>
		</xsl:variable>
		<!--	_________________	Paste exiting attributes into variables __________________ -->
		<xsl:variable name="result2" select="query:query($destQueryProcessor, $dest-query)"/>
		<xsl:variable name="oraclerolemember" select="$result2/nds/output/instance[1]/attr[@attr-name='CN']/value[1]"/>
		<xsl:if test="string($oraclerolemembershipsid)= '~ConnectedSystemName~'">
			<!-- These stored procedures must not be called within user-defined (manual) transactions -->
			<jdbc:statement jdbc:op-id="0">
				<jdbc:sql>
					<xsl:text>GRANT </xsl:text>
					<xsl:value-of select="$oraclerolemembership"/>
					<xsl:text> TO </xsl:text>
					<xsl:value-of select="$oraclerolemember"/>
				</jdbc:sql>
			</jdbc:statement>
		</xsl:if>
	</xsl:template>
	<!-- Adds SQL to remove a Oracle role membership for Group -->
	<xsl:template name="del-grmembership-sql" xmlns:Mapping="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.jdbc.util.MappingPolicy" xmlns:jdbc="urn:dirxml:jdbc">
		<xsl:param name="oraclemember"/>
		<xsl:param name="oraclemembership"/>
		<xsl:copy>
			<xsl:attribute name="jdbc:transaction-id">0</xsl:attribute>
			<xsl:attribute name="jdbc:op-id">0</xsl:attribute>
			<xsl:attribute name="jdbc:op-type">password-set-operation</xsl:attribute>
			<xsl:apply-templates select="node() | @*"/>
		</xsl:copy>
		<!--	_________________	Query eDirectory for existing attributes __________________ -->
		<xsl:variable name="src-query">
			<query class-name="Group" dest-dn="{$oraclemembership}" scope="entry">
				<read-attr attr-name="CN"/>
				<read-attr attr-name="OrclSID"/>
			</query>
		</xsl:variable>
		<!--	_________________	Paste exiting attributes into variables __________________ -->
		<xsl:variable name="result" select="query:query($srcQueryProcessor, $src-query)"/>
		<xsl:variable name="oraclerolemembership" select="$result/nds/output/instance[1]/attr[@attr-name='CN']/value[1]"/>
		<xsl:variable name="oraclerolemembershipsid" select="$result/nds/output/instance[1]/attr[@attr-name='OrclSID']/value[1]"/>
		<!--	_________________	Query Oracle for existing attributes __________________ -->
		<xsl:variable name="dest-query">
			<query class-name="Group" dest-dn="{$oraclemember}" scope="entry">
				<read-attr attr-name="CN"/>
			</query>
		</xsl:variable>
		<!--	_________________	Paste exiting attributes into variables __________________ -->
		<xsl:variable name="result2" select="query:query($destQueryProcessor, $dest-query)"/>
		<xsl:variable name="oraclerolemember" select="$result2/nds/output/instance[1]/attr[@attr-name='CN']/value[1]"/>
		<xsl:if test="string($oraclerolemembershipsid)= '~ConnectedSystemName~'">
			<!-- These stored procedures must not be called within user-defined (manual) transactions -->
			<jdbc:statement jdbc:op-id="0">
				<jdbc:sql>
					<xsl:text>REVOKE </xsl:text>
					<xsl:value-of select="$oraclerolemembership"/>
					<xsl:text> FROM </xsl:text>
					<xsl:value-of select="$oraclerolemember"/>
				</jdbc:sql>
			</jdbc:statement>
		</xsl:if>
	</xsl:template>
</xsl:transform>

Figure 38: Location for the 2 additionnal XSLT rules for Group DDL and Priv DDL

31. Create a new Publisher Command Transform Policy to take care of Adding or Removing Oracle Roles for User or Group. We detect Add/Delete events on the OrclPriv class, we identify Grantee and GrantedRole through the Priv object name, using our separator -Priv-, and then we add or remove the Group Membership.

Figure 39: New Publisher Command Transform Policy

Replacing the Triggers

Now we need to implement our replacement for Triggers (built-in Triggerless support). In earlier versions of my Oracle drivers, I had to design a custom process to tick a Clock object in the tree. That process would trigger a Policy to run a comparison between the information in eDirectory and Oracle, initiating the required changes. Now with Identity Manager 3.5, we can use Jobs to accomplish the same thing.

We need to create a new Job.

Figure 40: The new Jobs page for Identity Manager 3.5

1. Launch the Create Jobs wizard.

Figure 41: Create Job wizard

2. Select the Job to run daily - at 1:00 a.m., for example.

Figure 42: Selecting the Job to run daily

Figure 43: Job create for our Triggerless process.

3. Assign trustee rights to the driver for the Job "Oracle".

Figure 44: Assigning trustee rights

Figure 45: Assigned rights for the DirXML-AccessSubmitCommand attribute set to write.

If you start the Job manually, you should see the following:

Figure 46: Successfully running the Job manually

4. Look at DSTrace - the following Input document should be found:

<nds dtdversion="3.5" ndsversion="8.x">
<source>
<product version="3.5.1.20070411 ">DirXML</product>
<contact>Novell, Inc.</contact>
</source>
<input>
<trigger event-id="trigger-job:Oracle#20070508224731#0#0" source="Oracle">
<operation-data source="Oracle"/>
</trigger>
</input>
</nds>

We will use the above event as the trigger for comparing eDirectory and Oracle information. We can use the following match criteria:

<xsl:if test="starts-with(@event-id,'trigger-job:Oracle')">

Note: If you are working with Identity Manager 2.01 or 3.01, Jobs are not available. The alternative would be to use a Clock object, and the match criteria would be:

<xsl:template match="modify[@class-name='Clock']" xmlns:jdbc="urn:dirxml:jdbc">

You can refer to my past Articles for more information on how to use the Clock object: http://www.novell.comhttp://www.novell.com/coolsolutions/author/783.html

So, we need to add a new XSLT rule at the Subscriber Event Transform level, as shown below.

The following XSLT rule will react when the Job executes, read the entries from the 3 views in memory, read the information in eDirectory, and do a compare. For deltas, the rule will either add events to idmUser.DIRECT_PROCESS as if an event had occured on the Oracle side, or update eDirectory.

<?xml version="1.0" encoding="UTF-8"?><xsl:transform version="1.0" xmlns:cmd="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.XdsCommandProcessor" xmlns:query="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.XdsQueryProcessor" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:param name="destQueryProcessor"/>
	<xsl:param name="srcQueryProcessor"/>
	<xsl:param name="srcCommandProcessor"/>
	<xsl:param name="destCommandProcessor"/>
	<!-- identity template -->
	<xsl:template match="node()|@*">
		<xsl:if test="starts-with(@event-id,'trigger-job:Oracle')">
			<xsl:message>We are in the Sub-ET for a Job event</xsl:message>
				<xsl:call-template name="clock-tick-acct">
				</xsl:call-template>
				<xsl:call-template name="clock-tick-role">
				</xsl:call-template>
				<xsl:call-template name="clock-tick-priv">
				</xsl:call-template>
		</xsl:if>
		<xsl:copy>
			<xsl:apply-templates select="node()|@*"/>
		</xsl:copy>
	</xsl:template>
	<!-- Adds SQL for clock tick for Users -->
	<xsl:template name="clock-tick-acct" xmlns:Mapping="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.jdbc.util.MappingPolicy" xmlns:jdbc="urn:dirxml:jdbc">
		<xsl:message>We are in the stylesheet Sub-Clock2 for a clock tick for Accounts</xsl:message>
		<!-- Query Oracle for Users -->
		<xsl:variable name="query2">
			<jdbc:statement jdbc:type="query">
				<jdbc:sql>
					<xsl:text>SELECT pk_USERNAME FROM view_ORCLUSERS</xsl:text>
				</jdbc:sql>
			</jdbc:statement>
		</xsl:variable>
		<xsl:variable name="result2" select="query:query($destQueryProcessor,$query2)"/>
		<xsl:variable name="orcl_users" select="$result2//jdbc:result-set/jdbc:row/jdbc:column[@jdbc:name='PK_USERNAME']/jdbc:value"/>
		<xsl:message>Log the list of Oracle Users</xsl:message>
		<xsl:for-each select="$orcl_users">
			<xsl:message>
				<xsl:value-of select="."/>
			</xsl:message>
		</xsl:for-each>
		<!--	_________________	Query eDir for existing attributes __________________ -->
		<xsl:variable name="src-query">
			<query class-name="Group" dest-dn="concat('~ControlGroupOU~','\','~ConnectedSystemName~','grp')" scope="entry">
				<read-attr attr-name="Member"/>
			</query>
		</xsl:variable>
		<!--	_________________	Paste exiting eDir attributes into variables __________________ -->
		<xsl:variable name="result" select="query:query($srcQueryProcessor, $src-query)"/>
		<xsl:variable name="members" select="$result//value"/>
		<xsl:choose>
			<xsl:when test="$members">
				<xsl:for-each select="$members">
					<xsl:if test="$orcl_users != substring-after(.,concat('~UsersOU~','\'))">
						<xsl:message>Account has been removed from Oracle : <xsl:value-of select="substring-after(.,concat('~UsersOU~','\'))"/>
						</xsl:message>
						<xsl:call-template name="deactivate-acct">
							<xsl:with-param name="dn" select="."/>
						</xsl:call-template>
					</xsl:if>
				</xsl:for-each>
			</xsl:when>
			<xsl:otherwise>
				<xsl:message>There are no deleted Oracle users for this instance</xsl:message>
			</xsl:otherwise>
		</xsl:choose>
		<xsl:choose>
			<xsl:when test="$orcl_users">
				<xsl:for-each select="$orcl_users">
					<xsl:if test="concat('~UsersOU~','\',.) != $members">
						<xsl:message>Account has been added to Oracle : <xsl:value-of select="."/>
						</xsl:message>
						<xsl:call-template name="activate-acct">
							<xsl:with-param name="orcl_user" select="."/>
						</xsl:call-template>
					</xsl:if>
				</xsl:for-each>
			</xsl:when>
			<xsl:otherwise>
				<xsl:message>There are no new Oracle users for this instance</xsl:message>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>
	<!-- Deactivate acct -->
	<xsl:template name="deactivate-acct" xmlns:Mapping="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.jdbc.util.MappingPolicy" xmlns:jdbc="urn:dirxml:jdbc">
		<xsl:param name="dn"/>
		<xsl:message>We are in the stylesheet Sub-Clock2 for deactivating an account</xsl:message>
		<xsl:message>Account has been removed from Oracle</xsl:message>
		<!-- ============================================================ -->
		<!-- Generate the XDS fragment that will remove the Group Membership for MyDBGrp -->
		<!-- ============================================================ -->
		<xsl:variable name="add-cmd-update">
			<modify class-name="User" dest-dn="{$dn}">
				<modify-attr attr-name="Group Membership">
					<remove-value>
						<value>
							<xsl:value-of select="concat('~ControlGroupOU~','\','~ConnectedSystemName~','grp')"/>
						</value>
					</remove-value>
				</modify-attr>
			</modify>
			<remove-association>
				<xsl:value-of select="concat('PK_USERNAME=',substring-after($dn,concat('~glob.vault.u.context~','\')),',table=view_ORCLUSERS,schema=idmUser')"/>
			</remove-association>
		</xsl:variable>
		<!-- ============================================================ -->
		<!-- Send the XDS fragment generated above to the Src Directory -->
		<!-- ============================================================ -->
		<xsl:variable name="addResults" select="cmd:execute($srcCommandProcessor, $add-cmd-update)"/>
	</xsl:template>
	<!-- Activate acct -->
	<xsl:template name="activate-acct" xmlns:Mapping="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.jdbc.util.MappingPolicy" xmlns:jdbc="urn:dirxml:jdbc">
		<xsl:param name="orcl_user"/>
		<xsl:message>We are in the stylesheet Sub-Clock2 for activating an account</xsl:message>
		<xsl:message>Account has been added to Oracle</xsl:message>
		<xsl:message>Create user event in eventlog</xsl:message>
		<jdbc:statement jdbc:op-id="0" jdbc:transaction-id="0" jdbc:transaction-type="manual" jdbc:type="update">
			<jdbc:sql>
				<xsl:text>INSERT INTO idmUser.DIRECT_PROCESS</xsl:text>
				<xsl:text>(record_id, event_type, perpetrator, table_name, table_key)</xsl:text>
				<xsl:text>VALUES(idmUser.seq_log_record_id.nextval, 5, 'fake', 'view_ORCLUSERS', 'pk_USERNAME=</xsl:text>
				<xsl:value-of select="$orcl_user"/>
				<xsl:text>')</xsl:text>
			</jdbc:sql>
		</jdbc:statement>
	</xsl:template>
	<!-- Adds SQL for clock tick for Roles -->
	<xsl:template name="clock-tick-role" xmlns:Mapping="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.jdbc.util.MappingPolicy" xmlns:jdbc="urn:dirxml:jdbc">
		<xsl:message>We are in the stylesheet Sub-Clock2 for a clock tick for Roles</xsl:message>
		<!-- Query Oracle for Roles -->
		<xsl:variable name="query2">
			<jdbc:statement jdbc:type="query">
				<jdbc:sql>
					<xsl:text>SELECT pk_ROLE FROM view_ORCLROLES</xsl:text>
				</jdbc:sql>
			</jdbc:statement>
		</xsl:variable>
		<xsl:variable name="result2" select="query:query($destQueryProcessor,$query2)"/>
		<xsl:variable name="orcl_roles" select="$result2//jdbc:result-set/jdbc:row/jdbc:column[@jdbc:name='PK_ROLE']/jdbc:value"/>
		<xsl:message>Log the list of Oracle Roles</xsl:message>
		<xsl:for-each select="$orcl_roles">
			<xsl:message>
				<xsl:value-of select="."/>
			</xsl:message>
		</xsl:for-each>
		<!--	_________________	Query eDir for existing attributes __________________ -->
		<xsl:variable name="src-query">
			<query dest-dn="concat('~OracleOU~','\','~ConnectedSystemName~')" scope="subtree">
				<search-attr attr-name="OrclSID">
					<value type="string">~ConnectedSystemName~</value>
				</search-attr>
				<read-attr attr-name="CN"/>
			</query>
		</xsl:variable>
		<!--	_________________	Paste exiting eDir attributes into variables __________________ -->
		<xsl:variable name="result" select="query:query($srcQueryProcessor, $src-query)"/>
		<xsl:variable name="roles" select="$result//value"/>
		<xsl:choose>
			<xsl:when test="$roles">
				<xsl:for-each select="$roles">
					<xsl:if test="$orcl_roles != .">
						<xsl:message>Role has been removed from Oracle : <xsl:value-of select="."/>
						</xsl:message>
						<xsl:call-template name="deactivate-role">
							<xsl:with-param name="role" select="."/>
						</xsl:call-template>
					</xsl:if>
				</xsl:for-each>
			</xsl:when>
			<xsl:otherwise>
				<xsl:message>There are no deleted Oracle Roles for this instance</xsl:message>
			</xsl:otherwise>
		</xsl:choose>
		<xsl:choose>
			<xsl:when test="$orcl_roles">
				<xsl:for-each select="$orcl_roles">
					<xsl:if test="$roles != .">
						<xsl:message>Role has been added to Oracle : <xsl:value-of select="."/>
						</xsl:message>
						<xsl:call-template name="activate-role">
							<xsl:with-param name="role" select="."/>
						</xsl:call-template>
					</xsl:if>
				</xsl:for-each>
			</xsl:when>
			<xsl:otherwise>
				<xsl:message>There are no new Oracle Roles for this instance</xsl:message>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>
	<!-- DeActivate Role -->
	<xsl:template name="deactivate-role" xmlns:Mapping="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.jdbc.util.MappingPolicy" xmlns:jdbc="urn:dirxml:jdbc">
		<xsl:param name="role"/>
		<xsl:message>We are in the stylesheet Sub-Clock2 for deactivating a role</xsl:message>
		<xsl:message>Role has been removed from Oracle</xsl:message>
		<!-- ============================================================ -->
		<!-- Generate the XDS fragment that will remove the Group from eDirectory -->
		<!-- ============================================================ -->
		<xsl:variable name="del-group" select="concat('~OracleOU~','\','~ConnectedSystemName~','\',$role)"/>
		<xsl:variable name="add-cmd-update">
			<delete class-name="Group" dest-dn="{$del-group}"/>
		</xsl:variable>
		<!-- ============================================================ -->
		<!-- Send the XDS fragment generated above to the Src Directory -->
		<!-- ============================================================ -->
		<xsl:variable name="addResults" select="cmd:execute($srcCommandProcessor, $add-cmd-update)"/>
	</xsl:template>
	<!-- Activate Role -->
	<xsl:template name="activate-role" xmlns:Mapping="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.jdbc.util.MappingPolicy" xmlns:jdbc="urn:dirxml:jdbc">
		<xsl:param name="role"/>
		<xsl:message>We are in the stylesheet Sub-Clock2 for activating a Role</xsl:message>
		<xsl:message>Role has been added to Oracle</xsl:message>
		<xsl:message>Create Role event in eventlog</xsl:message>
		<jdbc:statement jdbc:op-id="0" jdbc:transaction-id="0" jdbc:transaction-type="manual" jdbc:type="update">
			<jdbc:sql>
				<xsl:text>INSERT INTO idmUser.DIRECT_PROCESS</xsl:text>
				<xsl:text>(record_id, event_type, perpetrator, table_name, table_key)</xsl:text>
				<xsl:text>VALUES(idmUser.seq_log_record_id.nextval, 5, 'fake', 'view_ORCLROLES', 'pk_ROLE=</xsl:text>
				<xsl:value-of select="$role"/>
				<xsl:text>')</xsl:text>
			</jdbc:sql>
		</jdbc:statement>
	</xsl:template>
	<!-- Adds SQL for clock tick for Role_Privs -->
	<xsl:template name="clock-tick-priv" xmlns:Mapping="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.jdbc.util.MappingPolicy" xmlns:jdbc="urn:dirxml:jdbc">
		<xsl:param name="cn"/>
		<xsl:param name="currenttime"/>
		<xsl:message>We are in the stylesheet Sub-Clock2 for a clock tick for Privs</xsl:message>
		<!-- Query Oracle for Privs -->
		<xsl:variable name="query2">
			<jdbc:statement jdbc:type="query">
				<jdbc:sql>
					<xsl:text>SELECT pk_GRANTEE ||'-Priv-' || pk_GRANTED_ROLE PRIV FROM view_ORCLPRIVS</xsl:text>
				</jdbc:sql>
			</jdbc:statement>
		</xsl:variable>
		<xsl:variable name="result2" select="query:query($destQueryProcessor,$query2)"/>
		<xsl:variable name="orcl_privs" select="$result2//jdbc:result-set/jdbc:row/jdbc:column[@jdbc:name='PRIV']/jdbc:value"/>
		<xsl:message>Log the list of Oracle Privs</xsl:message>
		<xsl:for-each select="$orcl_privs">
			<xsl:message>
				<xsl:value-of select="."/>
			</xsl:message>
		</xsl:for-each>
		<!--	_________________	Query eDir for existing attributes __________________ -->
		<xsl:variable name="src-query">
			<query dest-dn="concat('~OracleOU~','\','~ConnectedSystemName~')" scope="subtree">
				<search-attr attr-name="Object Class">
					<value type="string">OrclPriv</value>
				</search-attr>
				<read-attr attr-name="CN"/>
			</query>
		</xsl:variable>
		<!--	_________________	Paste exiting eDir attributes into variables __________________ -->
		<xsl:variable name="result" select="query:query($srcQueryProcessor, $src-query)"/>
		<xsl:variable name="privs" select="$result//value"/>
		<xsl:choose>
			<xsl:when test="$privs">
				<xsl:for-each select="$privs">
					<xsl:if test="$orcl_privs != .">
						<xsl:message>Priv has been removed from Oracle : <xsl:value-of select="."/>
						</xsl:message>
						<xsl:call-template name="deactivate-priv">
							<xsl:with-param name="priv" select="."/>
						</xsl:call-template>
					</xsl:if>
				</xsl:for-each>
			</xsl:when>
			<xsl:otherwise>
				<xsl:message>There are no Oracle Privs for this instance</xsl:message>
			</xsl:otherwise>
		</xsl:choose>
		<xsl:choose>
			<xsl:when test="$orcl_privs">
				<xsl:for-each select="$orcl_privs">
					<xsl:if test="$privs != .">
						<xsl:message>Priv has been added to Oracle : <xsl:value-of select="."/>
						</xsl:message>
						<xsl:call-template name="activate-priv">
							<xsl:with-param name="priv" select="."/>
						</xsl:call-template>
					</xsl:if>
				</xsl:for-each>
			</xsl:when>
			<xsl:otherwise>
				<xsl:message>There are no new Oracle Privs for this instance</xsl:message>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>
	<!-- DeActivate priv -->
	<xsl:template name="deactivate-priv" xmlns:Mapping="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.jdbc.util.MappingPolicy" xmlns:jdbc="urn:dirxml:jdbc">
		<xsl:param name="priv"/>
		<xsl:message>We are in the stylesheet Sub-Clock2 for Deactivating a Priv</xsl:message>
		<xsl:message>Priv has been added to Oracle</xsl:message>
		<xsl:message>Create Priv event in eventlog</xsl:message>
		<jdbc:statement jdbc:op-id="0" jdbc:transaction-id="0" jdbc:transaction-type="manual" jdbc:type="update">
			<jdbc:sql>
				<xsl:text>INSERT INTO idmUser.DIRECT_PROCESS</xsl:text>
				<xsl:text>(record_id, event_type, perpetrator, table_name, table_key)</xsl:text>
				<xsl:text>VALUES(idmUser.seq_log_record_id.nextval, 4, 'fake', 'view_ORCLPRIVS', 'pk_GRANTEE=</xsl:text>
				<xsl:value-of select="substring-before($priv,'-Priv-')"/>
				<xsl:text>+pk_GRANTED_ROLE=</xsl:text>
				<xsl:value-of select="substring-after($priv,'-Priv-')"/>
				<xsl:text>')</xsl:text>
			</jdbc:sql>
		</jdbc:statement>
	</xsl:template>
	<!-- Activate Priv -->
	<xsl:template name="activate-priv" xmlns:Mapping="http://www.novell.com/nxsl/java/com.novell.nds.dirxml.driver.jdbc.util.MappingPolicy" xmlns:jdbc="urn:dirxml:jdbc">
		<xsl:param name="priv"/>
		<xsl:message>We are in the stylesheet Sub-Clock2 for activating a Priv</xsl:message>
		<xsl:message>Priv has been added to Oracle</xsl:message>
		<xsl:message>Create Priv event in eventlog</xsl:message>
		<jdbc:statement jdbc:op-id="0" jdbc:transaction-id="0" jdbc:transaction-type="manual" jdbc:type="update">
			<jdbc:sql>
				<xsl:text>INSERT INTO idmUser.DIRECT_PROCESS</xsl:text>
				<xsl:text>(record_id, event_type, perpetrator, table_name, table_key)</xsl:text>
				<xsl:text>VALUES(idmUser.seq_log_record_id.nextval, 5, 'fake', 'view_ORCLPRIVS', 'pk_GRANTEE=</xsl:text>
				<xsl:value-of select="substring-before($priv,'-Priv-')"/>
				<xsl:text>+pk_GRANTED_ROLE=</xsl:text>
				<xsl:value-of select="substring-after($priv,'-Priv-')"/>
				<xsl:text>')</xsl:text>
			</jdbc:sql>
		</jdbc:statement>
	</xsl:template>
</xsl:transform>

We need to add a few things to our driver before we can testdrive it.

6. Create a new Policy at the Output Transform Level(Subscriber).

Figure 47: Creating a new Policy

This new Policy is there to prevent the driver to try to Sync events down to Oracle. If we don't Veto these events, the driver will try to update the views with new Users, Roles or Privs, which would result in errors.

Figure 48:

Figure 49: We also need to disable the above Policy unless we configure a SMTP server for e-mails. This can be enabled once the SMTP server config is available.

Testing

Now we are all ready to test-drive the driver for both channels.

Figure 50: Testing the driver

As soon as you run the Oracle Job, the driver should start importing Oracle Users/Accounts into eDirectory, under the UsersOU.

Figure 51: Importing the Oracle Users/Accounts into eDirectory

If you look at the Group Memberships for an Oracle User, you should see the Control Group + the Oracle Roles the User is a member of. You will also see the Oracle roles in eDirectory under the ConnectedSystemName/SID for your Oracle system.

Figure 52: Control Group for Oracle roles

The Priv objects can be seen under the ConnectedSystemName/SID for your Oracle system.

Figure 53: Priv objects

Now if you create a new User in eDirectory, the new User will be created in Oracle as soon as you add it to the Control Group(e.g. Ora10gR2grp). After that, if you add the User to a Oracle Role that belongs to your Oracle system, the Role will be granted in Oracle.

Also, if you create a new Group in eDirectory and add the Auxiliary Class OrclRole to this Group with a OrclSID value that corresponds to your Oracle system(e.g. Ora10gR2), the Role will be created in Oracle. To add the Auxiliary Class, you can use Plug-In Studio in iManager(create a Modify form for Group with the Aux Class), or the Schema/Object Extensions task in iManager.

Conclusion

I may submit further improvements to this driver based on my experimentations/projects or the feedback I will receive from the readers. Don't hesitate to send feedback, questions, or comments to me at mbluteau@novell.com.

Here is the link to my driver export, which you can use to save yourself some time.


Novell Cool Solutions (corporate web communities) are produced by WebWise Solutions. www.webwiseone.com

© 2014 Novell