10. Accounts and Privileges

MIRAJ controls who can connect and what each connection is allowed to do through a system of accounts, privileges and roles similar to that of the most widespread SQL servers. This chapter covers authentication, account management, privileges at their three levels, roles, and the information_schema tables useful for auditing rights.

10.1 Authentication#

At connection time, an account's password never travels in clear text over the network and is never stored in clear text on the server, nor even in a form that would allow it to be recovered:

• The server sends a random challenge on each connection.
• The client combines this challenge with the password entered by the user and sends back only the result of that computation — never the password itself.
• For each account, the server keeps only a verifier derived from the password (a two-level hash): it makes it possible to check a connection attempt, but does not allow the original password to be reconstructed.

In practice, this means:

• a capture of the network traffic does not reveal account passwords;
• a copy of the data directory (or of the account vault, see chapter 11) does not reveal the passwords in clear text either;
• there is therefore no way to "recover" the password of a forgotten account: only ALTER USER ... IDENTIFIED BY or SET PASSWORD can set a new one.

The exact cryptographic details (algorithms, challenge format) fall under the product's internal security and are not needed to use MIRAJ; they are documented in readme.txt for anyone who needs them.

10.2 Accounts: CREATE / ALTER / DROP USER#

Account identity: user + host#

A MIRAJ account is not identified by its user name alone, but by the pair 'user'@'host'. The same user name can thus designate different accounts, with different passwords and privileges, depending on the machine from which the connection is made:

• 'app'@'localhost' — the app account connecting only from the server machine;
• 'app'@'192.168.1.50' — the app account connecting from a specific address;
• 'app'@'192.168.1.%' — % pattern: the whole 192.168.1.* subnet;
• 'app'@'%' — any machine (the default host if @host is omitted).

The patterns % (any sequence of characters) and _ (any single character) are allowed in the host, as in a LIKE clause. When several accounts with the same name match the current connection, MIRAJ picks the most specific one: an exact host or localhost takes precedence over a pattern, and % is chosen as a last resort. localhost designates specifically the loopback interface (and not "any local address").

Creating, modifying, dropping an account#

CREATE USER 'lecteur'@'%' IDENTIFIED BY 'un-mot-de-passe-solide';

CREATE USER
    'app'@'10.0.0.%' IDENTIFIED BY 'secret1',
    'admin'@'localhost' IDENTIFIED BY 'secret2';

ALTER USER 'lecteur'@'%' IDENTIFIED BY 'nouveau-mot-de-passe';

ALTER USER 'lecteur'@'%' ACCOUNT LOCK;      -- locks the account: connection refused
ALTER USER 'lecteur'@'%' ACCOUNT UNLOCK;    -- unlocks it

DROP USER 'lecteur'@'%';

IDENTIFIED BY 'text' sets a clear-text password on the client side (immediately converted into a verifier on the server side); IDENTIFIED BY PASSWORD 'hash' directly sets a verifier that has already been computed.

ACCOUNT LOCK / ACCOUNT UNLOCK makes it possible to suspend an account without dropping it or changing its password — useful for temporarily disabling an application account or that of a team member who is leaving, without losing the history of their privileges.

DEFAULT ROLE ... can be set when creating or modifying an account (see §10.4).

A newly created account has no privileges on data: only USAGE, that is, the right to connect and nothing more. It must be explicitly granted privileges with GRANT (§10.3).

Changing your own password#

SET PASSWORD = 'nouveau-mot-de-passe';                 -- the session's account
SET PASSWORD FOR 'lecteur'@'%' = 'nouveau-mot-de-passe'; -- another account (CREATE USER privilege required)
SET PASSWORD = PASSWORD('nouveau-mot-de-passe');

An ordinary account can always change its own password; changing that of another account, or more generally managing accounts (CREATE USER, ALTER USER, DROP USER), requires the global CREATE USER privilege — otherwise error 1227 (see §10.6).

Identifying the current account#

SELECT CURRENT_USER();   -- 'user'@'host' of the ACCOUNT that authenticated
SELECT USER();           -- 'user'@'host' as the client requested it at connection time

