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

Custom Scripting for Database Application Tables (MSSQL) 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 IDENTITY(1, 1) NOT NULL PRIMARY KEY,
   USERNAME VARCHAR(50) NOT NULL,
   FIRSTNAME VARCHAR(50),
   LASTNAME VARCHAR(50),
   EMAIL VARCHAR(50) NOT NULL,
   COUNTRYCODE VARCHAR(20),
   DESCRIPTION VARCHAR(MAX),
   SALARY MONEY,
   JOININGDATE DATE,
   STATUS VARCHAR(20) NOT NULL,
   PASSWORD VARCHAR(MAX));

MYDBAT_GROUPS

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

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(50) NOT NULL,
   CONSTRAINT MYDBAT_PERSON_GROUP_PK PRIMARY KEY (USERID, GROUPID),
   CONSTRAINT MYDBAT_PERSON_FK1 FOREIGN KEY (USERID)
     REFERENCES MYDBAT_PERSON (USERID),
   CONSTRAINT MYDBAT_GROUPS_FK1 FOREIGN KEY (GROUPID)
     REFERENCES MYDBAT_GROUPS (GROUPID));

MYDBAT_PERSON_ROLE

USE {dataBase};
   CREATE TABLE MYDBAT_PERSON_ROLE  
   (USERID INT NOT NULL,
   ROLEID VARCHAR(50) NOT NULL,
   FROMDATE DATE,
   TODATE DATE,
   CONSTRAINT MYDBAT_PERSON_ROLE_PK PRIMARY KEY (USERID, ROLEID),
   CONSTRAINT MYDBAT_PERSON_FK2 FOREIGN KEY (USERID)
     REFERENCES MYDBAT_PERSON (USERID),
   CONSTRAINT MYDBAT_ROLES_FK1 FOREIGN KEY (ROLEID)
     REFERENCES MYDBAT_ROLES (ROLEID));

MYDBAT_COUNTRY

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

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.math.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.identityconnectors.framework.common.objects.*;
import java.lang.reflect.*;
import org.identityconnectors.common.security.GuardedString;
import java.text.*;
 
String ocName ;
var df = new SimpleDateFormat("yyyy-MM-dd");
var targetFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss z");
if( timing != "") {
    trace.info("[Execute Query] timing attribute value: "+ timing);
    ocName = timing.split(":")[1]
}
     
