Novell Home

Advanced triggerless Oracle JDBC driver for Users and Roles

Novell Cool Solutions: Feature
By Michel Bluteau

Digg This - Slashdot This

Posted: 17 Mar 2005
 

Advanced triggerless Oracle JDBC driver for Users and Roles

Michel Bluteau
mbluteauTAKETHISOUT@novell.com

*UPDATE: Support files are now available for this article:

This driver is derived from the Direct example provided with the JDBC Driver for Oracle (with views and the Eventlog table). It was put together using Identity Manager 2.01 and will work out of the box with Oracle 8i, 9i and 10g. Here are some of the goals I had in mind while working on this driver:

  • Reduce the logic required on the Oracle side, e.g., Staging tables, Triggers, Stored Procedures, DBMS_Jobs and other Packages.
  • Provide support for multiple Oracle drivers connecting multiple Oracle security stores (catalog). These stores are represented by multiple instances for the views DBA_USERS, DBA_ROLES, and DBA_ROLE_PRIVS, all in the same eDirectory tree.
  • Provide a way to provision Oracle security stores one by one. For example, a given eDirectory user may have accounts in some Oracle instances but not all of them, or its role memberships may be different in different instances.
  • Keep the schema extensions for eDirectory to a minimum.

Here are the main characteristics for this driver:

  • It requires a mechanism for time-triggered events/actions in eDirectory (see http://www.novell.com/coolsolutions/tip/8619.html).
  • It synchronizes Users, Roles, and Role Memberships for both Users and Roles.
  • It is bi-directional.
  • It provides support for password synchronization from eDirectory to Oracle.
  • It supports Oracle 8i, 9i and 10g through the thin JDBC driver provided by Oracle.
  • It leverages both Policy Builder rules and XSLT (for encapsulating SQL DDL statements).
  • It uses Group Memberships in eDirectory for Oracle Roles.

The following is a description of the driver's components. The synchronization events or scenarios that the driver must support are listed, and driver configuration information is included. My goal is to provide some way of understanding each and every piece of logic that is part of the driver, in order to facilitate customization or improvements.

Creating the Schema Extensions

Some schema extensions are required for eDirectory before the driver can be loaded. Here are the steps to follow:

1. Create an auxiliary class gqcrqOrclAcct, for the class User. The class contains one attribute, gqcrqOrclAcct, which is a single-value, case-insensitive string. This attribute contains the Oracle USERNAME for the user and should be identical to CN if possible.

Figure 1: Auxiliary class gqcrqOrclAcct for User.

*Larger image

2. Create an auxiliary class gqcrqOrclRole for Group, which contains the following three attributes:

  • gqcrqOrclRole, a single-value, case-insensitive string mapped to the Oracle Role name. This should be identical to CN for Group.
  • gqcrqOrclSID, a single-value, case-insensitive string. This must contain the Oracle SID name for the Oracle security store (MyDB in this article).
  • Group Membership, used to Grant a Group (Oracle Role) to another Group

Figure 2: Auxiliary class gqcrqOrclRole for Group.

*Larger image

3. Create an Effective class gqcrqOrclPriv, 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:

  • gqcrqOrclGrantee, 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.
  • gqcrqOrclGrantedRole, 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 3: Effective class gqcrqOrclPriv for Group Memberships on Publisher Channel.

*Larger image

This class is used by the driver to manage Roles on the Publisher Channel (Oracle to eDirectory). It does not need to be managed by the administrator, who is concerned only with managing Group Memberships.

Note: Objects need to be created on the Oracle side for the driver to operate. You need to create a user/schema where the eventlog table and three views for the systems views DBA_USERS, DBA_ROLES, and DBA_ROLE_PRIVS will reside. The main reason why we do not use the system views directly is that we need to rename some columns to mimic Primary Keys for a table, prefixing them with pk_. This is required by the IdM JDBC driver. The process for doing all this is described below.

4. Create the user/schema by connecting to Oracle with SYSTEM or an equivalent account, and executing the following DDL statements through SQLplus, iSQLplus, TOAD, etc:

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

The above exmaple creates a user named "idm" with a password ("dirxml"). It assigns the USERS tablespace to the user, and TEMP for the temporary tablespace. This will also allow idm to connect to Oracle.

5. Grant additional privileges necessary for the IDM2 driver to create accounts and assign Roles and Privileges:

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

The above grants sessions privileges to idm, as well as privileges to create Tables, Sequences, Procedures (optional) and Views in the idm Schema. It also enables idm to create, alter, and drop Users and Roles, as well as grant Roles and Privileges.

6. While logged in as SYSTEM, grant explicit "read-only" access to the security views:

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

Only the SELECT Privilege is required. Do not modify these views: instead, you will use DDL (Data Definition Language) statements such as CREATE USER in order to manage the security store (catalog).

7. Now that the idm user has been created, log in with idm and create the objects in the idm Schema. The following script will create the three views for the catalog:

CREATE OR REPLACE VIEW idm.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 idm.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 idm.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;

Note: You need to prefix the virtual Primary Keys with pk_ so the driver can uniquely identify records or rows.

8. Create the Eventlog Table, used by the driver as a changelog for Oracle events:

CREATE TABLE IDM.eventlog
(
	record_id;      NUMBER(32)       NOT NULL         UNIQUE,
	table_key;      VARCHAR2(96)     NOT NULL,		
	statusnbsp;     CHAR(1)          DEFAULT 'N',
	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_eventlog_event_type CHECK(event_type IN (1, 2, 3, 4, 5, 6, 7, 8))
);	
CREATE UNIQUE INDEX i_eventlog_1 ON idm.eventlog(table_key, record_id, status);
CREATE UNIQUE INDEX i_eventlog_2 ON idm.eventlog(record_id, table_key);

CREATE SEQUENCE idm.seq_recid
	START WITH 1
	INCREMENT BY 1
	NOMINVALUE
	NOMAXVALUE
	CACHE 100
	ORDER;

The Eventlog table is used, but not in a conventional way. Normally for an Oracle application, triggers must be implemented in the application tables. Because you're working with the catalog in the SYS Tablespace, using triggers is strongly discouraged - it could wreak havoc with the catalog if improperly defined, and they could be lost after an upgrade. Instead, the Subscriber Channel (eDirectory to Oracle) will be used to create "fake" triggers by querying the catalog through the views, and adding records into Eventlog. The Publisher Channel will detect those fake records/events, compare the information with eDirectory, and synchronize the information.

Creating the Driver

Now we are ready to move to the driver itself.

1. Import the xml template for the driver (if you can).

This driver is based on Time Triggered Events that are implemented through an object of type Clock. Some mechanism, such as CRON on UNIX/Linux or Windows Tasks Scheduler, must be able to run a script that uses LDAPMODIFY and updates an attribute for the Clock object in eDirectory, for example with the current time.

Figure 4: Clock object for Time Triggered Events

*Larger image

Clock is derived from Top. It uses CN for naming, and it has one optional attribute, currentTime, of type Time, single-value, sync-immediate.

The Clock is used as the triggering mechanism for the driver. It queries the Oracle catalog through view_ORCLUSERS, view_ORCLROLES and view_ORCLPRIVS, and updates eDirectory with the updates from Oracle. CRON or Windows Tasks Scheduler can be configured to run once an hour, once a day, or at any chosen interval, to detect changes in Oracle such as new or deleted Users or Roles, or Role Assignment.

You can create a special account for LDAPMODIFY that modifies the currentTime attribute only for the Clock object, because the password will appear in clear text in the script. For testing purposes, one can manually update the Clock through a plug-in Studio form. For more information, see the previously mentioned Cool Solutions article (http://www.novell.com/coolsolutions/tip/8619.html).

Figure 5: Plug-in Studio form for modifying the Clock object.

*Larger image

For the driver described in this article, I am using an arbitrary eDirectory structure that must be change to fit your own eDirectory structure. Using a proper text editor (that does not modify the format of the document),

1. Do a search and replace on the xml template document for the driver prior to importing the driver into eDirectory,

OR

Edit every Policy Builder rule or XSLT stylesheet to replace the static information associated to the eDirectory tree. Global Variables could be used for some but not all these pieces of information.

Here is a quick overview of the tree:

  • Treename: META
  • Users context: OU=users,O=novl,C=ca
  • Oracle objects context: OU=Oracle,O=novl,C=ca
  • Clock: CN=MyDBClock,OU=Oracle,O=novl,C=ca
  • Oracle Accounts Group, one per instance: CN=MyDBGrp,OU=Oracle,O=novl,C=ca
  • Oracle OU for Roles and Privs, one per instance: OU=MyDB,OU=Oracle,O=novl,C=ca

The driver for MyDB Oracle SID is shown below.

Figure 6: Driver for MyDB Oracle SID

*Larger image

2. Set up the Authentication context to match the IP Address, the Port (default is 1521) and the SID name (MyDB in the example) to match the Oracle server being used.

Figure 7: Authentication Page for the driver

3. Set the Subscriber Settings set for no Primary Key generation, because you won't be modifying the views directly.

Figure 8: Subscriber Settings (no Primary Key generation)

4. Create the mapping rules as shown below.

Figure 9: Mapping Rule

Figure 10: Mapping Rules, attributes for User

Figure 11: Mapping Rule for Group

Figure 12: Mapping Rule for Priv

5. Create the filter shown below.

Figure 13: Filter

The Filter does not need to Synchronize the Attribute for the Subscriber Channel (eDirectory to Oracle), so Attributes for User and Group are set to Notify on Subscriber. For the Publisher Channel, only the USERNAME and ROLE need to be synchronized back to eDirectory from Oracle.

Priv is not synchronized on the Subscriber Channel, because the Attribute Group Membership is used to udpate Oracle. Priv is only used as a stepping stone on the Publisher Channel, to maintain Group Membership.

Clock is set only to Notify, so any modification to Clock will trigger actions for the driver.

6. Create the matching rule as shown below.

Figure 14: Matching Rule for the driver

The Matching Rule looks at the Oracle USERNAME (gqcrqOrclAcct) for User, the Role Name (gqcrqOrclRole) for Group, and for Priv. The Priv is compared with GRANTEE (gqcrqOrclGrantee) and GRANTED_ROLE (gqcrqOrclGrantedRole) to uniquely identify a Role Membership.

7. Create the Publisher Create rule for required attributes, as shown below.

Figure 15: Publisher Create Rule, Required Attributes

The Publisher (Oracle to eDirectory) Create Rule will first validate that the required attributes are available before moving forward with the creation of new objects in eDirectory. For User, you need the Oracle USERNAME (gqcrqOrclAcct). For Group, you need the Role Name(gqcrqOrclRole). For Priv, you need both GRANTEE and GRANTED_ROLE.

8. If the Create Rule establishes that the required attributes are available, before it passes the event to the Placement Rule, it must add the extra attributes required for the driver:

Figure 16: Publisher Create Rule for adding attributes

For User, it must add first the Auxiliary Class gqcrqOrclAcct, through the attribute Object Class, because the attribute gqcrqOrclAcct belongs to the Auxiliary Class (not the Effective Class User). It must also add a Surname, which is a mandatory attribute for User. Because DBA_USERS does not include a Surname, one can be created (MyDB). It must also add the SID Group into the multi-value attribute Group Membership. This is how you know that the User owns an account in a given Oracle Instance. A User can own an account in zero, one, or multiple Oracle instances.

For Group, we must add the Auxiliary Class gqcrqOrclRole, and gqcrqOrclSID, which identify the Group to a Role for a given Oracle Instance.

9. Create the Subscriber Create rule for Group as shown below.

Figure 17: Subscriber Create Rule for Group

The Subscriber (eDirectory to Oracle) for Group must validate, before creating a new Role in Oracle, that the ROLE NAME (gqcrqOrclRole) is available, and that the SID (gqcrqOrclSID) belongs to this Instance of Oracle.

Figure 18: Subscriber Create Rule for Clock and User

The Subscriber Create Rule must prevent the creation of the Clock object in Oracle, because it does not exist there. Without this Rule, a cosmetic error message would appear in red in DSTrace, informing us of the failure to create the Clock object in Oracle.

Note: Make sure that the User belongs to this specific Oracle Instance (associated to this driver) by validating that the User is a member of the Control Group for the Instance (MyDBGrp).

10. Add associations for User and Group as shown below.

Figure 19: Subscriber Create Rule for adding Associations for User and Group

Because we do not allow the driver to modify the catalog directly through the views, we need to add some logic in order to maintain Associations. Here in the Create Rule for Subscriber, we add Associations for User and Group.

11. Create an Output Transform for Subscriber that will remove associations, as shown below.

Figure 20: Subscriber Output Transform for Removing Associations for Delete on User

The Output Transform for Subscriber detects when a User has an Association but has been removed from the Control Group for the Instance. The triggered Action removes the Association.

12. Block any direct (non-DDL) attempts to add or delete objects.

Figure 21: Subscriber Output Transform preventing Add and Delete event to get to Oracle directly

Because we use DDL statements(to be described later) instead of trying to add/delete objects directly through the views, we must veto such attempts.

13. Create a Publisher Event Transfor for User as shown below.

Figure 22: Publisher Event Transform for User

The eDirectory User should not be deleted whenever an Associated Account in Oracle is deleted. (Remember that the User can have accounts in zero, one, or multiple Oracle Instances). Therefore, a Delete Event on the Publisher Channel must be transformed into a Remove Group Membership for the Instance's Control Group. The Association must also be removed.

14. Create a Publisher Placement rule as shown below.

Figure 23: Publisher Placement Rule.

A Placement Rule is not needed for the Subscriber Channel (the catalog is flat), but it is needed for Publisher, since eDirectory is not flat.

Users need to be created in the right place. The driver is set up to manage a flat structure for Users. In most deployments, a flat structure for Users is preferred for the eDirectory tree handling drivers for connected systems (which are mostly flat). Another eDirectory tree (not flat) is linked to the tree for connected systems through the eDirectory driver. This helps isolate end-user services like iManager and eGuide from physical connections to connected systems, among other reasons.

15. Create the Group in the OU assigned to this specific Instance. This same OU is used for Priv, and the name is set to GRANTEE-GRANTED_ROLE.

Driver Details

Now let's move on to the core elements for this driver. First we will look at the logic on the Subscriber Channel (eDirectory to Oracle) used for creating Oracle Accounts and Roles, and for managing Role Memberships and Privileges.

Figure 24: Subscriber Command Transform for Creating Accounts and Roles in Oracle

Because SQL DDL statements are used to modify the Oracle Catalog, XSLT is the way to go. This is the one example I am aware of for which Policy Builder cannot be used today. This first XSLT Stylesheet takes care of Creating Accounts and Roles in Oracle, and it takes care of Password Sync from eDirectory to Oracle. Let's quickly look at the most important pieces.

1. Make sure that if no rule (template) in this stylesheet is selected, the event is passed (copied) "as is" for the next StyleSheet or Policy:

<xsl:template match="node()|@*">
		<xsl:copy>
			<xsl:apply-templates select="node()|@*"/>
		</xsl:copy>
	</xsl:template><

2. Detect User Creation Events:

<!-- Adds a database user/login account -->
	<xsl:template match="input/add[@class-name='User']" xmlns:jdbc="urn:dirxml:jdbc">

3. If a User Creation Event is detected, set up a variable with the desired Oracle USERNAME, and make sure that the USERNAME value is not null. Then call the template to add the User in Oracle:

		<xsl:variable name="orclacct" select="add-attr[@attr-name='gqcrqOrclAcct']/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>

4. In the template, add a User in Oracle by copying the event and setting variables. The template also sets the initial password to the USERNAME:

<!-- 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='gqcrqOrclAcct']"/>
			<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>

5. Use the template to create the User with the Connect Privilege. The template encapsulates the SQL DDL statements in order to create the User and grant the privilege to connect:

<!-- 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"/>
				<!-- <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>

In this XSLT Stylesheet there is a template to detect a User Delete Event, and an associated template that encapsulates the SQL DDL statements for dropping the User in Oracle.

6. Use the following template to modify password Events, through the nspmDistributionPassword attribute:

<!-- 
		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']"/>

7. Use the following templates to synchronize Oracle and eDirectory passwords.

The first template will query eDirectory for the the USERNAME, which may not be part of the Event, and call another template to synchronize the new password with Oracle:

<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="gqcrqOrclAcct"/>
					</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='gqcrqOrclAcct']/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>

