Oracle RDBMS
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';
Connector Troubleshooting and Tips
Connector troubleshooting could be done by raising logging level to DEBUG mode (-Dlogging.level.org.openiam=DEBUG)