SQL Query to get Process Instance Key of particular Resource for user in OIM

 Query to get Process Instance Key:

SELECT ORC_KEY FROM ORC,PKG,USR WHERE ORC.PKG_KEY=PKG.PKG_KEY AND ORC.USR_KEY=USR.USR_KEY AND PKG_NAME=’[Insert Resource Name here]’ AND USR_LOGIN=’[Insert User Login here]’; Example: To get Process Instance Key of AD User resource for TESTUSER user: SELECT ORC_KEY FROM ORC,PKG,USR WHERE ORC.PKG_KEY=PKG.PKG_KEY AND ORC.USR_KEY=USR.USR_KEY AND PKG_NAME='AD User' AND USR_LOGIN=’TESTUSER’;

SQL query to get all the accounts of user in OIM

Query to get active/enabled accounts:

SELECT OBJ.OBJ_NAME 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 USR.USR_LOGIN='[Insert User Login here]' AND OST.OST_STATUS IN ('Enabled','Provisioned'); Example: To get all the active/enabled accounts of user TESTUSER SELECT OBJ.OBJ_NAME 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 USR.USR_LOGIN='TESTUSER' AND OST.OST_STATUS IN ('Enabled','Provisioned');

Query to get disabled accounts:

SELECT OBJ.OBJ_NAME 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 USR.USR_LOGIN='[Insert User Login here]' AND OST.OST_STATUS IN ('Disabled'); Example: To get all the active/enabled accounts of user TESTUSER SELECT OBJ.OBJ_NAME 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 USR.USR_LOGIN='TESTUSER' AND OST.OST_STATUS IN ('Disabled');

Query to get revoked accounts:

SELECT OBJ.OBJ_NAME  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 USR.USR_LOGIN='[Insert User Login here]' AND OST.OST_STATUS IN ('Revoked'); Example: To get all the revoked accounts of user TESTUSER SELECT OBJ.OBJ_NAME  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 USR.USR_LOGIN='TESTUSER' AND OST.OST_STATUS IN ('Revoked');

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.