Oracle Database system connector

Introduction

This article describes the CoreOne system connector for Oracle Database and how the target system has to be configured in the CoreOne Suite.

Target system configuration parameters

The following target system configuration parameters are available

General host parameters

Parameter	Description / Remarks
Server (mehrere getrennt mit ";")	Oracle server host name, can be multiple hosts delimited by the “;” character. All hosts use the same credentials and parameters as defined below
Usernamen	User name for the database
Passwort	Password for the database
Port	Numeric port value, default is 1521
SID / Servicenamen	SID or Service name of the Oracle DB instance

SQL parameters

See below for an explanation of the different SQL command types

The CoreOne Suite - Core service has to be restarted after each change of the SQL parameters for it to take effect.

Mapping of SQL parameters

The input parameters are mapped from the corresponding identity or resource provisioning configuration. The attributes from this provision configuration are then used to replace the parameters in the stored procedures and select queries. The parameter name that gets replaced is taken from the tables system_identity_type_attribute/system_resource_type_attribute and from there, it uses the value in the column target_system_property_name

Example:

Attribute name: First Prename, Target system property name: first_prename

SQL query: Select * from users where firstprename = {first_prename}

The {first_prename} word gets replaced by the value from the Attribute First Prename

Multi value attributes: Attributes with multiple values are currently not supported by the Oracle DB connector. If you use a multi value attribute, the value will always be empty.

Identity methods

The Parameter IDENTITY_ID ist hard-coded and can’t be changed/defined.

System connector method	SQL command type	Input parameters (See above how the mapping works for these parameters)	Excepted return value	Description
SQL CreateIdentity	Stored procedure	Identity attributes, parameter names are taken from target_system_property_name in the system_identity_type_attribute table	IDENTITY_ID Unique identifier of the identity from the target system	Creates the user in the target system
SQL GetIdentityParameters	Select query (function)	SQL replacement for identity identifier: {IDENTITY_ID} SQL replacements for identity attributes: {target_system_property_name}	All identity parameters from the SELECT query	Returns all the user parameters of a user
SQL CheckIdentityExists	Select query (function)	SQL replacement for identity identifier: {IDENTITY_ID}	Result row(s) or nothing	Checks if the user in the target system exists, if rows are returned → true, else false
SQL FindObjectIdentifiersByAttributes	Select query (function)	SQL replacement for system identity type: {SYSTEM_IDENTITY_TYPE_ID} SQL replacements for attribute values: {target_system_property_name}	Result rows with column name IDENTITY_ID	Finds object identifiers of users by their attribute values. Returns all the object identifiers of users where the attributes values were found
SQL UpdateIdentity (→ calls SQL GetIdentityParameters)	Stored procedure	Identity identifier: IDENTITY_ID Identity attributes, parameter names are taken from target_system_property_name in the system_identity_type_attribute table	-	Updates a user with the attribute values
SQL DeleteIdentity	Stored procedure	Identity identifier: IDENTITY_ID	-	Deletes a user by their identity identifier
SQL ValidateCredentials	Not implemented	-	-	-
SQL IsIdentityActivate	Select query (function)	SQL replacement for identity identifier: {IDENTITY_ID} SQL replacements for identity attributes: {target_system_property_name}	Result row(s) or nothing	Checks if the user in the target system is active, if rows are returned → true, else false
SQL ActivateIdentity	Stored procedure	Identity identifier: IDENTITY_ID	-	Activates a user in the target system
SQL DeactivateIdentity	Stored procedure	Identity identifier: IDENTITY_ID	-	Deactivates a user in the target system
SQL SetIdentityPassword	Not implemented	-	-	-
SQL IsPropertyValueUnique	Select query (function)	SQL replacement for unique property: {UNIQUEPROPERTY} SQL replacement for unique property value: {UNIQUEPROPERTYVALUE}	Result row(s) or nothing	Checks if a property value is unique in the target system. There are 3 cases that gets checked to determine if the property value is unique: Returing more than one row: Not unique → false Returing 1 row: If property value is on the user with the given user object id → Is unique → true If the property value is on another user → Is unique → false Returning now rows: Is unique → true

