Useful OIM queries

OIM - SQL query to get all proxy assigned for users.

SELECT DISTINCT USR.USR_LOGIN "User_Login",
PU.USR_LOGIN "Proxy_User_Login",
TO_CHAR(P.PXD_START_DATE,'DD-MON-YYYY') "Proxy_Start_Date",
TO_CHAR(P.PXD_END_DATE,'DD-MON-YYYY') "Proxy_End_Date"
FROM PXD P, USR, USR PU
WHERE P.PXD_ORIG_USR_KEY = USR.USR_KEY
AND P.PXD_PROXY_KEY = PU.USR_KEY
AND P.PXD_END_DATE > SYSDATE;


To find SID in Oracle database.
sql> select sys_context('userenv','instance_name') from dual;

To find 'Service Name' in Oracle database.


sql> select value from v$parameter where name='service_names';

To get all the entitlements provisioned to the user.

SELECT EL.ENT_CODE "Entitlement Name"
FROM ENT_LIST EL, ENT_ASSIGN EA, USR
WHERE EL.ENT_LIST_KEY = EA.ENT_LIST_KEY
AND EA.USR_KEY = USR.USR_KEY
AND EA.ENT_STATUS = 'Provisioned'
AND UPPER(USR.USR_LOGIN) = UPPER('');
To get all the User's provisioned/enabled accounts.

SELECT OBJ.OBJ_NAME, OST_STATUS
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 OST.OST_STATUS IN ('Enabled','Provisioned')
AND UPPER(USR.USR_LOGIN) = UPPER('');
To get all the members of specific Role.

SELECT UGP.UGP_NAME,USR.USR_LOGIN
FROM UGP, USG, USR
WHERE USG.USR_KEY = USR.USR_KEY
AND UGP.UGP_KEY = USG.UGP_KEY
AND UPPER(UGP_NAME) = UPPER('');
To get all the members of specific Admin Role.

SELECT USR.USR_LOGIN, AR.ROLE_NAME
FROM ADMIN_ROLE_MEMBERSHIP ARM, ADMIN_ROLE AR, USR
WHERE ARM.USER_ID = USR.USR_KEY
AND ARM.ROLE_ID = AR.ROLE_ID
AND USR.USR_STATUS = 'Active'
AND AR.ROLE_NAME=  '';
To get all the Users having specific entitlement provisioned.

SELECT USR.USR_LOGIN, USR.USR_FIRST_NAME, USR.USR_LAST_NAME
FROM ENT_LIST EL, ENT_ASSIGN EA, USR
WHERE EL.ENT_LIST_KEY = EA.ENT_LIST_KEY
AND EA.USR_KEY = USR.USR_KEY
AND EA.ENT_STATUS = 'Provisioned'
AND EL.ENT_CODE = '';
To get all the Users having specific account provisioned.

SELECT USR.USR_LOGIN, USR.USR_FIRST_NAME, USR.USR_LAST_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 OST.OST_STATUS IN ('Enabled','Provisioned')
AND OBJ.OBJ_NAME = '';
To get all proxy assigned for users.
SELECT DISTINCT USR.USR_LOGIN "User_Login",
PU.USR_LOGIN "Proxy_User_Login",
TO_CHAR(P.PXD_START_DATE,'DD-MON-YYYY') "Proxy_Start_Date",
TO_CHAR(P.PXD_END_DATE,'DD-MON-YYYY') "Proxy_End_Date"
FROM PXD P, USR, USR PU
WHERE P.PXD_ORIG_USR_KEY = USR.USR_KEY
AND P.PXD_PROXY_KEY = PU.USR_KEY
AND P.PXD_END_DATE > SYSDATE;
How to Remove Account from User's Account Tab in OIM.
Below SQL queries will remove specified account from user account tab.

1. Delete entry from child table:

Note: Execute this query if application has entitlements.

DELETE FROM
WHERE ORC_KEY IN
  (SELECT OIU.ORC_KEY FROM OIU, CT
     WHERE OIU.ORC_KEY = CT.ORC_KEY
     AND OIU.APP_INSTANCE_KEY = (SELECT APP_INSTANCE_KEY FROM  APP_INSTANCE WHERE APP_INSTANCE_NAME = '')
  );
