# User DDL Manual Verification Guide

This guide verifies the currently supported Cassandra-style user DDL against the proxy:

- `CREATE USER`
- `ALTER USER`
- `DROP USER`
- `LIST USERS`
- `GRANT ... ON KEYSPACE ... TO ...`

The implementation maps CQL users to TiDB accounts at host `%`, so the verification also checks TiDB-side authentication and keyspace privileges.

## Prerequisites

Start the proxy and prepare an admin CQL account that can manage TiDB users. The admin account must have the TiDB privileges needed for the statements being tested:

- `CREATE USER`, `ALTER USER`, and `DROP USER` for user lifecycle tests.
- Read access to `mysql.user` for `LIST USERS`.
- `GRANT OPTION` for keyspace grant tests.
- Enough privileges to create and drop the test keyspace/table.

Set these values for the examples below:

```bash
export CQL_HOST=127.0.0.1
export CQL_PORT=9042
export ADMIN_USER=abc.root
export ADMIN_PASS='xxxxx'
export TEST_USER=verify_user_001
export TEST_PASS_1='VerifyPass_001'
export TEST_PASS_2='VerifyPass_002'
export TEST_KEYSPACE=verify_user_ddl_ks
```

The commands below use `cqlsh`. If your proxy listens on a non-default port, pass the host and port explicitly:

```bash
cqlsh "$CQL_HOST" "$CQL_PORT" -u "$ADMIN_USER" -p "$ADMIN_PASS"
```

## Cleanup First

Run cleanup before and after verification so repeated runs are deterministic.

```sql
DROP KEYSPACE IF EXISTS verify_user_ddl_ks;
DROP USER IF EXISTS verify_user_001;
```

Expected result: both statements succeed. If the objects do not exist, `IF EXISTS` makes the statements no-ops.

## 1. CREATE USER

Connect as the admin user:

```bash
cqlsh "$CQL_HOST" "$CQL_PORT" -u "$ADMIN_USER" -p "$ADMIN_PASS"
```

Create the test user:

```sql
CREATE USER IF NOT EXISTS verify_user_001 WITH PASSWORD 'VerifyPass_001' NOSUPERUSER;
```

Expected result: the statement succeeds without rows.

Open a new shell and verify the new user can authenticate:

```bash
cqlsh "$CQL_HOST" "$CQL_PORT" -u verify_user_001 -p VerifyPass_001
```

Expected result: `cqlsh` connects successfully.

Negative check:

```bash
cqlsh "$CQL_HOST" "$CQL_PORT" -u verify_user_001 -p WrongPassword
```

Expected result: authentication fails.

## 2. LIST USERS

Run as the admin user:

```sql
LIST USERS;
```

Expected result: the output includes `verify_user_001`, and the `super` column is `False`.

Example shape:

```text
 name            | super
-----------------+-------
 verify_user_001 | False
```

`super` is always `False` in the current TiDB-native implementation.

## 3. ALTER USER Password

Run as the admin user:

```sql
ALTER USER IF EXISTS verify_user_001 WITH PASSWORD 'VerifyPass_002';
```

Expected result: the statement succeeds.

Verify the old password no longer works:

```bash
cqlsh "$CQL_HOST" "$CQL_PORT" -u verify_user_001 -p VerifyPass_001
```

Expected result: authentication fails.

Verify the new password works:

```bash
cqlsh "$CQL_HOST" "$CQL_PORT" -u verify_user_001 -p VerifyPass_002
```

Expected result: `cqlsh` connects successfully.

## 4. GRANT ON KEYSPACE

Run setup as the admin user:

```sql
CREATE KEYSPACE IF NOT EXISTS verify_user_ddl_ks
WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 1};

CREATE TABLE IF NOT EXISTS verify_user_ddl_ks.grant_items (
    id int PRIMARY KEY,
    name text
);

INSERT INTO verify_user_ddl_ks.grant_items (id, name) VALUES (1, 'seed');
```

