General information

Oracle connector provides an ability to get user data and user permissions from Oracle RDBMS system and provision data to Oracle RDBMS. The operations that could be performed by using OpenIAM are - create, modify, enable, disable, end date user, add or remove profile/priveleges/roles/table spaces etc of user.

Installation and connection to OpenIAM

In an RPM installation please use the general startup script to start the connector. Dockerized deployment version is also available for the connector.

General usage

Connector settings

configure additional fields (Left menu → Connector Configuration):

Managed system settings

Login Id: OracleConnectorUser Password: \<password> JDBC driver: oracle.jdbc.driver.OracleDriver Connection String: jdbc:oracle:thin:@oracle_host:port/sid Search filter for User: USERNAME=? Search filter for Group: Role=? Search filters must have char "?" which will be replaced by principal name to find the object. Filter is used in reconciliations as well.

Steps to be performed on Oracle DB side

Create a service account in Oracle using following script:

create user OracleConnectorUser identified by passwd00;
grant resource, connect, dba, create session, UNLIMITED TABLESPACE to OracleConnectorUser;
alter user OracleConnectorUser default role all profile default;

Synchronization

Instruction how to set up synchronization is provided in a separate document. OpenIAM provides out of the box sync configurations and groovy scripts.

Example of query:

• • select all records; USERNAME=Jons% - select by user name (will use where USERNAME like ('Jons%') = "Start with") You can concat some filters like : USERNAME=Jons%;ACCOUNT_STATUS=OPEN where ";" = "AND" List of fields for filtering : USERNAME ACCOUNT

Oracle scripts behind the scenes

Oracle SQL used in the connector CREATE NEW USER: CREATE USER \<user_name> IDENTIFIED BY \<password> DEFAULT TABLESPACE \<default_tablespace> TEMPORARY TABLESPACE \<temporary_tablespace> COUNT \<account_status>; ALTER USER \<user_name> DEFAULT ROLE \<default_roles>; GRANT \<roles>,\<privileges>

MODIFY USER : ALTER USER \<user_name> DEFAULT TABLESPACE \<default_tablespace> TEMPORARY TABLESPACE \<temporary_tablespace> PROFILE \<profile> ACCOUNT \<account_status>; ALTER USER \<user_name> DEFAULT ROLE \<default_roles>; GRANT \<roles>,\<privileges>;

Additional information

Selected fields on search user:

SELECT * FROM DBA_USERS WHERE USERNAME='TEST_ORACLECONNECTORUSER';

Privileges from Oracle:

SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE='TEST_ORACLECONNECTORUSER';

Roles from Oracle:

SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='TEST_ORACLECONNECTORUSER';

SQL query will be performed to pull users:

SELECT * FROM DBA_USERS WHERE USERNAME='TEST_ORACLECONNECTORUSER';

To include privileges from Oracle add in /sync/user/oracle/UserSyncAttributes.groovy "privileges" then following SQL will be performed:

SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE='TEST_ORACLECONNECTORUSER';

To include role from Oracle add in /sync/user/oracle/UserSyncAttributes.groovy "roles" then following SQL will be performed:

SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='TEST_ORACLECONNECTORUSER';

Make sure service account you are using has permissions to perform SQL mentioned above

Connector Troubleshooting and Tips

Connector troubleshooting could be done by raising logging level to DEBUG mode (-Dlogging.level.org.openiam=DEBUG)