Database User Source

The Database Authentication type uses an external database instead of storing data inside Ignition. Managing users is done via direct interaction with the database. This section addresses how to set up a database user source. The Database Authentication type requires you have a connection to an existing database, like SQL Server, Oracle, or MySQL. It stores all users, roles, schedules, and and more in the database, and uses queries to check login credentials. When you create a database user source, you have the option of setting it up in Automatic or Manual mode.

Automatic Mode

In Automatic mode, Ignition will create and manage the database tables for you. You can specify a prefix for the tables that are created automatically for you, but their names after the prefix are chosen by the user source. In this mode, the user source will be fully manageable in Ignition.

Manual Mode

In Manual mode, you must provide SQL queries for various functions of the user source. In this mode, the user source will not be manageable from the Gateway or the Clients. You'll have to manage the users directly through the database. Examples for each of the queries are given on the user source setup page. Read each query description carefully to make sure you design your queries to return all the columns that are defined in the query's description as shown below. 

Property Reference

Database User Sources have the following properties, organized by category

Main Properties 

Details on the Main Properties can be found on the Classic Authentication Strategy page. The Database User Source also has the following properties:

NameDescription
DatabaseThe database connection this User Source will retrieve user information from.
Mode

How the Gateway should manage the database tables. Has two settings:

Automatic: The gateway will automatically create the database tables necessary, and all interactions with the table will use the built-in queries. When this option is set, the Tablename Prefix property is utilized.

Manual: The Gateway will not automatically create any database tables, nor will it automatically modify users or roles. When set to manual, it is assumed that you want to manually write the queries that update the tables, or are utilizing another system. When Mode is set to this option, the Manual Mode properties are used to determine how the Gateway should query user data.

On this page ...

IULocgo

Database Authentication



Automatic Mode Properties

NameDescription
Tablename PrefixWhen 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.

Manual Mode

NameDescription
Authentication Query

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 QueryA 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 QueryA 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

The following feature is new in Ignition version 8.1.6
Click here to check out the other new features
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].

Example: SELECT username, firstname, lastname, schedule, FROM USERS WHERE username = ?

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 QueryA 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:

  • Start(date)
  • End(date)
  • Available(boolean)
  • Note(string)
Extra Properties QueryA 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

  1. 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. 

  2.  Choose the Database authentication type, and click Next.



  3.  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.



      Table Creation

      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.



  4. 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.
     
  5. 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.
     
  6. 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. 






  • No labels