Your Database is Better at Security Than Your App

You're rebuilding auth logic your database already ships with. SQL Server can impersonate users and enforce per-user boundaries at the engine level, which means less code in your app and a smaller blast radius when something gets breached.

Most apps treat the database as a dumb bucket. One privileged login, db_owner or worse, and every authorization decision gets reimplemented in application code. Then you WHERE user_id = ? your way through every query and hope nobody forgets a clause.

There's a better way, and it's not new. It's been sitting in your database engine the whole time.

The idea: your app connects with a single login, and after it authenticates a user, it impersonates that user's database identity. The engine enforces the boundaries from there. I've built this in SQL Server. Here's how it works, where it bites, how it stacks up against the usual alternatives, and a security-focused review checklist to go with it.

The Impersonation-Based Authentication Flow

Your app uses a single privileged server login. Call it __web_user. It opens one connection (or a pool) instead of connecting as each individual user.

When a user authenticates at the application level, __web_user calls the server's impersonation feature, EXECUTE AS USER = 'ApplicationDbUser', to temporarily become that specific database user and inherit exactly their permissions. Nothing more.

The security decision moves out of your app code and into the engine.

This isn't a SQL Server party trick, either. The implementation below is T-SQL because that's where I've shipped it, but Postgres gives you the same pattern with its own primitives:

• Impersonation: SET ROLE or SET SESSION AUTHORIZATION in place of EXECUTE AS USER, with RESET ROLE to revert.
• Per-user scoping: Row-Level Security policies keyed off current_user, which is arguably cleaner than SQL Server's per-user views since the engine enforces the filter at the table itself, not in a view you have to remember to query through.
• Permissions: roles plus GRANT EXECUTE on functions, same as the role model here.

So if you're on Postgres, read EXECUTE AS / USER_ID() / scoped-view as SET ROLE / current_user / RLS policy and the rest carries over.

Strengths

Simpler connection management. Your app manages one connection or pool under __web_user. No per-user connection juggling.

Less password exposure. You don't store or ship individual user database passwords anywhere in the app layer, no session stores, no client-side storage. If the app server gets breached, there's no pile of credentials to walk away with.

You actually use the database's security features. Create database users (not logins) for your app users, assign them to roles, and grant fine-grained permissions. The engine already does this well. Let it.

It forces least privilege. Grant the AuthenticatedUser role execute rights on specific stored procedures and views, and nothing else. No direct table access. No CREATE USER, DROP USER, ALTER PERMISSIONS. The user can do exactly what you allow and not one thing more.

Authorization lives in one place. Permissions on procs and views are managed centrally in the database instead of being scattered across application authorization logic you have to keep in sync.

Weaknesses

I'm not going to sell you the upside without the downside. Here's where it's vulnerable:

Your procs and views are now the security boundary. Direct table access is gone, so the attack surface shifts to your stored procedures and views. A SQL injection or a logic flaw in one of those can still escalate privileges or leak data. That risk exists in every approach, but be clear-eyed that this is where it concentrates here.

If __web_user is compromised, an attacker can impersonate any user. That's bad. It's also dramatically less-bad than handing out table-level access or db_owner. The attacker is still confined to the union of what your application users can do, not the whole database.

Information leakage is still possible. No direct table access doesn't mean no leakage. A flawed proc or view can still hand an impersonated user data they shouldn't see.

Auditing takes deliberate work. It's doable, but your audit trail has to attribute every action to both the originating __web_user and the impersonated AuthenticatedUser. Get that wiring right up front or forensics will be miserable later.

Alternatives, and Why They're Generally Worse

Individual Database Logins per User (uncommon)

A separate server login and database user for every application user, with the app connecting using those specific credentials.

Why it's worse:

• Credential exposure. You're back to storing and retrieving individual database credentials in the app layer. Breach the server's memory, get the credentials.
• It doesn't scale. Managing thousands or millions of server logins is an operational nightmare.
• Connection overhead. Spinning up and managing that many distinct connections leads straight to bottlenecks and resource exhaustion.

A Single App Login with Full Database Access (typical)

