1. Home
  2. Archive
  3. Hypersocket Prime
  4. Configuration
  5. System Configuration
  6. User Databases
  7. Creating a MySQL (Database Tables) Realm

Creating a MySQL (Database Tables) Realm

Can't find what you're looking for?
Request Supporthere

admin 8 January, 2019

Introduction

This article shows you how to connect to a custom MySQL database table as your source for users/groups and authentication.

The MySQL connector is a very flexible means of accessing users for any service that uses a MySQL database on the backend. It also means that this can get quite complicated as this is accomplished by custom MySQL queries so the configuration will vary widely according to which service you are connecting to. For the purposes of this document, only reading in of users from a Drupal website will be shown in the screenshots.

Pre-requisite

The MySQL Connector extension must be installed.

The MySQL service you are connecting to must be listening on an IP/port that the Hypersocket server can connect to AND the MySQL user must have permissions to allow connections from the Hypersocket server’s IP address.

For example, looking at open ports you can see that this mysql service is listening on all interfaces (0.0.0.0) on port 3306.

 

Here you can see the mysql user with correct permissions.

 

Configuration

In the User Realms page, click Create to start the process. Give the new realm a name and select MySQL (Database Tables) as the Realm Type.

The first tab, Connection, is where the details for the database you are connecting to is held.

 

The options on this tab are:

  • Database: The name of the MySQL database that you are connecting to.
  • Read Only: Should users be only be read or should updates be allowed for creating/editing users? (defaults to OFF).
  • Hostname: The hostname or IP address of the MySQL server.
  • Port: The port on which the database server is running (default 3306).
  • Username: The username to connect to MySQL as.
  • Password: The password for the above user.

 

The second table is Users, which defines what user properties exist, which table users are in and custom MySQL queries for finding, updating and deleting users. Not all fields require to be completed, if a user property does not exist or you do not wish to have certain queries (to disallow deleting users for example), then leave that field blank.

 

The fields available in this tab are:

  • User Table: the name of the MySQL table that contains your users
  • User SELECT: The query to run to retrieve an individual user. Note that this and the other statements below use user attributes which will be replaced automatically according to the query. E.g ${identityTable} will be the value of User Table entered above, ${identityTablePrincipalName} will be the value of the Username column mentioned further down this tab. A MySQL statement does not have to have to be on a single line. More complicated multiple queries might need to be run in some instances, for example if a user’s information exists across multiple tables.
  • Users SELECT: As above, but the query to run to get a list of all users on the system.
  • User DELETE: The statement to run in order to delete a user from the database.
  • User INSERT: The statement to run to create a new user account.
  • User UPDATE: The statement to run to edit an existing user’s details.
  • GUID column: The name of a column in the users table that contains a value that is unique to a user account, such as a uid or similar. If no such value exists, use the same value as Username.
  • Username column: The name of the column that contains the user’s username. This will become the username used for logging in to the Hypersocket server.
  • Full name column: The name of the column containing the user’s full name.
  • Other name column: The name of the column containing any other names for the user.
  • Email column: The name of the column containing the user’s email address.
  • Mobile column: The name of the column containing the user’s mobile phone number.
  • Last Sign On: The name of the column that contains the user’s last signon time.
  • Enabled Column: The name of the column that contains information relating to the user being enabled or disabled.
  • Enabled value: The value to look for in the enabled column for a user that is enabled. When this value is seen, the user is considered to be enabled.
  • Disabled value: The value to look for in the enabled column for a user that is disabled. When this value is seen, the user is considered to be disabled.
  • Locked column: The name of the column that contains information relating to the user account being locked or not.
  • Locked value: The value to look for in the locked column for a user account being locked.
  • Unlocked value: The value to look for in the locked column for a user account being unlocked.

 

For the Drupal 8 installation mentioned earlier, the Users tab had the following settings:

  • User Table: users_field_data
  • The SELECT, INSERT, DELETE and UPDATE statements kept as default.
  • GUID column: gid
  • Username column: name
  • Email column: mail
  • All other fields and all the other tabs (apart from Password) were set to empty values.

 

 

The third tab, Groups, contains settings for any groups or roles that exist in the database.

As per Users, if groups do not exist it is safe to leave any of these fields blank.

 

The settings are:

  • Group table: The name of the database table that contains any groups.
  • Group SELECT: The select statement to run to retrieve all of the groups in the database.
  • GUID column: The name of the column that contains unique identifiers for groups.
  • Group name column: The name of the column that contains the group name. This is the name that will be displayed in Hypersocket.

 

