User Guide for Connecting a JDBC resource system

1 In order to connect a resource of JDBC type, you should be provided with a full resource overview by resource managers. It should contain all resource parameters that are needed for connection, such as:
1.1 Database name;
1.2 Database host name or IP address;
1.3 Database port
1.4 Database credentials (username and password)

Make sure that the database is accessible over tcp/ip from the VELO host. It is highly recommended to check the connection to the database with any database client.
Also, the overview should contain all needed API's / queries for performing all resource operations you are planning to implement (create account, delete account, suspend/resume account, security groups actions, reconciliation operations etc.)

2 Create a new resource in VELO by adding it on Resource page.
2.1 Fill the details of the resource in GENERAL tab.
2.1.1 Fill in the Unique Name, Display Name and Description
2.2.2 The Type field should be JDBC
2.2.3 Reconcile Policy and Password Policy Container should be chosen from existing values
2.2.4 Gateway is not relevant for JDBC resources

2.2 Fill the details of the resource in ADDITIONAL tab
2.2.1 Adapter Properties could remain as they are
2.2.2 Specific-Type properties should be filled with database properties; make sure, that you specify a correct driver for the resource. For example, if the resource is Oracle based, the driver should be as following:
oracle.jdbc.driver.OracleDriver
2.2.3 The URL template depends on the database type too, for example, the Oracle ones should be like this:
jdbc:oracle:thin:@%h:%p:%d
h = hostname
p = port
d = database name
Make sure, that upon creation of the new resource system, a new directory named as the new resource was created under your workspace directory. It should contain subfolder named sync_files.

2.3 Add an admin to the resource on the Resources page by clicking on Admins link. Afterwards, you may verify that your link to a new resource is working by the Test Connectivity button on the resource page

2.4 Add all needed resource attribute (you should map resource attributes you will need for a new account creation action on the preparations stage). At least one attribute marked as Account ID should be created. On the page of attribute creation, you can map it to Velo user ID or to some existing identity attribute. If there are any other attributes that are involved in account creation process, they should be added too. For example, if the procedure of creation account requires a user's first name and a last name, you'll have to create two attributes, "firstName" and "lastName", although there are such identity attributes defined already. Of course, these attributes do not require special handling - you may just map them directly to the appropriate identity attributes of first and last name. But there can be attributes, that need special attitude, i.e. should have some custom or calculated value. In such a case we have to attach a converter to the attribute, and it will produce a value for the attribute, as a result of some kind of processing or just assignment of some numeric or literal value.

3. Now, after the new resource being added to VELO, you could start implement it's actions. The order of implementations is not so important, but it's recommended to start with reconciliation operations - it will allow you to build roles and to bind it to real security groups, if those exist, and will allow to start creating new accounts more quickly.

3.1 The reconciliation. This process consists of two stages - "offline fetch active data", and the "reconciliation" stage. You have to implement the PRE-phase of the first stage, in other words, regarding the JDBC systems, you have to supply a query that will bring to us list of active accounts, security groups and relations between them. For making this, you should go to the resource page, and choose from a list of supported operations the RESOURCE_FETCH_ACTIVE_DATA_OFFLINE operation. If there is any need in additional phases, you can choose the same operation several times and add other phases' implementations correspondingly. But, as it was specified already, the PRE phase is a must.

3.1.1 There is a sample of script in GROOVY scripting language with explanations. Script can be written in any supported languages

def accountsQueryManager = cntx.get("accountsQueryManager");
def groupsQueryManager = cntx.get("groupsQueryManager");
String accountsQuery = "select username as username from <table_name> t  where <query_condition>";
String groupsQuery = "SELECT GROUP_ID as uniqueId, SECURITY_GROUP_NAME as displayName, DESCRIPTION as description FROM  <table_name> where <query_condition>";
accountsQueryManager.add(accountsQuery);
groupsQueryManager.add(groupsQuery);
cntx.set("accountsQueryManager",accountsQueryManager);
cntx.set("groupsQueryManager",groupsQueryManager);

cntx is a variable, that encapsulates the context of the operation.

accountsQueryManager contains all that is needed for running query that brings in the accounts list from resource database .

groupsQueryManager contains all that is needed for running query that brings in the security groups list from resource database
After that the queries are formed, they are injected into appropriate queryManager. Aterwards, queryManagers containing queries, are injected back into the context. So, the only thing you should do is to form queries appropriately.

3.1.2 There is no action you should define for a second stage of the reconciliation process. But, if you know, that naming convention of the resource is a different from the one that is accepted by IDM, and you know how you can bind resource account names to IDM user entities, you can create a correlation rule (TODO: add description of creation of correlation rule process here)

3.1.3 Once the "offline fetch active data" part is implemented, you can perform your first operation with the resource - to synchronize accounts and security groups with the resource. Just press the "Reconcile Resource Now!" button. Make sure that after successfully running all relevant tasks there are new accounts and new security groups that belong to the resource. Now you can start preparing some roles for the next operation - account creation.

3.2 Creation of a new account. Before starting to write an action script, learn well the procedure or query that creates an account on the resource, and make sure that you have defined all attributes you will need for running the procedure. Here is a sample in GROOVY scripting language with explanations. Script can be written in any supported languages.

def attrs = cntx.get("attrs");
def queryManager = cntx.get("queryManager");
String accountName = attrs["username"].values[0].asString;
String firstName = attrs["firstName"].values[0].asString;
String lastName = attrs["lastName"].values[0].asString;
String email = attrs["email"].values[0].asString;
long updatedBy = 12345; 


