Configure MFA admin with SQL user store
This solution document describes how to replace the LDAP connection with a SQL connection for the MFA admin web application.
This instruction should only be used by a certified PhenixID Server administrator.
Please note that you will not be able to edit the MFA Admin settings via Scenario when you finished this instruction. All the configuration must be done using the Advanced tab.
Please be aware that due to the unpredictable nature of SQL (no "standard" schema), it's not possible to create a fully dynamic configuration.
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 | userid |
| Password | password |
| Given name | firstName |
| Surname | givenName |
| Mobile | mobile |
| Administration role | role |
Configure database connection
Configure a JDBC datasource by following the scenario for JDBC.
Please note the ID of the datasource. This will be used later in the configuration.
Configure MFA Admin webapplication for JDBC
Configure the MFA Admin 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 = userid (SQL username)
First Name = givenName (SQL first name)
Last Name = lastName (SQL last name)
Email = email (SQL email)
Mobile = mobile (SQL mobile)
Modify pipe used for Authentication
Go to Advanced->Pipes and locate the pipe with alias = "auth_pipe"
In the valve_refs parameter, change the two first values to "sql_auth,sql_auth_fail".
Click Stage changes and Commit changes.
Go to Advanced->Pipe valves and add these valves.
Please make sure to modify connection_ref, statement and pipe_ref to suite your environment. Please be aware that the statement also includes the administration role lookup.
{
"alias": "auth_pipe_search",
"name": "StatementExecutorValve",
"enabled": "true",
"config": {
"connection_ref": "bc644df0-1aeb-478c-b2c0-3f1aacbf2a12",
"statement": "select userid from users where userid='{{request.username}}' and password=PASSWORD('{{request.password}}') AND role='admin'"
},
"pipe_ref": "421af3d0-60eb-49b5-b5ee-7f440e5bccc1",
"created": "2019-04-17T11:24:13.164Z",
"id": "sql_auth"
},{
"id": "sql_auth_fail",
"name": "FlowFailValve",
"enabled": "true",
"config": {
"message": "guides.authentication.common.failed_authentication",
"exec_if_expr": "(!flow.isSingle() || request.get('username') == null)"
},
"created": "2019-04-17T11:13:55.142Z"
}
Click Stage changes and commit changes.
Please test authentication by going to https://<phenixid_server>:<port>/mfaadmin (if using multiple mfa admin applications, pls change the uri to suite your environment. ) Check the server.log file for hints if the test user is not able to login.
Modify pipe used for user search
Go to Advanced->Pipes and locate the pipe with alias = "search_pipe". Copy the id of the pipe. Then remove the pipe from the configuration.
Add this pipe.
{
"id": "3bea0ea5-150a-4c93-933b-b431f3fca0bc",
"alias": "search_pipe",
"valves": [
{
"name": "ItemCreateValve",
"config": {
"dest_id": "item"
}
},
{
"name": "PropertyAddValve",
"config": {
"name": "filter",
"value": "{{request.attrs_filter}}"
}
},
{
"name": "PropertyReplaceValve",
"config": {
"source": "filter",
"token": "=",
"replacement": "='"
}
},
{
"name": "PropertyReplaceValve",
"config": {
"source": "filter",
"token": ")",
"replacement": "')"
}
},
{
"name": "PropertyReplaceValve",
"config": {
"source": "filter",
"token": ")(",
"replacement": ") AND ("
}
},
{
"name": "PropertyReplaceValve",
"config": {
"source": "filter",
"token": "*",
"replacement": "%"
}
},
{
"name": "PropertyReplaceValve",
"config": {
"source": "filter",
"token": "=",
"replacement": " LIKE "
}
},
{
"name": "StatementExecutorValve",
"enabled": "true",
"created": "2019-04-17T11:24:13.403Z",
"config": {
"connection_ref": "bc644df0-1aeb-478c-b2c0-3f1aacbf2a12",
"statement": "select {{request.attributes_search}},userid from users where {{item.filter}}"
}
},
{
"name": "ItemRemoveValve",
"config": {
"item_include_expr": "item.containsProperty('filter')"
}
}
]
}Change the id to the previously copied id.
Please make sure to modify connection_ref and statement of the StatementExecutorValve to suite your environment. Please be aware that the username column name (in this example userid) must be added to the column selection.
Click Stage changes and Commit changes.
Modify pipe used for user load
Go to Advanced->Pipes and locate the pipe with alias = "user_load_pipe". Copy the id of the pipe. Then remove the pipe from the configuration.
Add this pipe.
{
"id": "29885829-96db-4905-8cf2-a1b815ecfaf0",
"alias": "user_load_pipe",
"valves": [
{
"name": "ItemCreateValve",
"config": {
"dest_id": "item"
}
},
{
"name": "PropertyAddValve",
"config": {
"name": "filter",
"value": "{{request.attrs_filter}}"
}
},
{
"name": "PropertyReplaceValve",
"config": {
"source": "filter",
"token": "=",
"replacement": "='"
}
},
{
"name": "PropertyReplaceValve",
"config": {
"source": "filter",
"token": ")",
"replacement": "')"
}
},
{
"name": "PropertyReplaceValve",
"config": {
"source": "filter",
"token": ")(",
"replacement": ") AND ("
}
},
{
"name": "PropertyReplaceValve",
"config": {
"source": "filter",
"token": "*",
"replacement": "%"
}
},
{
"name": "PropertyReplaceValve",
"config": {
"source": "filter",
"token": "=",
"replacement": " LIKE "
}
},
{
"name": "StatementExecutorValve",
"enabled": "true",
"created": "2019-04-17T11:24:13.403Z",
"config": {
"connection_ref": "bc644df0-1aeb-478c-b2c0-3f1aacbf2a12",
"statement": "select {{request.attributes_load}},userid from users where userid='{{request.dn}}'",
"_attributes": "userid,password,displayName"
}
},
{
"name": "ItemRemoveValve",
"config": {
"item_include_expr": "item.containsProperty('filter')"
}
}
]
}Change the id to the previously copied id.
Please make sure to modify connection_ref and statement of the StatementExecutorValve to suite your environment. Please be aware that the username column name (in this example userid) must be added to the column selection.
Modify pipe used for updating user attributes
Go to Advanced->Pipes and locate the pipe with alias = "user_update_pipe". Copy the id of the pipe. Then remove the pipe from the configuration.
Add this pipe.
{
"id": "33171189-f71c-4d71-bdee-e7f56cb9b340",
"alias": "user_update_pipe",
"valves": [
{
"name": "ItemCreateValve",
"config": {
"dest_id": "item"
}
},
{
"name": "PropertyAddValve",
"config": {
"name": "mobile",
"value": "{{request.mobile}}"
}
},
{
"name": "PropertyReplaceValve",
"config": {
"source": "mobile",
"token": "[\"",
"replacement": ""
}
},
{
"name": "PropertyReplaceValve",
"config": {
"source": "mobile",
"token": "\"]",
"replacement": ""
}
},
{
"name": "StatementExecutorValve",
"enabled": "true",
"created": "2019-04-17T11:24:13.403Z",
"config": {
"connection_ref": "bc644df0-1aeb-478c-b2c0-3f1aacbf2a12",
"statement": "update users set mobile='{{item.mobile}}' where userid='{{request.dn}}'",
"exec_if_expr": "request.get('mobile')!=null"
}
},
{
"name": "PropertyAddValve",
"config": {
"name": "mail",
"value": "{{request.mail}}"
}
},
{
"name": "PropertyReplaceValve",
"config": {
"source": "mail",
"token": "[\"",
"replacement": ""
}
},
{
"name": "PropertyReplaceValve",
"config": {
"source": "mail",
"token": "\"]",
"replacement": ""
}
},
{
"name": "StatementExecutorValve",
"enabled": "true",
"created": "2019-04-17T11:24:13.403Z",
"config": {
"connection_ref": "bc644df0-1aeb-478c-b2c0-3f1aacbf2a12",
"statement": "update users set mail='{{item.mail}}' where userid='{{request.dn}}'",
"exec_if_expr": "request.get('mail')!=null"
}
},
{
"name": "PropertyAddValve",
"config": {
"name": "givenName",
"value": "{{request.givenName}}"
}
},
{
"name": "PropertyReplaceValve",
"config": {
"source": "givenName",
"token": "[\"",
"replacement": ""
}
},
{
"name": "PropertyReplaceValve",
"config": {
"source": "givenName",
"token": "\"]",
"replacement": ""
}
},
{
"name": "StatementExecutorValve",
"enabled": "true",
"created": "2019-04-17T11:24:13.403Z",
"config": {
"connection_ref": "bc644df0-1aeb-478c-b2c0-3f1aacbf2a12",
"statement": "update users set givenName='{{item.givenName}}' where userid='{{request.dn}}'",
"exec_if_expr": "request.get('givenName')!=null"
}
},
{
"name": "PropertyAddValve",
"config": {
"name": "lastName",
"value": "{{request.lastName}}"
}
},
{
"name": "PropertyReplaceValve",
"config": {
"source": "lastName",
"token": "[\"",
"replacement": ""
}
},
{
"name": "PropertyReplaceValve",
"config": {
"source": "lastName",
"token": "\"]",
"replacement": ""
}
},
{
"name": "StatementExecutorValve",
"enabled": "true",
"created": "2019-04-17T11:24:13.403Z",
"config": {
"connection_ref": "bc644df0-1aeb-478c-b2c0-3f1aacbf2a12",
"statement": "update users set lastName='{{item.lastName}}' where userid='{{request.dn}}'",
"exec_if_expr": "request.get('lastName')!=null"
}
}
]
}Change the id to the previously copied id.
For all database columns to be updated there are four valves:
1. PropertyAddValve
Change the name to suite your database column name. ("name" and "value" parameter must be changed)
2. PropertyReplaceValve
Change the name to suite your database column name. ("source" must be changed)
3. PropertyReplaceValve
Change the name to suite your database column name. ("source" must be changed)
4. StatementExecutorValve
Change the connection_ref to suite your environment.
Change the statement: table name, userid column name, column name for attribute and value for attribute (item.<attribute_name>) must be changed to suite your environment
Change the exec_if_expr: request parameter name must be changed to suite your environment (must be the same as the database column name).