SQL query to get all the users having account on particular resource in OIM

Query to get users list having active/enabled account:

SELECT USR.USR_LOGIN FROM OBJ,OBI,OIU,OST,USR WHERE OBJ.OBJ_KEY=OBI.OBJ_KEY AND OBI.OBI_KEY=OIU.OBI_KEY AND OIU.USR_KEY=USR.USR_KEY AND OIU.OST_KEY=OST.OST_KEY AND OST.OBJ_KEY=OBJ.OBJ_KEY AND OBJ.OBJ_NAME=' [Insert Resource Name here]' AND OST.OST_STATUS IN ('Enabled','Provisioned'); Example: To get the users having active/enabled account on AD User resource: SELECT USR.USR_LOGIN FROM OBJ,OBI,OIU,OST,USR WHERE OBJ.OBJ_KEY=OBI.OBJ_KEY AND OBI.OBI_KEY=OIU.OBI_KEY AND OIU.USR_KEY=USR.USR_KEY AND OIU.OST_KEY=OST.OST_KEY AND OST.OBJ_KEY=OBJ.OBJ_KEY AND OBJ.OBJ_NAME='AD User' AND OST.OST_STATUS IN ('Enabled','Provisioned');

Query to get users list having disabled account:

SELECT USR.USR_LOGIN FROM OBJ,OBI,OIU,OST,USR WHERE OBJ.OBJ_KEY=OBI.OBJ_KEY AND OBI.OBI_KEY=OIU.OBI_KEY AND OIU.USR_KEY=USR.USR_KEY AND OIU.OST_KEY=OST.OST_KEY AND OST.OBJ_KEY=OBJ.OBJ_KEY AND OBJ.OBJ_NAME=' [Insert Resource Name here]' AND OST.OST_STATUS IN ('Disabled'); Example: To get the users having disabled account on AD User resource: SELECT USR.USR_LOGIN FROM OBJ,OBI,OIU,OST,USR WHERE OBJ.OBJ_KEY=OBI.OBJ_KEY AND OBI.OBI_KEY=OIU.OBI_KEY AND OIU.USR_KEY=USR.USR_KEY AND OIU.OST_KEY=OST.OST_KEY AND OST.OBJ_KEY=OBJ.OBJ_KEY AND OBJ.OBJ_NAME='AD User' AND OST.OST_STATUS IN ('Disabled');

Query to get users list having revoked account:

SELECT USR.USR_LOGIN FROM OBJ,OBI,OIU,OST,USR WHERE OBJ.OBJ_KEY=OBI.OBJ_KEY AND OBI.OBI_KEY=OIU.OBI_KEY AND OIU.USR_KEY=USR.USR_KEY AND OIU.OST_KEY=OST.OST_KEY AND OST.OBJ_KEY=OBJ.OBJ_KEY AND OBJ.OBJ_NAME=' [Insert Resource Name here]' AND OST.OST_STATUS IN ('Revoked'); Example: To get the users having revoked account on AD User resource: SELECT USR.USR_LOGIN FROM OBJ,OBI,OIU,OST,USR WHERE OBJ.OBJ_KEY=OBI.OBJ_KEY AND OBI.OBI_KEY=OIU.OBI_KEY AND OIU.USR_KEY=USR.USR_KEY AND OIU.OST_KEY=OST.OST_KEY AND OST.OBJ_KEY=OBJ.OBJ_KEY AND OBJ.OBJ_NAME='AD User' AND OST.OST_STATUS IN ('Revoked');

SQL query to get all the Completed/Rejected tasks of particular resource for user in OIM

SQL Query to get Completed tasks of particular resource for User:

SELECT MIL_NAME as TASKS FROM USR, ORC, MIL, PKG, TOS, SCH, OSI  WHERE OSI.MIL_KEY=MIL.MIL_KEY AND OSI.ORC_KEY=ORC.ORC_KEY AND ORC.USR_KEY=USR.USR_KEY AND OSI.TOS_KEY=TOS.TOS_KEY AND OSI.PKG_KEY=PKG.PKG_KEY AND OSI.SCH_KEY=SCH.SCH_KEY AND PKG.PKG_NAME='[Insert Resource Name here]' AND SCH.SCH_STATUS ='C' AND USR.USR_LOGIN='[Insert User Login here]' ORDER BY OSI.OSI_UPDATE; Example: To get Completed tasks of AD User resource for TESTUSER user: SELECT MIL_NAME as TASKS  FROM USR, ORC, MIL, PKG, TOS, SCH, OSI  WHERE OSI.MIL_KEY=MIL.MIL_KEY AND OSI.ORC_KEY=ORC.ORC_KEY AND ORC.USR_KEY=USR.USR_KEY AND OSI.TOS_KEY=TOS.TOS_KEY AND OSI.PKG_KEY=PKG.PKG_KEY AND OSI.SCH_KEY=SCH.SCH_KEY AND PKG.PKG_NAME='AD User' AND SCH.SCH_STATUS ='C' AND USR.USR_LOGIN='TESTUSER' ORDER BY OSI.OSI_UPDATE;

SQL Query to get Rejected tasks of particular resource for User:

SELECT MIL_NAME as TASKS FROM USR, ORC,MIL, PKG, TOS, SCH, OSI WHERE OSI.MIL_KEY=MIL.MIL_KEY AND OSI.ORC_KEY=ORC.ORC_KEY AND ORC.USR_KEY=USR.USR_KEY AND OSI.TOS_KEY=TOS.TOS_KEY AND OSI.PKG_KEY=PKG.PKG_KEY AND OSI.SCH_KEY=SCH.SCH_KEY AND PKG.PKG_NAME='[Insert Resource Name here]' AND SCH.SCH_STATUS ='R' AND USR.USR_LOGIN='[Insert User Login here]' ORDER BY OSI.OSI_UPDATE; Example: To get Rejected tasks of AD User resource for TESTUSER user: SELECT MIL_NAME as TASKS FROM USR, ORC,MIL, PKG, TOS, SCH, OSI WHERE OSI.MIL_KEY=MIL.MIL_KEY AND OSI.ORC_KEY=ORC.ORC_KEY AND ORC.USR_KEY=USR.USR_KEY AND OSI.TOS_KEY=TOS.TOS_KEY AND OSI.PKG_KEY=PKG.PKG_KEY AND OSI.SCH_KEY=SCH.SCH_KEY AND PKG.PKG_NAME='AD User' AND SCH.SCH_STATUS ='R' AND USR.USR_LOGIN='TESTUSER' ORDER BY OSI.OSI_UPDATE;

oracle.iam.connectors.icfcommon.prov.ICProvisioningManager : createObject : Error while creating user java.lang.IllegalArgumentException: Password configuration property is empty.

Getting this Exception while trying to provision/Create an user on the target in OIM.

<ORACLE.IAM.CONNECTORS.ICFCOMMON.PROV.ICPROVISIONINGMANAGER> <BEA-000000> <oracle.iam.connectors.icfcommon.prov.ICProvisioningManager : createObject : Error while creating user

java.lang.IllegalArgumentException: Password configuration property is empty.

Main Cause:

If password to establish a connection to the target, blank/missed in the IT Resource.
Before performing any CRUD operations on target, OIM uses connection details from IT Resource to establish a connection. But before connection firstly check whether all the required details are present or not. If missed to provide password above exception will occur.

Solution:

Provide target service account password which is required to establish connection.

                                                           Hope this will help!!!

oracle.iam.connectors.icfcommon.prov.ICProvisioningManager : createObject : Error while creating user java.lang.IndexOutOfBoundsException: Invalid attribute name (PASSWORD)

Getting this Exception while trying to provision/Create user on the target using Database Application Table (DBAT) Connector in OIM.

<Error> <ORACLE.IAM.CONNECTORS.ICFCOMMON.PROV.ICPROVISIONINGMANAGER> <BEA-000000> <oracle.iam.connectors.icfcommon.prov.ICProvisioningManager : createObject : Error while creating user

java.lang.IndexOutOfBoundsException: Invalid attribute name (PASSWORD)

Check oim_server1-digaonistic.log, under oim_server1/logs to find the cause of create operation failure using DBAT connector.

Main Cause:

If password flowing from OIM to DB Application Table target while provisioning/creating user and value of passwordColumn attribute provided in IT Resource(that value would be the name of DB application table column which is holding password).
Suppose column namely PASSWORD for holding password in DB Application Table, then passwordColumn  attribute value would been PASSWORD in IT Resource as in given below screenshot.

