15. 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.

15.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 16) 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.

Every password definition (CREATE USER … IDENTIFIED BY, ALTER USER … IDENTIFIED BY, SET PASSWORD, initial root password) also computes a SCRAM-SHA-256 verifier, whatever the instance's language: it is used by PostgreSQL clients of an instance in PostgreSQL language (§ 16.10.2), so a language change blocks no account. A password given by its digest (IDENTIFIED BY PASSWORD '*…') has none, nor does an account defined before this version: to give it one, redefine its password. The verifiers follow the accounts in the encrypted vault, in the replication of a cluster's accounts and in --reset-accounts.

15.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. IDENTIFIED {WITH | VIA} <plugin> followed by BY 'text', by {AS | USING} 'hash' or by {AS | USING} PASSWORD('text') is accepted for the tools that write it, when the plugin is the protocol's native plugin, under its usual name or under the name miraj_password (the one the accounts view displays): it is the only authentication plugin. Any other plugin (caching_sha2_password, auth_socket…) is refused with error 1524, and several plugins (VIA a OR b) with error 1235. IDENTIFIED WITH plugin alone gives an account without a password (removed by ALTER USER): the require_password rule then applies (error 9033 for an account reachable from another machine).

Remote accounts without a password are refused. As long as the global variable require_password is ON (default, --require-password server option), an account whose host is not strictly the loopback (localhost, 127.0.0.1, ::1) must have a password: CREATE USER 'app'@'%' without IDENTIFIED BY is refused with error 9033, as is an ALTER USER or a SET PASSWORD that would remove the password of such an account. A local account without a password remains allowed. SET GLOBAL require_password = OFF (SUPER or SYSTEM_VARIABLES_ADMIN privilege) lifts the rule, for example while reloading an export of old accounts.

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. The lock can also be written at creation (CREATE USER … IDENTIFIED BY '…' ACCOUNT LOCK) or with a password change (ALTER USER … IDENTIFIED BY '…' ACCOUNT LOCK). A connection to a locked account is refused with error 3118; the console displays it like this:

connection to [email protected]:7106 failed: SQL error 1105 (HY000): 3118 (HY000): Access denied for user 'lk'@'localhost'. Account is locked.

Account already existing or missing. CREATE USER of an account that already exists, or DROP USER of a missing account, fails with 1396 and neither creates nor drops any of the accounts in the list:

ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'localhost'
ERROR 1396 (HY000): Operation DROP USER failed for 'zz'@'%'

CREATE USER IF NOT EXISTS, ALTER USER IF EXISTS and DROP USER IF EXISTS then skip the account concerned with a warning (3163 "… already exists", 3162 "… does not exist") and process the others; CREATE ROLE IF NOT EXISTS and DROP ROLE IF EXISTS do the same.

DEFAULT ROLE ... can be set when creating or modifying an account (see §15.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 (§15.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 §15.6).

System accounts (SYSTEM_USER). An account that holds the SYSTEM_USER privilege, directly or through a role granted to it — as the initial root does — can be modified (ALTER USER, SET PASSWORD FOR), dropped (DROP USER), stripped (REVOKE … FROM) or given privileges and roles (GRANT … TO) only by an account that itself holds SYSTEM_USER; otherwise error 1227 naming SYSTEM_USER. A role that carries SYSTEM_USER can be granted and dropped only under the same conditions. Thus, an administration account that has been entrusted with CREATE USER to manage application accounts cannot take control of root. Every account keeps the right to change its own password.

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 (%, _).

15.3 Privileges: GRANT and REVOKE#

The 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 privilege [, privilege ...] ON [TABLE] level TO account [, account ...] [WITH GRANT OPTION];

REVOKE [IF EXISTS] privilege [, privilege ...] ON [TABLE] level FROM account [, account ...];
REVOKE [IF EXISTS] ALL [PRIVILEGES], GRANT OPTION FROM account [, account ...];

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). At table level (ON db.table), the table, or even the database, may not exist yet if the privileges granted include CREATE (or ALL); otherwise error 1146 is returned.

