Develop Custom Scripts for Database Application Tables (MySQL) Using Groovy

Custom Scripting for Database Application Tables (MySQL) Overview

When you provision accounts from Oracle Access Governance using the Database Application Tables integration, operations such as create, update, and delete are implemented using the default supplied code. On occasions where you wish to modify the default supplied operations, you can optionally provide your own custom scripts which implement your own specific provisioning operation requirements. This step is completely optional, you do not have to create custom scripts if the default operations provide you with what you need. You can add custom scripts to any operations supported. If you choose custom scripts, you only need to add them where you require the default operation to be modified, you can have a combination of custom and default scripts for the operations supported, though you can only have one or the other option for each specific operation. For example, the create operation might be implemented with a custom script that adds some functionality specific to your organization, while the delete operation is unchanged and uses default functionality.

Once you have implemented and configured your Database Application Tables to use a custom script then that script will be used when you next perform a provisioning or data load operation.

Note:

Any custom script must be implemented using Groovy format. Other scripting formats are not supported.

When you create a Database Application Tables orchestrated system you can identify scripts to be run for a number of provisioning operations on the database application containing account data. These operations are:

• Create
• Update
• Delete
• Dataload
• Add relationship data
• Remove relationship data

These scripts should be located on the agent host, in the install directory of the agent, for example, /app/<custom script> . You configure the agent with the location of the scripts in the integration settings for your orchestrated system. You should ensure that the operating system user running the agent has read/write permissions for any custom scripts.

When you perform a provisioning task, your script will be run as a replacement to the standard processing associated with the task. The script must handle the default provisioning task such as create or update, and can also have custom tasks above and beyond the default provisioning process, such as:

• Perform custom table updates
• Custom auditing
• Send custom notifications

This means that you have two options for provisioning processing using the Database Application Tables integration:

1. Use the default logic provided with the Database Application Tables connector
2. Use the custom logic implemented in scripts

Custom scripts are only used when configured in your orchestrated system. So, if you have specified a create script when creating your orchestrated system, but no script for update, then the custom script will be used for the create provisioning task, while the update task will be implemented using the default connector processing.

You should also note that all custom script types are supported for an orchestrated system configured for managed system mode. The only script type supported for authoritative source mode is the Dataload type, which is supported for both modes.

Sample Database Schema

The samples provided in the following sections are based on the database tables described in this section.

MYDBAT_PERSON

USE {dataBase};
 
CREATE TABLE MYDBAT_PERSON (
    USERID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    USERNAME VARCHAR(50) NOT NULL,
    FIRSTNAME VARCHAR(50),
    LASTNAME VARCHAR(50),
    EMAIL VARCHAR(50) NOT NULL,
    COUNTRYCODE VARCHAR(20),
    DESCRIPTION VARCHAR(50),
    SALARY DECIMAL(10, 2),
    JOININGDATE DATE,
    STATUS VARCHAR(50),
    LASTUPDATED TIMESTAMP(6),
    PASSWORD VARCHAR(50));

MYDBAT_GROUPS

USE {dataBase};
 
CREATE TABLE MYDBAT_GROUPS (
    GROUPID VARCHAR(20) NOT NULL,
    GROUPNAME VARCHAR(20) NOT NULL,
    PRIMARY KEY (GROUPID));

MYDBAT_ROLES

USE {dataBase};
 
CREATE TABLE MYDBAT_ROLES (
    ROLEID VARCHAR(50) NOT NULL PRIMARY KEY,
    ROLENAME VARCHAR(50) NOT NULL);

MYDBAT_PERSON_GROUP

USE {dataBase};
 
CREATE TABLE MYDBAT_PERSON_GROUP(
    USERID INT NOT NULL,
    GROUPID VARCHAR(20) NOT NULL,
    PRIMARY KEY (USERID,GROUPID));

ALTER TABLE DBAT_PERSON_GROUP
ADD CONSTRAINT FK_USERID
FOREIGN KEY (USERID) REFERENCES DBAT_PERSON(USERID);

MYDBAT_PERSON_ROLE

USE {dataBase};
 
CREATE TABLE MYDBAT_PERSON_ROLE(
    USERID INT NOT NULL,
    ROLEID VARCHAR(20) NOT NULL,
    PRIMARY KEY (USERID,ROLEID));

ALTER TABLE MYDBAT_PERSON_ROLE
ADD CONSTRAINT FK_USERIDROLE
FOREIGN KEY (USERID) REFERENCES DBAT_PERSON(USERID);

MYDBAT_COUNTRY

USE {dataBase};
 