Grant read access:

```sql
GRANT SELECT ON KEYSPACE verify_user_ddl_ks TO verify_user_001;
```

Expected result: the statement succeeds. If it fails with a TiDB `Grant Option` error, the admin account does not have enough TiDB privilege to grant access.

Connect as the test user with the updated password:

```bash
cqlsh "$CQL_HOST" "$CQL_PORT" -u verify_user_001 -p VerifyPass_002
```

Verify `USE` and `SELECT`:

```sql
USE verify_user_ddl_ks;
SELECT name FROM grant_items WHERE id = 1;
```

Expected result: the query returns `seed`.

Verify write access is still blocked before `MODIFY`:

```sql
INSERT INTO grant_items (id, name) VALUES (2, 'blocked');
```

Expected result: TiDB rejects the statement because the user only has `SELECT`.

Grant write access as the admin user:

```sql
GRANT MODIFY ON KEYSPACE verify_user_ddl_ks TO verify_user_001;
```

Reconnect as the test user to force a fresh TiDB connection, then verify writes:

```bash
cqlsh "$CQL_HOST" "$CQL_PORT" -u verify_user_001 -p VerifyPass_002 -k verify_user_ddl_ks
```

```sql
INSERT INTO grant_items (id, name) VALUES (2, 'granted');
SELECT name FROM grant_items WHERE id = 2;
```

Expected result: the insert succeeds and the select returns `granted`.

Optional full permission check:

```sql
GRANT ALL PERMISSIONS ON KEYSPACE verify_user_ddl_ks TO verify_user_001;
```

Expected result: maps to TiDB database-level `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `CREATE`, `ALTER`, `DROP`, and `INDEX`.

## 5. Unsupported GRANT Cases

Verify unsupported Cassandra auth semantics are rejected:

```sql
GRANT AUTHORIZE ON KEYSPACE verify_user_ddl_ks TO verify_user_001;
```

Expected result: invalid request. `AUTHORIZE` is intentionally not mapped to TiDB `GRANT OPTION`.

```sql
GRANT SELECT ON TABLE verify_user_ddl_ks.grant_items TO verify_user_001;
```

Expected result: invalid request. The current implementation only supports `ON KEYSPACE`.

## 6. DROP USER

Run as the admin user:

```sql
DROP USER IF EXISTS verify_user_001;
```

Expected result: the statement succeeds.

Verify new authentication fails:

```bash
cqlsh "$CQL_HOST" "$CQL_PORT" -u verify_user_001 -p VerifyPass_002
```

Expected result: authentication fails.

Existing CQL sessions that were already connected before `DROP USER` are not forcibly disconnected by this first implementation. The check above validates new authentication attempts.

## Final Cleanup

Run as the admin user:

```sql
DROP KEYSPACE IF EXISTS verify_user_ddl_ks;
DROP USER IF EXISTS verify_user_001;
```

Expected result: both statements succeed.

## Quick Verification Script

This sequence can be pasted into an admin `cqlsh` session for the positive-path setup:

```sql
DROP KEYSPACE IF EXISTS verify_user_ddl_ks;
DROP USER IF EXISTS verify_user_001;

CREATE USER IF NOT EXISTS verify_user_001 WITH PASSWORD 'VerifyPass_001' NOSUPERUSER;
LIST USERS;
ALTER USER IF EXISTS verify_user_001 WITH PASSWORD 'VerifyPass_002';

CREATE KEYSPACE IF NOT EXISTS verify_user_ddl_ks
WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 1};

CREATE TABLE IF NOT EXISTS verify_user_ddl_ks.grant_items (
    id int PRIMARY KEY,
    name text
);

INSERT INTO verify_user_ddl_ks.grant_items (id, name) VALUES (1, 'seed');
GRANT SELECT ON KEYSPACE verify_user_ddl_ks TO verify_user_001;
GRANT MODIFY ON KEYSPACE verify_user_ddl_ks TO verify_user_001;
```