2. Delete entry from parent table:

DELETE FROM
WHERE ORC_KEY IN
  (SELECT OIU.ORC_KEY FROM OIU, PT
 WHERE OIU.ORC_KEY= PT.ORC_KEY
 AND OIU.APP_INSTANCE_KEY = (SELECT APP_INSTANCE_KEY FROM APP_INSTANCE WHERE APP_INSTANCE_NAME = '')
  );

3. Delete entry from OIU table:

DELETE FROM OIU
WHERE ORC_KEY IN
  (SELECT OIU.ORC_KEY FROM OIU, ORC
 WHERE OIU.ORC_KEY = ORC.ORC_KEY
 AND OBI_KEY IN (SELECT OBI_KEY FROM OBI WHERE OBJ_KEY = (SELECT OBJ_KEY FROM OBJ WHERE OBJ_NAME = ''))
);



SELECT EL.ENT_CODE "Entitlement Name"
FROM ENT_LIST EL, ENT_ASSIGN EA, USR
WHERE EL.ENT_LIST_KEY = EA.ENT_LIST_KEY
AND EA.USR_KEY = USR.USR_KEY
AND EA.ENT_STATUS = 'Provisioned'
AND UPPER(USR.USR_LOGIN) = UPPER('G801633');

select * from usr where usr_login='G800247';
select count(*) from ent_assign where usr_key=23946;
delete from ent_assign where usr_key=23946 and ent_assign_key between 73148 and 373149;

SELECT USR.USR_LOGIN, USR.USR_FIRST_NAME, USR.USR_LAST_NAME,OIU.USR_KEY,oiu_key
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_KEY=23946;


select * from OBI;
Select * from OIU where usr_key=28792;
select * from ost;

update oiu set ost_key=145 where oiu_key in(10893,10894,17940,10895,14283,20020,19440,29410);

code to fetch it resource values

public class OIMUserUtils {

    OIMClient oimClient = null;

             

public void intialize() {

        Hashtable

        env.put(OIMClient.JAVA_NAMING_FACTORY_INITIAL,

                "weblogic.jndi.WLInitialContextFactory");

        env.put(OIMClient.JAVA_NAMING_PROVIDER_URL,

                "t3://xxxx:14000");

        System.setProperty("java.security.auth.login.config",

                           "C:\\\\Documents\\Backup\\Software\\DesignConsoles\\designconsole - Test\\config\\authwl.conf");

        System.setProperty("OIM.AppServerType", "wls");

        System.setProperty("APPSERVER_TYPE", "wls");

        oimClient = new OIMClient(env);

        try {

            oimClient.login("xelsysadm", "password".toCharArray(), env);

            System.out.println("Successfully logged into oim");

        } catch (Exception ex) {

            System.out.println(ex.getMessage());

        }

    }

             

                  public final Hashtable getITResourceProperties(String itResourceName) {

        Hashtable map = new Hashtable();

        try{

        System.out.println("getITResourceProperties -- IT Resource Name: " +

                           itResourceName);

        tcITResourceInstanceOperationsIntf moitres = oimClient.getService(tcITResourceInstanceOperationsIntf.class);

        long svrKey = 0;

      

        map.put("IT Resources.Name", itResourceName);

        System.out.println("test");

       

        tcResultSet set = moitres.findITResourceInstances(map);

       

        ByteArrayOutputStream baos = new ByteArrayOutputStream();

        XMLEncoder xmlEncoder = new XMLEncoder(baos);

        xmlEncoder.writeObject(set);

        xmlEncoder.close();

       

        String xml = baos.toString();

        System.out.println(xml);

       

        svrKey = set.getLongValue("IT Resources.Key");

        System.out.println("getITResourceProperties -- IT Resources.Key = " +

                           svrKey);

        set = moitres.getITResourceInstanceParameters(svrKey);

        map.clear();

        map.put("svr_key",

                (new StringBuffer(String.valueOf(svrKey))).toString());

        System.out.println("getITResourceProperties-- svr_key = " +

                           String.valueOf(svrKey).toString());

        for (int i = 0; i < set.getRowCount(); i++) {

            set.goToRow(i);

            String s3 = set.getStringValue("IT Resources Type Parameter.Name");

            String s4 =

                set.getStringValue("IT Resources Type Parameter Value.Value");

            map.put(s3, s4);

            System.out.println(s3 + "----" + s4);

        }

           

        

           

        }

       

        catch(Exception ex) {

           

            System.out.println(ex.getMessage());

        }

       

        return map;

    }

             

             