String query="""
  declare
     in_o                                                                        NUMBER;
     o_last_update_date                    DATE;
     o_entity_last_update_date             DATE;
     o_message_type                       NUMBER;
     o_message_name                        VARCHAR2(200);
     o_message                             VARCHAR2(200);
begin
      ADD_GROUP_MEMBERSHIP_SAMPLE_PROC('INSERT', in_o,  ?, ?, null, null, null, ?, 'LDAP', ?, ?, sysdate, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, ?, null, null, null, null, null, null, null, null, null, null, null, o_last_update_date, o_entity_last_update_date, o_message_type, o_message_name, o_message);
       dbms_output .put_line( in_o);
       dbms_output .put_line(o_last_update_date);
       dbms_output .put_line( o_entity_last_update_date);
       dbms_output .put_line(o_message_type);
       dbms_output .put_line(o_message_name);
       dbms_output .put_line(o_message);
end;
""";

queryManager.add(query, updatedBy, accountName, email, firstName, lastName, accountName );
cntx.set("queryManager",queryManager);

The script defines several variables - accountName, firstName, lastName, email that are met in the procedure call.

cntx is a variable, that incapsulates the context of the operation.

attrs is a Map object that contains key-value pairs of resource attributes and their values

queryManager contains all that is needed for running query/API. The formed query will be injected into this object.

When the query or procedure call is formed with parameters instead of their values, it is added to the queryManager,
while parameters are substituted with real values. And, finally, the ready queryManager is injected into the operation context.

3.3 Adding/removing responsibilities to the account. Often account should be a member in some security or permissions group(s), or should be assigned with some responsibility. If the resource has such facility, we should implement operations of adding/removing account to certain security group. In VELO, these operations are called ADD_GROUP_MEMBERSHIP and DELETE_GROUP_MEMBERSHIP respectively. Just like in case of creation of account, we should implement the PRE phase of these operations. Sometimes other phases may be required, for example, when we want to perform some logic after adding account to the security group. In such a case we can implement POST phase. But in most common case we have to implement only PRE-phase, that supplies a query to perform. Although add group membership usually is an integral part of account creation, sometimes it can be a stand-alone task, when we need to modify a set of user's permissions. In case of new account creation, if there are any security groups to add account to, the task of account creation will be consist of two parts - creation of account and adding it to the group(s). But the action of adding group membership will be defined independently. There is a sample of script that adds group membership to the specified account

def queryManager = cntx.get("queryManager");
String accountName = cntx.get("accountName");
String groupUniqueId = cntx.get("groupUniqueId");
String groupType = cntx.get("groupType");
long updatedBy = 123456;
String query = """
declare
 o_final_rowcount   NUMBER;
 o_message_type NUMBER;
 o_message_name VARCHAR2(200);
 o_message VARCHAR2(200);
  begin
  ADD_GROUP_MEMBERSHIP_SAMPLE_PROC(?, ?, NULL, ?, ?, NULL, NULL, o_final_rowcount, o_message_type, o_message_name, o_message);
 dbms_output .put_line(o_final_rowcount);
 dbms_output .put_line(o_message_type);
 dbms_output .put_line(o_message_name);
 dbms_output .put_line(o_message);

 end;
""";
queryManager.add(query, groupType, updatedBy, accountName, groupUniqueId);
cntx.set("queryManager",queryManager);

The script defines several variables: accountName, groupUniqueId, groupType that are met in the procedure call.

cntx is a variable, that incapsulates the context of the operation.

queryManager contains all that is needed for running query that brings in the accounts list from resource database. The formed query will be injected into this object.

The script for removing account from the group membership will be look pretty alike, with call to appropriate API.

3.4 Suspension of the account. The account may be suspended according to business logic of a company. The script for this operation should be pretty similar to the one of account creation, with a call to appropriate API or query
3.5 Resuming the account. The account may be resumed after suspension according to the business logic of a company. The script for this operation should be pretty similar to the one of account creation, with a call to appropriate API or query
3.6 Removal of the account. This operation may be needed in some cases. Here is a sample of the script:

def attrs = cntx.get("attrs");
def queryManager = cntx.get("queryManager");
String accountName = cntx.get("accountName");
String nationalIdentifier = attrs["nationalIdentifier"].values[0].asString;
String query = """
		DECLARE INVANLOGIN VARCHAR2(200);  
		INIDNUM VARCHAR2(200);  
		OUTEXITCODE NUMBER; 
		OUTEXITMSG VARCHAR2(200);
		BEGIN 
		INVANLOGIN := ?;  
		INIDNUM := ?; 
		OUTEXITCODE := NULL;  
		OUTEXITMSG := NULL;  
		DELETE_ACCOUNT_SAMPLE_PROC (INVANLOGIN, INIDNUM, OUTEXITCODE, OUTEXITMSG );
		DBMS_OUTPUT.Put_Line('OUTEXITCODE = ' || TO_CHAR(OUTEXITCODE));  DBMS_OUTPUT.Put_Line('OUTEXITMSG = ' || OUTEXITMSG);
		if OUTEXITCODE=-1 then raise_application_error(-20001, OUTEXITMSG);end if;
		END;
		"""
queryManager.add(query, accountName, nationalIdentifier);
cntx.set("queryManager",queryManager);

The script defines several variables: accountName, nationalIdentifier that are met in the procedure call.

cntx is a variable, that encapsulates the context of the operation.

attrs is a Map object that contains key-value pairs of resource attributes and their values

queryManager contains all that is needed for running API/query The formed query will be injected into this object.