Authentication with DBGate
Table of Contents
- Introduction
- Authentication Schemas
- HTTP and HTTPs
- Authenticating Users
- Sample of Authenticating Using Stored Procedures
Introduction
DBGate creates endpoints for each connection defined in the appsettings file.
DBGate requires the authentication if the connection string contains the user
and pass
placeholders.
To authenticate, DBGate asks for username and password, changes the placeholders to the actual values, and tries to connect to a database.
For example, appsettings.json
has the following contents:
{ "ConnectionStrings": { "mssql": { "ProviderName": "System.Data.SqlClient", "ConnectionString": "Data Source=.\\SQLEXPRESS;Initial Catalog=master;User ID=user;Pwd=pass" }, "mssql-023": { "ProviderName": "System.Data.SqlClient", "ConnectionString": "Data Source=mssql.savetodb.com;Initial Catalog=AzureDemo100;User ID=sample02_user3;Pwd=Usr_2011#_Xls4168" } } }
In this example, the mssql
endpoints require the authentication and the mssql-023
endpoints do not.
Authentication Schemas
DBGate supports:
- Basic authentication, as specified in RFC2617
- JWT authentication
You may choose the schema using the Auth
setting in the appsettings file.
The JWT schema also allows using the Basic authentication.
Both schemas are safe when using over HTTPs.
DBGate returns error 401 "Unauthorized" for unauthorized requests of any resource for the Basic authentication.
DBGate returns error 403 "Forbidden" for unauthorized requests of protected resources only for the JWT authentication.
HTTP and HTTPs
Do not use DBGate over HTTP except for localhost as the browser sends usernames and passwords as plain text.
Always turn on HTTPS and redirect HTTP to HTTPS.
Authenticating Users
DBGate supports two ways to verify user's login and password:
- using the database login and password
- using stored procedures
The first way is default. DBGate changes the user
and pass
placeholders of the connection string and tries to connect to a database.
If the connection is successful, DBGate loads the model and serves the user requests. Otherwise, it returns the connection error.
In the second way, DBGate calls a defined stored procedure passing the username and password.
The procedure must check the user credentials and return data with empty message for success and non-empty message for bad credentials.
This traditional way for website apps allows managing users in a database without creating database logins.
Sample of Authenticating Using Stored Procedures
Suppose we have a marketplace
database to serve buyers and sellers.
We have the user
table that contains the required fields like id
, uid
, username
, email
, password_hash
, role
, seller_id
.
We have to check user credentials using the usp_sign_in
procedure and execute next requests with specific logins for the buyer
or seller
roles passing the acquired user or seller id.
Different logins allow managing object permissions according to the user role and having different models. So, buyers will see buyer objects and sellers will see seller database objects only.
Here is a sample configuration:
"marketplace": { "ProviderName": "MySqlConnector", "ConnectionString": "Server=localhost;Password=pass;User ID=user;Database=marketplace", "SignIn": "marketplace.usp_sign_in", "AuthContextParams": "auth_user_id auth_seller_id", "RoleUsers": { "auth": { "Username": "marketplace_auth", "Password": "Usr_2011#_Xls4168" }, "default": { "Username": "marketplace_buyer", "Password": "Usr_2011#_Xls4168" }, "buyer": { "Username": "marketplace_buyer", "Password": "Usr_2011#_Xls4168" }, "seller": { "Username": "marketplace_seller", "Password": "Usr_2011#_Xls4168" } } },
You may see the complete field description in the appsettings file.
Below we highlight important notes:
SignIn
contains the name of the procedure to check credentials.RoleUsers
contains the requiredauth
section with the connection credentials to execute the sing-in procedure.RoleUsers
contains the requireddefault
section with the connection credentials used when the user role is not defined.RoleUsers
contains thebuyer
andseller
sections with the connection credentials used for thebuyer
andseller
roles.AuthContextParams
contains names of fields returned from the sign-in procedure and passed as parameters to the following procedure calls of the user.
Below is a sample stored procedure in the MySQL dialect:
DROP PROCEDURE IF EXISTS usp_sign_in; DELIMITER // CREATE DEFINER=marketplace_dev
@localhost
PROCEDURE usp_sign_in(email varchar(50),password
varchar(50)) BEGIN DECLARE user_id int; DECLARE uid varchar(50); DECLARE role varchar(50); DECLARE seller_id int; DECLARE matched tinyint; SELECT u.id, u.uid, u.role, u.seller_id, CASE WHEN get_password_hashed(password
, u.password_hash) = u.password_hash THEN 1 ELSE 0 END AS matched INTO user_id, uid, role, seller_id, matched FROM user u WHERE u.email = LOWER(email) OR u.username = LOWER(email) LIMIT 1; SELECT CASE WHEN matched = 1 THEN user_id ELSE NULL END AS auth_user_id , CASE WHEN matched = 1 THEN uid ELSE NULL END AS uid , CASE WHEN matched = 1 THEN role ELSE NULL END AS role , CASE WHEN matched = 1 THEN seller_id ELSE NULL END AS auth_seller_id , CASE WHEN matched = 1 THEN NULL WHEN user_id IS NOT NULL THEN 'Password not matched' ELSE 'User not found' END AS message; END // DELIMITER ; GRANT EXECUTE ON PROCEDURE usp_sign_in TO 'marketplace_auth'@'localhost';
Pay attention to the following:
- The procedure must have two parameters, for username and password. The order is important. The names are not.
- You must have a single parameter for username, even a user may use an email or username. Just check both.
- Return the empty message for success or error description for errors in the
message
field. - Grant the
EXECUTE
permission on the procedure to the user defined in theRoleUsers
:auth
section.