Authentication with ODataDB

Table of Contents

• Introduction
• Authentication Schemas
• HTTP and HTTPs
• Authenticating Users
• Sample of Authenticating Using Stored Procedures

Introduction

ODataDB creates endpoints for each connection defined in the appsettings file.

ODataDB requires the authentication if the connection string contains the user and pass placeholders.

To authenticate, ODataDB 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

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

ODataDB returns error 401 "Unauthorized" for unauthorized requests of any resource for the Basic authentication.

ODataDB returns error 403 "Forbidden" for unauthorized requests of protected resources only for the JWT authentication.

HTTP and HTTPs

Do not use ODataDB 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

ODataDB supports two ways to verify user's login and password:

1. using the database login and password
2. using stored procedures

The first way is default. ODataDB changes the user and pass placeholders of the connection string and tries to connect to a database.

If the connection is successful, ODataDB loads the model and serves the user requests. Otherwise, it returns the connection error.

In the second way, ODataDB 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:

1. SignIn contains the name of the procedure to check credentials.
2. RoleUsers contains the required auth section with the connection credentials to execute the sing-in procedure.
3. RoleUsers contains the required default section with the connection credentials used when the user role is not defined.
4. RoleUsers contains the buyer and seller sections with the connection credentials used for the buyer and seller roles.
5. 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:

1. The procedure must have two parameters, for username and password. The order is important. The names are not.
2. You must have a single parameter for username, even a user may use an email or username. Just check both.
3. Return the empty message for success or error description for errors in the message field.
4. Grant the EXECUTE permission on the procedure to the user defined in the RoleUsers:auth section.