Database Authentication

If your list of users is managed by a database then you can setup dbFront so that it will call a stored procedure to validate users.

Enable Database Authentication

To enable Database Authentication for users you need to update the Database Connection so that it uses Database Authentication.  For more details see: Connection Access.  Once you choose Database Authentication, a second tab will be opened where you can complete the setup.

• Source Database: You will need to select the name of the database that contains the validation procedures.
• Validate Procedure: Enter the name of the validation procedure.  The user used to create the connection must have access to this procedure.
• Access Level: The access level of users who are validated by this procedure.

Login UI Change

Enabling Database Authentication will add a new dropdown to the Login Screen named Directory.  Users can then use this dropdown to select the name of the Connection to login.

Validation Procedure

The validation procedure will be called with 6 parameters and if the login is successful it must return a single database row.  Simple example implementations are given below.

Two important notes:

1. Storing and comparing RAW passwords is extremely dangerous.   In each case, you should only store password hashes, preferably individually salted, and then hash the inbound password and compare the hashes.
2. The benefit of a Stored Procedure is that it allows the DBA complete control over what a successful login looks like.

MySql

CREATE PROCEDURE `validateUser`(
          IN `p_username` VARCHAR(64),
          IN `p_password` VARCHAR(64),
          IN `p_hostname` VARCHAR(64),
          IN `p_ip4` VARCHAR(64),
          IN `p_ip6` VARCHAR(256),
          IN `p_useragent` VARCHAR(256))
-- Check to see if the user exists..
select * from tblauthors t where t.id = p_username and t.password=p_password;

MySql Troubleshooting

• Error: Can't load [%procedurename%] parameters.

SQL Server

CREATE PROCEDURE validateUser
          @username  VARCHAR(64),
          @password  VARCHAR(64),
          @hostname  VARCHAR(64),
          @ip4   VARCHAR(64),
          @ip6   VARCHAR(256),
          @useragent VARCHAR(256)
AS
BEGIN
-- Check to see if the user exists...
select * from tblauthors t where t.id = @username and t.password=@password;
END;

Oracle

CREATE PROCEDURE validateUser (
          p_username  VARCHAR(64),
          p_password  VARCHAR(64),
          p_hostname  VARCHAR(64),
          p_ip4   VARCHAR(64),
          p_ip6   VARCHAR(256),
          p_useragent VARCHAR(256)
          p_result out sys_refcursor)
IS
BEGIN
-- Check to see if the user exists..
OPEN cur for select * from "ProductCategory" t where t.id = p_username and t.password=p_password;
END;

More Security Options

For more security options see: Authentication Overview