# Cassandra User DDL on TiDB

This proxy supports a first TiDB-native subset of Cassandra user DDL. The goal is to let CQL clients create, update, drop, and list login accounts that are authenticated by TiDB.

For manual verification steps, see [User DDL Manual Verification Guide](user-ddl-testing.md).

## Execution model

- CQL user names are mapped to TiDB accounts using host `%`.
- User management statements run through the currently authenticated TiDB account.
- TiDB performs the privilege checks. The current account must have the privileges required by TiDB for `CREATE USER`, `ALTER USER`, `DROP USER`, `GRANT`, and reading `mysql.user` for `LIST USERS`.
- Password verification remains TiDB-native: after a user is created, future CQL authentication succeeds only if TiDB accepts the username and password.
- `ALTER USER` and `DROP USER` invalidate proxy connection pools for the affected username.
- Keyspace permissions are mapped to TiDB database-level privileges because a CQL keyspace maps to a TiDB database.

## Supported statements

### CREATE USER

```sql
CREATE USER [IF NOT EXISTS] <user> WITH PASSWORD '<password>' [NOSUPERUSER];
```

Maps to:

```sql
CREATE USER [IF NOT EXISTS] '<user>'@'%' IDENTIFIED BY '<password>';
```

`WITH PASSWORD` is required in this TiDB-native mode. `NOSUPERUSER` is accepted for Cassandra compatibility and has no TiDB effect.

`SUPERUSER` is rejected because Cassandra superuser semantics do not map cleanly to a single TiDB privilege.

### ALTER USER

```sql
ALTER USER [IF EXISTS] <user> WITH PASSWORD '<password>';
```

Maps to:

```sql
ALTER USER '<user>'@'%' IDENTIFIED BY '<password>';
```

`IF EXISTS` is handled by the proxy. If no matching TiDB user exists, the statement is a no-op.

`SUPERUSER` and `NOSUPERUSER` are rejected for `ALTER USER`.

### DROP USER

```sql
DROP USER [IF EXISTS] <user>;
```

Maps to:

```sql
DROP USER [IF EXISTS] '<user>'@'%';
```

### LIST USERS

```sql
LIST USERS;
```

Returns rows with:

| Column | Type | Notes |
| --- | --- | --- |
| `name` | `varchar` | TiDB user name from `mysql.user` |
| `super` | `boolean` | Always `false` in the first implementation |

### GRANT ON KEYSPACE

```sql
GRANT <permission> ON KEYSPACE <keyspace> TO <user>;
GRANT ALL PERMISSIONS ON KEYSPACE <keyspace> TO <user>;
```

Supported Cassandra permissions and TiDB mappings:

| CQL permission | TiDB privileges |
| --- | --- |
| `SELECT` | `SELECT` |
| `MODIFY` | `INSERT`, `UPDATE`, `DELETE` |
| `CREATE` | `CREATE` |
| `ALTER` | `ALTER`, `INDEX` |
| `DROP` | `DROP` |
| `ALL [PERMISSIONS]` | `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `CREATE`, `ALTER`, `DROP`, `INDEX` |

Example:

```sql
GRANT SELECT ON KEYSPACE chat_test TO verify_user_001;
GRANT MODIFY ON KEYSPACE chat_test TO verify_user_001;
```

Maps to TiDB database grants such as:

```sql
GRANT SELECT ON `chat_test`.* TO 'verify_user_001'@'%';
GRANT DELETE, INSERT, UPDATE ON `chat_test`.* TO 'verify_user_001'@'%';
```

The target TiDB account must already exist at host `%`, and the target keyspace must already exist. `AUTHORIZE` is rejected because mapping it to TiDB `GRANT OPTION` would grant permission-management power that is broader than this first implementation intends to expose.

## Unsupported Cassandra semantics

The following Cassandra auth features are not implemented in this first version:

- `CREATE USER ... SUPERUSER`
- `ALTER USER ... SUPERUSER`
- `ALTER USER ... NOSUPERUSER`
- Cassandra role inheritance
- Cassandra role permission resources such as `ALL ROLES`
- `GRANT` resources other than `ON KEYSPACE`
- `GRANT AUTHORIZE`
- `REVOKE`, `LIST ROLES`, and `LIST PERMISSIONS`
- Populating `system_auth.roles` with full role metadata