Below is the template that encapsulates that SQL DDL statements that change the password in Oracle to match the eDirectory 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"/>
				<xsl:text> IDENTIFIED BY {$$password}</xsl:text>
			</jdbc:sql>
		</jdbc:statement>
	</xsl:template>

8. Use an XSLT StyleSheet to manage create/drop Roles in Oracle.

Figure 25: Subscriber Command Transform for Creating/Dropping Roles in Oracle.

This template detects the event when a Role is created:

<!-- 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='gqcrqOrclRole']/value[1]"/>
		<xsl:variable name="sid" select="add-attr[@attr-name='gqcrqOrclSID']/value[1]"/>
		<xsl:choose>
			<xsl:when test="$role and string($sid) = 'MyDB'">
				<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 'gqcrqOrclRole' value or 'gqcrqOrclSID' not equal to 'MyDB'.</xsl:text>
					</status>
				</xsl:message>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>

Then a template is called to create the Role:

<!-- 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='gqcrqOrclRole' or @attr-name='gqcrqOrclSID']"/>
			<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"/>

			</jdbc:sql>
		</jdbc:statement>
	</xsl:template>

This StyleSheet also includes a pair of templates for dropping Roles.

9. Use a Command Transform to manage Role memberships.

Figure 26: Subscriber Command Transform that takes care of Transforming eDirectory Group Memberships in Oracle Role Memberships.