CREATE TABLE MYDBAT_COUNTRY(
    COUNTRYCODE VARCHAR(20) NOT NULL,
    COUNTRYNAME VARCHAR(20) NOT NULL,
    PRIMARY KEY (COUNTRYCODE)
);

Note:

Child tables such as mydbat_roles, mydbat_groups, and mydbat_country should have a primary key constraint defined. If no primary key is defined for child tables then your validate operation will fail and you will see an error Key for table <tablename> are not defined.

Groovy Script Arguments

The following arguments can be used in your Groovy scripts:

Table - Script Arguments

Argument	Description
connector	The Database Application Tables connector object.
timing	When the Groovy script is called. The timing attribute also explains the type of operation being performed. For example, if it is a search operation, then the object class being searched is also returned. The following is the format of the timing argument for lookup field synchronization: executeQuery:OBJECT_CLASS In this format OBJECT_CLASS is replaced with the type of object being reconciled. For example, for a lookup field synchronization scheduled job that contains the object type Role, the value of the timing argument will be as follows: executeQuery:Role
attributes	All attributes.
trace	Logger as a script trace bridge to the application
where	String where condition for execute query, or null.
handler	resultSetHandler or SyncResultsHandler for the connector objects produced by the execute query, sync operation or null return.
quoting	The type of table name quoting to be used in SQL. The default value is an empty string. The value of this argument is obtained from the integration settings.
nativeTimestamps	Specifies whether the script retrieves the timestamp data of the columns as java.sql.Timestamp type from the database table. This information is obtained from the integration settings.
allNative	Specifies whether the script must retrieve the data type of the columns in a native format from the database table. The value of this argument is obtained from the integration settings. The value of this argument specifies whether the script must throw exceptions when a zero (0x00) error code is encountered.
enableEmptyString	Specifies whether support for writing an empty string instead of a NULL value must be enabled. The value of this argument is obtained from the integration settings.
filterString	String filter condition for execute query, or null.
filterParams	List of filter parameters. Each parameter is present in the COLUMN_NAME:VALUE format. For example, FIRSTNAME:test.
syncattribute	Name of the database column configured for incremental reconciliation. This argument is available in the sync script, which is called during an incremental reconciliation run.
synctoken	Value of the sync attribute. This argument is available in the sync script.

Sample Dataload Script

The data load script reads the data from all the tables for all the defined entities. In this scenario, the term data load refers to the full data load and the lookup data load.

This sample script reads user data from the MYDBAT_PERSON table, and the users' relationship data from the MYDABAT_PERSON_ROLE and MYDBAT_PERSON_GROUP tables. Entitlements data is read from the MYDBAT_GROUPS table, and lookup data is read from the MYDBAT_COUNTRY table. It also has support for a basic filter search on MYDBAT_PERSON table. All these data reads are done using stored procedures.

Dataload Script

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.identityconnectors.framework.common.objects.*;
import org.identityconnectors.common.security.GuardedString;
 
ResultSet rs = null;
CallableStatement st = null;
 
