Database users, roles & privileges

UserManager creates database users and roles, and grants and revokes their
privileges. One API, five very different implementations underneath: MySQL's
'user'@'host' pairs, PostgreSQL's roles, SQL Server's split between a login
and a user, and Oracle's PL/SQL wrapper blocks are all handled by the dialect.
SQLite has no user system and says so.

The methods are available both on the connection and through the manager:

import { UserManager, PRIVILEGE_LEVELS } from 'ts-prorm-orm';

const users = new UserManager(prorm);
// …or straight off the connection:
await prorm.createUser('reporter', { password: process.env.REPORTER_PW! });

Creating users

await users.createUser('reporter', {
  password: process.env.REPORTER_PW!,
  host: '%',                 // MySQL: the 'user'@'host' pair
  requireSSL: true,
  maxConnections: 10,
});

The SQL each dialect sends (with the password shown as s3cret):

Throws User management is not supported by SQLite.

[!NOTE]
PostgreSQL has no per-user TLS switch. requireSSL becomes a comment in
the SQL; require TLS with hostssl lines in pg_hba.conf.

ifNotExists: true differs the most between engines:

Dialect	With `ifNotExists: true`
PostgreSQL	Throws: `PostgreSQL has no CREATE USER IF NOT EXISTS`. Check with `users.userExists(name)` first.
MySQL / MariaDB	`CREATE USER IF NOT EXISTS 'reporter'@'%' IDENTIFIED BY 's3cret'`
SQL Server	`IF NOT EXISTS (SELECT name FROM sys.server_principals …) CREATE LOGIN …; IF NOT EXISTS (SELECT name FROM sys.database_principals …) CREATE USER …`
Oracle	A `BEGIN … EXECUTE IMMEDIATE 'CREATE USER …' … EXCEPTION` block that ignores ORA-955 and ORA-1920 (already exists)

Other options: passwordHash, authPlugin, defaultRole, comment,
expirePassword, accountLocked, and the MySQL resource limits
maxQueriesPerHour / maxUpdatesPerHour / maxConnectionsPerHour.

await users.alterUser('reporter', { password: newPw, accountLocked: false });
await users.dropUser('reporter', { ifExists: true, host: '%' });
await users.userExists('reporter');
await users.getUsers();   // UserInfo[]

getUsers() reads each engine's catalogue — pg_user + pg_authid,
mysql.user, sys.database_principals, dba_users — and normalises the rows
to { user, host, plugin, passwordExpired, accountLocked, createTime }.

Granting privileges

A grant is privileges × scope × grantee:

await users.grant({
  privileges: ['SELECT'],
  on: { level: 'table', database: 'app', table: 'users' },
  to: 'reporter',
});

Dialect	SQL
PostgreSQL	`GRANT SELECT ON TABLE "users" TO "reporter"`
MySQL	GRANT SELECT ON `app`.`users` TO 'reporter'@'%'
SQL Server	`GRANT SELECT ON OBJECT::[app].[users] TO [reporter]`
Oracle	`GRANT SELECT ON "app"."users" TO "reporter"`
SQLite	throws `GRANT is not supported by SQLite`

Scopes

`on`	Meaning
`{ level: 'global' }`	Everything (`.` on MySQL).
`{ level: 'database', database }`	One database / schema.
`{ level: 'table', database?, table }`	One table.
`{ level: 'column', database?, table, columns }`	Named columns only.
`{ level: 'routine', database?, routine, routineType }`	A procedure or function.
`{ level: 'schema', schema }`	A schema (`ON SCHEMA s`).
`{ level: 'sequence', schema, sequence? }`	One sequence, or without `sequence`, every sequence in the schema (PostgreSQL).
`{ level: 'allTablesInSchema', schema }`	`ON ALL TABLES IN SCHEMA s` (PostgreSQL).
`{ level: 'function' \| 'procedure', function \| procedure, schema?, args? }`	One routine; `args` are its argument types, which PostgreSQL needs to pick an overload.