Besides managing Users and Roles in Oracle, you also need to take care of Role Memberships. That is handled through a Command Transform. The template below detects modifications Events for the Group Membership attributes for the User, which can mean that one or more Memberships have been added or deleted:

<!-- 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"/>

Because the deleted values may be multiple in nature, for both new Memberships and removed Memberships, "for-each" is used to parse the values. The Groups need to be mapped to Oracle Roles for this Instance, so you can use a matching pattern to make sure that they are located in the right OU:

<xsl:variable name="rolepattern" select="concat('MyDB','\')"/>
		<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>

For each value, call the appropriate template that takes care of the add or delete event:

<!-- 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="gqcrqOrclRole"/>
				<read-attr attr-name="gqcrqOrclSID"/>
			</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='gqcrqOrclRole']/value[1]"/>
		<xsl:variable name="oraclesid" select="$result/nds/output/instance[1]/attr[@attr-name='gqcrqOrclSID']/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="gqcrqOrclAcct"/>
			</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='gqcrqOrclAcct']/value[1]"/>
		<xsl:if test="string($oraclesid) = 'MyDB'">
			<!-- 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>

We also take care of Group Memberships for Groups in this template in a similar way.

Publisher Channel

Now let's move on to the Publisher Channel for detecting and synchronizing updates in Oracle.