Rights apply from the statement that changes them: FLUSH PRIVILEGES, which some tools send after a GRANT, is accepted with no effect (RELOAD privilege). The DELETE HISTORY privilege (temporal tables, chapter 12) is granted, revoked and displayed like the others and is part of ALL PRIVILEGES; yet DELETE HISTORY FROM t requires only DELETE on the table (see 15.3.1).

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.%';

Column privileges#

SELECT, INSERT, UPDATE and REFERENCES can also be granted on some of the columns of a table or view: the privilege name is followed by the list of columns.

-- Customer service reads customer identity and corrects their address, never their balance
GRANT SELECT (id, nom, ville), UPDATE (ville) ON gestion.clients TO 'support'@'localhost';
SHOW GRANTS FOR 'support'@'localhost';

+-------------------------------------------------------------------------------------------------------+
| Grants for support@localhost                                                                          |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `support`@`localhost`                                                           |
| GRANT SELECT (`id`, `nom`, `ville`), UPDATE (`ville`) ON `gestion`.`clients` TO `support`@`localhost` |
+-------------------------------------------------------------------------------------------------------+

-- Table and column privileges in the same statement
GRANT INSERT (id, nom), DELETE ON gestion.prospects TO 'support'@'localhost';
REVOKE UPDATE (ville) ON gestion.clients FROM 'support'@'localhost';
SHOW GRANTS FOR 'support'@'localhost';

+--------------------------------------------------------------------------------------+
| Grants for support@localhost                                                         |
+--------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `support`@`localhost`                                          |
| GRANT SELECT (`id`, `nom`, `ville`) ON `gestion`.`clients` TO `support`@`localhost`  |
| GRANT INSERT (`id`, `nom`), DELETE ON `gestion`.`prospects` TO `support`@`localhost` |
+--------------------------------------------------------------------------------------+

Every column used by a statement is checked, where the account has the privilege only on some columns:

A SQL SECURITY DEFINER view, procedure, function or trigger is checked column by column under its definer's identity: a view that reads a column denied to its definer returns 1356. A denied column gives error 1143:

ERROR 1143 (42000): SELECT command denied to user 'support'@'localhost' for column 'solde' in table 'clients'

Routine privileges#

EXECUTE and ALTER ROUTINE can also be granted on a single procedure or a single function, rather than on its whole database (GRANT EXECUTE ON db.*):

-- Customer service calls two management routines, and no other
GRANT EXECUTE ON PROCEDURE gestion.relancer_client TO 'support'@'%';
GRANT EXECUTE ON FUNCTION gestion.solde_client TO 'support'@'%' WITH GRANT OPTION;
-- The operations account can drop this procedure (DROP PROCEDURE)
GRANT ALTER ROUTINE ON PROCEDURE gestion.purge TO 'exploitation'@'localhost';
REVOKE EXECUTE ON PROCEDURE gestion.relancer_client FROM 'support'@'%';

• A procedure and a function with the same name are two distinct levels.
• The routine must exist at the time of the GRANT (1305); only EXECUTE, ALTER ROUTINE (and GRANT OPTION) are allowed on it (1144 for any other privilege); ALL grants both.
• The call (CALL, function in a query, with constant arguments or row by row) is checked as for the database level: 1370 (execute command denied to user 'support'@'…' for routine 'gestion.solde_client') without EXECUTE on the routine, its database or at global level.
• ALTER ROUTINE on the routine allows DROP PROCEDURE / DROP FUNCTION of that routine; CREATE ROUTINE remains a database privilege.
• Passing on a routine right requires the GRANT option on the routine (or broader): 1370 (grant command denied …) otherwise. A REVOKE of a right that is absent returns 1403 (There is no such grant defined for user 'support' on host '%' on routine 'relancer_client').
• Dropping the routine (DROP PROCEDURE / DROP FUNCTION) removes the rights granted on it to all accounts: recreated, it no longer carries any.
• A right on a routine makes its database visible (USE, SHOW DATABASES).
• SHOW GRANTS shows them after tables, then procedures, then functions, privileges named one by one (GRANT EXECUTE ON PROCEDURE `gestion`.`relancer_client` TO `support`@`%`); the system table miraj.procs_priv (columns Host, Db, User, Routine_name, Routine_type, Grantor, Proc_priv, Timestamp) and information_schema.ROUTINE_PRIVILEGES (§15.5) list them for administration tools. Grantor is empty and Timestamp is NULL: Miraj keeps neither the author nor the time of a GRANT.

