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.

Authentication is required if the connection string contains the user and pass placeholders.

To authenticate, ODataDB prompts for a username and password, replaces the placeholders with the actual values, and attempts to connect to the database.

For example, consider the following appsettings.json:

{
  "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 authentication, while the mssql-023 endpoints do not.

Authentication Schemas

ODataDB supports:

• Basic authentication, as specified in RFC2617
• JWT authentication

You can select the authentication schema using the Auth setting in the appsettings file.

The JWT schema also supports Basic authentication.

Both schemas are secure when used over HTTPS.

For unauthorized requests with Basic authentication, ODataDB returns a 401 "Unauthorized" error. For the JWT authentication, it returns a 403 "Forbidden" error for unauthorized requests to protected resources.

HTTP and HTTPS

Avoid using ODataDB over HTTP, except for localhost, as browsers transmit usernames and passwords in plain text.

Always enable HTTPS and redirect HTTP traffic to HTTPS.

Authenticating Users

ODataDB supports two methods for verifying a user's login and password:

1. Using the database login and password (default method).
2. Using stored procedures.

In the first method, ODataDB replaces the user and pass placeholders in the connection string and attempts to connect to the database. If the connection is successful, ODataDB loads the model and processes user requests. If not, it returns a connection error.

In the second method, ODataDB calls a specified stored procedure, passing the username and password. The procedure must validate the credentials and return an empty message for success or a non-empty message for failure.

This approach allows for user management in the database without creating separate database logins.

Sample of Authenticating Using Stored Procedures

Consider a marketplace database serving buyers and sellers.

The user table contains fields such as id, uid, username, email, password_hash, role, and seller_id.

We will check user credentials using the usp_sign_in procedure and execute subsequent requests with specific logins for the buyer or seller roles, passing the acquired user or seller ID.

Different logins allow for managing object permissions based on user roles, ensuring that buyers see buyer objects and sellers see seller objects only.

Here’s 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"
    }
  }
}

Refer to the appsettings file for a complete field description.

Important notes:

1. SignIn specifies the procedure name for credential verification.
2. RoleUsers includes the required auth section with connection credentials to execute the sign-in procedure.
3. RoleUsers contains the default section for connection credentials used when the user role is undefined.
4. RoleUsers includes buyer and seller sections for connection credentials specific to those roles.
5. AuthContextParams lists the fields returned from the sign-in procedure, which are passed as parameters to subsequent procedure calls.

Here’s a sample stored procedure in MySQL:

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';

Key points to note:

1. The procedure must have two parameters for username and password. The order matters, but the names do not.
2. Use a single parameter for the username, allowing either an email or username for login. Check both.
3. Return an empty message for success or an error description in the message field for failures.
4. Grant EXECUTE permission on the procedure to the user defined in the RoleUsers:auth section.