Figure 27: Subscriber Event Transform for detecting triggering the Publisher channel

The following logic enables an operational Publisher Channel (Oracle to eDirectory), but in a "twisted" way. Oracle events are actually faked by "stuffing" the Eventlog table with bogus Creation Events for every Oracle User, Role, and Privilege. This should not be an issue as long as the number of Oracle Users is below a few hundred per instance.

Typically, Users that require Oracle accounts are developers, DBAs, backup operators, etc. Regular end users typically need access to Oracle Applications that manage their security through their own tables (not the catalog), through Oracle Internet Directory, or through shared (public) accounts. Some applications don't even manage accounts but identities only, which are stored in Application Tables, not the catalog. So the approach used for this driver, although somewhat rough, should not cause a problem. The polling interval (clock tick) can be set to hourly or daily - Oracle Accounts, Roles or Privileges will not change very often under most circumstances.

Simulating Creation Events

1. Detect a Clock tick.

2. Start querying the catalog through the View and Insert records/rows in the Eventlog table to simulate Creation Events (type 5). If the driver sees a Creation Event but an association already exists, the event will be changed into a Modify Event.

The example below detects a Clock tick and queries eDirectory for additional information about the Clock that ticked:

<!-- Detect a clock tick -->
	<xsl:template match="modify[@class-name='Clock']" xmlns:jdbc="urn:dirxml:jdbc">
		<xsl:message>We are in the stylesheet Sub-Clock for a clock tick</xsl:message>
		<!--	_________________	Query eDir for existing attributes __________________ -->
		<xsl:variable name="src-query">
			<query class-name="Clock" dest-dn="{@src-dn}" scope="entry">
				<read-attr attr-name="CN"/>
				<read-attr attr-name="currentTime"/>
			</query>
		</xsl:variable>
		<!--	_________________	Paste exiting eDir attributes into variables __________________ -->
		<xsl:variable name="result" select="query:query($srcQueryProcessor, $src-query)"/>
		<xsl:variable name="clock" select="$result/nds/output/instance[1]/attr[@attr-name='CN']/value[1]"/>
		<xsl:variable name="currenttime" select="$result/nds/output/instance[1]/attr[@attr-name='currentTime']/value[1]"/>

