Using Roles in MySQL 8.0

2018-09-01 · mysql, security, permissions, roles

I've copy-pasted the same GRANT statements across too many accounts, and inevitably one user ended up with a table missing. Roles fix that. A role is a named bundle of privileges: grant privileges to the role once, then grant the role to users. When the app gains a table, grant it to the role and every member picks it up. MariaDB has had roles since 10.0, and MySQL 8.0 finally has a very similar implementation.

Below I set up roles for a typical application. Watch out for the detail that trips almost everyone: roles aren't active by default when a user logs in.

Create the roles

Roles live in mysql.user alongside regular accounts, with account locking and no password. Name them for what they permit, not for who holds them.

CREATE ROLE 'app_read', 'app_write', 'app_admin';

Check they exist:

SELECT User, Host, account_locked FROM mysql.user
WHERE User LIKE 'app_%';

Roles default to the % host and show account_locked = Y, which is why nobody can log in as a role directly.

Grant privileges to the roles

Same as for a user, with the role name in place of the account.

GRANT SELECT ON appdb.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON appdb.* TO 'app_write';
GRANT ALL PRIVILEGES ON appdb.* TO 'app_admin';

Roles can also be granted to other roles. If app_write should always include reading:

GRANT 'app_read' TO 'app_write';

Now SHOW GRANTS FOR 'app_write' lists both its own privileges and the inherited role.

Users and role assignment

CREATE USER 'reporting'@'10.0.0.%' IDENTIFIED BY 'a-strong-password';
CREATE USER 'webapp'@'10.0.0.%' IDENTIFIED BY 'another-strong-password';
CREATE USER 'dba_alice'@'localhost' IDENTIFIED BY 'yet-another-strong-password';

GRANT 'app_read' TO 'reporting'@'10.0.0.%';
GRANT 'app_write' TO 'webapp'@'10.0.0.%';
GRANT 'app_admin' TO 'dba_alice'@'localhost';

Nothing has changed yet for these users when they connect. Read on.

Activating the roles

This is the part that matters. A granted role is dormant until it is set active in the session. If webapp connects and runs SELECT CURRENT_ROLE(); it gets NONE, and any query fails with access denied.

There are three ways to activate roles:

Per session, by the user:

SET ROLE 'app_write';
SET ROLE ALL;

Per user, persistently, with a default role that activates at login:

SET DEFAULT ROLE 'app_write' TO 'webapp'@'10.0.0.%';
SET DEFAULT ROLE ALL TO 'reporting'@'10.0.0.%', 'dba_alice'@'localhost';

Or server-wide, by making every granted role active automatically at login. In /etc/mysql/mysql.conf.d/mysqld.cnf on Ubuntu or /etc/my.cnf on CentOS:

[mysqld]
activate_all_roles_on_login = ON

Or at runtime and persisted with 8.0's SET PERSIST:

SET PERSIST activate_all_roles_on_login = ON;

For application accounts I stick with SET DEFAULT ROLE, because it's explicit per user and survives a config rebuild. activate_all_roles_on_login is fine on a server where roles are the only privilege mechanism.

Mandatory roles

If every account should have some minimum privilege, for example USAGE plus access to a shared common schema, define a mandatory role that is granted implicitly to everyone:

CREATE ROLE 'baseline';
GRANT SELECT ON common.* TO 'baseline';
SET PERSIST mandatory_roles = 'baseline';

Mandatory roles cannot be revoked or dropped while listed in mandatory_roles, which is a useful safeguard against a careless REVOKE.

Checking what's active

Connect as the application user:

mysql -h db.internal -u webapp -p -e "SELECT CURRENT_USER(), CURRENT_ROLE();"

Expect webapp@10.0.0.% and `app_write`@`%`. Then confirm the effective privileges including inherited roles:

SHOW GRANTS FOR 'webapp'@'10.0.0.%' USING 'app_write';

Without USING, SHOW GRANTS lists only the role grant itself, not what the role contains. Finally, test that a SELECT and an INSERT succeed and that a DROP TABLE fails with ERROR 1142 (42000): DROP command denied.

The mysql.role_edges table shows the complete graph of who holds what:

SELECT FROM_USER AS role, TO_USER AS grantee, TO_HOST FROM mysql.role_edges;

Errors you'll probably see

ERROR 1142 (42000): SELECT command denied to user 'webapp'@'10.0.0.5' for table 'orders' even though the role has the privilege. CURRENT_ROLE() will show NONE. Set a default role or turn on activate_all_roles_on_login.

ERROR 3530 (HY000): 'app_admin'@'%' is not granted to 'dba_alice'@'localhost' on SET DEFAULT ROLE. Grant the role first; a default role must already be granted.

ERROR 3523 (HY000): Unknown authorization ID 'app_read'@'%' on GRANT 'app_read' TO .... The role name has a typo, or you created it with an explicit host other than %. Roles are matched on user@host exactly like accounts.

Revoking from the role does not take effect for connected sessions. Privilege changes to a role apply at the next SET ROLE or reconnect, so long-lived connection pools keep the old privileges until they cycle.

Always set a default role. Skip that and everything looks fine until the first login fails.