                  public static void main(String[] s) {

        OIMUserUtils OIMUserUtils = new OIMUserUtils();

       

            OIMUserUtils.intialize();

           

        OIMUserUtils.getITResourceProperties("Exchange IT Resource");

       

        

        }

                            

                             }

Decrypt the password using wlst

wls:/offline> domain = "/app/Oracle/Middleware/user_projects/domains/base_domain"

wls:/offline> service = weblogic.security.internal.SerializedSystemIni.getEncryptionService(domain)

wls:/offline> encryption = weblogic.security.internal.encryption.ClearOrEncryptedService(service)

wls:/offline> print encryption.encrypt("pwd_oimsys1")

Code to fetch UserKey from UserLogin OIM

public String getUserkey(String userLogin) {
          logger.info("getUserkey method started");
          UserManager usrMgr = Platform.getService(UserManager.class);
          User user = new User(userLogin);
          Set restSet = new HashSet();
          restSet.add("usr_key");
          String userKey = null;

          try {
              user =
    usrMgr.getDetails("User Login", userLogin, null); // getting the user key based on userlogin
          } catch (NoSuchUserException e) {
              logger.log(Level.SEVERE, e.getMessage(), e);
          } catch (UserLookupException e) {
              logger.log(Level.SEVERE, e.getMessage(), e);
          } catch (SearchKeyNotUniqueException e) {
              logger.log(Level.SEVERE, e.getMessage(), e);
          } catch (NullPointerException e) {
              logger.log(Level.SEVERE, e.getMessage(), e);
          } catch (Exception e) {
              logger.log(Level.SEVERE, e.getMessage(), e);
          }

          userKey = user.getEntityId();

          return userKey;
      }

Code to fetch DB connection from weblogic datasource

Context initialContext = new InitialContext();
            datasource =
                    (DataSource)initialContext.lookup("jdbc/operationsDB");
            con = datasource.getConnection();
            stmt = con.createStatement();

Code to fetch OIM lookup details

    public Map getLookupMap() {
        final String methodName = " :: getLookupMap - ";
        logger.info(methodName + "START");
        String lookupName = "Lookup.MySyn.userType";
        Map lookupMap = new HashMap();
        tcLookupOperationsIntf lookupService =
            Platform.getService(tcLookupOperationsIntf.class);
        try {
            tcResultSet resultSet = lookupService.getLookupValues(lookupName);
            String codeKey, meaningValue;
            for (int i = 0; i < resultSet.getRowCount(); i++) {
                resultSet.goToRow(i);
                codeKey =
                        resultSet.getStringValue("Lookup Definition.Lookup Code Information.Code Key");
                meaningValue =
                        resultSet.getStringValue("Lookup Definition.Lookup Code Information.Decode");
                lookupMap.put(codeKey, meaningValue);
                //logger.info(methodName + " \nLookup Key: [" + codeKey +
                //          "], Lookup Value:[" + meaningValue + "])");
            }
        } catch (tcAPIException e) {
            logger.severe(methodName +
                          "Exception occured while reading lookup " +
                          e.getMessage());
            e.printStackTrace();
        } catch (tcInvalidLookupException e) {
            logger.severe(methodName +
                          "Exception occured while reading lookup " +
                          e.getMessage());
            e.printStackTrace();
        } catch (tcColumnNotFoundException e) {
            logger.severe(methodName +
                          "Exception occured while reading lookup " +
                          e.getMessage());
            e.printStackTrace();
        }
        logger.info(methodName + "END");
        return lookupMap;
    }

Query to read lookup values from OIM DB

select LKV_ENCODEd, LKV_DECODED from LKV, LKU WHERE lku.lku_type_string_key='LookupName' and lkv.lku_key = lku.lku_key