One database user with broad privileges, db_owner or even sysadmin, and all authorization handled in application code. This is the common paradigm, and people often run DDL migrations through this login too.

Why it's worse:

• Catastrophic single point of failure. Compromise that login or land one SQL injection, and the attacker owns the entire database. Every application-level check is bypassed. Total data loss or theft is on the table.
• It violates least privilege by design. The whole system rides on one over-privileged account.
• Auditing is hard. Every action looks like it came from the same login, so you're building your own audit trail from scratch.

If you want a real-world look at what full DML and DDL access buys you when it goes wrong, read the MOVEit transfer breach deep dive. Thousands of firms, zero-day flaws, file-transfer software with too much reach.

Implementation Example

Implementation is where it gets tricky. My first attempt was a headbanger, and not the good kind. It's an unorthodox way to build, and there were concepts I had to shake off. The big one: "user" is now under SQL Server's control, not my app's. Sitting with that actually pushed me toward a more philosophical understanding of auth systems and how an app relates to its database.

For those who need to see, smell, and touch it, here's a simplified example. I've built this in SQL Server, so the database side is T-SQL. The app side I'll do in Python, because it's terser than the TypeScript I'd normally reach for, and this is about the workflow, not the shape of the data.

SQL Implementation

Create the application login and app users

-- Application login used by the web app
CREATE LOGIN __web_user WITH PASSWORD = 'StrongPassword!123';
CREATE USER __web_user FOR LOGIN __web_user;

-- Application database users (no logins)
CREATE USER alice WITHOUT LOGIN;
CREATE USER bob WITHOUT LOGIN;

-- Shared role for impersonated users
CREATE ROLE AuthenticatedUser;
EXEC sp_addrolemember 'AuthenticatedUser', 'alice';
EXEC sp_addrolemember 'AuthenticatedUser', 'bob';

-- Grant impersonation rights only to __web_user
GRANT IMPERSONATE ON USER::alice TO __web_user;
GRANT IMPERSONATE ON USER::bob TO __web_user;

Create tables with sensitive data

-- Master customer table
CREATE TABLE Customer (
    DbUserId INT PRIMARY KEY,  -- maps to USER_ID()
    FirstName NVARCHAR(100),
    LastName NVARCHAR(100)
);

-- Credit cards (one-to-many per customer)
CREATE TABLE CreditCard (
    DbUserId INT,
    Last4 CHAR(4),
    Name NVARCHAR(100),
    PRIMARY KEY (DbUserId, Last4),
    FOREIGN KEY (DbUserId) REFERENCES Customer(DbUserId)
);

-- Payments (many per card)
CREATE TABLE Payment (
    DbUserId INT,
    CreditCard CHAR(4),
    PaymentNo INT,
    Amount MONEY,
    PRIMARY KEY (DbUserId, CreditCard, PaymentNo),
    FOREIGN KEY (DbUserId, CreditCard) REFERENCES CreditCard(DbUserId, Last4)
);

Create views filtered by the current user

This is the core trick. USER_ID() returns the impersonated user, so the view scopes itself automatically. No WHERE user_id = ? to forget.

-- View for authenticated users to see their credit cards
CREATE VIEW MyCreditCards_V AS
SELECT DbUserId, Last4, Name
FROM CreditCard
WHERE DbUserId = USER_ID(); -- Scoped by current user

-- View for authenticated users to see their payments
CREATE VIEW MyPayments_V AS
SELECT DbUserId, CreditCard, PaymentNo, Amount
FROM Payment
WHERE DbUserId = USER_ID(); -- Scoped by current user

-- Grant SELECT on views to authenticated users
GRANT SELECT ON MyCreditCards_V TO AuthenticatedUser;
GRANT SELECT ON MyPayments_V TO AuthenticatedUser;

Create stored procedures

For __web_user:

-- Only __web_user can run this
CREATE PROCEDURE CreateCustomer_trx
    @UserName NVARCHAR(128),
    @FirstName NVARCHAR(100),
    @LastName NVARCHAR(100)