3. Validate the currentTime attribute.

You need to validate that there is a value for the Operation Attribute currentTime, and that you are dealing with the correct Clock. You can use a single Clock for all Oracle drivers, or separate Clocks so they do not sync all at once.

<xsl:choose>
			<xsl:when test="$currenttime and string($clock) = 'MyDBClock'">
				<xsl:call-template name="clock-tick">
					<xsl:with-param name="clock" select="$clock"/>
					<xsl:with-param name="currenttime" select="$currenttime"/>
				</xsl:call-template>
			</xsl:when>
			<xsl:otherwise>
				<xsl:message>
					<status level="error" type="password-set-operation">
						<xsl:text>Unable to tick clock. </xsl:text>
						<xsl:text>Missing 'currenttime' value or wrong clock.</xsl:text>
					</status>
				</xsl:message>
				<xsl:copy>
					<xsl:apply-templates select="@*|node()"/>
				</xsl:copy>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>

4. Once you are satisfied with the Clock tick Event, query the catalog and insert records into the Eventlog Table:

<!-- Adds SQL for clock tick -->
	<xsl:template name="clock-tick" 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-Clock for processing a clock tick</xsl:message>
		<xsl:copy>
			<xsl:apply-templates select="node() | @*"/>
		</xsl:copy>
		<xsl:message>Create fake user events 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 idm.eventlog</xsl:text>
				<xsl:text>(record_id, event_type, perpetrator, table_name, table_key)</xsl:text>
				<xsl:text>SELECT idm.seq_recid.nextval, 5, 'fake', 'view_ORCLUSERS', 'pk_USERNAME=' || pk_USERNAME FROM idm.view_ORCLUSERS WHERE pk_USERNAME LIKE 'TESTU%'</xsl:text>
			</jdbc:sql>
		</jdbc:statement>
		<xsl:message>Create fake role events 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 idm.eventlog</xsl:text>
				<xsl:text>(record_id, event_type, perpetrator, table_name, table_key)</xsl:text>
				<xsl:text>SELECT idm.seq_recid.nextval, 5, 'fake', 'view_ORCLROLES', 'pk_ROLE=' || pk_ROLE FROM idm.view_ORCLROLES WHERE pk_ROLE LIKE 'TESTR%'</xsl:text>
			</jdbc:sql>
		</jdbc:statement>
		<xsl:message>Create fake priv events 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 idm.eventlog</xsl:text>
				<xsl:text>(record_id, event_type, perpetrator, table_name, table_key)</xsl:text>
				<xsl:text>SELECT idm.seq_recid.nextval, 5, 'fake', 'view_ORCLPRIVS', 'pk_GRANTEE=' || pk_GRANTEE || '+pk_GRANTED_ROLE=' || pk_GRANTED_ROLE FROM idm.view_ORCLPRIVS WHERE pk_GRANTEE LIKE 'TEST%' AND pk_GRANTED_ROLE LIKE 'TEST%'</xsl:text>
			</jdbc:sql>
		</jdbc:statement>
		<xsl:copy>
			<xsl:apply-templates select="@*|node()"/>
		</xsl:copy>
	</xsl:template>

In this template a perpetrator (fake user) is specified. Otherwise, the default user would be the one used by the driver - the idm user. In that case, a loopback prevention mechanism would filter the Event, so nothing would happen.

Once records/rows are present in the Eventlog Table, depending on the Polling Interval, the Publisher Channel will kick in and start acting on the "fake" events.

Privs Object and Group Memberships

We have already discussed how Users and Groups are being taken care of by the Publisher Channel, but now let's touch quickly on the Privs objects and associated Group Memberships.

Note that the Query for the Insert Statement is being restricted to USERNAMES/Roles that start with the letters "TEST". This synchronizes only those USERNAMES/Roles that start with TEST, and it filters out others like SYSTEM or DBA - this is safer for testing purposes. After the tests are conclusive, you can extend the Synchronization to other records by modifying the Query. You can also choose to always filter out accounts like SYS and SYSTEM, while synchronizing all Roles. The important thing here is to first test the driver, making sure you understand what is going on, and then extend the scope for the driver.

1. Create a Publisher Command Transform Policy for Priv.

Figure 28: Publisher Command Transform Policy for Priv

Rules are needed that detect add/drop Priv events for both User and Group. You need to add/remove the Group Membership associated with the Privilege (an Oracle Role is mapped to an eDirectory Group).

2. Detect Add operations and validate Grantees for synchronization.

Figure 29: Rule Builder for Add Priv for User.

You need to detect Add operations for the Priv object (gqcrqOrclPriv), and to validate that the Oracle Grantee, either User or Role, falls into the group for synchronization. That is done by ensuring that the Grantee name starts with "TEST":

starts-with(add-attr[@attr-name='gqcrqOrclGrantee']/value, "TEST")

3. Create a Publisher Policy that can detect when Priv events are dropped.

Figure 30: Publisher Policy for Detecting Drop Privilege Events

