Whenever you create new user from admin console or infacmd Informatica stores user details into the repository tables. You can query this reopository table to get user details and can make join with access table to get interaction details with different Informatica objects. Informatica stores this details in following tables and views. We have listed them along with only important and useful columns.
User Table from Repository Schema
OPB_USERS
| USER_ID |
| USER_NAME |
| USER_PASSWD2 |
| USER_PRIVILEGES1 |
| USER_DESC |
| USER_EXTERNAL_ID |
REP_USERS
| USER_ID |
| NAME_SPACE |
| USER_NAME |
| STATUS |
User details from Domain Schema
If you need to extract user account audit details such as user creation and modification timestamp, last password modification dates etc. then you should use PO_USERINFO table from domain schema. Older version of Informatica may have table INF_USER storing similar information. Note that some of the details in below columns will only be populated if Account Management policy is configured from an Admin Console.
PO_USERINFO
| POU_FULLNAME |
| POU_INVALIDLOGINATTEMPTS |
| POU_PASSWORDLASTMODIFIEDT |
| POU_ACCOUNTLOCKED |
| POU_ACCOUNTLOCKED |
| POU_PASSWORD |
| POU_ACCOUNTLOCKED |
| POU_PASSWORDEXPIRED |
| POU_ENCRYPTEDPASSWORD |
Informatica Queries - User Details
User Privileges
Informatica assigns privilege to users according to the groups to which user belongs. The privilege id of user can be found inside table OPB_GROUPS.
SELECT * FROM OPB_USERS WHERE USER_PRIVILEGES1='privilege_id';List users with privilege id 1234.
SELECT * FROM OPB_USERS WHERE USER_PRIVILEGES1=1234;Get users with Administrator Privileges.
SELECT * FROM OPB_USERS WHERE USER_PRIVILEGES1=-1;Active or Disabled Users
List of active users.
SELECT * FROM OPB_USERS WHERE USER_ISENABLED=0;List of disabled users.
SELECT * FROM OPB_USERS WHERE USER_ISENABLED!=0;Repository Passwords
Informatica stores password in encrypted format. You can get encrypted passwords with following.
SELECT USER_PASSWD2 FROM OPB_USERS WHERE USER_NAME='john';External Login Details (LDAP)
If you have configred external login for users like LDAP Authentication then you can get those details from column USER_EXTERNAL_ID. List all users with external login id.
SELECT * FROM OPB_USERS WHERE USER_EXTERNAL_ID IS NOT NULL;Deleted Users
List of users which have been created but deleted later on.
SELECT * FROM REP_USERS WHERE USER_NAME LIKE '%DELETED%';Native Users
List of native informatica users which are active.
SELECT * FROM REP_USERS WHERE NAME_SPACE='Native' and STATUS=0;
Audit Details from Domain Schema
Locked Users
List of locked users.
SELECT * FROM PO_USERINFO WHERE POU_ACCOUNTLOCKED!=0;
Invalid Login Attempts Count
List of users where invalid login attempts count is grater than X.
SELECT * FROM PO_USERINFO WHERE POU_INVALIDLOGINATTEMPTS >= 5;
Expired Users
List of users where user password is expired.
SELECT * FROM PO_USERINFO WHERE POU_PASSWORDEXPIRED != 0;
Users Password Modification
List of users who have not modified password since creation.
SELECT * FROM PO_USERINFO WHERE POU_PASSWORDLASTMODIFIEDT = 0;