UserManager refuses function, procedure and allTablesInSchema on a
dialect that does not declare support for them, rather than let an older
builder fall through to a broader grant. PostgreSQL supports them. Each
engine also refuses what it cannot express: MySQL has no allTablesInSchema
or sequence grant, and Oracle has no schema-wide object grant and cannot
limit SELECT to columns.

Privilege groups

PRIVILEGE_LEVELS bundles the combinations you actually reach for:

Constant	Privileges
`ALL`	`ALL PRIVILEGES`
`READ`	`SELECT`
`WRITE`	`INSERT`, `UPDATE`, `DELETE`
`DML`	`SELECT`, `INSERT`, `UPDATE`, `DELETE`
`DDL`	`CREATE`, `DROP`, `ALTER`, `INDEX`
`ROUTINE`	`EXECUTE`, `CREATE ROUTINE`, `ALTER ROUTINE`
`VIEW`	`CREATE VIEW`, `SHOW VIEW`

await users.grant({
  privileges: PRIVILEGE_LEVELS.READ,
  on: { level: 'database', database: 'app' },
  to: ['reporter', 'dashboard'],
});

withGrantOption: true lets the grantee pass the privilege on; asUser maps to
PostgreSQL's GRANTED BY.

Revoking

await users.revoke({
  privileges: ['SELECT'],
  on: { level: 'table', database: 'app', table: 'users' },
  from: 'reporter',
  cascade: true,          // PostgreSQL
  grantOptionFor: false,  // REVOKE GRANT OPTION FOR …
});

MySQL requires flushPrivileges() (FLUSH PRIVILEGES) after some grant-table
edits. Elsewhere it sends a harmless SELECT 1 with a comment, and on SQLite
it throws.

Roles

await users.createRole('analyst', { login: true, connectionLimit: 5, inherit: true });
await users.grantRole('analyst', 'reporter');
await users.revokeRole('analyst', 'reporter');
await users.dropRole('analyst', { ifExists: true });
await users.getRoles();

Dialect	Create	Grant
PostgreSQL	`CREATE ROLE "analyst" INHERIT LOGIN CONNECTION LIMIT 5`	`GRANT "analyst" TO "reporter"`
MySQL 8+ / MariaDB	`CREATE ROLE 'analyst'`	`GRANT 'analyst' TO 'reporter'@'%'`
SQL Server	`CREATE ROLE [analyst]`	`ALTER ROLE [analyst] ADD MEMBER [reporter]`
Oracle	`CREATE ROLE "analyst"`	`GRANT "analyst" TO "reporter"`

Only PostgreSQL uses the role options; the other engines create a plain role.

RoleOptions also carries the PostgreSQL attributes createRole, superuser,
replication, bypassRLS, password and comment.

Example: least privilege for one application

A PostgreSQL database app with its tables in the schema app. Three
logins, and nobody but the migrator can change the schema:

import { Prorm, UserManager, PRIVILEGE_LEVELS } from 'ts-prorm-orm';

const admin = new Prorm({ dialect: 'postgres', url: process.env.ADMIN_DATABASE_URL });
const users = new UserManager(admin);

// Two group roles nobody signs in as.
await users.createRole('app_read', { login: false });
await users.createRole('app_write', { login: false });
await users.grantRole('app_read', 'app_write');          // writers can read too

await users.grant({ privileges: ['CONNECT'], on: { level: 'database', database: 'app' }, to: 'app_read' });
await users.grant({ privileges: ['USAGE'], on: { level: 'schema', schema: 'app' }, to: 'app_read' });
await users.grant({ privileges: PRIVILEGE_LEVELS.READ, on: { level: 'allTablesInSchema', schema: 'app' }, to: 'app_read' });
await users.grant({ privileges: PRIVILEGE_LEVELS.WRITE, on: { level: 'allTablesInSchema', schema: 'app' }, to: 'app_write' });
await users.grant({ privileges: ['USAGE', 'SELECT'], on: { level: 'sequence', schema: 'app' }, to: 'app_write' });