Privileges are not only granted on tables: see §15.3.1 for sequences, system-versioned tables and administration privileges.

15.3.1 Sequences, history and administration privileges#

• Sequences. A sequence is named like a table (GRANT SELECT ON gestion.seq_facture TO …). INSERT allows NEXTVAL / NEXT VALUE FOR and SETVAL; SELECT allows LASTVAL, reading (SELECT * FROM seq_facture) and SHOW CREATE SEQUENCE. CREATE SEQUENCE, ALTER SEQUENCE and DROP SEQUENCE require CREATE, ALTER and DROP on the database. Without the privilege, the error is 1142 (chapter 7, "Sequences").
• System-versioned tables. The history of a table (its companion table, chapter 12) is read and written with the privileges of the table itself: SELECT … FOR SYSTEM_TIME … requires SELECT, DELETE HISTORY FROM t requires DELETE on t. The DELETE HISTORY privilege can be granted, revoked and displayed like the others and is part of ALL PRIVILEGES, but is not required in addition.
• SHUTDOWN: the SHUTDOWN statement (server shutdown, chapter 16) requires the SHUTDOWN privilege.
• BACKUP_ADMIN: BACKUP DATABASE, and with CREATE (plus DROP with REPLACE) RESTORE DATABASE (chapter 19); it also allows setting journal_archive* with SET GLOBAL.
• EXPLAIN ANALYZE / ANALYZE execute the statement: they require the privileges of the analyzed statement, and ANALYZE UPDATE|DELETE really modifies the rows (chapter 9, 9.21).

15.3.2 List of privileges#

SHOW PRIVILEGES lists the privileges the server knows. Table privileges can also be granted at database or global level; administration privileges only at global level (ON *.*), otherwise error 1144 (GRANT SUPER ON gestion.* …).

ALL [PRIVILEGES] at global level gives all the privileges written in words (from SELECT to DROP ROLE), but neither GRANT OPTION, which is added with WITH GRANT OPTION, nor those written in capitals with an underscore (ROLE_ADMIN … ENCRYPTION_KEY_ADMIN), which are granted one by one. The initial root holds them all:

SHOW GRANTS FOR CURRENT_USER;

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                                                                                 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION                                                                                                                       |
| GRANT ROLE_ADMIN,CONNECTION_ADMIN,SYSTEM_VARIABLES_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,BACKUP_ADMIN,ENCRYPTION_KEY_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

15.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();

For the rapport account of the examples above (SELECT privilege on gestion.*, role lecture_gestion granted):

+-----------------------------------------------------+
| Grants for [email protected].%                         |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO `rapport`@`10.0.0.%`          |
| GRANT SELECT ON `gestion`.* TO `rapport`@`10.0.0.%` |
| GRANT `lecture_gestion`@`%` TO `rapport`@`10.0.0.%` |
+-----------------------------------------------------+

+-----------------------------------------------------------------------------------------------------+
| CREATE USER for [email protected].%                                                                    |
+-----------------------------------------------------------------------------------------------------+
| CREATE USER 'rapport'@'10.0.0.%' IDENTIFIED BY PASSWORD '*7AED0DAAF571D30F9B8058AE21DC2D88E0707207' |
+-----------------------------------------------------------------------------------------------------+

CURRENT_ROLE() returns NONE with no active role, otherwise the list of active roles (`lecture_gestion`@`%`). An unknown role gives 3523 (Unknown authorization ID `nor`@`%`), a role that is not granted gives 3530.