The fourth tab is User-Group, which is where the user to group relationships are defined.

 

The settings here are:

  • User Groups table: The name of the database table that contains the user to group relationships. The users and groups must be keyed by their GUIDs.
  • Group SELECT users: The MySQL statement to run to list the users who are in the specified group.
  • User GUID: The name of the column that contains the user GUID in the User-Group table.
  • Group GUID: The name of the column that contains the group GUID in the User-Group table.

 

On the fifth tab, Password, are settings related to user passwords.

 

These settings are:

  • Password column: The name of the column in the user table that contains the password.
  • Authenticate SELECT: Usually this can be left blank as a combination of the User SELECT query and the password column are enough information for authentications to be done. However, if you need a custom MySQL statement running to authenticate a user, this is the location to enter that statement.
  • Password UPDATE: The statement to run in order to change a user’s password. If ${encodedPassword} is used in the statement then the password is encoded according to the Password encoding value below.
  • Password encoding: The password encoding type to use for creation of new users as well as changing existing users’ passwords and authenticating users. There are many encoding types available which can be chosen from the dropdown list.

 

For the Drupal example, the Password column was set to pass and the Password encoding to drupal7.

 

 

The following tab is Hosts. If more than one realm is being set up it is advised to configure the settings in this tab. 

  • Restrict Hosts: if this option is turned on, then users from another realm will not be able to authenticate to the Hypersocket server when accessing via the defined Realm Host. This can be especially useful in a Managed Service Provider environment. If Restrict Hosts is off, then a user from another realm will be able to authenticate as long as their username is not a duplicate of one on this realm.
  • Realm Hosts: It is strongly advised to configure this setting. Type in the hostname that your users will be using to access the Hypersocket server for this realm. The Hypersocket server then will know to send authentication attempts to this host to the correct User Database. A different hostname should be used for each realm, which requires you are able to configure your domain’s DNS settings so that these hostnames point to the Hypersocket server.

Type in the realm host and press enter to add the host. Multiple hostnames can be configured on a realm.

Without a Realm Host setting, Hypersocket will attempt to authenticate a user on the Default realm first, before trying to find that user on other realms. This can lead to unpredictable behavior in a multi-realm environment.

 

 

The seventh tab, Filter, allows you to restrict which user objects are cached by the server. Individual Users and/or Groups can be defined to exclude them.

Type in the user or group you wish to exclude then press enter to add it to the list.

 

 

 

The eighth tab, Reconcile, contains settings relating to how the users are cached in Hypersocket. Hypersocket connects to the remote user database periodically to update its list of cached users and then performs an update (reconcile) of this cache by either adding new users, deleting users that no longer exist or updating existing users.

 

The reconcile settings are:

  • Reconcile Every (mins): The number of minutes between each successive user re-caching periods. Defaults to 60 minutes.
  • Retry on Failure (mins): If a connection attempt fails during the reconcile, the number of minutes to wait before retrying. Defaults to 5 minutes.
  • Rebuild Cache: On next reconcile, delete the cache and import all user objects from scratch. This takes more time than a normal reconcile. Defaults to OFF.
  • Purge Duplicates: On rare occasions an out of date cache can cause duplicate users to be created in the cache. If that happens, this option can force removal of these duplicate users and rebuilds the cache. Defaults to OFF.
  • Cache Passwords: Hypersocket will generate a one-way hash of the users’ passwords the next time they log on and cache this. Subsequent authentication attempts do not need to contact the domain controller for authentication until the Hypersocket server is restarted. Defaults to OFF.
  • Reconcile at Login: Performs a reconcile of the user’s account at login. This can ensure that the latest information for that user (such as group membership and AD attributes) are perfectly up to date each login. Generally this is not needed as user accounts do not change very often. Defaults to OFF.

 

 

There is one more tab that only appears after editing the Realm after it has been created. This tab is Status and contains information relating to the reconcile status:

  • Status: Contains the status of the last reconcile, which can be Completed or Failed.
  • Next Due: The date and time that the next reconcile is due to run.
  • Last Performed: The date and time that the last reconcile was performed.
  • Last Error: If the last reconcile failed, any errors appear in this field.

 

Click Create when all of the information has been entered to create the new realm, after which you should be able to see your users in Hypersocket by navigating to Access Control.