AS
BEGIN
    BEGIN TRANSACTION;
        INSERT INTO Customer (DbUserId, FirstName, LastName)
        VALUES (USER_ID(@UserName), @FirstName, @LastName);
    COMMIT TRANSACTION;
END;

GRANT EXECUTE ON CreateCustomer_trx TO __web_user;

For AuthenticatedUser:

-- Credit card addition - scoped by impersonated user
CREATE PROCEDURE AddCreditCard_trx
    @Last4 CHAR(4),
    @Name NVARCHAR(100)
AS
BEGIN
    IF EXISTS (SELECT 1 FROM CreditCard WHERE Last4 = @Last4 AND DbUserId = USER_ID())
        RAISERROR('Credit card already exists', 16, 1);
        GOTO EXIT_ERROR;

    -- Insert credit card
    BEGIN TRANSACTION;
        INSERT INTO CreditCard (DbUserId, Last4, Name)
        VALUES (USER_ID(), @Last4, @Name);
    COMMIT TRANSACTION;
        RETURN 0;

    EXIT_ERROR:
        RETURN 1;
END;


-- Payment processing - scoped by impersonated user
CREATE PROCEDURE MakePayment_trx
    @CreditCard CHAR(4),
    @PaymentNo INT,
    @Amount MONEY
AS
BEGIN
    IF NOT EXISTS (SELECT 1 FROM CreditCard WHERE Last4 = @CreditCard AND DbUserId = USER_ID())
        RAISERROR('Credit card not found', 16, 1);
        GOTO EXIT_ERROR;

    BEGIN TRANSACTION;
        INSERT INTO Payment (DbUserId, CreditCard, PaymentNo, Amount)
        VALUES (USER_ID(), @CreditCard, @PaymentNo, @Amount);
    COMMIT TRANSACTION;
        RETURN 0;

    EXIT_ERROR:
        RETURN 1;
END;

-- Add permissions
GRANT EXECUTE ON AddCreditCard_trx TO AuthenticatedUser;
GRANT EXECUTE ON MakePayment_trx TO AuthenticatedUser;

Putting the flow together

-- Step 1: Create new user via app-level login
EXEC CreateCustomer_trx @UserName = 'alice', @FirstName = 'Alice', @LastName = 'Jones';

-- Step 2: Authenticated flow
EXECUTE AS USER = 'alice';
    EXEC AddCreditCard_trx @Last4 = '1234', @Name = 'Alice Visa';
    EXEC MakePayment_trx @CreditCard = '1234', @PaymentNo = 1, @Amount = 49.99;
REVERT;

Language-Level Implementation

Validate before the data ever reaches the database. People confuse compile-time types with runtime safety constantly. Your types don't stop trash from walking in the front door, so you validate at the edge and inside your class methods. You don't know how your API will be consumed, HTTP, CLI, workers, so don't assume the caller did your validation for you.

Here's an RPC-style class with Pydantic models doing the validation:

# ---------------------------
# Pydantic models
# ---------------------------

class CreateCustomerInput(BaseModel):
    user_name: constr(max_length=128)
    first_name: constr(min_length=1)
    last_name: constr(min_length=1)

class AddCreditCardInput(BaseModel):
    user_name: constr(max_length=128)
    last4: constr(min_length=4, max_length=4)
    name: constr(min_length=1)

class MakePaymentInput(BaseModel):
    user_name: constr(max_length=128)
    credit_card: constr(min_length=4, max_length=4)
    payment_no: conint(ge=1)
    amount: PositiveFloat


# ---------------------------
# ImpersonationDB API Class
# ---------------------------