15.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 granted to the session's account, and active roles
SELECT * FROM information_schema.APPLICABLE_ROLES;
SELECT * FROM information_schema.ENABLED_ROLES;

Result for root@localhost, to whom the role lecture_gestion was granted and then activated by SET ROLE 'lecture_gestion':

+--------------------+-----------+---------+--------------+-----------------+-----------+--------------+------------+--------------+
| USER               | HOST      | GRANTEE | GRANTEE_HOST | ROLE_NAME       | ROLE_HOST | IS_GRANTABLE | IS_DEFAULT | IS_MANDATORY |
+--------------------+-----------+---------+--------------+-----------------+-----------+--------------+------------+--------------+
| 'root'@'localhost' | localhost | root    | localhost    | lecture_gestion | %         | NO           | NO         | NO           |
+--------------------+-----------+---------+--------------+-----------------+-----------+--------------+------------+--------------+

+-----------------+-----------+
| ROLE_NAME       | ROLE_HOST |
+-----------------+-----------+
| lecture_gestion | %         |
+-----------------+-----------+

With no active role, ENABLED_ROLES returns a single row NULL, NULL. For the roles of another account, read SHOW GRANTS FOR account (the roles appear there as GRANT role TO account).

The column privileges of an account:

SELECT * FROM information_schema.COLUMN_PRIVILEGES WHERE TABLE_NAME = 'clients';

+-----------------------+---------------+--------------+------------+-------------+----------------+--------------+
| GRANTEE               | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+-----------------------+---------------+--------------+------------+-------------+----------------+--------------+
| 'support'@'localhost' | def           | gestion      | clients    | id          | SELECT         | NO           |
| 'support'@'localhost' | def           | gestion      | clients    | nom         | SELECT         | NO           |
| 'support'@'localhost' | def           | gestion      | clients    | ville       | SELECT         | NO           |
| 'support'@'localhost' | def           | gestion      | clients    | ville       | UPDATE         | NO           |
+-----------------------+---------------+--------------+------------+-------------+----------------+--------------+

An ordinary account sees, in these tables as in the system accounts table miraj.user, only what concerns it; authentication_string is always NULL in miraj.user, and any attempt to write to it is refused (error 1044, root included) — they are not tables but a computed view of the account vault: accounts and rights can be changed only by CREATE USER, GRANT, REVOKE, etc.

15.6 Privilege error codes#

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

Messages as the console displays them:

ERROR 1044 (42000): Access denied for user 'app'@'localhost' to database 'shop'
ERROR 1142 (42000): INSERT command denied to user 'app'@'localhost' for table `err`.`p`
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
ERROR 1370 (42000): execute command denied to user 'app'@'localhost' for routine 'err.f1'

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. information_schema.COLUMNS follows privileges column by column: an account that has privileges only on some columns of a table sees only those there, and the PRIVILEGES column returns the ones it holds on each (select,update, for example). DESCRIBE and SHOW [FULL] COLUMNS follow the same rule (the Privileges column of SHOW FULL COLUMNS names the rights held); only privileges that apply to a column count (SELECT, INSERT, UPDATE, REFERENCES): an account that has only DELETE on the table sees none.

A table is visible only through a privilege that can apply to it, granted on the table, on its database or at global level: a database privilege that has no meaning for a table (EXECUTE, CREATE ROUTINE, EVENT, LOCK TABLES…) or a global privilege such as PROCESS leaves the database visible but shows none of its tables. information_schema.TRIGGERS and SHOW TRIGGERS list only the triggers of the tables on which the account has the TRIGGER privilege, and information_schema.CHECK_CONSTRAINTS only the constraints of visible tables.

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.