Keep in mind that some Users, Roles, or Privileges can be dropped in Oracle in between two Clock ticks. You must be able to detect such Events even when triggers are not leveraged. You need to validate every Oracle Account, Role, and Privilege (Role Membership) in eDirectory, through a query against the Oracle catalog through the views. If the Account, Role, or Privilege still exists in Oracle, that's good. However, if the Query returns no result, then the Account, Role, or Privilege is gone, and you need to update eDirectory accordingly.

Detecting Dropped Accounts, Roles, and Privileges in Oracle

Below is a StyleSheet that detecting dropped Accounts, Roles, and Privileges in Oracle.

Figure 31: Subscriber Write-Back Event Transform StyleSheet for detecting dropped Accounts, Roles, and Privileges in Oracle

Here are the steps and templates to follow to accomplish the detection process.

1. Detect a Clock tick, and make sure you are dealing with the right Clock:

<xsl:template match="modify[@class-name='Clock']" xmlns:jdbc="urn:dirxml:jdbc">
		<xsl:message>We are in the stylesheet Sub-Clock2 for a clock tick</xsl:message>
		</xsl:message>
		<!--	_________________	Query eDir for existing attributes __________________ -->
		<xsl:variable name="src-query">
			<query class-name="Clock" dest-dn="{@src-dn}" scope="entry">
				<read-attr attr-name="CN"/>
				<read-attr attr-name="currentTime"/>
			</query>
		</xsl:variable>
		<!--	_________________	Paste exiting eDir attributes into variables __________________ -->
		<xsl:variable name="result" select="query:query($srcQueryProcessor, $src-query)"/>
		<xsl:variable name="clock" select="$result/nds/output/instance[1]/attr[@attr-name='CN']/value[1]"/>
		<xsl:variable name="currenttime" select="$result/nds/output/instance[1]/attr[@attr-name='currentTime']/value[1]"/>

2. Call three templates - one for Accounts, one for Roles, and one for Privileges:

<xsl:choose>
			<xsl:when test="$currenttime and string($clock) = 'MyDBClock'">
				<xsl:call-template name="clock-tick-acct">
					<xsl:with-param name="clock" select="$clock"/>
					<xsl:with-param name="currenttime" select="$currenttime"/>
				</xsl:call-template>
				<xsl:call-template name="clock-tick-role">
					<xsl:with-param name="clock" select="$clock"/>
					<xsl:with-param name="currenttime" select="$currenttime"/>
				</xsl:call-template>
				<xsl:call-template name="clock-tick-priv">
					<xsl:with-param name="clock" select="$clock"/>
					<xsl:with-param name="currenttime" select="$currenttime"/>
				</xsl:call-template>
			</xsl:when>

3. Validate the Account against the Oracle catalog.

For Accounts, you need to a) query the Control Group in eDirectory for this Oracle Instance, b) obtain the list of Members, and c) for each member, call a template that validates the account against the Oracle catalog (DBA_USERS through view_ORCLUSERS):

<!-- Adds SQL for clock tick -->
	<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:param name="cn"/>
		<xsl:param name="currenttime"/>
		<xsl:message>We are in the stylesheet Sub-Clock2 for a clock tick for Account</xsl:message>
		<!--	_________________	Query eDir for existing attributes __________________ -->
		<xsl:variable name="src-query">
			<query class-name="Group" dest-dn="ca\novl\Oracle\MyDBGrp" 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:call-template name="validate-acct">
						<xsl:with-param name="dn" select="."/>
					</xsl:call-template>
				</xsl:for-each>
			</xsl:when>
			<xsl:otherwise>
				<xsl:message>There are no existing Oracle users for this instance</xsl:message>
				<xsl:copy>
					<xsl:apply-templates select="@*|node()"/>
				</xsl:copy>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>

The following template queries the Oracle catalog. If it cannot find a given Account for the current Member, it launches the process to update eDirectory:

</xsl:template>
	<!-- Validate acct -->
	<xsl:template name="validate-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 validating account</xsl:message>
		<!--	_________________	Query eDir for existing attributes __________________ -->
		<xsl:variable name="src-query">
			<query class-name="User" dest-dn="{$dn}" scope="entry">
				<read-attr attr-name="gqcrqOrclAcct"/>
			</query>
		</xsl:variable>
		<!--	_________________	Paste exiting eDir attributes into variables __________________ -->
		<xsl:variable name="result" select="query:query($srcQueryProcessor, $src-query)"/>
		<xsl:variable name="acct" select="$result//value"/>
		<!-- Query Oracle for username -->
		<xsl:variable name="query2">
			<jdbc:statement jdbc:type="query">
				<jdbc:sql>
					<xsl:text>SELECT pk_USERNAME FROM view_orclusers WHERE pk_USERNAME = '</xsl:text>
					<xsl:value-of select="$acct"/>
					<xsl:text>'</xsl:text>
				</jdbc:sql>
			</jdbc:statement>
		</xsl:variable>
		<xsl:variable name="result2" select="query:query($destQueryProcessor,$query2)"/>
		<xsl:variable name="username" select="$result2//jdbc:result-set[1]/jdbc:row[1]/jdbc:column[@jdbc:name='PK_USERNAME']/jdbc:value"/>
		<xsl:if test="string($username) != string($acct)">
			<xsl:message>Account has been removed from Oracle</xsl:message>

