Configure Selfservice for SQL

This solution document describes how to replace the LDAP connection with a SQL connection for the self service web application.

This instruction should only be used by a certified PhenixID Server administrator.

Please note that editing user data from the GUI will not be supported.

Please note you will not be able to edit the self service scenario i web GUI once done with this instruction!

Prerequisities

A SQL database with columns for username, password, firstname, lastname, mail and mobile.

Description of SQL DB used in this example:

 

Column used for Column name
Username uniqueID
Password password
Given name firstName
Surname lastName
Mobile mobile
Mail email

Configure database connection

Configure a JDBC datasource by following the scenario for JDBC.

Please note the ID of the datasource as this will be used later in the configuration.

Configure Selfservice webapplication for JDBC

Configure the self service application according to your needs using the scenario. Use the column names (case sensitive) from your column in order to prepare for using the JDBC DB.

Example:

SearchBase = Dummy

User identifier attribute = uniqueID (SQL userid)

First Name = firstName (SQL first name)

Last Name = lastName (SQL last name)

Email = email (SQL email)

Mobile = mobile (SQL mobile)

Locate roles required for Self service

Expand the Modules entity by using the Advanced tab.

Locate the module for selfservice

Note the value for requires_role.

Modify pipe used for Authentication

Modify the pipe called "Self service user authentication" according to the example below.

Please make sure to modify connection_ref and statement for PreparedStatementExecutorValve as well as value for PropertyAddValve to suite your environment.

<p>{
		"name": "Self service user authentication",
		"alias": "selfserviceAuth",
		"description": "Pipe to find and authenticate users for selfservice module",
		"enabled": "true",
		"guide_id": "guides.selfservice",
		"guide_ref": "5334bee1-a48c-413b-b250-496739248c74",
		"created": "2017-04-22T07:46:26.736Z",
		"id": "09fc56f6-eba2-4c53-90dc-c18c138d4c48",
		"valves": [
				{
				"name": "PreparedStatementExecutorValve",
				"config": {
					"connection_ref": "cd915f34-0976-471f-a52d-70351a45b5a4",
					"statement": "select uniqueID from PERSON where uniqueID = {{request.username}} and password = {{request.password}}"
				}
			},
			{
				"name": "FlowFailValve",
				"config": {
					"skip_if_expr": "flow.isSingle()"
				}
			},
			{
				"name": "PropertyAddValve",
				"enabled": "true",
				"config": {
					"name": "roles",
					"value": "auth:804d9242-1cc2-4976-bd2d-f88ed1e53926",
					"enable_multi_value": "true"
				}
			}
		]
	}</p>
Click to copy

Modify pipe used for Loading data

Modify the pipe called "Self service user load" according to the example below.

Please make sure to modify connection_ref and statement for StatementExecutorValve to suite your environment.

<p>{
		"name": "Self service user load",
		"alias": "selfserviceLoad",
		"description": "Pipe loading/finding entity by uid for selfservice module",
		"enabled": "true",
		"guide_id": "guides.selfservice",
		"guide_ref": "5334bee1-a48c-413b-b250-496739248c74",
		"created": "2017-04-22T07:46:26.783Z",
		"id": "72d2c6fb-242c-49ec-9ce3-a6199d09e459",
		"valves": [
			{
				"name": "StatementExecutorValve",
				"config": {
					"connection_ref": "cd915f34-0976-471f-a52d-70351a45b5a4",
					"statement": "select uniqueID,{{request.attributes_load}} from PERSON where uniqueID = '{{request.username}}'"
				}
			}
		],
		"modified": "2017-04-22T07:46:28.943Z"
	}</p>
Click to copy