Search Results :


Setup User Provisioning in MySQL

User Provisioning in MySQL streamlines access management (IAM) & automation of your external and internal users across applications. miniOrange allows you to create, manage, & delete users access to on-premises, cloud, and hybrid apps

    User Provisioning between Database and miniOrange

There are three major parts to setting up user sync between your database and miniOrange IdP. They are as follows:

  • Part A: Adding your MySQL Database as a User Store in miniOrange IdP
  • Part B: Configuring Queries for User Provisioning
  • Part C: Configuring User Provisioning & Attribute Mapping

Consider the following schema for setting up user provisioning/deprovisioning. We have used this schema to illustrate how to configure the options for a simple database. You can use this example to configure your own database as a user store in miniOrange.

Example Setup:

Database Type Mysql-5.7
Database Name Wordpress
Database JDBC URL (for MySQL) jdbc:mysql://{server-domain}:3306/wordpress
Table where user info is stored wp_users

wp_users table description:

Column Type Description
ID bigint(20) unsigned User ID column
user_login VARCHAR Username column
user_pass VARCHAR Stores password of the user; will store default password during user creation
display_name VARCHAR Display name of user
user_email VARCHAR Email address of user

Part A: Adding your MySQL Database as a User Store in miniOrange IdP

  • Refer to steps 1-3 from this document.

Part B: Configuring User Provisioning for your Database

      There are two directions in which user provisioning can be configured: Inbound (i.e. from your database into miniOrange) and Outbound (from miniOrange into your database).

      Inbound provisioning deals with user import, whereas Outbound deals with user creation/deletion/update. In both cases, attribute mapping is necessary. Attribute mapping between database columns and miniOrange IdP attributes is done via a special notation. Column values are inferred by the miniOrange IdP using this notation.

    • To configure user provisioning for your database, you will first have to navigate to the User Store section. Here, you will see the list of configured user stores.
    • User Provisioning, Database Provisoning User Store List
    • Click on the dropdown near your configured database and select ‘Edit’:
    • User Provisioning, Database Provisoning Edit User Store dropdown
    • This will open the edit page for your configured user store.
    • User Provisioning, Database Provisoning Query Strings
    • Now you are ready to configure all the required provisioning features.

    Import Users:

    • This is an example of inbound provisioning. Users are imported from the database into the miniOrange default user store. The attributes to be selected are specified in the attributes section of Database as a User Store. For each attribute (except for username and password) that you want to import & map to a user in the miniOrange default user store, you must specify a SELECT query.
    • You do not need to specify a SELECT query for username(user_login) and password(user_pass) column values; they are imported automatically using the column names used when setting up your database as a user store.
    • In the edit user store page, scroll down until you can see the option for adding database attributes.
    • User Provisioning, Database Provisoning attributes
    • Add as many input fields as required. For this example, we have 2 database columns - display_name and user_email - excluding the USERNAME and PASSWORD columns. The queries for these columns will be as follows:
      • Query for Firstname → SELECT ‘##display_name##’, FIRSTNAME FROM wp_users WHERE user_name=?
      • Query for Email Address → SELECT ‘##user_email##’, user_email FROM wp_users WHERE user_name=?
    • The [ ‘##COLUMN_NAME##’, COLUMN_NAME ] notation in the query tells miniOrange that this field should be populated with the value of the column from the database, based on the unique username identifier.
    • Your configuration should look like this after you’re done adding the queries:
    • User Provisioning, Database Provisoning Query Strings filled

    Check Users:

    • This query is used to check if a particular user already exists in the database. A user that already exists in the database will not be created again. The structure of this query should be such that it checks all the columns in the table that define a distinct user.
    • For our example, we will compare the values of the user_login and user_email columns against the relevant fields in miniOrange:
      • SELECT * FROM wp_users WHERE user_login=’##user_login##’ AND user_email=’##user_email##’
      User Provisioning, Database Provisoning Filled-in Check Users query

    Create Users:

    • These queries create a user in the database whenever a user is created in miniOrange. The query should contain the appropriate column names. Here, the password is set to a default string (that must be changed later).
    • These queries use the same notation to specify the attributes that must be mapped from miniOrange IdP to the database. This is an example of outbound provisioning.
    • In our example, we will sync the user_login,user_pass,display_name and user_email of the newly created user from miniOrange to the database. We will pass a default password string (‘defaultPassword123#’) to the database:
      • INSERT INTO wp_users (user_email, user_pass, display_name, user_email) VALUES (‘##user_login##’, ‘defaultPassword123#’, ‘##display_name##’’,’##user_email##’)
      User Provisioning, Database Provisoning Filled-in Create Users query

    • In this example, the [ ‘##user_login##’, ‘##display_name##’, ‘##user_email##’ ] strings will be replaced by the username, display_name, and email values of the newly created user in miniOrange.

    Once you have filled in the required values, scroll down and click on 'Save' to save your current configuration.

    After saving you should see the following success message:

    User Provisioning, Database Provisoning Save Configuration message

Part C: Configuring User Provisioning and Attribute Mapping

    We have successfully set up our database as a user store and entered all the queries for inbound as well as outbound provisioning. Now, we must configure the user provisioning section to activate user provisioning for our database.

    Step 1: Set your database as the default user store

    • Navigate to the user store section. You will be shown a list of all your configured user stores. Check if you can see the user store you set up in Part A.
    • Click on the dropdown for your database entry, and select ‘Make default’.

    Step 2: Navigate to the user provisioning section; from the dropdown, select ‘Database’

    • You should be able to see the database attributes that we have configured in Part B. These database attributes are as yet unmapped, so the values of the ‘miniOrange Attributes’ column will be blank.
    • User Provisioning, Database Provisoning Configured query attributes

    Step 3: Map the database attributes to their respective field names in miniOrange

    • The database attributes can be mapped to 3 different types of attributes:
      • Default User Profile Attributes
      • Custom User Profile Attributes
      • Custom Attributes
    • For this example, all fields configured for our database are mapped to the Default User Profile Attributes.
    • Map all the required attributes to their values in the miniOrange IdP. This is how the mapping will look for our example.
    • User Provisioning, Database Provisoning Default User Profile Attributes mapping list

    Step 4: Enable Provisioning Features

    • Here, you have to choose which provisioning features you want to enable for your database.
    • User Provisioning, Database Provisoning List of User Provisioning Features
    • You can choose some or all of these features, as per your requirement. For this example, we will choose the basic inbound & outbound provisioning features:
    • User Provisioning, Database Provisoning Basic Inbound & Outbound provisioning features

    Step 5: Save your configuration

    • Once you have mapped your database attributes to the miniOrange IdP attributes & enabled your desired features, you can go ahead and save this configuration. If everything is properly configured, you should see the following success message:
    • User Provisioning, Database Provisoning Save configuration success message

    Step 6: Test your configuration

    • You can test your configuration by performing one of the actions for which you have enabled the provisioning feature. In this example, we will test it by importing database users into miniOrange.
    • We will now import the users from the database into miniOrange. Navigate to Provisioning → Import Users, and select ‘Database’ from the dropdown.
    • User Provisioning, Database Provisoning Import Users
    • Click on ‘Import’. If the configuration was set up correctly, you should see the following message:
    • User Provisioning, Database Provisoning Import Users success message
    • You can verify if the import was successful by navigating to Users → Users List
    • User Provisioning, Database Provisoning Import Users verification
Hello there!

Need Help? We are right here!

Contact miniOrange Support

Thanks for your inquiry.

If you dont hear from us within 24 hours, please feel free to send a follow up email to