class ImpersonationDB:
    def __init__(self, conn_str: str):
        self.conn_str = conn_str

    def _get_conn(self):
        return pyodbc.connect(self.conn_str, autocommit=False)

    def create_customer(self, data: Union[CreateCustomerInput, Dict]):
        validated = CreateCustomerInput.parse_obj(data)
        with self._get_conn() as conn:
            with conn.cursor() as cur:
                cur.execute(
                    "EXEC CreateCustomer_trx ?, ?, ?",
                    validated.user_name, validated.first_name, validated.last_name
                )
            conn.commit()

    def add_credit_card(self, data: Union[AddCreditCardInput, Dict]):
        validated = AddCreditCardInput.parse_obj(data)
        with self._get_conn() as conn:
            with conn.cursor() as cur:
                cur.execute("EXECUTE AS USER = ?", validated.user_name)
                cur.execute("EXEC AddCreditCard_trx ?, ?", validated.last4, validated.name)
                cur.execute("REVERT")
            conn.commit()

    def make_payment(self, data: Union[MakePaymentInput, Dict]):
        validated = MakePaymentInput.parse_obj(data)
        with self._get_conn() as conn:
            with conn.cursor() as cur:
                cur.execute("EXECUTE AS USER = ?", validated.user_name)
                cur.execute(
                    "EXEC MakePayment_trx ?, ?, ?",
                    validated.credit_card, validated.payment_no, validated.amount
                )
                cur.execute("REVERT")
            conn.commit()

    def get_credit_cards(self, user_name: str) -> List[Dict]:
        with self._get_conn() as conn:
            with conn.cursor() as cur:
                cur.execute("EXECUTE AS USER = ?", user_name)
                cur.execute("SELECT DbUserId, Last4, Name FROM MyCreditCards_V")
                rows = [dict(zip([col[0] for col in cur.description], row)) for row in cur.fetchall()]
                cur.execute("REVERT")
        return rows

    def get_payments(self, user_name: str) -> List[Dict]:
        with self._get_conn() as conn:
            with conn.cursor() as cur:
                cur.execute("EXECUTE AS USER = ?", user_name)
                cur.execute("SELECT DbUserId, CreditCard, PaymentNo, Amount FROM MyPayments_V")
                rows = [dict(zip([col[0] for col in cur.description], row)) for row in cur.fetchall()]
                cur.execute("REVERT")
        return rows

Yes, it's verbose. You fix that with a little meta-programming. In TypeScript I take an object and turn it into EXEC MyProc @KeyName=?, @OtherKey=? with a meta utility, then wrap the IMPERSONATE and REVERT calls in another helper so the EXECUTE AS USER = ? boilerplate disappears. The function shrinks to the part that matters.

You'd pull user_name from your HTTP session, JWT, or job payload. And depending on the scenario, you'd spin up different logins for different apps.

At this point you're probably asking:

Wait. If __web_user can't create users, how does my web app create users? Doesn't giving it that access defeat the whole point?

Good question. That's what background workers are for. Where users get created doesn't matter, as long as it happens away from your public-facing apps.

Offload Security-Sensitive Tasks to a Privileged Worker

First, users need a way to sign up. The web app can enqueue the request without holding any privilege to fulfill it:

CREATE PROCEDURE SignUp_trx
    @UserName NVARCHAR(128)
AS
BEGIN

    IF USER_ID(@UserName) IS NOT NULL
      RAISERROR('User already exists', 16, 1);
      GOTO EXIT_ERROR;

    BEGIN TRANSACTION;
        INSERT INTO AuthEvent (Type, DbUserName, RoleName, Status)
        VALUES ('CreateUser', @UserName, NULL, 'pending');
    COMMIT TRANSACTION;
        RETURN 0;

    EXIT_ERROR:
        RETURN 1;
END;

-- Give your web user the ability to sign up users
GRANT EXECUTE ON SignUp_trx TO __web_user;

Then create the privileged worker that does the actual provisioning, far from the public:

-- Background privileged worker login
CREATE LOGIN __privileged_worker WITH PASSWORD = 'PrivilegedOnly!456';
CREATE USER __privileged_worker FOR LOGIN __privileged_worker;

GRANT ALTER ANY LOGIN TO __privileged_worker;
GRANT IMPERSONATE ANY LOGIN TO __privileged_worker WITH GRANT OPTION;

And a background SQL queue to hand work between them:

-- AuthEvent Table
CREATE TABLE AuthEvent (
    Type NVARCHAR(50),              -- e.g. 'CreateUser', 'GrantRole', 'RevokeRole'
    DbUserName NVARCHAR(128),
    RoleName NVARCHAR(128),         -- Nullable for SignUp
    Status NVARCHAR(50),            -- e.g. 'pending', 'completed', 'error'
    EnqueuedAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    UpdatedAt DATETIME2 NULL,
    PRIMARY KEY (EnqueuedAt, DbUserName)
);

-- View for Worker Polling
CREATE VIEW AuthEvent_V AS
SELECT Type, DbUserName, RoleName, Status, EnqueuedAt, UpdatedAt
FROM AuthEvent
WHERE Status = 'pending';

-- Procedure to update the queue
CREATE PROCEDURE ModifyAuthEvent_trx
    @EnqueuedAt DATETIME2,
    @LastUpdatedAt DATETIME2,
    @DbUserName NVARCHAR(128),
    @Status NVARCHAR(50)
AS
BEGIN

    -- Validation block
    IF NOT EXISTS (
        SELECT 1
        FROM AuthEvent
        WHERE EnqueuedAt = @EnqueuedAt
            AND DbUserName = @DbUserName
            AND UpdatedAt = @LastUpdatedAt
    )
        RAISERROR('Auth event not found or modified elsewhere', 16, 1);
        GOTO EXIT_ERROR;

    BEGIN TRANSACTION;
        UPDATE AuthEvent
        SET Status = @Status,
            UpdatedAt = SYSUTCDATETIME()
        WHERE EnqueuedAt = @EnqueuedAt
            AND DbUserName = @DbUserName
            AND UpdatedAt = @LastUpdatedAt;

        -- Success guarantees, scale guarantees
        IF @@ROWCOUNT <> 1
        BEGIN
          RAISERROR('Auth event modified elsewhere', 16, 1)
          ROLLBACK TRANSACTION;
          GOTO EXIT_ERROR;
        END

    COMMIT TRANSACTION;

        RETURN 0;

    EXIT_ERROR:
        RETURN 1;
END;


-- Privileged worker can read and act on the queue
GRANT SELECT ON AuthEvent_V TO __privileged_worker;
GRANT EXECUTE ON ModifyAuthEvent_trx TO __privileged_worker;

Now you consume that queue in a private subnet. Notice the @LastUpdatedAt clause: it's your optimistic concurrency check. If two workers grab the same task, only the first to modify it wins. The second fails early and moves on. That's what lets this scale horizontally without a distributed lock.

class AuthWorker:
    def __init__(self, db: ImpersonationDB):
        self.db = db

    def run(self):
        while True:
            event = self._fetch_next_event()
            if not event:
                time.sleep(1)
                continue

            logging.info(f"Found pending auth event: {event}")
            try:
                updated = self.db.modify_auth_event(
                    enqueued_at=event["EnqueuedAt"],
                    last_updated_at=event["UpdatedAt"],
                    db_user_name=event["DbUserName"],
                    new_status="processing"
                )

                if not updated:
                    # Event was modified concurrently, skip silently
                    continue

                self._perform_action(event)

                self.db.modify_auth_event(
                    enqueued_at=event["EnqueuedAt"],
                    last_updated_at=event["UpdatedAt"],
                    db_user_name=event["DbUserName"],
                    new_status="success"
                )
                logging.info(f"Auth event succeeded: {event}")

            except Exception as e:
                msg = str(e)
                if "modified elsewhere" in msg.lower():
                    # Skip silently — someone else picked it up
                    continue
                logging.exception(f"Error while processing auth event: {event}")
                self.db.modify_auth_event(
                    enqueued_at=event["EnqueuedAt"],
                    last_updated_at=event["UpdatedAt"],
                    db_user_name=event["DbUserName"],
                    new_status="failed"
                )

    def _fetch_next_event(self) -> Optional[dict]:
        rows = self.db.get_auth_events(limit=1)  # Expects SELECT * FROM AuthEvent_V
        return rows[0] if rows else None

    def _perform_action(self, event: dict):
        kind = event["Type"]
        user = event["DbUserName"]
        role = event.get("RoleName")

        if kind == "CreateUser":
            self.db.create_user(user) # will also attach AuthenticatedUser role
            self.db.grant_impersonation(user) # to __web_user
        elif kind == "GrantRole":
            self.db.add_user_to_role(user, role)
        elif kind == "RevokeRole":
            self.db.remove_user_from_role(user, role)
        else:
            raise ValueError(f"Unsupported AuthEvent type: {kind}")

