Security and access control_

MySQL controls access in two layers:

• An account is an identity that can connect.
• Privileges decide what an account may do to each schema, table, or column.

This page covers how that model works, what your Appwrite database's admin account can and cannot do, and the patterns that restrict access to data in practice.

The privilege model

A MySQL account is a user name plus a host pattern, such as 'reporting'@'%'. Privileges attach to accounts at four scopes: global, schema, table, and column. GRANT gives them and REVOKE takes them away:

Inspect any account's effective privileges with SHOW GRANTS.

Your database's admin account

Your Appwrite database provides one account, admin, with full privileges scoped to your schema:

SHOW GRANTS FOR CURRENT_USER();

GRANT USAGE ON *.* TO `admin`@`%`
GRANT ALL PRIVILEGES ON `your-database`.* TO `admin`@`%`

admin owns your data completely: all reads, writes, schema changes, and index management. It does not hold global privileges, so instance-level operations stay with the platform, and it cannot create additional MySQL accounts:

CREATE USER 'reporting'@'%' IDENTIFIED BY 'a-strong-password';

ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation

This means access control below the admin account happens in your application and schema design rather than in MySQL's account system. The sections below cover the tools that work within that model.

Restrict what queries can see with views

A view exposes a controlled subset of a table: fewer columns, fewer rows, or both. Give your application's read paths a view instead of the raw table, and sensitive columns never leave the database:

CREATE TABLE users (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    email         VARCHAR(255) NOT NULL,
    display_name  VARCHAR(100) NOT NULL,
    password_hash VARBINARY(255) NOT NULL,
    is_deleted    BOOLEAN NOT NULL DEFAULT FALSE
);

INSERT INTO users (email, display_name, password_hash) VALUES
    ('ada@example.com', 'Ada', 0x01), ('grace@example.com', 'Grace', 0x02);
UPDATE users SET is_deleted = TRUE WHERE display_name = 'Grace';

CREATE VIEW visible_users AS
SELECT id, display_name
FROM users
WHERE is_deleted = FALSE;

SELECT * FROM visible_users;

id	display_name
1	Ada

The view hides email and password_hash entirely and filters out soft-deleted rows. Queries against visible_users cannot reach what the view doesn't select.

Enforce integrity in the schema

Constraints are a security layer too: they hold no matter which code path performs the write. CHECK constraints, NOT NULL, foreign keys, and ENUM types stop invalid states at the database boundary; see Tables and data types. For multi-step changes that must not interleave with other writers, use transactions and row locks; see Transactions.

Scope tenants in the application

MySQL has no row-level security policies, so multi-tenant isolation lives in your queries. Keep it reliable by centralizing it:

• Put a tenant_id column on every tenant-owned table, indexed and NOT NULL, with a foreign key to the tenants table.
• Route all data access through one layer (a repository module or ORM scope) that always applies WHERE tenant_id = ?, rather than trusting every call site to remember.
• For read paths, per-tenant views or parameterized views over tenant_id make the scoping visible and testable.

Use Appwrite Auth as the tenant identity

Native MySQL has no built-in link to Appwrite's permission system. Permissions apply to Appwrite databases, not to the raw engine. You can still scope tenants to Appwrite users: your backend verifies the caller's Appwrite session as a JWT and uses the verified user ID as the tenant_id in every query:

import { Client, Account } from 'node-appwrite';
import mysql from 'mysql2/promise';

const pool = mysql.createPool(process.env.DATABASE_URL);

export async function listDocuments(jwt) {
    // Verify the Appwrite session. Throws if the JWT is invalid or expired.
    const client = new Client()
        .setEndpoint('https://<REGION>.cloud.appwrite.io/v1')
        .setProject('<PROJECT_ID>')
        .setJWT(jwt);
    const user = await new Account(client).get();

    // The verified user ID scopes the query. Never accept a tenant ID from the request body.
    const [rows] = await pool.execute(
        'SELECT * FROM documents WHERE tenant_id = ?',
        [user.$id]
    );
    return rows;
}

The user ID comes from the verified session, never from request input, so a client cannot query another tenant by sending a different ID.

Protect the connection itself

Since the admin credentials are the whole story, treat them accordingly:

• Rotate the password when a person or system that held it should lose access. See Connections.
• Restrict which networks can reach the database with an IP allowlist. See Network security.
• All connections require TLS; never disable certificate verification in production drivers.
• Use parameterized queries everywhere. SQL injection against an account with ALL PRIVILEGES on the schema is a full compromise of your data.