try {
    String ocName = "";
    if (timing != "") {
        trace.info("[Execute Query] timing attribute value: " + timing);
        ocName = timing.split(":")[1];
    }
 
    trace.info("[Execute Query] for objectClass: " + ocName);
 
    if (ocName.equals("ACCOUNT") || ocName.equals("TARGETACCOUNT")) {
        if (filterString != "") {
            trace.info("[Execute Query] Performing Recon with Filter. Filter is: " + filterString + " And Filter Params are: " + filterParams);
            // Example: Filter is MYDBAT_PERSON.USERID = ? And Filter Params are [MYDBAT_PERSON.USERID:21]
            String[] filter = filterParams.get(0).split(":");
            st = conn.prepareCall("{call EXECUTE_QUERY_WITH_FILTER(?, ?)}");
            st.setString(1, filter[0]); // Column name (e.g., MYDBAT_PERSON.USERID)
            st.setInt(2, Integer.parseInt(filter[1])); // Value to filter (e.g., 21)
        } else {
            trace.info("[Execute Query] Performing Full Recon.");
            st = conn.prepareCall("{call EXECUTE_QUERY_PERSON()}");
        }
 
        // Execute the procedure and get the ResultSet
        rs = st.executeQuery(); // No need to register OUT parameter
        SimpleDateFormat targetFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss z");
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
 
        while (rs.next()) {
            ConnectorObjectBuilder cob = new ConnectorObjectBuilder();
            cob.setObjectClass(ObjectClass.ACCOUNT);
 
            Attribute fname = AttributeBuilder.build("FIRSTNAME", rs.getString("FIRSTNAME"));
            Attribute lname = AttributeBuilder.build("LASTNAME", rs.getString("LASTNAME"));
            Attribute uid = AttributeBuilder.build("__UID__", rs.getString("USERID"));
            Attribute name = AttributeBuilder.build("__NAME__", rs.getString("USERNAME"));
            Attribute email = AttributeBuilder.build("EMAIL", rs.getString("EMAIL"));
            Attribute description = AttributeBuilder.build("DESCRIPTION", rs.getString("DESCRIPTION"));
 
            Date dbDate = rs.getDate("JOININGDATE");
            String joinDateStr = null;
            Long joinDate = null;
            if (dbDate != null) {
                java.util.Date date = df.parse(dbDate.toString());
                joinDateStr = targetFormat.format(date);
                joinDate = date.getTime();
                trace.info("date: " + date + " ---- joinDate: " + joinDate);
            }
 
            if (joinDate != null) {
                trace.info("Setting joinDate: " + joinDate);
                Attribute joindate = AttributeBuilder.build("JOININGDATE", joinDate);
                cob.addAttribute(joindate);
            }
 
            Attribute status = AttributeBuilder.build("STATUS", rs.getString("STATUS"));
            Attribute countryCode = AttributeBuilder.build("COUNTRYCODE", rs.getString("COUNTRYCODE"));
 
            cob.addAttribute(fname);
            cob.addAttribute(lname);
            cob.addAttribute(uid);
            cob.addAttribute(name);
            cob.addAttribute(email);
            cob.addAttribute(description);
            cob.addAttribute(status);
            cob.addAttribute(countryCode);
 
            if (!handler.handle(cob.build())) return;
        }
    } else if (ocName.equals("DBAT_COUNTRY")) {
        trace.info("[Execute Query] for Lookup: " + ocName);
        CallableStatement countryStmt = conn.prepareCall("{call GET_COUNTRIES()}");
        rs = countryStmt.executeQuery();
 
        while (rs.next()) {
            ConnectorObjectBuilder cob = new ConnectorObjectBuilder();
            cob.setObjectClass(new ObjectClass("DBAT_COUNTRY"));
            Attribute groupId = AttributeBuilder.build("__UID__", rs.getString(1));
            Attribute groupName = AttributeBuilder.build("__NAME__", rs.getString(2));
            cob.addAttribute(groupId);
            cob.addAttribute(groupName);
            if (!handler.handle(cob.build())) return;
        }
 
        rs.close();
        countryStmt.close();
    } else if (ocName.equals("DBAT_GROUPS")) {
        trace.info("[Execute Query] for Entitlement: " + ocName);
        CallableStatement groupStmt = conn.prepareCall("{call GET_GROUPS()}");
        rs = groupStmt.executeQuery();
 
        while (rs.next()) {
            ConnectorObjectBuilder cob = new ConnectorObjectBuilder();
            cob.setObjectClass(new ObjectClass("DBAT_GROUPS"));
            Attribute groupId = AttributeBuilder.build("__UID__", rs.getString(1));
            Attribute groupName = AttributeBuilder.build("__NAME__", rs.getString(2));
            cob.addAttribute(groupId);
            cob.addAttribute(groupName);
            if (!handler.handle(cob.build())) return;
        }
 
        rs.close();
        groupStmt.close();
    }else if (ocName.equals("DBAT_ROLES")) {
        trace.info("[Execute Query] for Entitlement: " + ocName);
        CallableStatement groupStmt = conn.prepareCall("{call GET_ROLES()}");
        rs = groupStmt.executeQuery();
 
        while (rs.next()) {
            ConnectorObjectBuilder cob = new ConnectorObjectBuilder();
            cob.setObjectClass(new ObjectClass("DBAT_ROLESS"));
            Attribute groupId = AttributeBuilder.build("__UID__", rs.getString(1));
            Attribute groupName = AttributeBuilder.build("__NAME__", rs.getString(2));
            cob.addAttribute(groupId);
            cob.addAttribute(groupName);
            if (!handler.handle(cob.build())) return;
        }
 
        rs.close();
        groupStmt.close();
    }
} finally {
    if (rs != null) rs.close();
    if (st != null) st.close();
}

Stored Procedure: Load Users

USE {dataBase};
 
DELIMITER //
CREATE PROCEDURE EXECUTE_QUERY_PERSON()
BEGIN
  SELECT USERID,
         FIRSTNAME,
         LASTNAME,
         EMAIL,
         DESCRIPTION,
         SALARY,
         JOININGDATE,
         STATUS,
         COUNTRYCODE,
         USERNAME
  FROM MYDBAT_PERSON;
END //
DELIMITER ;

Stored Procedure: Filtered User Search