CURRENT_USER() reflects the account actually retained by the server for authentication (after resolving the most specific host pattern); USER() reflects the client's initial request. The two coincide in the vast majority of cases; they can differ when the client connects under a host that matches an account defined by a pattern (%, _).

10.3 Privileges: GRANT and REVOKE#

The three levels#

A privilege held at one level also applies to everything that level encompasses: a SELECT privilege granted on ventes.* applies to all tables of the ventes database, present and future, without needing to be granted again for each new table.

Syntax#

GRANT privilège [, privilège ...] ON [TABLE] niveau TO compte [, compte ...] [WITH GRANT OPTION];

REVOKE [IF EXISTS] privilège [, privilège ...] ON [TABLE] niveau FROM compte [, compte ...];
REVOKE [IF EXISTS] ALL [PRIVILEGES], GRANT OPTION FROM compte [, compte ...];

ALL (or ALL PRIVILEGES) grants or removes everything the targeted level allows. WITH GRANT OPTION additionally lets the grantee pass on these privileges (or a subset) to other accounts, at the same level or at a level it encompasses.

To grant or revoke a privilege at a given level, you must yourself hold that privilege at that level (or at an encompassing level) with the GRANT option. GRANT accepts only accounts that already exist: it no longer creates any (creating an account is the role of CREATE USER).

Concrete examples#

Read-only application account on a database:

CREATE USER 'rapport'@'10.0.0.%' IDENTIFIED BY 'mot-de-passe-1';
GRANT SELECT ON gestion.* TO 'rapport'@'10.0.0.%';

Application account that reads and writes in a database, without being able to modify its schema:

CREATE USER 'app'@'10.0.0.%' IDENTIFIED BY 'mot-de-passe-2';
GRANT SELECT, INSERT, UPDATE, DELETE ON gestion.* TO 'app'@'10.0.0.%';

Account limited to a single sensitive table:

GRANT SELECT, UPDATE ON gestion.parametres TO 'support'@'localhost';

Database administrator account, which can in turn delegate:

CREATE USER 'dba_gestion'@'localhost' IDENTIFIED BY 'mot-de-passe-3';
GRANT ALL ON gestion.* TO 'dba_gestion'@'localhost' WITH GRANT OPTION;

Removing a privilege that is no longer needed:

REVOKE INSERT, UPDATE, DELETE ON gestion.* FROM 'rapport'@'10.0.0.%';

What is not supported#

Column-level privileges (GRANT SELECT (col1, col2) ON ...) and routine-level privileges (GRANT EXECUTE ON FUNCTION ... / ON PROCEDURE ...) are not supported in this version (error 1235). Privileges are therefore granted at the level of an entire table at minimum.

10.4 Roles#

A role groups a set of privileges under a name, so that they can then be granted all at once to several accounts, rather than repeating the same GRANTs account by account.

Creating and administering a role#

CREATE ROLE 'lecture_gestion';
GRANT SELECT ON gestion.* TO 'lecture_gestion';

DROP ROLE 'lecture_gestion';

CREATE ROLE requires the CREATE ROLE (or CREATE USER) privilege; DROP ROLE requires DROP ROLE (or CREATE USER). Dropping a role immediately removes it from all the accounts that held it.

Granting a role to an account#

GRANT 'lecture_gestion' TO 'rapport'@'10.0.0.%';
GRANT 'lecture_gestion' TO 'autre_compte'@'%' WITH ADMIN OPTION;

REVOKE 'lecture_gestion' FROM 'rapport'@'10.0.0.%';

Granting or revoking a role requires the ROLE_ADMIN (or SUPER) privilege, or the ADMIN option held specifically on that role (given by WITH ADMIN OPTION).

Activating a role in the session#

A role granted to an account is not automatically active at connection time, unless it has been set as a default role:

SET DEFAULT ROLE 'lecture_gestion' TO 'rapport'@'10.0.0.%';   -- activated on every connection
SET DEFAULT ROLE ALL TO 'rapport'@'10.0.0.%';                 -- all granted roles
SET DEFAULT ROLE NONE TO 'rapport'@'10.0.0.%';                -- none by default

