# User DDL Usage

This document describes the currently supported Cassandra-style user DDL behavior from a user-facing CQL perspective.

## Scope

The current implementation supports a practical subset of Cassandra user and permission management:

- Create login users.
- Change user passwords.
- Drop users.
- List users.
- Grant keyspace-level permissions to users.

The current implementation does not provide full Cassandra role management. Unsupported Cassandra auth features are listed in [Unsupported Features](#unsupported-features).

## General Behavior

- User DDL statements must be executed by an authenticated account with sufficient administrative privileges.
- Passwords must be string literals.
- A user created through CQL can authenticate through the CQL protocol after creation.
- Password changes affect new authentication attempts. Existing sessions may remain connected until they are closed.
- Dropping a user prevents new authentication attempts for that user. Existing sessions that were already connected before the drop are not forcibly disconnected in the current implementation.
- Permission changes may require reconnecting a client session before the new privileges are observed consistently.

## Supported Statements

### CREATE USER

Syntax:

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

Examples:

```sql
CREATE USER app_user WITH PASSWORD 'AppPass_001' NOSUPERUSER;
CREATE USER IF NOT EXISTS app_user WITH PASSWORD 'AppPass_001' NOSUPERUSER;
```

Behavior:

- Creates a login user.
- `WITH PASSWORD` is required.
- `IF NOT EXISTS` makes the statement succeed when the user already exists.
- `NOSUPERUSER` is accepted for compatibility.
- `SUPERUSER` is not supported.

Expected failures:

- Missing `WITH PASSWORD`.
- Unsupported `SUPERUSER`.
- Invalid user name.
- Insufficient administrative privilege.

### ALTER USER

Syntax:

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

Examples:

```sql
ALTER USER app_user WITH PASSWORD 'AppPass_002';
ALTER USER IF EXISTS app_user WITH PASSWORD 'AppPass_002';
```

Behavior:

- Changes the password for an existing user.
- `IF EXISTS` makes the statement a no-op when the user does not exist.
- New authentication attempts must use the updated password.
- Existing sessions may remain connected until closed.

Expected failures:

- Missing `WITH PASSWORD`.
- `SUPERUSER` or `NOSUPERUSER` in `ALTER USER`.
- Invalid user name.
- User does not exist when `IF EXISTS` is not used.
- Insufficient administrative privilege.

### DROP USER

Syntax:

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

Examples:

```sql
DROP USER app_user;
DROP USER IF EXISTS app_user;
```

Behavior:

- Drops a login user.
- `IF EXISTS` makes the statement succeed when the user does not exist.
- New authentication attempts for the dropped user fail.
- Existing sessions are not forcibly disconnected in the current implementation.

Expected failures:

- Invalid user name.
- User does not exist when `IF EXISTS` is not used.
- Insufficient administrative privilege.

### LIST USERS

Syntax:

```sql
LIST USERS;
```

Behavior:

- Returns known users.
- The result includes a `name` column.
- The result includes a `super` column for Cassandra compatibility.
- `super` is always reported as `False` in the current implementation.

Example output shape:

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

Expected failures:

- Insufficient privilege to list users.

### GRANT ON KEYSPACE

Syntax:

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

Examples:

```sql
GRANT SELECT ON KEYSPACE chat TO app_user;
GRANT MODIFY ON KEYSPACE chat TO app_user;
GRANT ALL PERMISSIONS ON KEYSPACE chat TO app_user;
```

Behavior:

- Grants keyspace-level permissions to a user.
- The target user must already exist.
- The target keyspace must already exist.
- The account executing the statement must have permission to grant access.
- A client may need to reconnect before newly granted permissions are visible.

Supported permissions:

| Permission | Meaning |
| --- | --- |
| `SELECT` | Allows reading data in the keyspace. |
| `MODIFY` | Allows writing, updating, and deleting data in the keyspace. |
| `CREATE` | Allows creating objects in the keyspace. |
| `ALTER` | Allows altering objects in the keyspace. |
| `DROP` | Allows dropping objects in the keyspace. |
| `ALL PERMISSIONS` | Grants all currently supported keyspace permissions. |

`ALL` is accepted with or without `PERMISSION` or `PERMISSIONS` where the parser accepts that form.

Expected failures:

- Target user does not exist.
- Target keyspace does not exist.
- Unsupported permission.
- Unsupported resource type.
- Insufficient grant privilege.

## Unsupported Features

The following Cassandra auth features are not supported in the current implementation:

- `CREATE USER ... SUPERUSER`
- `ALTER USER ... SUPERUSER`
- `ALTER USER ... NOSUPERUSER`
- `CREATE ROLE`
- `ALTER ROLE`
- `DROP ROLE`
- `LIST ROLES`
- Role inheritance.
- Login/no-login role separation.
- Role options beyond password management.
- `REVOKE`
- `LIST PERMISSIONS`
- Table-level grants, such as `GRANT SELECT ON TABLE ...`
- Function-level grants.
- Role-level grants, such as `GRANT role_a TO role_b`
- `GRANT AUTHORIZE`
- Permission resources other than `ON KEYSPACE`
- Full population of system auth metadata beyond the compatibility fields currently exposed.

## Recommended Usage Pattern

Use an administrative account for user lifecycle and permission management:

```sql
CREATE USER IF NOT EXISTS app_user WITH PASSWORD 'AppPass_001' NOSUPERUSER;
GRANT SELECT ON KEYSPACE chat TO app_user;
GRANT MODIFY ON KEYSPACE chat TO app_user;
```

Use the application account for normal reads and writes:

```sql
USE chat;
SELECT * FROM messages WHERE id = 1;
INSERT INTO messages (id, content) VALUES (2, 'hello');
```

For password rotation:

```sql
ALTER USER IF EXISTS app_user WITH PASSWORD 'AppPass_002';
```

Then reconnect clients with the new password.

For cleanup:

```sql
DROP USER IF EXISTS app_user;
```

## Compatibility Notes

- `NOSUPERUSER` is accepted only for create-user compatibility.
- The `super` column in `LIST USERS` is present for client compatibility and always returns `False`.