trace.info("[Execute Query] for objectClass: "+ ocName);
switch (ocName) {
    //Lookup
    case "MYDBAT_COUNTRY":
        CallableStatement callableStatement = null;
        ResultSet resultSet = null;
        try {
            callableStatement = conn.prepareCall("{call GET_COUNTRIES}");
            resultSet = (ResultSet) callableStatement.executeQuery();
             
            while (resultSet.next()) {
                var cob = new ConnectorObjectBuilder();
                cob.setObjectClass(new ObjectClass("MYDBAT_COUNTRY"));
                cob.addAttribute(AttributeBuilder.build(Uid.NAME, resultSet.getString(1)));
                cob.addAttribute(AttributeBuilder.build(Name.NAME, resultSet.getString(2)));
                 
                if(!handler.handle(cob.build())) return;
            }
        } finally {
            if(resultSet != null)
                resultSet.close();
            if(callableStatement != null)
                callableStatement.close();
        }
        break;
     
    //Entitlement
    case "MYDBAT_GROUPS":
        CallableStatement callableStatement = null;
        ResultSet resultSet = null;
        try {
            callableStatement = conn.prepareCall("{call GET_GROUPS}");
            resultSet = (ResultSet) callableStatement.executeQuery();
             
            while (resultSet.next()) {
                var cob = new ConnectorObjectBuilder();
                cob.setObjectClass(new ObjectClass("MYDBAT_GROUPS"));
                cob.addAttribute(AttributeBuilder.build(Uid.NAME, resultSet.getString(1)));
                cob.addAttribute(AttributeBuilder.build(Name.NAME, resultSet.getString(2)));
                 
                if(!handler.handle(cob.build())) return;
            }
        } finally {
            if(resultSet != null)
                resultSet.close();
            if(callableStatement != null)
                callableStatement.close();
        }
        break;
     
    //Entitlement  
    case "DBAT_ROLES":
        CallableStatement callableStatement = null;
        ResultSet resultSet = null;
        try {
            callableStatement = conn.prepareCall("{call GET_ROLES}");
            resultSet = (ResultSet) callableStatement.executeQuery();
             
            while (resultSet.next()) {
                var cob = new ConnectorObjectBuilder();
                cob.setObjectClass(new ObjectClass("MYDBAT_ROLES"));
                cob.addAttribute(AttributeBuilder.build(Uid.NAME, resultSet.getString(1)));
                cob.addAttribute(AttributeBuilder.build(Name.NAME, resultSet.getString(2)));
                 
                if(!handler.handle(cob.build())) return;
            }
        } finally {
            if(resultSet != null)
                resultSet.close();
            if(callableStatement != null)
                callableStatement.close();
        }
        break;
         
    case "ACCOUNT":
    case "TARGETACCOUNT":
        CallableStatement parentCallableStatement = null;
        ResultSet parentResultSet = null;
        try {
            if (filterString != "") {
                trace.info("[Execute Query] Performing Recon with Filter. Filter is:: "+ filterString +" And Filer Params are:: "+ filterParams);
                //[Execute Query] Performing Recon with Filter. Filter is::MYDBAT_PERSON.USERID = ? And Filer Params are::Params are:: [MYDBAT_PERSON.USERID:31]
                parentCallableStatement = conn.prepareCall("{call GET_PERSON_BY_USERID(?)}");
                parentCallableStatement.setString(1, filterParams.get(0).split(":")[1]);
            } else {
                trace.info("[Execute Query] Performing Full Recon.");
                parentCallableStatement = conn.prepareCall("{call GET_PERSONS}");
            }
            parentResultSet = (ResultSet) parentCallableStatement.executeQuery();
            while (parentResultSet.next()) {
                var cob = new ConnectorObjectBuilder();
                cob.setObjectClass(ObjectClass.ACCOUNT);
                cob.addAttribute(AttributeBuilder.build(Uid.NAME, parentResultSet.getString(1)));
                cob.addAttribute(AttributeBuilder.build("FIRSTNAME", parentResultSet.getString(2)));
                cob.addAttribute(AttributeBuilder.build("LASTNAME", parentResultSet.getString(3)));
                cob.addAttribute(AttributeBuilder.build("EMAIL", parentResultSet.getString(4)));
                cob.addAttribute(AttributeBuilder.build("DESCRIPTION", parentResultSet.getString(5)));
                cob.addAttribute(AttributeBuilder.build("SALARY", parentResultSet.getDouble(6)));
                var joiningDbDate = parentResultSet.getDate(7);
                if( joiningDbDate != null ) {
                    var date = df.parse(joiningDbDate.toString());
                    var joinDateStr = targetFormat.format(date);
                    var joinDate = date.getTime();
                    trace.info("date : "+ date +" ---- joinDate : "+ joinDate);
                    trace.info("Setting joinDate: "+ joinDate);
                    cob.addAttribute(AttributeBuilder.build("JOININGDATE", joinDate));
                }
                cob.addAttribute(AttributeBuilder.build(OperationalAttributes.ENABLE_NAME, "ACTIVE".equalsIgnoreCase(parentResultSet.getString(8))));
                cob.addAttribute(AttributeBuilder.build("COUNTRYCODE", parentResultSet.getString(9)));
                cob.addAttribute(AttributeBuilder.build(Name.NAME, parentResultSet.getString(10)));
     
                if (ocName.equals("TARGETACCOUNT")) {
                    CallableStatement callableStatement = null;
                    ResultSet resultSet = null;
                    try {
                        //Person role
                        callableStatement = conn.prepareCall("{call GET_PERSON_ROLE(?)}");
                        callableStatement.setString(1, parentResultSet.getString(1));
                        resultSet = (ResultSet) callableStatement.executeQuery();
                                 
                        var eoList = new ArrayList<EmbeddedObject>();
                         
                        while (resultSet.next()) {
                            var roleEA = new EmbeddedObjectBuilder();
                            roleEA.setObjectClass(new ObjectClass("MYDBAT_ROLES"));
                            roleEA.addAttribute(AttributeBuilder.build("ROLEID", resultSet.getString(2)));
                             
                            var fromDbDate = resultSet.getDate(3);
                            if( fromDbDate != null ) {
                                var date = df.parse(fromDbDate.toString());
                                var fromDateStr = targetFormat.format(date);
                                var fromDate = date.getTime();
                                trace.info("Setting roles fromDate : "+ fromDate);
                                roleEA.addAttribute(AttributeBuilder.build("FROMDATE", fromDate));
                            }
                             
                            var toDbDate = resultSet.getDate(4);
                            if( toDbDate != null ) {
                                var date = df.parse(toDbDate.toString());
                                var toDateStr = targetFormat.format(date);
                                var toDate = date.getTime();
                                trace.info("Setting roles toDate: "+ toDate);
                                roleEA.addAttribute(AttributeBuilder.build("TODATE", toDate));
                            }
                            eoList.add(roleEA.build());
                        }
                         
                        var roleEm = eoList.toArray(new EmbeddedObject[eoList.size()]);
                        cob.addAttribute(AttributeBuilder.build("MYDBAT_PERSON_ROLE", (Object[]) roleEm));
                    } finally {
                        if(resultSet != null)
                            resultSet.close();
                        if(callableStatement != null)
                            callableStatement.close();
                    }
                     
                    try {
                        //Person group
                        callableStatement = conn.prepareCall("{call GET_PERSON_GROUP(?)}");
                        callableStatement.setString(1, parentResultSet.getString(1));
                        resultSet = (ResultSet) callableStatement.executeQuery();
                         
                        var geoList = new ArrayList<EmbeddedObject>();
                         
                        while (resultSet.next()) {
                            var groupEA = new EmbeddedObjectBuilder();
                            groupEA.setObjectClass(new ObjectClass("MYDBAT_GROUPS"));
                            groupEA.addAttribute(AttributeBuilder.build("GROUPID", resultSet.getString(2)));
                            geoList.add(groupEA.build());
                        }
                         
                        var groupEm = geoList.toArray(new EmbeddedObject[geoList.size()]);
                        cob.addAttribute(AttributeBuilder.build("DBAT_PERSON_GROUP", (Object[]) groupEm));
                    } finally {
                        if( resultSet != null )
                            resultSet.close();
                        if( callableStatement != null )
                            callableStatement.close();
                    }
                }
                 
                if(!handler.handle(cob.build())) return;
            }
        } finally {
            if( parentResultSet != null )
                parentResultSet.close();
            if( parentCallableStatement != null )
                parentCallableStatement.close();
        }
        break;
}