-- In the current session:
SET ROLE 'lecture_gestion';
SET ROLE ALL;
SET ROLE ALL EXCEPT 'un_autre_role';
SET ROLE NONE;
SET ROLE DEFAULT;   -- reverts to the account's default roles

SET ROLE can only activate roles already granted to the session's account. SET DEFAULT ROLE for an account other than the session's requires the CREATE USER privilege.

Viewing active privileges and roles#

SHOW GRANTS;                              -- for the session's account (active roles merged in)
SHOW GRANTS FOR 'rapport'@'10.0.0.%';     -- for another account (CREATE USER privilege required)
SHOW GRANTS FOR 'rapport'@'10.0.0.%' USING 'lecture_gestion';
SHOW CREATE USER 'rapport'@'10.0.0.%';    -- statement that recreates the account (CREATE USER privilege
                                          -- required for another account): password verifier
                                          -- (IDENTIFIED BY PASSWORD '*…', never the password), lock

SELECT CURRENT_ROLE();

10.5 Auditing rights through information_schema#

Several virtual tables of information_schema give a query-friendly view of privileges and roles, built directly from the account vault:

Examples:

-- All privileges granted on the gestion database
SELECT * FROM information_schema.SCHEMA_PRIVILEGES WHERE TABLE_SCHEMA = 'gestion';

-- All accounts that have a global privilege (to be watched closely)
SELECT * FROM information_schema.USER_PRIVILEGES;

-- Roles available to the account 'rapport'@'10.0.0.%'
SELECT * FROM information_schema.APPLICABLE_ROLES WHERE USER = 'rapport' AND HOST = '10.0.0.%';

-- Roles active in the current session
SELECT * FROM information_schema.ENABLED_ROLES;

An ordinary account sees, in these tables as in information_schema.USERS, only what concerns it; authentication_string is always NULL there, and any attempt to write to them is refused (error 1044) — they are not tables but a computed view of the account vault.

10.6 Privilege error codes#

Each statement is checked before execution, at the privilege level it requires:

An important point for application security: when an account has no rights on a table, a query targeting it returns 1142 even if the table does not exist — never the "unknown table" error (1146), which would reveal the table's absence to an account that in any case has no right to see it.

SHOW DATABASES, SHOW TABLES and reads of information_schema (SCHEMATA, TABLES, COLUMNS, ...) are silently filtered according to the same rules: an account only ever sees what it is entitled to reach, with no error message for what is hidden from it.

Procedures, functions and scheduled events run, by default, with the privileges of their definer (SQL SECURITY DEFINER) rather than those of the caller; SQL SECURITY INVOKER reverses this choice to use the caller's privileges.

Role-related errors: unknown role (3523), role not granted to the account concerned (3530), nonexistent GRANT grantee (1410, GRANT no longer creating accounts as it used to).

10.7 The root account and best practices#

A root account is created automatically with the data directory, holding all privileges and the GRANT option. It is the account that the console (miraj-cli) and the API use by default in embedded access.

Best practices when opening a new data directory:

• Change the root account's password right at installation (ALTER USER 'root'@'%' IDENTIFIED BY '...' or SET PASSWORD), in particular before any network exposure.
• Do not expose miraj-server on a non-local address without first creating restricted application accounts (§10.3): by default, the server listens only on the loopback interface, and MIRAJ flags accounts without a password that are reachable remotely.
• Create a dedicated account per use (one per application, one per human user) rather than sharing root or a single account: this makes it possible to trace actions, to limit the damage of a password leak, and to lock (ACCOUNT LOCK) a single account without affecting the others.
• Grant the minimum necessary: a read-only application account needs only SELECT on the databases it actually queries, never global privileges.

The details of the account vault — its encryption, the location and protection of its key, and the recovery procedure when the vault is unusable — are covered in chapter 11, "Server Administration".

See also#

• Chapter 9, "Transactions and Concurrency", for what happens once connected and authorized.
• Chapter 11, "Server Administration", for the encrypted account vault, its key and --reset-accounts.