Version: 8.1

Database Authentication

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:

Name	Description
Database	The 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.

Name

Description

Database

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

Automatic Mode Properties

Name	Description
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.

Manual Mode

Name	Description
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 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	New in 8.1.6 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 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: Start(date) End(date) Available(boolean) Note(string)
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.
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.
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.

Password Encryption

When a database user source Mode is set to Automatic, Ignition encrypts the user’s password in the database using SHA1. However, it is possible to allow users to change their passwords using an update query. The script below takes a password string, encrypts it, and updates the password in the database.

Simple Password Encryption Script
# Get the current logged in user’s username.
username = system.tag.read("[System]Client/User/Username").value

# Take the user’s new password from a component. We are using the Password Field component.
newPass = event.source.parent.getComponent('Current Password').text

# Import SecurityUtils from com.inductiveautomation.ignition.common.util.
from com.inductiveautomation.ignition.common.util import SecurityUtils

# Encrypt the password from the second line using SecurityUtils.
# This will encrypt your password as SHA1.
encryptPass = SecurityUtils.sha1String(newPass)

# Add our update query.
query = "UPDATE scada_users SET passwd = ? WHERE username = ?"

# Execute our query to update the password in the database.
system.db.runPrepUpdate(query, (encryptPass, username), 'database_connection')

This is a simplified script, but you can add additional logic to your script. For example, the following script also validates the user’s current password before allowing them to update their password.

Password Encryption Script with User Validation
# Get the current logged in user’s username.
username = system.tag.read("[System]Client/User/Username").value

# Create a variable for the user’s current password. In the client this is where the user will enter their current password into a component.
password = event.source.parent.getComponent(‘Current Password’).text

# If the component does not contain an empty string the script will move on, 
# else, it will open a warning box instructing the user to enter their current password.
if password != "":
    # Here we are validating the username and password for the current logged in user.
    confirmPassword = system.security.validateUser(username, password)

    # If the user is validated we will move on, 
    # else, it will open a warning box informing the user that an incorrect password was entered.
    if confirmPassword:

        # Fields have been added to the Client for the user to enter and confirm their new password. 
        # These are the variables for the new password.
        pass1 = event.source.parent.getComponent('Password 1').text
        pass2 = event.source.parent.getComponent('Password 2').text
        
        # Now we have to compare the two new passwords entered to make sure they match exactly. 
        # If the passwords match the script continues, 
        # else, it will open a warning box informing the user of non-matching passwords.
        if pass1 == pass2:

            # Import SecurityUtils from com.inductiveautomation.ignition.common.util.
            from com.inductiveautomation.ignition.common.util import SecurityUtils
            
            # Encrypt the new password as SHA1.
            newPass = SecurityUtils.sha1String(pass1)
            
            # Add our update query.
            query = "UPDATE support_users SET passwd = ? WHERE username = ?
            
            # Execute our query to update the password in the database.
            system.db.runPrepUpdate(query, (newPass,username), 'database_connection')

            # Show a message indicating the password has been updated.  
            system.gui.messageBox("Your password has been updated.")
            
        else:
            system.gui.warningBox("Password 1 and Password 2 do not match")
    else:
        system.gui.warningBox("Incorrect password")
else:
    system.gui.warningBox("Enter current password")