Database users, roles & privileges

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: Creating users Dialect | SQL | --- | --- | PostgreSQL | CREATE USER IF NOT EXISTS "reporter" WITH PASSWORD 's3cret' LOGIN | MySQL / MariaDB | CREATE USER IF NOT EXISTS 'reporter'@'%' IDENTIFIED BY 's3cret' | SQL Server | IF NOT EXISTS (…) CREATE LOGIN [reporter] WITH PASSWORD = 's3cret'; CREATE USER [reporter] FOR LOGIN [reporter] | Oracle | a BEGIN … EXECUTE IMMEDIATE 'CREATE USER …' … EXCEPTION block that swallows ORA-955/ORA-1920 (already exists) | SQLite | throws User management is not supported by SQLite | Other options: passwordHash, authPlugin, defaultRole, comment, expirePassword, accountLocked, and the MySQL resource limits maxQueriesPerHour / maxUpdatesPerHour / maxConnectionsPerHour. getUsers() reads each engine's catalogue — pguser + pgauthid, mysql.user, sys.databaseprincipals, dbausers — and normalises the rows to { user, host, plugin, passwordExpired, accountLocked, createTime }. Granting privileges A grant is privileges × scope × grantee: Dialect | SQL | --- | --- | PostgreSQL | GRANT SELECT ON TABLE "users" TO reporter | MySQL | GRANT SELECT ON app.users TO 'reporter'@'%' | SQL Server | 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. | Privilege groups PRIVILEGELEVELS 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 | withGrantOption: true lets the grantee pass the privilege on; asUser maps to PostgreSQL's GRANTED BY. Revoking MySQL requires flushPrivileges() (FLUSH PRIVILEGES) after some grant-table edits; it is a no-op elsewhere. Roles Dialect | Create | Grant | --- | --- | --- | PostgreSQL | CREATE ROLE "analyst" LOGIN | 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] | RoleOptions also carries the PostgreSQL attributes createRole, superuser, replication, bypassRLS, password and comment. Inspecting grants Dialect | Source | --- | --- | PostgreSQL | informationschema.roletablegrants ∪ rolecolumngrants | MySQL | SHOW GRANTS FOR 'reporter'@'%' | SQL Server | sys.databasepermissions joined to sys.databaseprincipals | Oracle | usertabprivs ∪ usercolprivs | Practical notes Passwords appear in the SQL. These statements carry credentials in plain text; they will land in query logs and in the server's own statement log. Keep logging off for this work, and prefer passwordHash where the engine supports it. 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. Related reading Schema objects — row-level security policies Compliance — application-level access control Dialects — per-engine notes