Stored Procedure: Load Users

USE {dataBase};
 
CREATE OR ALTER PROCEDURE GET_PERSONS
AS
BEGIN
    SELECT USERID,
           FIRSTNAME,
           LASTNAME,
           EMAIL,
           DESCRIPTION,
           SALARY,
           JOININGDATE,
           STATUS,
           COUNTRYCODE,
           USERNAME
    FROM MYDBAT_PERSON
END;

Stored Procedure: Filtered User Search

USE {dataBase};
 
CREATE OR ALTER PROCEDURE GET_PERSON_BY_USERID
@user_id INT
AS
BEGIN
   SELECT USERID,
          FIRSTNAME,
          LASTNAME,
          EMAIL,
          DESCRIPTION,
          SALARY,
          JOININGDATE,
          STATUS,
          COUNTRYCODE,
          USERNAME
   FROM MYDBAT_PERSON 
   WHERE USERID = @user_id;
END;

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};
 
CREATE OR ALTER PROCEDURE GET_ROLES
AS
BEGIN
   SELECT ROLEID,
          ROLENAME 
   FROM MYDBAT_ROLES;
END;

Stored Procedure: Get User Roles

USE {dataBase};
 
CREATE OR ALTER PROCEDURE GET_PERSON_ROLE
@user_id INT
AS
BEGIN
   SELECT USERID,
          ROLEID,
          FROMDATE,
          TODATE
   FROM MYDBAT_PERSON_ROLE WHERE USERID = @user_id
 END;

Stored Procedure: Get Groups

USE {dataBase};
 
CREATE OR ALTER PROCEDURE GET_GROUPS
AS
BEGIN
   SELECT GROUPID,
          GROUPNAME
   FROM MYDBAT_GROUPS;
 END;

Stored Procedure: Get User Groups

USE {dataBase};
 