Solution:

There are some special attributes in OIM ICF based connector's Provisioning and Reconciliation Lookup, which are prefixed and suffixed by __ [underscore] like __NAME__, __UID__, __PASSWORD__ and ICF framework understand them by these name only. These all are attributes hold sensible information.

Similarly in DBAT connector, if password flowing from OIM to DB Application table and target password column name provided in IT Resource. Then please make sure in Provisioning and Reconciliation lookup, decode value for password field should be  __PASSWORD__ instead of only PASSWORD. Please see below screenshot for more details.

                                                Hope this will help !!! 

oracle.iam.connectors.icfcommon.prov.ICProvisioningManager : doUpdate : Error while updating user[[ org.identityconnectors.framework.common.exceptions.UnknownUidException: Object with Uid 'Attribute: {Name=__UID__, Value=[2D8273319FE5CAE2E050EA9D62281277]}' and ObjectClass 'ObjectClass: __ACCOUNT__' does not exist!

Issue: 

Update, enable and disable operation on OID target fails for some users with below exception:

[2016-03-17T03:57:29.068-07:00] [oim_server1] [ERROR] [] [ORACLE.IAM.CONNECTORS.ICFCOMMON.PROV.ICPROVISIONINGMANAGER] [tid: [ACTIVE].ExecuteThread: '4' for queue: 'weblogic.kernel.Default (self-tuning)'] [userId: xelsysadm] [ecid: 77bddbf65a4931ae:64858e41:1537b129d1d:-8000-000000000002311f,0] [APP: oim#11.1.2.0.0] [DSID: 0000LE46_tz7u1^Y1T2FVV1Mu3Ne000034] oracle.iam.connectors.icfcommon.prov.ICProvisioningManager : doUpdate : Error while updating user[[

org.identityconnectors.framework.common.exceptions.UnknownUidException: Object with Uid 'Attribute: {Name=__UID__, Value=[2D8273319FE5CAE2E050EA9D62281277]}' and ObjectClass 'ObjectClass: __ACCOUNT__' does not exist!

        at org.identityconnectors.framework.impl.serializer.CommonObjectHandlers$8.createException(CommonObjectHandlers.java:218)

        at org.identityconnectors.framework.impl.serializer.CommonObjectHandlers$8.createException(CommonObjectHandlers.java:215)

        at org.identityconnectors.framework.impl.serializer.CommonObjectHandlers$ThrowableHandler.deserialize(CommonObjectHandlers.java:115)

        at org.identityconnectors.framework.impl.serializer.binary.BinaryObjectDecoder$InternalDecoder.readObject(BinaryObjectDecoder.java:162)

        at org.identityconnectors.framework.impl.serializer.binary.BinaryObjectDecoder.readObject(BinaryObjectDecoder.java:313)

        at org.identityconnectors.framework.impl.serializer.binary.BinaryObjectDecoder.readObjectField(BinaryObjectDecoder.java:417)

        at org.identityconnectors.framework.impl.serializer.MessageHandlers$5.deserialize(MessageHandlers.java:155)

        at org.identityconnectors.framework.impl.serializer.binary.BinaryObjectDecoder$InternalDecoder.readObject(BinaryObjectDecoder.java:162)

        at org.identityconnectors.framework.impl.serializer.binary.BinaryObjectDecoder.readObject(BinaryObjectDecoder.java:313)

        at org.identityconnectors.framework.impl.api.remote.RemoteFrameworkConnection.readObject(RemoteFrameworkConnection.java:153)

        at org.identityconnectors.framework.impl.api.remote.RemoteOperationInvocationHandler.invoke(RemoteOperationInvocationHandler.java:101)

        at com.sun.proxy.$Proxy636.update(Unknown Source)

Debugging:

Check oim_server1-digaonistic.log, under oim_server1/logs to find the cause of update/disable/enable operation failure.

Cause:

If you added new objectclass along with attribute in OID and added that attribute in Provisioning lookup.

Solution:

As such there is no solution to make newly created objectclass applicable for all the existing OID users.

Please make sure all the custom objectclasses are created in OID before provisioning the users on target.

Note: If you created any custom Objectclass and using the same while provisioning, updating, disabling and enabling the user, please make sure that entry of the same added in Configuration Lookup against ObjectClass Code.

Connector pick terminate or disable value in status column at the time of create user and enable user in Database Application Table or DBAT connector.

Background:

In an enterprise setup, many applications in the organization may use relational database tables as a repository for user data. Oracle has provided OOTB connector to perform CRUD operations on the DB tables called Database Application Table or DBAT Connector.

Problem : 

Connector pick terminate or disable value in status column at the time of create user and enable user operation in DBAT connector.

Example: If you provided E as enable value, D as disable value and STATUS as status column for target in the IT Resource configuration, now while creating or enabling user connector should pick E for column STATUS but connector picking disable value D instead.

Solution:

Remove the status column mapping from provisioning lookup if any, as IT Resource already has enabled, disabled and status column values. 

If provided status mapping like User Status | __Enable__  in Provisioning lookup, then it will cause ambiguity to connector as get status column values from IT resource as well as Process form and due to which connector pick Disabled value for status column.

Role Membership Rules using OIM apis

Roles is one of the important concept in OIM. Roles are used to create and manage the records of a collection of users to whom want to permit access to common functionality, such as access rights, roles, or permissions.

Role Membership rules determine which users can be assigned or removed as direct membership to or from the role. OIM provides functionality to create role membership rule through UI using Identity Self Service console. But one of the weakness while creating rule through self service console is that, only five operators are available for conditions in role membership rule =, !=, Starts with, Ends with, Contains and IN.

But what if requirement is to use other than these five operators like DOES_NOT_CONTAIN, DOES_NOT_BEGIN_WITH, DOES_NOT_ENDS_WITH, NOT_IN, LESS_THAN, GREATER_THAN, GREATER_EQUAL, LESS_EQUAL etc.

Yes its possible in OIM through SearchRule api. Following code is the example of the same.

public class CreateMembershipRule {
    private RoleManager roleManager = null;
 
    public static void main(String[] arg) throws Exception {
        CreateMembershipRule objCreateMembershipRule = new CreateMembershipRule();
        oimClient = objCreateMembershipRule.connectToOim();
        roleManager = oimClient.getService(RoleManager.class);
objCreateMembershipRule.createRoleMemRule();
    }

    public void createRoleMemRule(){
        SearchRule userMembershipRule1 = new SearchRule("Telephone Number", "127",       SearchRule.Operator.DOES_NOT_CONTAIN);
        SearchRule userMembershipRule2 = new SearchRule("Telephone Number","", SearchRule.Operator.NOT_EQUAL);
        SearchRule combinedRule = new SearchRule(userMembershipRule1, userMembershipRule2, SearchRule.Operator.AND);
SearchRule userMembershipRule3 = new SearchRule("Last Name","BOR", SearchRule.Operator.DOES_NOT_CONTAIN);
SearchRule mainRule = new SearchRule(combinedRule, userMembershipRule3, SearchRule.Operator.AND);

        roleManager.createRoleMembershipRule(mainRule,"ROLE_NAME"); 
    }

    private OIMClient connectToOim() {
   String oimUserName = "xelsysadm";
   String oimPassword = "**********";
   String oimURL = "t3://<HostName>:14000";
   String oimInitialContextFactory = "weblogic.jndi.WLInitialContextFactory";
   String wlsAuthConfig = "authwl.conf";
   String oimAppSrvType = "wls";
            java.util.Hashtable<String, String> env = new java.util.Hashtable<String, String>();
            env.put(oracle.iam.platform.OIMClient.JAVA_NAMING_FACTORY_INITIAL,         oimInitialContextFactory);
            env.put(oracle.iam.platform.OIMClient.JAVA_NAMING_PROVIDER_URL, oimURL);
            System.setProperty("java.security.auth.login.config",wlsAuthConfig);
    System.setProperty("OIM.AppServerType", oimAppSrvType);
    System.setProperty("APPSERVER_TYPE", oimAppSrvType);
    OIMClient oimclient = new oracle.iam.platform.OIMClient(env);
   try {
   oimclient.login(oimUserName, oimPassword.toCharArray());
   System.out.println("Successfully connected to OIM");
   } catch (LoginException e) {
            System.out.println("Exception in OIMClient.connectToOim()" + e.getMessage());                               e.printStackTrace();
   }
return oimclient;
    }
}

Role membership rule will look like this