To update eDirectory for a dropped/removed Oracle Account, remove the User from the Control Group, and also remove the Association:

<!-- ============================================================ -->
			<!-- 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="'\META\ca\novl\oracle\MyDBGrp'"/>
							</value>
						</remove-value>
					</modify-attr>
				</modify>
				<remove-association>
					<xsl:value-of select="concat('PK_USERNAME=',$acct,',table=VIEW_ORCLUSERS,schema=IDM')"/>
				</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)"/>

4. Find the Groups that have corresponding Roles in Oracle.

For Roles, a different approach is needed. Instead of a Control Group, the location (one flat OU) and the gqcrqOrclSID attribute are used to identify the Group with a specific Oracle Instance. You need to Query the eDirectory subtree under the Instance's OU and obtain the list of Groups for which eDirectory believes there is a corresponding Role in Oracle:

<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:param name="cn"/>
		<xsl:param name="currenttime"/>
		<xsl:message>We are in the stylesheet Sub-Clock2 for a clock tick for role</xsl:message>
		<!--	_________________	Query eDir for existing attributes __________________ -->
		<xsl:variable name="src-query">
			<query dest-dn="\META\ca\novl\Oracle\MyDB" scope="subtree">
				<search-attr attr-name="gqcrqOrclSID">
					<value type="string">MyDB</value>
				</search-attr>
				<read-attr attr-name="gqcrqOrclRole"/>
			</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:call-template name="validate-role">
						<xsl:with-param name="role" select="."/>
					</xsl:call-template>
				</xsl:for-each>
			</xsl:when>
			<xsl:otherwise>
				<xsl:message>There are no existing Oracle roles for this instance</xsl:message>
				<xsl:copy>
					<xsl:apply-templates select="@*|node()"/>
				</xsl:copy>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>

For each such Group, call another template to validate that the corresponding Role still exists in the Oracle catalog. This template needs to query the Oracle catalog (DBA_ROLES through view_ORCLRoles) for the Role. If the query returns no result, then simply remove/delete the Group from eDirectory:

<xsl:template name="validate-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 validating a role</xsl:message>
		<!-- Query Oracle for rolename -->
		<xsl:variable name="query">
			<jdbc:statement jdbc:type="query">
				<jdbc:sql>
					<xsl:text>SELECT pk_ROLE FROM view_ORCLROLES WHERE pk_ROLE = '</xsl:text>
					<xsl:value-of select="$role"/>
					<xsl:text>'</xsl:text>
				</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:if test="string($rolename) != string($role)">
			<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('\META\ca\novl\oracle\MyDB\',$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:if>
		<xsl:copy>
			<xsl:apply-templates select="@*|node()"/>
		</xsl:copy>
	</xsl:template>

5. Validate the Priv objects.

For Privileges (Role Memberships), the first is similar to the one used for Groups. You need to query eDirectory for each and every Privilege (gqcrqOrclPriv) object, and then validate each Priv object one by one with another template:

<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 priv</xsl:message>
		<!--	_________________	Query eDir for existing attributes __________________ -->
		<xsl:variable name="src-query">
			<query dest-dn="\META\ca\novl\Oracle\MyDB" scope="subtree">
				<search-attr attr-name="Object Class">
					<value type="string">gqcrqOrclPriv</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:call-template name="validate-priv">
						<xsl:with-param name="priv" select="."/>
					</xsl:call-template>
				</xsl:for-each>
			</xsl:when>
			<xsl:otherwise>
				<xsl:message>There are no existing Oracle privs for this instance</xsl:message>
				<xsl:copy>
					<xsl:apply-templates select="@*|node()"/>
				</xsl:copy>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>

This will work even if the Privilege has been created in eDirectory (through a Group Membership) and then synchronized to Oracle through the Subscriber Channel. This is because a Priv object will have been created after the first Clock tick following the synchronization.

This last template for validating Privileges is a little more complex. You need to obtain, for each Priv object, the values for Grantee and Granted_Role. Then you must query the Oracle catalog (DBA_ROLE_PRIV through view_ORCLPRIVS) to find out if this Privilege (Role Membership) is still current in Oracle:

<xsl:template name="validate-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 validating a priv</xsl:message>
		<!--	_________________	Query eDir for existing attributes __________________ -->
		<xsl:variable name="priv-dn" select="concat('\META\ca\novl\Oracle\MyDB\',$priv)"/>
		<xsl:variable name="src-query">
			<query class-name="gqcrqOrclPriv" dest-dn="{$priv-dn}" scope="entry">
				<read-attr attr-name="gqcrqOrclGrantee"/>
				<read-attr attr-name="gqcrqOrclGrantedRole"/>
			</query>
		</xsl:variable>
		<!--	_________________	Paste exiting eDir attributes into variables __________________ -->
		<xsl:variable name="result" select="query:query($srcQueryProcessor, $src-query)"/>
		<xsl:variable name="grantee" select="$result/nds/output/instance[1]/attr[@attr-name='gqcrqOrclGrantee']/value[1]"/>
		<xsl:variable name="grantedrole" select="$result/nds/output/instance[1]/attr[@attr-name='gqcrqOrclGrantedRole']/value[1]"/>
		<!-- Query Oracle for priv -->
		<xsl:variable name="query2">
			<jdbc:statement jdbc:type="query">
				<jdbc:sql>
					<xsl:text>SELECT pk_GRANTEE, pk_GRANTED_ROLE FROM view_ORCLPRIVS WHERE pk_GRANTEE = '</xsl:text>
					<xsl:value-of select="$grantee"/>
					<xsl:text>' AND pk_GRANTED_ROLE = '</xsl:text>
					<xsl:value-of select="$grantedrole"/>
					<xsl:text>'</xsl:text>
				</jdbc:sql>
			</jdbc:statement>
		</xsl:variable>
		<xsl:variable name="result2" select="query:query($destQueryProcessor,$query2)"/>
		<xsl:variable name="granteename" select="$result2//jdbc:result-set[1]/jdbc:row[1]/jdbc:column[@jdbc:name='PK_GRANTEE']/jdbc:value"/>
		<xsl:variable name="grantedname" select="$result2//jdbc:result-set[1]/jdbc:row[1]/jdbc:column[@jdbc:name='PK_GRANTED_ROLE']/jdbc:value"/>

If the Query returns no result, then this Privilege (Group Membership) must be removed from eDirectory (both the Priv object and corresponding Group Membership for the User or Group must be removed). But first, if the Privileges needs to be removed, you need to determine if the Grantee is a User or a Group:

<xsl:if test="string($granteename) != string($grantee)">
			<xsl:message>Priv has been removed from Oracle</xsl:message>
			<!--	_________________	Query eDir for existing attributes __________________ -->
			<xsl:variable name="src-query3">
				<query dest-dn="\META\ca\novl" scope="subtree">
					<search-attr attr-name="CN">
						<value type="string">
							<xsl:value-of select="$grantee"/>
						</value>
					</search-attr>
					<read-attr attr-name="Object Class"/>
				</query>
			</xsl:variable>
			<!--	_________________	Paste exiting eDir attributes into variables __________________ -->
			<xsl:variable name="result3" select="query:query($srcQueryProcessor, $src-query3)"/>
			<xsl:variable name="objectclass" select="$result3//value"/>

Through the Object Class attribute, you can now distinguish between User and Group and then proceed with the update. This is the update section for User:

<!-- ============================================================ -->
			<!-- Generate the XDS fragment that will remove the Group Membership for MyDBGrp -->
			<!-- ============================================================ -->
			<xsl:choose>
				<xsl:when test="$result3//value[./text()='User']">
					<xsl:variable name="user-dn" select="concat('\META\ca\novl\users\',$grantee)"/>
					<xsl:variable name="user-priv-dn" select="concat('\META\ca\novl\Oracle\MyDB\',$grantee,'-',$grantedrole)"/>
					<xsl:variable name="add-cmd-update">
						<modify dest-dn="{$user-dn}">
							<modify-attr attr-name="Group Membership">
								<remove-value>
									<value>
										<xsl:value-of select="concat('\META\ca\novl\Oracle\MyDB\',$grantedrole)"/>
									</value>
								</remove-value>
							</modify-attr>
						</modify>
						<!-- ============================================================ -->
						<!-- Generate the XDS fragment that will remove the Priv object -->
						<!-- ============================================================ -->
						<delete class-name="gqcrqOrclPriv" dest-dn="{$user-priv-dn}"/>
					</xsl:variable>
					<!-- ============================================================ -->
					<!-- Send the XDS fragment generated above to the Src Directory -->
					<!-- ============================================================ -->
					<xsl:variable name="addResults" select="cmd:execute($srcCommandProcessor, $add-cmd-update)"/>
				</xsl:when>

And the following is the update section for Group:

<xsl:when test="$result3//value[./text()='Group']">
					<xsl:variable name="group-dn" select="concat('\META\ca\novl\Oracle\MyDB\',$grantee)"/>
					<xsl:variable name="grp-priv-dn" select="concat('\META\ca\novl\Oracle\MyDB\',$grantee,'-',$grantedrole)"/>
					<xsl:variable name="add-cmd-update2">
						<modify dest-dn="{$group-dn}">
							<modify-attr attr-name="Group Membership">
								<remove-value>
									<value>
										<xsl:value-of select="concat('\META\ca\novl\Oracle\MyDB\',$grantedrole)"/>
									</value>
								</remove-value>
							</modify-attr>
						</modify>
						<!-- ============================================================ -->
						<!-- Generate the XDS fragment that will remove the Priv object -->
						<!-- ============================================================ -->
						<delete class-name="gqcrqOrclPriv" dest-dn="{$grp-priv-dn}"/>
					</xsl:variable>
					<!-- ============================================================ -->
					<!-- Send the XDS fragment generated above to the Src Directory -->
					<!-- ============================================================ -->
					<xsl:variable name="addResults2" select="cmd:execute($srcCommandProcessor, $add-cmd-update2)"/>
				</xsl:when>

The reason why you must distinguish between User and Group is that static OUs are used in the template, which are different for User and Group. This is because we know only the CN part through the Priv object. You could perform a query higher up in the tree to find the DN using one section/block, but you may be better off with 2 sections/blocks rather than with an entire tree scan.

Conclusion

This is not the first Oracle driver that I have put together, but this one is improved over the other ones I have put together so far. I will continue to test drivers against Oracle and possibly improve this one, through testing and feedback. So do not hesitate to send feedback or questions my way (mbluteauTAKETHISOUT@novell.com), and I will do my best to assist those who would like to test this driver.


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

© 2014 Novell