Login with MySQL Database as User Store

miniOrange provides ready to use Single Sign-On solution using MySQL Database.This solution ensures that you are ready to roll out secure access to any of your application using MySQL Credentials within minutes.

Where is SSO (Single Sign-On) with MySQL Database applicable?

Various User Stores i.e. CRM/HRM/CMS/LMS where users are stored, do not support Single Sign-On or any other authentication protocol inherently.Here miniOrange MySQL SSO solution comes into the picture and provides different SSO services to these type of applications.

Step 1: Setup MySQL Database as Authentication Source

• Login into your miniOrange Admin console and navigate to User Stores >> Add User Store.
  
  
  
• Select the Database Tab as shown below.



• Enter the Database Identifier which is basically the Database name you are adding up, it can be any name relevant to the User-store.
• Select the Database type as MySQL.Following table shows the default values and syntax of the fields in the configuration page.

Note :-The default port number for MySQL is 3306.


Database Type	Connection URL	Default Port Number	Password Hash Algorithm
MySQL	jdbc:mysql://hostname:port/database-name	3306	PHPASS

• Database Host URL is basically the Connection URL.
  1. The hostname in the Connection URL can be either localhost or any other remote host.
  2. The port is by default 3306 for MySQL Database type.
  3. The database-name is basically the Database which stores the users for authentication purpose.
• Enter the Admin Username and Admin Password.
  1. In case of localhost, the Admin Username is basically 'root', and in case of any other remote host please contact your administrator.
  2. Enter the Admin Password for the corresponding Admin Username.
• The User Table is basically the table name of the Database where users are stored for authentication.For Example, in case of Wordpress the User Table is wp_users.
• Enter the name of the corresponding Username column and Password column.
  1. Username column is the column which stores the username of the users to be authenticated.For Example, in case of Wordpress the Username column is user_login.
  2. Password column is the column which stores the corresponding passwords of the users.For Example, in case of Wordpress the Password column is user_pass.
• The Hashing type which is used for MySQL is ,PHPASS.
• Advanced Settings:-
  1. User-activated query,in which you can enter the query for checking if the User is Active or not.
     Example:- select user_activated_column from user_tables where user_name=? and user_activated_column='true';
  2. Enable for EndUsers,enable this option if you want your endusers to log in to their corresponding End-User Dashboard using IDP Credentials.
  3. Authenticate via miniOrange,if you enable this option, then the users present in an external database will be authenticated directly through the miniOrange IdP, without being created in the miniOrange IdP. It is helpful in case when the database from where the authentication is being performed contains some private or sensitive information about users.
  4. Send Configured Attributes,if you enable this option, then only the attributes configured below will be sent in attributes at the time of login.
  5. Attribute Mapping allows us to add extra attributes which we can send from Database to any configured application.Click on the + symbol to map an attribute.
     
     
     
     
     1. Attribute Keys are actually mapped with the corresponding Attribute Values.
        For Example:- Above image utters that username is mapped with user_login and email is mapped with user_email.
     2. At the time of Test Attribute Mapping, username will be the Attribute Key and user_login (which is basically fetched from the column user_login in the User Table) will be the Attribute Value.Similar explanation for email as well.
     3. These mapped attributes can be fetched using the Test Attribute Mapping action ,which is discussed later in this document.
• Click on Save to save your configuration.

Step 2: Test Connnection:

• In order to check if connection is established with the Database or not, Test Connection is required to be done.Kindly navigate to Select >> Test Connection.
  
  
• Enter the credentials of the user, stored in the User Table of the corresponding Database for testing if the connection is correctly established.Click on Test to check if connection was successful or not.
  
  
• If Test Connection is successful, you are good to go!.The success message for the Test Connection will look like :--
  
  
• If Test Connection is not successful,kindly check your configuration once again or contact your administrator.Another probable reason can be that, you are entering wrong credentials for Test Connection

Step 3: Test Attribute Mapping:

• Navigate to Select >> Edit in the corresponding Database User Store for which you want to perform Test Attribute Mapping action.On the configuration page, enable Send Configured Attributes.If you enable this option, then only the attributes configured below will be sent in attributes at the time of login.
  
  
  
  
• In order to check if the attributes you have added are mapped correctly with reference to the Database or not, Test Attribute Mapping is required to be done.Kindly navigate to Select >> Test Attribute Mapping.
  
  
• Enter the credentials of the user stored in the User Table of the corresponding Database and,click on Test to check if the attributes are fetched properly or not.
  
  
• If Test Attribute Mapping is successful, you will see a table which looks like :--
  
  
• Suggested Attributes that you can map are the attributes which were added by you during configuration.
• Configured Attributes Fetched for the User Account are the attributes which are basically the attributes corresponding to the user fetched from the User Table of the Database which is being used as an authentication source.
• If Test Attribute Mapping is not successful,it means that wrong credentials were entered or the user is not present in the User Table.You should get result as:-
  
  

Step 4: User Provisioning

• Navigate to Users >> User Provisioning settings.

• Select the Database from the drop down menu.
• Check the provisioning features.

• To import the users from Database, go to the User Provisioning, Click on the Import Users button.
• Select the Database from the drop down menu and save the configuration.

• Now go to the Users >> User List and you will find the all the users imported from Database.

5. Configure your Application in miniOrange