// The migrator owns the schema's tables, so tables it creates later need default privileges.
if (!(await users.userExists('migrator'))) {
  await users.createUser('migrator', { password: process.env.MIGRATOR_PW! });
}
await users.grant({ privileges: ['CREATE', 'USAGE'], on: { level: 'schema', schema: 'app' }, to: 'migrator' });
await users.alterDefaultPrivileges({ forRole: 'migrator', inSchema: 'app', grant: ['SELECT'], on: 'tables', to: 'app_read' });
await users.alterDefaultPrivileges({ forRole: 'migrator', inSchema: 'app', grant: ['INSERT', 'UPDATE', 'DELETE'], on: 'tables', to: 'app_write' });
await users.alterDefaultPrivileges({ forRole: 'migrator', inSchema: 'app', grant: ['USAGE', 'SELECT'], on: 'sequences', to: 'app_write' });

// The logins the services use.
await users.createUser('api', { password: process.env.API_DB_PW!, maxConnections: 20 });
await users.createUser('reporter', { password: process.env.REPORTER_PW!, maxConnections: 5 });
await users.grantRole('app_write', 'api');
await users.grantRole('app_read', 'reporter');

The SQL it sends

ALL TABLES IN SCHEMA covers only the tables that exist now. The
alterDefaultPrivileges calls cover the ones migrator creates later.
alterDefaultPrivileges runs on PostgreSQL only; other dialects validate the
options and then throw

ALTER DEFAULT PRIVILEGES is not supported by the …
dialect yet

On MySQL, the same shape uses database scopes, because MySQL has no
allTablesInSchema or sequence grant:

await users.createRole('app_read');
await users.createRole('app_write');
await users.grant({ privileges: PRIVILEGE_LEVELS.READ, on: { level: 'database', database: 'app' }, to: 'app_read' });
await users.grant({ privileges: PRIVILEGE_LEVELS.WRITE, on: { level: 'database', database: 'app' }, to: 'app_write' });
await users.createUser('api', { password: process.env.API_DB_PW!, host: '10.0.%', requireSSL: true, maxConnections: 20 });
await users.grantRole('app_write', 'api@10.0.%');   // GRANT 'app_write' TO 'api'@'10.0.%'

[!WARNING]
grantRole has no host option. A bare name means 'user'@'%', which is a
different account from 'api'@'10.0.%': name the host in the grantee
('api@10.0.%'), or the grant names an account that does not exist.

Inspecting grants

const grants = await users.showGrants('reporter');
// PrivilegeInfo[]: { grantee, privilegeType, tableSchema, tableName, columnName, isGrantable }

Dialect	Source
PostgreSQL	`information_schema.role_table_grants` ∪ `role_column_grants`
MySQL	`SHOW GRANTS FOR 'reporter'@'%'`
SQL Server	`sys.database_permissions` joined to `sys.database_principals`
Oracle	`user_tab_privs` ∪ `user_col_privs`

Practical notes

Passwords appear in the SQL. See the warning below.

These are database accounts, not application accounts. They govern who may
connect and what SQL they may run. Application users, sessions and roles are a
different problem — see Compliance for row-level security
and session isolation.

Least privilege pays off here. A connection that only reads should be a
user with only SELECT; that is a real defence against an application bug,
and it costs two calls to set up.

[!CAUTION]
createUser and alterUser put the password in the statement text. It
lands in the ORM's query log and in the server's own statement log. Keep
logging off for this work, and prefer passwordHash where the engine
supports it.

Schema objects — row-level security policies

Compliance — application-level access control

Dialects — per-engine notes

Database users, roles & privileges

Creating users#

Granting privileges#

Scopes#

Privilege groups#

Revoking#

Roles#

Example: least privilege for one application#

Inspecting grants#

Practical notes#

Related reading#