USE {dataBase};
 
DELIMITER //
CREATE PROCEDURE EXECUTE_QUERY_WITH_FILTER(
  IN filter VARCHAR(255),
  IN filterValue int
)
BEGIN
  SET @sql_query = CONCAT(
    'SELECT USERID,
            FIRSTNAME,
            LASTNAME,
            EMAIL,
            DESCRIPTION,
            SALARY,
            JOININGDATE,
            STATUS,
            COUNTRYCODE,
            USERNAME ',
    'FROM MYDBAT_PERSON WHERE ', filter, ' = ?'
  );
 
  PREPARE stmt FROM @sql_query;
  SET @filter_value = filterValue;
   
  EXECUTE stmt USING @filter_value;
   
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

This is a very basic example of filter search with only one filter condition, for example, MYDBAT_PERSON.USERID:21. This is used specifically for writeBack processing after the create operation

Stored Procedure: Get Roles

USE {dataBase};
 
DELIMITER //
CREATE PROCEDURE GET_ROLES()
BEGIN
  SELECT ROLEID,
         ROLENAME
  FROM MYDBAT_ROLES;
END //
DELIMITER ;

Stored Procedure: Get User Roles

USE {dataBase};
 
DELIMITER //
CREATE PROCEDURE GET_USERROLE(
  IN userin int
)
BEGIN
  SELECT USERID,
         ROLEID,
         FROMDATE,
         TODATE
  FROM MYDBAT_PERSON_ROLE
  WHERE USERID = userin;
END //
DELIMITER ;

Stored Procedure: Get Groups

USE {dataBase};
 
DELIMITER //
CREATE PROCEDURE GET_GROUPS()
BEGIN
  SELECT GROUPID,
         GROUPNAME
  FROM MYDBAT_GROUPS;
END //
DELIMITER ;

Stored Procedure: Get User Groups

USE {dataBase};
 
DELIMITER //
CREATE PROCEDURE GET_USERGROUP(
  IN userin int
)
BEGIN
  SELECT USERID,
         GROUPID
  FROM MYDBAT_PERSON_GROUP
  WHERE USERID = userin;
END //
DELIMITER ;

Stored Procedure: Get Lookups (Country)

USE {dataBase};
 
DELIMITER //
CREATE PROCEDURE GET_COUNTRIES()
BEGIN
  SELECT COUNTRYCODE,
         COUNTRYNAME
  FROM MYDBAT_COUNTRY;
END //
DELIMITER ;

Sample Create Script

This script is invoked during provisioning of a new account from Oracle Access Governance. Here we are inserting data into the MYDBAT_PERSON table.

Create Script

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.CallableStatement;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.identityconnectors.common.security.GuardedString;
import org.identityconnectors.framework.common.objects.*;
 
trace.info("[Create-Groovy] Attributes::" + attributes);
 
// Get all the attributes from script argument
GuardedString pass = attributes.get("__PASSWORD__") != null ? attributes.get("__PASSWORD__").getValue().get(0) : null;
String uname = attributes.get("__NAME__") != null ? attributes.get("__NAME__").getValue().get(0) : null;
boolean enableValue = attributes.get("__ENABLE__") != null ? attributes.get("__ENABLE__").getValue().get(0) : true;
String email = attributes.get("EMAIL") != null ? attributes.get("EMAIL").getValue().get(0) : null;
String first = attributes.get("FIRSTNAME") != null ? attributes.get("FIRSTNAME").getValue().get(0) : null;
String last = attributes.get("LASTNAME") != null ? attributes.get("LASTNAME").getValue().get(0) : null;
String desc = attributes.get("DESCRIPTION") != null ? attributes.get("DESCRIPTION").getValue().get(0) : null;
Object salary = attributes.get("SALARY") != null ? attributes.get("SALARY").getValue().get(0) : null; // Changed to Object
String countryCode = attributes.get("COUNTRYCODE") != null ? attributes.get("COUNTRYCODE").getValue().get(0) : null;
Object joiningdate = attributes.get("JOININGDATE") != null ? attributes.get("JOININGDATE").getValue().get(0) : null; // Changed to Object
 
// Prepare callable statement for the stored procedure
CallableStatement createStmt = null;
 