CREATE OR ALTER PROCEDURE GET_PERSON_GROUP
@user_id INT
AS
BEGIN
   SELECT USERID,
          GROUPID
   FROM MYDBAT_PERSON_GROUP where USERID = @user_id;
END;

Stored Procedure: Get Lookups (Country)

use {dataBase};
 
CREATE OR ALTER PROCEDURE GET_COUNTRIES
AS
BEGIN
   SELECT COUNTRYCODE,
          COUNTRYNAME
   FROM MYDBAT_COUNTRY;
 END;

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.CallableStatement;
import java.sql.Types;
import java.util.Date.*;
import org.identityconnectors.common.security.GuardedString;
import org.identityconnectors.framework.common.objects.*;
import java.text.*;
       
CallableStatement callableStatement = null;
String uid = null;
try {
    trace.info("[Create-Groovy] Attributes:: " + attributes);
     
    callableStatement = conn.prepareCall("{call ADD_PERSON(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
    callableStatement.setString(1, attributes.get(Name.NAME) != null ? attributes.get(Name.NAME).getValue().get(0) : null);
    callableStatement.setString(2, attributes.get("FIRSTNAME") != null ? attributes.get("FIRSTNAME").getValue().get(0) : null);
    callableStatement.setString(3, attributes.get("LASTNAME") != null ? attributes.get("LASTNAME").getValue().get(0) : null);
    callableStatement.setString(4, attributes.get("EMAIL") != null ? attributes.get("EMAIL").getValue().get(0) : null);
    callableStatement.setString(5, attributes.get("COUNTRYCODE") != null ? attributes.get("COUNTRYCODE").getValue().get(0) : null);
    callableStatement.setString(6, attributes.get("DESCRIPTION") != null ? attributes.get("DESCRIPTION").getValue().get(0) : null);
    callableStatement.setString(7, (attributes.get(OperationalAttributes.ENABLE_NAME) != null ? attributes.get(OperationalAttributes.ENABLE_NAME).getValue().get(0) : true) ? "ACTIVE" : "INACTIVE");
     
    var joiningdate = attributes.get("JOININGDATE") != null ? attributes.get("JOININGDATE").getValue().get(0) : null;
    callableStatement.setString(8, (joiningdate != null && joiningdate != 0) ? new SimpleDateFormat("yyyy-MM-dd").format(new Date(joiningdate)) : null); 
     
    var salary = attributes.get("SALARY") != null ? attributes.get("SALARY").getValue().get(0) : null;
    if(salary != null)
        callableStatement.setDouble(9, salary);
    else
        callableStatement.setNull(9, Types.DOUBLE)
     
    var password = attributes.get(OperationalAttributes.PASSWORD_NAME) != null ? attributes.get(OperationalAttributes.PASSWORD_NAME).getValue().get(0) : null;
    if (password != null) {
        password.access(new GuardedString.Accessor() {
            public void access(char[] clearChars) { callableStatement.setString(10, new String(clearChars));}
        });
    } else
        callableStatement.setString(10, null);
     
    callableStatement.registerOutParameter(11, Types.NVARCHAR);
    callableStatement.execute();
     
    uid = callableStatement.getString(11);
} finally {
    if (callableStatement != null)
        callableStatement.close();
}
trace.info("[Create] Created User:: " +uid);
return new Uid(uid);

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 java.sql.PreparedStatement;
import org.identityconnectors.framework.common.objects.*;
import java.text.*;
  
trace.info("[addMultiValuedAttributeScript-Groovy] Adding Child data:: " + attributes);
String userId = attributes.get(Uid.NAME).getValue().get(0);
PreparedStatement childst = null;
Attribute dbatChild = null;
try {
    dbatChild = attributes.get("MYDBAT_PERSON_GROUP");
    if (dbatChild != null) {
        var childDataEOSet = dbatChild.getValue();
        childst = conn.prepareStatement("INSERT INTO MYDBAT_PERSON_GROUP VALUES (?, ?)");
         
        if (childDataEOSet != null) {
            trace.info("[addMultiValuedAttributeScript] Adding Group data.");
            for ( iterator = childDataEOSet.iterator(); iterator.hasNext(); ) {
                eo = iterator.next();
                attrsSet = eo.getAttributes();
                grpAttr = AttributeUtil.find("GROUPID", attrsSet);
                if (grpAttr != null) {
                    childst.setString(1, userId);
                    childst.setString(2, grpAttr.getValue().get(0));
                    childst.executeUpdate();
                    childst.clearParameters();
                }
            };
        }
    }
} finally {
    if (childst != null)
        childst.close();       
};
  
try {
    dbatChild = attributes.get("MYDBAT_PERSON_ROLE");
    if (dbatChild != null) {
        var childDataEOSet = dbatChild.getValue();
        childst = conn.prepareStatement("INSERT INTO MYDBAT_PERSON_ROLE VALUES (?, ?, ?, ?)");
         
        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) {
                    childst.setString(1, userId);
                    childst.setString(2, roleattr.getValue().get(0));
                    childst.setString(3, AttributeUtil.find("FROMDATE", attrsSet) != null ? new SimpleDateFormat("yyyy-MM-dd").format(new Date(AttributeUtil.find("FROMDATE", attrsSet).getValue().get(0))) : null);
                    childst.setString(4, AttributeUtil.find("TODATE", attrsSet) != null ? new SimpleDateFormat("yyyy-MM-dd").format(new Date(AttributeUtil.find("TODATE", attrsSet).getValue().get(0))) : null);   
                     
                    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 java.sql.CallableStatement;
import org.identityconnectors.framework.common.objects.*;
 
trace.info("[removeMultiValuedAttributeScript] Removing Child data:: "+ attributes);   
  
var uid = attributes.get(Uid.NAME).getValue().get(0);
CallableStatement callableStatement = null;
Attribute dbatChild = null;
try {
    dbatChild = attributes.get("MYDBAT_PERSON_GROUP");
    if (dbatChild != null) {
        var childDataEOSet = dbatChild.getValue();
        //Delete child data using stored procedure
        callableStatement = conn.prepareCall("{call DELETE_PERSON_GROUP(?, ?)}");
        if(childDataEOSet != null) {
            trace.info("[removeMultiValuedAttributeScript] Removing Group data.");
            //Iterate through child data and delete
            for( iterator = childDataEOSet.iterator(); iterator.hasNext(); ) {
                eo = iterator.next();
                grpattr = AttributeUtil.find("GROUPID", eo.getAttributes());
                if (grpattr != null) {
                    callableStatement.setString(1, uid);
                    callableStatement.setString(2, grpattr.getValue().get(0));
                    callableStatement.executeUpdate();
                    trace.info("[removeMultiValuedAttributeScript] Deleted Group:: "+ grpattr);
                }
            };
        }
    }
} finally {
    if (callableStatement != null)
        callableStatement.close();
};
 
try {
    dbatChild = attributes.get("MYDBAT_PERSON_ROLE");
    if (dbatChild != null) {
        var childDataEOSet = dbatChild.getValue();
        callableStatement = conn.prepareCall("{call DELETE_PERSON_ROLE(?, ?)}");
        if(childDataEOSet != null) {
            trace.info("[removeMultiValuedAttributeScript] Removing Role data.");
            for ( iterator = childDataEOSet.iterator(); iterator.hasNext(); ) {
                eo = iterator.next();
                roleattr = AttributeUtil.find("ROLEID", eo.getAttributes());
                if(roleattr != null) {
                    callableStatement.setString(1, uid);
                    callableStatement.setString(2, roleattr.getValue().get(0));
                    callableStatement.executeUpdate();
                    trace.info("[removeMultiValuedAttributeScript] Deleted Role:: "+ roleattr);
                }
            };
        }
    }
} finally {
    if (callableStatement != null)
        callableStatement.close();
};

Stored Procedure: Remove Child

USE {dataBase};
 
CREATE OR ALTER PROCEDURE DELETE_PERSON_GROUP
 @user_id INT, @group_id nvarchar(50)
 AS
 BEGIN
    DELETE from MYDBAT_PERSON_GROUP where USERID = @user_id AND GROUPID = @group_id
 END;

USE {dataBase};
 
CREATE OR ALTER PROCEDURE DELETE_PERSON_ROLE
 @user_id INT, @role_id nvarchar(50)
 AS
 BEGIN
   DELETE FROM MYDBAT_PERSON_ROLE where USERID = @user_id AND ROLEID = @role_id
 END;

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.CallableStatement;
import org.identityconnectors.framework.common.objects.*;
    
var uid = attributes.get(Uid.NAME).getValue().get(0);
CallableStatement callableStatement = null;  
try {
    trace.info("[Delete-Groovy] Deleting user:: " + uid);
 
    callableStatement = conn.prepareCall("{call DELETE_PERSON(?)}");
    callableStatement.setString(1, uid);
    callableStatement.execute();
} finally {
    if (callableStatement != null)
        callableStatement.close();
};
trace.info("Deleted user:: " + uid);

Stored Procedure: Delete

USE {dataBase};
 
CREATE OR ALTER PROCEDURE DELETE_PERSON
 @user_id INT
 AS
 BEGIN
   DELETE FROM MYDBAT_PERSON_ROLE where USERID = @user_id;
   DELETE FROM MYDBAT_PERSON_GROUP where USERID = @user_id;
   DELETE FROM MYDBAT_PERSON WHERE USERID = @user_id;
 END;

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 java.sql.PreparedStatement;
import java.sql.Types;
import org.identityconnectors.framework.common.objects.*;
import java.text.*;
import org.identityconnectors.framework.common.exceptions.*;
import org.identityconnectors.common.security.GuardedString;
   
trace.info("[Update-Groovy] Atrributes:: " + attributes);
 
PreparedStatement stmt = null;
String userId = null;
try {
    userId = attributes.get(Uid.NAME) != null ? attributes.get(Uid.NAME).getValue().get(0) : null;
     
    if (userId == null)
        throw new ConnectorException("UID Cannot be Null");
 
    stmt = conn.prepareStatement("UPDATE MYDBAT_PERSON SET FIRSTNAME = COALESCE(?, FIRSTNAME), LASTNAME = COALESCE(?, LASTNAME), EMAIL = COALESCE(?, EMAIL), COUNTRYCODE = COALESCE(?, COUNTRYCODE), DESCRIPTION = COALESCE(?, DESCRIPTION), STATUS = COALESCE(?, STATUS), JOININGDATE = COALESCE(?, JOININGDATE), SALARY = COALESCE(?, SALARY), PASSWORD = COALESCE(?, PASSWORD) WHERE USERID = ?");
    stmt.setString(1, attributes.get("FIRSTNAME") != null ? attributes.get("FIRSTNAME").getValue().get(0) : null);
    stmt.setString(2, attributes.get("LASTNAME") != null ? attributes.get("LASTNAME").getValue().get(0) : null);
    stmt.setString(3, attributes.get("EMAIL") != null ? attributes.get("EMAIL").getValue().get(0) : null);
    stmt.setString(4, attributes.get("COUNTRYCODE") != null ? attributes.get("COUNTRYCODE").getValue().get(0) : null);
    stmt.setString(5, attributes.get("DESCRIPTION") != null ? attributes.get("DESCRIPTION").getValue().get(0) : null);
    stmt.setString(6, (attributes.get(OperationalAttributes.ENABLE_NAME) != null ? attributes.get(OperationalAttributes.ENABLE_NAME).getValue().get(0) : true) ? "ACTIVE" : "INACTIVE");
     
    var joiningdate = attributes.get("JOININGDATE") != null ? attributes.get("JOININGDATE").getValue().get(0) : null;
    stmt.setString(7, joiningdate != null ? new SimpleDateFormat("yyyy-MM-dd").format(new Date(joiningdate)) : null)
     
    var salary = attributes.get("SALARY") != null ? attributes.get("SALARY").getValue().get(0) : null;
    if(salary != null)
        stmt.setDouble(8, salary);
    else
        stmt.setNull(8, Types.DOUBLE)  
     
    var password = attributes.get(OperationalAttributes.PASSWORD_NAME) != null ? attributes.get(OperationalAttributes.PASSWORD_NAME).getValue().get(0) : null;
    if (password != null) {
        password.access(new GuardedString.Accessor() {
            public void access(char[] clearChars) { stmt.setString(9, new String(clearChars));}
        });
    } else
        stmt.setString(9, null);
    stmt.setString(10, userId);
     
    stmt.executeUpdate();
} finally {
    if (stmt != null)
        stmt.close();
};
trace.info("[Update] Updated user:: " + userId);
return new Uid(userId);

---

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.