Automatic Mode Properties
|Tablename Prefix||When set to Automatic mode, this property determines the prefix that will be used on all automatically created tables. Useful when multiple database User Sources are connected to the same database scheme.|
A query that must return a row if the given username and password combination provided is valid. The query will run as a prepared statement, so use the question mark character (?) to represent username first and then password. The returned row may contain the user's basic properties under the column names: [firstname, lastname, schedule, language, notes]
Note that the Gateway will pass both the username and password the user typed in, so this query MUST utilize exactly two question marks, otherwise an exception will occur.
|Badge Authentication Query||A query that must return a row if the given badge provided is valid. The query will be run as a prepared statement, so use question mark (?) to represent the badge. The returned row must contain the username.|
Example: SELECT username FROM USERS WHERE badge = ?
|List Roles Query||A query that returns all possible roles that any user could be a member of. The role names must be returned in the first column of the query's results.|
|User's Roles Query|
A query that returns all of the roles that the provided user belongs to. The roles must be strings and must be in the first column of the query's results. The query will be run as a prepared statement with one parameter: the username.
|Find User Query|
A query that must return a row if a user with the given username exists. The query will be run as a prepared statement, so use question mark (?) to represent username. There must be at least one column: the username. Other columns are optional, supported columns are: [username, firstname, lastname, schedule, language, notes, badge].
When the User Source is paired with an Ignition Identity Provider, enabling this option can provide a modest performance improvement during session login.
|List Users Query|
A query that returns a row containing each username. There must be at least one column: the username. Other columns are optional, supported columns are: [username, firstname, lastname, schedule, language, notes].
|Contact Info Query||A query that returns all of the contact info for the user. The first column must be the contact type, the second column the contact value. Optional, may be blank.|
|Schedule Adjustment Query|
A query that returns the upcoming schedule adjustments for the user. This property is optional, and may be left blank.
The results set expects the following columns:
|Extra Properties Query||A query that returns name, value pairs of extra properties for the user. Will be run with one parameter: the username. Optional, may be blank.|
To Create a Database User Source
- On the Gateway Webpage under the Config tab, go Security > Users, Roles.
The User Sources page will be displayed. Click the blue arrow, Create new User Source.
- Choose the Database authentication type, and click Next.
- The New User Source window will open. Some properties are optional, but if you're using Automatic mode, enter the following properties as appropriate.
- Name: DBAuth - name of the user source.
- Failover Source: default - failover user source ('default' is the internal user source).
- Failover Mode: Hard - if the source is unreachable, then use the failover source. (Can choose the Hard or Soft option).
- Database: MySQL - external database.
- Mode: Automatic - tables in the external database will be automatically created when needed.
- Tablename Prefix: 'auth_' is the prefix for all the tables that get created. (You can leave this field blank, but if you use a prefix when the tables get created, they will contain the specified prefix in their name).
When finished, click Create New User Source.
The tables in the database will not be created in the database until they are needed. For example, as soon as a user or role is added, the associated tables will automatically get created.
- Now that your Authentication profile is created, add a user. On the right, click on the More > Manage Users link. Click on the Add User link and fill in the required fields.
- Now that your tables are created we can verify them. To view the tables, go into Designer and from the menu bar, select Tools > Database Query Browser.
You will see all the tables that were created beginning with 'auth_' when the user and role get created.
- Double click on any of tables beginning with 'auth_', and click Execute. In this example, you will see the tables associated with 'roles' and 'users' displayed in the Schema area.