Calling a routine always requires EXECUTE on the routine itself or on its database, whatever the context of the call: CALL p(), a function called row by row (SELECT f(id) FROM t) or with constant arguments (SELECT f(1), SET @v = f(1), INSERT … VALUES (f(1)), WHERE f(1) = 0). Without it, the statement fails with 1370 before any execution of the routine (no effect of its body), even if the caller otherwise holds all rights on the tables that the body reads or writes. A call with constant arguments is executed once, before the statement: its rights (EXECUTE, then the body under the definer's identity) are checked before that execution, along with those of the rest of the statement. In the body of a routine, trigger or view, EXECUTE is required of the definer of that body (of the caller for a SQL SECURITY INVOKER routine or view), not of the account whose statement fires it.

Triggers always run with the privileges of their definer, whatever the account whose write fires them; a function called row by row (SELECT f(id) FROM t, WHERE f(x) > 0) follows the same rule as any function (definer, or caller with SQL SECURITY INVOKER). Their bodies are checked with the statement that fires them, once for it and not on each row: every statement of the body, including in a branch that execution will not take, the procedures called (under their own definer), the triggers of the tables the body writes and the views it reads. A function called by a view is checked in the same way, under the identity of the view's definer. A right missing from the definer makes the firing statement fail (1142, 1044, 1370 or 1227, which name the definer; 1356 for a view whose definer can no longer read the tables); a dropped definer gives 1449. A REVOKE applies from the next statement. Example: an account that has only TRIGGER and INSERT on shop.ventes cannot create a trigger that modifies compta.ecritures (1142 at creation), and a trigger created by root with DEFINER = app fails on every INSERT, even by root, as long as app does not have rights on compta.

Statements forbidden in the body of a trigger or of a function called row by row (and in the procedures they call), because they depend on the session's identity: KILL, LOAD DATA, SELECT … INTO OUTFILE / DUMPFILE, LOCK TABLES, BACKUP / RESTORE and account and rights management (GRANT, REVOKE, CREATE USER, SET ROLE…). CREATE TRIGGER refuses them outright (1314, or 1422 for account management, which commits implicitly); a procedure that contains one fails with 1314 when a trigger calls it. In a trigger, LOAD_FILE reads a file only if the definer has the FILE privilege.

The system table proc (miraj.proc) shows only the routines of the databases on which the account has a right, like information_schema.ROUTINES. The system table procs_priv (miraj.procs_priv) shows an account that does not have CREATE USER only its own per-routine rights.

Database imported from another server. Triggers, routines and views keep the definer recorded at their creation (DEFINER = gestium@localhost, for example). As long as that account does not exist in Miraj's account vault, any write that fires such a trigger fails with 1449, as does a function called row by row, and reading such a view fails with 1356; if the account exists without the necessary rights on the tables its bodies read or write, the statement fails with 1142 (1356 for a view). After a migration, therefore, create each definer (the DEFINER column of information_schema.TRIGGERS, ROUTINES and VIEWS lists them) and grant it the rights its bodies need, for example CREATE USER gestium@localhost IDENTIFIED BY '…'; GRANT ALL ON gestium.* TO gestium@localhost. Without an account vault (embedded engine without accounts), none of these checks takes place.

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).

15.7 The root account and best practices#

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

Initial password. On the first startup of miraj-server on a directory, the account vault is created and root receives a randomly generated 24-character password (letters and digits, without look-alike characters such as 0/O or 1/l), displayed only once on the console and never written to server.log. For a scripted installation, give your own with --initial-root-password or the environment variable MIRAJ_INITIAL_ROOT_PASSWORD (or initial_root_password in miraj_config.xml, to be removed afterward): it is then not displayed. These settings are ignored, with a warning, once the vault has been created. --reset-accounts recreates root with a new password, generated or given in the same way (see chapter 16). In a cluster, only the primary creates accounts: a secondary receives them from it. The embedded engine alone (library, miraj-cli on a local directory) does not write a vault on opening: its root stays without a password until the directory has been opened by miraj-server or an account has been modified.

Best practices when opening a new data directory:

• Replace the temporary root password at first connection (ALTER USER root@localhost 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 (§15.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 16, "Server Administration".

See also#

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