if __name__ == "__main__":
    db = ImpersonationDB(CONN_STR)
    worker = AuthWorker(db)
    logging.info("Auth worker started. Polling for events...")
    worker.run()

The auth flow calls signUp and then waits for the user to exist:

class ImpersonationDB:
    # ...
    def wait_for_user_creation(self, username: str, max_secs: int = 10) -> bool:
        """
        Polls USER_ID(username) every second for up to max_secs.
        Returns True if the user exists in the database.
        """
        deadline = time.time() + max_secs

        while time.time() < deadline:
            with self._get_conn() as conn:
                with conn.cursor() as cur:
                    cur.execute("SELECT USER_ID(?)", username)
                    result = cur.fetchone()
                    if result and result[0] is not None:
                        return True
            time.sleep(1)

        return False

It's oversimplified, but it works, and it's surprisingly secure.

So what do you actually get when you lean on the server's features instead of fighting them?

• Far less logic at the language level. There's just less to build.
• No hand-rolled RBAC in your app.
• Relational permissioning and scoping, straight from USER_ID() in your views and procs.
• Data guarantees, because you can validate against the database itself.
• Fewer network round trips.
• Audit trails.
• Relational queues.
• Scoped views, so no more map/filter/reduce gymnastics to enforce ownership.

Security-Focused Code Review Checklist

This approach narrows the attack surface, but the surface that remains, your procs and views, has to be reviewed deliberately. Bake this into your review process:

Input Validation

• Validate all external inputs to procs and views for type, length, format, range, and allowed characters, at the strictest possible point: UI, route, language-level API, and critically, inside the stored procedure itself.
• Test any validation regexes for bypasses.
• Prefer allow lists over block lists. Whitelisting is inherently safer than blacklisting.

SQL Injection Prevention

• Parameterized queries. All dynamic SQL inside procs uses parameterized queries (sp_executesql) for data values.
• Identifier escaping. Apply QUOTENAME() to every dynamically generated object name (tables, columns) to block structural injection.
• Disallowed characters. Review inputs for ;, ', --, /*, */, xp_, unless explicitly needed and carefully handled.
• Contextual escaping. Escape data based on where it's going: SQL, LDAP, OS, or third-party commands.

Stored Procedure Logic

• Least privilege. Scrutinize what the AuthenticatedUser role can execute. It should be the absolute minimum.
• Logic flaws. Hunt for flaws that produce unintended results even with valid inputs.
• Error handling. Procs fail gracefully and never leak sensitive info in error messages. Scrub your error output.

Impersonation and Permissions

• IMPERSONATE scope. Restrict __web_user's impersonation rights to the necessary app users only. Never sa or other high-privileged accounts.
• Role assignments. Audit AuthenticatedUser membership regularly.

Auditing and Logging

• Comprehensive logging. Capture both the __web_user login and the impersonated AuthenticatedUser for every relevant action, especially writes and sensitive reads.
• Alerting. Configure alerts for suspicious activity, failed impersonation attempts, and unusual access patterns.
• Secure development practices. Keep data separate from commands and from client-side scripts. Make secure code review a regular habit, not a pre-release scramble.

Conclusion

This isn't just "good security hygiene." Database-level access control gives you real, measurable wins, especially on mature platforms like Postgres, SQL Server, IBM DB2, or SAP HANA. Defer authentication and authorization to the engine and you get two things at once: less risk and less application complexity.

You don't reinvent RBAC in your app layer. You don't rent it. You don't manually filter table access on every query and pray you didn't miss one. You lean on the database: views scoped to the current user, privileges granted at the right level, and the engine enforcing the boundaries for you.

Simpler, safer, and it scales better than rolling your own, or worse, outsourcing it to a third-party auth provider that might get breached anyway.

Your database has spent decades getting good at this. Use it.