Resource methods

System connector method	SQL command type	Input parameters	Excepted return value	Description
SQL CreateResource	Stored procedure	Resource attributes, parameter names are taken from target_system_property_name in the system_resource_type_attribute table	RESOURCE_ID Unique identifier of the resource from the target system	Creates the resource in the target system
SQL GetResourceParameters	Select query (function)	SQL replacement for resource identifier: {RESOURCE_ID} SQL replacements for resource attributes: {target_system_property_name}	All resource parameters from the SELECT query	Returns all the resource parameters of a resource
SQL CheckResourceExists	Select query (function)	SQL replacement for resource identifier: {RESOURCE_ID} SQL replacements for resource attributes: {target_system_property_name}	Result row(s) or nothing	Checks if the resource in the target system exists, if rows are returned → true, else false
SQL UpdateResource (→ calls SQL GetResourceParameters)	Stored procedure	Resource identifier: RESOURCE_ID Resource attributes, parameter names are taken from target_system_property_name in the system_resource_type_attribute table	-	Updates a resource with the attribute values
SQL DeleteResource	Stored procedure	Resource identifier: RESOURCE_ID	-	Deletes a resource in the target system
SQL GetResources	Select query (function)	SQL replacement for system resource type: {SYSTEM_RESOURCE_TYPE_ID} SQL replacements for the search value: {textPattern} SQL replacements for maximun amount of results returned: {maxResults}	Result row with the resource identifiers that were found with the column name RESOURCE_ID	Returns resources of a specific system resource type, filtered by the text pattern and limited by the max results value
SQL IsResourcePropertyValueUnique	Select query (function)	SQL replacement for unique property: {UNIQUEPROPERTY} SQL replacement for unique property value: {UNIQUEPROPERTYVALUE} SQL replacement for system resource type: {SYSTEM_RESOURCE_TYPE_ID}	Result row(s) or nothing	Checks if a property value is unique in the target system. There are 3 cases that gets checked to determine if the property value is unique: Returing more than one row: Not unique → false Returing 1 row: If property value is on the user with the given user object id → Is unique → true If the property value is on another user → Is unique → false Returning now rows: Is unique → true

Identity-resource membership methods

System connector method	SQL command type	Input parameters	Excepted return value	Description
SQL AddIdentityToResource	Stored procedure	Identity identifier: IDENTITY_ID Resource identifier: RESOURCE_ID	-	Adds the user to the resource
SQL RemoveIdentityFromResource	Stored procedure	Identity identifier: IDENTITY_ID Resource identifier: RESOURCE_ID	-	Removes the user identity from the resource
SQL GetResourcesFromIdentity	Select query (function)	SQL replacement for identity identifier: {IDENTITY_ID} SQL replacement for the system resource type: {SYSTEM_RESOURCE_TYPE_ID}	Result rows with the resources from the identity, column names to return: RESOURCE_ID → unique identifier of the resource RESOURCE_DISPLAY_NAME → display name of the resource	Gets all the resources from the identity
SQL GetIdentityMembersByResourceIdentifier	Select query (function)	SQL replacement for resource identifier: {RESOURCE_ID}	Result rows with the identities from the resource, column names to return: IDENTITY_ID → unique identifier of the identity	Gets all the user identities from the resource

SQL command types

Select Query (function)

This can be a simple SELECT query from a table or a view. It’s also possible to call a function inside a SELECT query.

There mustn’t be a semicolon at the end of the query

Examples:

SELECT * FROM t.users
SELECT * FROM v.users
SELECT * FROM get_users('all')

Stored procedure

If a stored procedure is needed for the SQL parameter, you have to specify the name of the stored procedure. The input and output parameters will be added in the system connector and are defined below.