How to Implement JWT Authentication with a Dynamic Database in Django? (User table is in each database its not central)

I'm working on a Django application where the user data is not stored in a central database. Instead, each client has their own separate database hosted on their own server.

Here's the setup:

• JWT Authentication is used for the app.
• The central Django server handles authentication requests but must dynamically connect to a client's specific database based on information provided in the JWT token.
• The employee_id is stored in each client's database, not in the central Django database.
• The connection string to the client's database is included in the JWT token payload.

My Implementation:

I've created a custom JWTAuthentication class where:

1. Token Validation: The JWT token is validated.
2. Database Connection: Based on the token payload, I dynamically connect to the client's database using the connection string provided in the token.
3. User Verification: I fetch the user from the client’s database using the employee_id in the token and check if the user is active.

Here's a simplified version of my JWTAuthentication class:

from rest_framework import authentication
from rest_framework.request import Request
from .models import TokenUser
from .settings import api_settings
from .tokens import Token
from .exceptions import AuthenticationFailed, InvalidToken
import pymssql

class JWTAuthentication(authentication.BaseAuthentication):
    def authenticate(self, request: Request):
        header = self.get_header(request)
        if header is None:
            return None

        raw_token = self.get_raw_token(header)
        if raw_token is None:
            return None

        validated_token = self.get_validated_token(raw_token)
        return self.get_user(validated_token), validated_token

    def get_user(self, validated_token: Token):
        employee_id = validated_token.get(api_settings.USER_ID_CLAIM)
        conn_string = validated_token.get('conn')
        role = validated_token.get('role')

        if not employee_id:
            raise InvalidToken("Token contained no recognizable user identification")

        if role == 0:
            # Local database user fetching logic
            try:
                user = self.user_model.objects.get(**{api_settings.USER_ID_FIELD: employee_id})
                if not user.is_active:
                    raise AuthenticationFailed("User is inactive", code="user_inactive")
                return user
            except self.user_model.DoesNotExist:
                raise AuthenticationFailed("User not found", code="user_not_found")
        else:
            # External database connection logic
            if not conn_string:
                raise InvalidToken("Token contained no connection string")

            def reconnect(conn_string):
                conn_details = conn_string.split(':')
                server, database, username, password = conn_details[1:5]
                return pymssql.connect(server=server, user=username, password=password, database=database)

            with reconnect(conn_string) as connection:
                cursor = connection.cursor()
                cursor.execute("""
                    SELECT [btEmpIsActive]
                    FROM [tblMstEmployee]
                    WHERE [inEmpId] = %s
                """, [employee_id])

                row = cursor.fetchone()
                if not row:
                    raise AuthenticationFailed("User not found in the external database", code="user_not_found")

                is_active = row[0]
                if not is_active:
                    raise AuthenticationFailed("User is inactive in the external database", code="user_inactive")

            return TokenUser(token=validated_token)

My Questions:

1. Is This Approach Secure?:

   • By connecting dynamically to client databases, am I exposing any significant security risks?
   • How can I ensure that the connection string is not tampered with in the token?

2. Handling User Verification:

   • Currently, I strictly verify the employee_id and the active status of the user. Is there a safe way to bypass or modify this verification if needed?
   • What best practices should I follow to securely manage user identification across multiple databases?

3. Alternative Approaches:

   • Are there alternative methods for handling authentication when the user table is not in a central database but spread across dynamic, client-specific databases?
   • Would using a microservices architecture with a dedicated authentication service be a better approach?

Additional Information:

• Django Version: 3.x
• Database: MSSQL (using pymssql for connections)
• Authentication: JWT (with Django REST framework)

I'm open to suggestions or best practices that could make this setup more secure and efficient. Thank you in advance!

I want to build authentication system which will satisfy my situation where there is not one central database instead there are multiple clients databases in server

Back to Top