try {
    // Prepare the SQL statement to call the stored procedure
    createStmt = conn.prepareCall("{CALL ADD_PERSON(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
 
    // Set the input parameters for the stored procedure
    createStmt.setString(1, uname);    // USERNAME
    createStmt.setString(2, first);    // FIRSTNAME
    createStmt.setString(3, last);     // LASTNAME
    createStmt.setString(4, email);    // EMAIL
    createStmt.setString(5, countryCode); // COUNTRYCODE
    createStmt.setString(6, desc);     // DESCRIPTION
 
    // Handling SALARY: Ensure it's numeric or set as NULL
    if (salary != null && salary instanceof String) {
        try {
            createStmt.setBigDecimal(9, new BigDecimal((String) salary)); // Parse to BigDecimal
        } catch (NumberFormatException e) {
            trace.error("[Create-Groovy] Invalid SALARY format: " + salary);
            createStmt.setNull(9, java.sql.Types.DECIMAL); // Set NULL if invalid
        }
    } else {
        createStmt.setNull(9, java.sql.Types.DECIMAL); // NULL for SALARY if not provided
    }
 
    // Handling JOININGDATE: Convert from long to MySQL DATE format
    if (joiningdate != null) {
        if (joiningdate instanceof Long) {
            // Convert long to java.util.Date
            Date dateObj = new Date((Long) joiningdate);
            // Format the date to 'yyyy-MM-dd'
            SimpleDateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd");
            String formattedDate = dateFormatter.format(dateObj);
            createStmt.setString(8, formattedDate); // Set the formatted date
        } else {
            trace.error("[Create-Groovy] Invalid JOININGDATE format: Expected Long but got " + joiningdate.getClass());
            createStmt.setNull(8, java.sql.Types.DATE); // Set NULL if the format is invalid
        }
    } else {
        createStmt.setNull(8, java.sql.Types.DATE); // NULL for JOININGDATE if not provided
    }
 
    // STATUS: ACTIVE or INACTIVE
    if (enableValue) {
        createStmt.setString(7, "ACTIVE"); // STATUS
    } else {
        createStmt.setString(7, "INACTIVE"); // STATUS
    }
 
    // PASSWORD Handling
    if (pass != null) {
        pass.access(new GuardedString.Accessor() {
            public void access(char[] clearChars) {
                createStmt.setString(10, new String(clearChars)); // PASSWORD
            }
        });
    } else {
        createStmt.setString(10, null); // NULL for PASSWORD if not provided
    }
 
    // The output parameter for USERID (generated by the stored procedure)
    createStmt.registerOutParameter(11, java.sql.Types.VARCHAR);
 
    // Execute the stored procedure
    createStmt.executeUpdate();
 
    // Retrieve the generated USERID (either auto-increment or UUID)
    String generatedUserId = createStmt.getString(11); // Get the output parameter (USERID)
    trace.info("[Create] Created User with USERID::" + generatedUserId);
 
    // Return the generated USERID as Uid
    return new Uid(generatedUserId);
 
} catch (Exception e) {
    trace.error("[Create-Groovy] Error during user creation: " + e.getMessage());
    throw e;  // Re-throw exception to signal failure
} finally {
    // Clean up resources
    if (createStmt != null) {
        createStmt.close();
    }
}

Create Account Stored Procedure

DELIMITER $$
  
CREATE PROCEDURE ADD_PERSON (
    IN USERNAME VARCHAR(50),
    IN FIRSTNAME VARCHAR(50),
    IN LASTNAME VARCHAR(50),
    IN EMAIL VARCHAR(50),
    IN COUNTRYCODE VARCHAR(20),
    IN DESCRIPTION VARCHAR(50),
    IN STATUS VARCHAR(50),
    IN JOININGDATE DATE,
    IN SALARY DECIMAL(10,2),
    IN PASSWORD VARCHAR(50),
    OUT USERID VARCHAR(50)
)
BEGIN
    DECLARE generated_user_id VARCHAR(50);
    DECLARE is_auto_increment VARCHAR(50);  -- Change to VARCHAR to hold string values like 'auto_increment'
  
    -- Check if USERID column has the 'auto_increment' flag in the EXTRA column
    SELECT EXTRA
    INTO is_auto_increment
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'MYDBAT_PERSON'
      AND COLUMN_NAME = 'USERID'
      AND TABLE_SCHEMA = DATABASE();
  
    -- Check if 'auto_increment' is present in the EXTRA column
    IF is_auto_increment LIKE '%auto_increment%' THEN
        -- If USERID is auto-increment
        INSERT INTO MYDBAT_PERSON (
            USERNAME, FIRSTNAME, LASTNAME, EMAIL, COUNTRYCODE, DESCRIPTION, STATUS, JOININGDATE, SALARY, PASSWORD
        )
        VALUES (
            USERNAME, FIRSTNAME, LASTNAME, EMAIL, COUNTRYCODE, DESCRIPTION, STATUS, JOININGDATE, SALARY, PASSWORD
        );
        -- Retrieve the auto-generated USERID and cast it to VARCHAR
        SET USERID = CAST(LAST_INSERT_ID() AS CHAR);
    ELSE
        -- If USERID is NOT auto-increment, generate a UUID for USERID
        SET generated_user_id = UUID();
        -- Insert the record with the generated UUID
        INSERT INTO MYDBAT_PERSON (
            USERID, USERNAME, FIRSTNAME, LASTNAME, EMAIL, COUNTRYCODE, DESCRIPTION, STATUS, JOININGDATE, SALARY, PASSWORD
        )
        VALUES (
            generated_user_id, USERNAME, FIRSTNAME, LASTNAME, EMAIL, COUNTRYCODE, DESCRIPTION, STATUS, JOININGDATE, SALARY, PASSWORD
        );
        -- Set the generated UUID in the output, as a VARCHAR
        SET USERID = generated_user_id;
    END IF;
END$$
  
DELIMITER ;

This stored procedure is a sample implementation for creating a user in the database. The script checks if the USERID column of the MYDBAT_PERSON table has auto-increment set, which means that the userid is populated automatically. If auto-increment is set then the user is created using a SQL statement without the userid as this is set automatically by the database. If auto-increment is not set then the user is created using a SQL statement which generates the userid and inserts it into the MYDBAT_PERSON table. As the implementation of the USERID may vary in different implementations, this stored procedure should according to your specific requirements.

Sample Add Child Script

This script is invoked during provisioning of entitlements/permissions to users from Oracle Access Governance. Here we are inserting data into the MYDBAT_PERSON_GROUP and MYDBAT_PERSON_ROLE tables.

Add Child Script

import org.identityconnectors.framework.common.objects.*
import java.text.*
 
trace.info("[addMultiValuedAttributeScript-Groovy] Adding Child data::" + attributes)
childst = null
try {
    // Adding Group data
    childDataEOSet = null
     
    // Logic for handling simple multi-valued attributes
    if (attributes.get("MYDBAT_PERSON_GROUP") != null) {
        childDataEOSet = attributes.get("MYDBAT_PERSON_GROUP").getValue()
        childst = conn.prepareStatement("INSERT INTO dbat.MYDBAT_PERSON_GROUP (USERID, GROUPID) VALUES (?, ?)")
        int id = attributes.get("__UID__").getValue().get(0).toInteger()
         
        if (childDataEOSet != null) {
            trace.info("[addMultiValuedAttributeScript] Adding Group data.")
            // Iterate through child data and insert into table
            for (iterator = childDataEOSet.iterator(); iterator.hasNext(); ) {
                eo = iterator.next()
                attrsSet = eo.getAttributes()
                grpattr = AttributeUtil.find("GROUPID", attrsSet)
                 
                if (grpattr != null) {
                    // Extract group ID and insert record
                    groupid = grpattr.getValue().get(0)
                    childst.setInt(1, id)
                    childst.setString(2, groupid)
                    childst.executeUpdate()
                    childst.clearParameters()
                }
            }
        }
    }
} finally {
    if (childst != null)
        childst.close()
}
 
try {
    childDataEOSet = null
    // Logic for handling complex multi-valued attributes
    if (attributes.get("MYDBAT_PERSON_ROLE") != null) {
        childDataEOSet = attributes.get("MYDBAT_PERSON_ROLE").getValue()
        childst = conn.prepareStatement("INSERT INTO dbat.MYDBAT_PERSON_ROLE (USERID, ROLEID) VALUES (?, ?)")
         
        int id = attributes.get("__UID__").getValue().get(0).toInteger()
         
        if (childDataEOSet != null) {
            trace.info("[addMultiValuedAttributeScript] Adding Role data.")
            for (iterator = childDataEOSet.iterator(); iterator.hasNext(); ) {
                eo = iterator.next()
                attrsSet = eo.getAttributes()
                roleattr = AttributeUtil.find("ROLEID", attrsSet)
                 
                if (roleattr != null) {
                    // Extract role ID and insert record
                    roleid = roleattr.getValue().get(0)
                    childst.setInt(1, id)
                    childst.setString(2, roleid)
                     
                    childst.executeUpdate()
                    childst.clearParameters()
                }
            }
        }
    }
} finally {
    if (childst != null)
        childst.close()
}

Sample Remove Child Script

This script is invoked during deprovisioning of entitlements/permissions from users from Oracle Access Governance. Here we are removing data from MYDBAT_PERSON_GROUP and MYDBAT_PERSON_ROLE tables using stored procedures.

Remove Child Script

import org.identityconnectors.framework.common.objects.*
 
trace.info("[removeMultiValuedAttributeScript] Removing Child data::" + attributes)
 
try {
    childDataEOSet = null
    delSt = null
    // Get UID and convert to int
    int id = Integer.parseInt(attributes.get("__UID__").getValue().get(0))
     
    // Handle removal of person group data
    if (attributes.get("MYDBAT_PERSON_GROUP") != null) {
        childDataEOSet = attributes.get("MYDBAT_PERSON_GROUP").getValue()
         
        // Call the MySQL stored procedure
        delSt = conn.prepareCall("{CALL DELETE_USERGROUP(?, ?)}")
         
        if (childDataEOSet != null) {
            trace.info("[removeMultiValuedAttributeScript] Removing Group data.")
            // Iterate through child data and delete
            for (iterator = childDataEOSet.iterator(); iterator.hasNext(); ) {
                eo = iterator.next()
                attrsSet = eo.getAttributes()
                grpattr = AttributeUtil.find("GROUPID", attrsSet)
                 
                if (grpattr != null) {
                    groupid = grpattr.getValue().get(0)
                    delSt.setInt(1, id) // Use setInt for integer ID
                    delSt.setString(2, groupid)
                    delSt.executeUpdate()
                    trace.info("[removeMultiValuedAttributeScript] Deleted Group::" + groupid)
                }
            }
        }
    }
} finally {
    if (delSt != null)
        delSt.close()
}
 
try {
    childDataEOSet = null
    delSt = null
    // Get UID and convert to int
    int id = Integer.parseInt(attributes.get("__UID__").getValue().get(0))
     
    // Handle removal of person role data
    if (attributes.get("MYDBAT_PERSON_ROLE") != null) {
        childDataEOSet = attributes.get("MYDBAT_PERSON_ROLE").getValue()
         
        // Call the MySQL stored procedure
        delSt = conn.prepareCall("{CALL DELETE_USERROLE(?, ?)}")
         
        if (childDataEOSet != null) {
            trace.info("[removeMultiValuedAttributeScript] Removing Role data.")
            for (iterator = childDataEOSet.iterator(); iterator.hasNext(); ) {
                eo = iterator.next()
                attrsSet = eo.getAttributes()
                roleattr = AttributeUtil.find("ROLEID", attrsSet)
                 
                if (roleattr != null) {
                    rolename = roleattr.getValue().get(0)
                    delSt.setInt(1, id) // Use setInt for integer ID
                    delSt.setString(2, rolename)
                    delSt.executeUpdate()
                    trace.info("[removeMultiValuedAttributeScript] Deleted Role::" + rolename)
                }
            }
        }
    }
} finally {
    if (delSt != null)
        delSt.close()
}

Stored Procedure: Remove Child

Delete User Role

DELIMITER $$
  
CREATE PROCEDURE DELETE_USERROLE (
    IN input_userid INT,
    IN input_roleid VARCHAR(20)
)
BEGIN
    -- Check if the record exists before attempting deletion
    IF EXISTS (
        SELECT 1
        FROM MYDBAT_PERSON_ROLE
        WHERE USERID = input_userid AND ROLEID = input_roleid
    ) THEN
        -- Perform the deletion
        DELETE FROM MYDBAT_PERSON_ROLE
        WHERE USERID = input_userid AND ROLEID = input_roleid;
    ELSE
        -- If no record exists, signal an error or do nothing
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'The specified USERID and ROLEID combination does not exist in MYDBAT_PERSON_ROLE.';
    END IF;
END$$
  
DELIMITER ;

Delete User Group

DELIMITER $$
  
CREATE PROCEDURE DELETE_USERGROUP (
    IN input_userid INT,
    IN input_groupid VARCHAR(20)
)
BEGIN
    -- Check if the record exists before attempting deletion
    IF EXISTS (
        SELECT 1
        FROM MYDBAT_PERSON_GROUP
        WHERE USERID = input_userid AND GROUPID = input_groupid
    ) THEN
        -- Perform the deletion
        DELETE FROM MYDBAT_PERSON_GROUP
        WHERE USERID = input_userid AND GROUPID = input_groupid;
    ELSE
        -- If no record exists, signal an error or do nothing
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'The specified USERID and GROUPID combination does not exist in MYDBAT_PERSON_GROUP.';
    END IF;
END$$
  
DELIMITER ;

Sample Delete Script

This script is invoked during revocation of an account from Oracle Access Governance. Here we are deleting the data user relationship tables, MYDBAT_PERSON_ROLE and MYDBAT_PERSON_GROUP, as well as data from the MYDBAT_PERSON table

Delete Script

import java.sql.PreparedStatement;
import org.identityconnectors.framework.common.objects.*;
 
// Get the UID from the input map 'attributes'
String uid = attributes.get("__UID__").getValue().get(0);
trace.info("[Delete-Groovy] Deleting user:: " + uid);
 
try {
    // Delete data from child tables and then, main table
    // Delete user roles
    st = conn.prepareStatement("DELETE FROM dbat.MYDBAT_PERSON_ROLE WHERE userid=?");
    st.setString(1, uid);
    st.executeUpdate();
    st.close();
 
    // Delete user groups
    st = conn.prepareStatement("DELETE FROM dbat.MYDBAT_PERSON_GROUP WHERE userid=?");
    st.setString(1, uid);
    st.executeUpdate();
    st.close();
 
    // Delete user account
    st = conn.prepareStatement("DELETE FROM dbat.MYDBAT_PERSON WHERE userid=?");
    st.setString(1, uid);
    st.executeUpdate();
} finally {
    if (st != null)
        st.close();
};
 
trace.info("Deleted user:: " + uid);

Sample Update Script

This script is invoked during provisioning operations when account is updated from Oracle Access Governance. Here we are updating the data in MYDBAT_PERSON table

Update Script

import org.identityconnectors.framework.common.objects.*;
import java.text.*;
import org.identityconnectors.framework.common.exceptions.*;
import java.sql.*;
 
trace.info("[Update-Groovy] Attributes::" + attributes);
 
/** During an Update operation, AGCS sends the UID attribute along with updated attributes. Get all the values of attributes **/
String id = attributes.get("__UID__") != null ? attributes.get("__UID__").getValue().get(0) : null;
String firstName = attributes.get("FIRSTNAME") != null ? attributes.get("FIRSTNAME").getValue().get(0) : null;
String lastName = attributes.get("LASTNAME") != null ? attributes.get("LASTNAME").getValue().get(0) : null;
String email = attributes.get("EMAIL") != null ? attributes.get("EMAIL").getValue().get(0) : null;
String description = attributes.get("DESCRIPTION") != null ? attributes.get("DESCRIPTION").getValue().get(0) : null;
String salary = attributes.get("SALARY") != null ? attributes.get("SALARY").getValue().get(0) : null;
String joindate = attributes.get("JOININGDATE") != null ? attributes.get("JOININGDATE").getValue().get(0) : null;
Boolean enableValue = attributes.get("__ENABLE__") != null ? attributes.get("__ENABLE__").getValue().get(0) : true;
 
// Throw exception if uid is null
if (id == null) throw new ConnectorException("UID Cannot be Null");
 
PreparedStatement stmt = null;
try {
    // Create prepared statement to update the MYDBAT_PERSON table
    stmt = conn.prepareStatement("UPDATE dbat.MYDBAT_PERSON SET FIRSTNAME=IFNULL(?, FIRSTNAME), LASTNAME=IFNULL(?, LASTNAME), EMAIL=IFNULL(?, EMAIL), SALARY=IFNULL(?, SALARY), JOININGDATE=IFNULL(?, JOININGDATE), STATUS=IFNULL(?, STATUS) WHERE USERID =?");
 
    // Set SQL input parameters
    stmt.setString(1, firstName); // First name
    stmt.setString(2, lastName); // Last name
    stmt.setString(3, email);    // Email
 
    // Handle salary: Convert to BigDecimal if not null, otherwise set SQL NULL
    if (salary != null) {
        stmt.setBigDecimal(4, new BigDecimal(salary));
    } else {
        stmt.setNull(4, java.sql.Types.DECIMAL); // Set SQL NULL for salary
    }
 
    // Handle joindate: Convert to MySQL date format if not null
    String dateStr = null;
    if (joindate != null) {
        Date date = new Date(joindate);
        DateFormat targetFormat = new SimpleDateFormat("yyyy-MM-dd"); // MySQL date format
        dateStr = targetFormat.format(date);
    }
    stmt.setString(5, dateStr); // Joining date
 
    // Handle enable/disable status
    if (enableValue) {
        stmt.setString(6, "Enabled");
    } else {
        stmt.setString(6, "Disabled");
    }
 
    // Set UID for the WHERE condition
    stmt.setString(7, id);
 
    // Execute the update
    stmt.executeUpdate();
} finally {
    // Ensure the statement is closed to release resources
    if (stmt != null) stmt.close();
}
 
trace.info("[Update] Updated user::" + id);
return new Uid(id);

---

Title and Copyright Information

Copyright ©2025,2025,

Oracle and/or its affiliates.

Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.

Access to Oracle Support

Oracle customer access to and use of Oracle support services will be pursuant to the terms and conditions specified in their Oracle order for the applicable services.