MySQL 9.5 Reference Manual Including MySQL NDB Cluster 9.5

8.2.12 Privilege Restriction Using Partial Revokes

It is possible to grant privileges that apply globally if the partial_revokes system variable is enabled. Specifically, for users who have privileges at the global level, partial_revokes enables privileges for specific schemas to be revoked while leaving the privileges in place for other schemas. Privilege restrictions thus imposed may be useful for administration of accounts that have global privileges but should not be permitted to access certain schemas. For example, it is possible to permit an account to modify any table except those in the mysql system schema.

Note

For brevity, CREATE USER statements shown here do not include passwords. For production use, always assign account passwords.

Using Partial Revokes

The partial_revokes system variable controls whether privilege restrictions can be placed on accounts. By default, partial_revokes is disabled and attempts to partially revoke global privileges produce an error: 

mysql> CREATE USER u1;
mysql> GRANT SELECT, INSERT ON *.* TO u1;
mysql> REVOKE INSERT ON world.* FROM u1;
ERROR 1141 (42000): There is no such grant defined for user 'u1' on host '%'

To permit the REVOKE operation, enable partial_revokes: 

SET PERSIST partial_revokes = ON;

SET PERSIST sets a value for the running MySQL instance. It also saves the value, causing it to carry over to subsequent server restarts. To change the value for the running MySQL instance without having it carry over to subsequent restarts, use the GLOBAL keyword rather than PERSIST. See Section 15.7.6.1, “SET Syntax for Variable Assignment”.

With partial_revokes enabled, the partial revoke succeeds: 

mysql> REVOKE INSERT ON world.* FROM u1;
mysql> SHOW GRANTS FOR u1;
+------------------------------------------+
| Grants for u1@%                          |
+------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO `u1`@`%`  |
| REVOKE INSERT ON `world`.* FROM `u1`@`%` |
+------------------------------------------+

SHOW GRANTS lists partial revokes as REVOKE statements in its output. The result indicates that u1 has global SELECT and INSERT privileges, except that INSERT cannot be exercised for tables in the world schema. That is, access by u1 to world tables is read only.

The server records privilege restrictions implemented through partial revokes in the mysql.user system table. If an account has partial revokes, its User_attributes column value has a Restrictions attribute: 

mysql> SELECT User, Host, User_attributes->>'$.Restrictions'
       FROM mysql.user WHERE User_attributes->>'$.Restrictions' <> '';
+------+------+------------------------------------------------------+
| User | Host | User_attributes->>'$.Restrictions'                   |
+------+------+------------------------------------------------------+
| u1   | %    | [{"Database": "world", "Privileges": ["INSERT"]}] |
+------+------+------------------------------------------------------+
Note

Although partial revokes can be imposed for any schema, privilege restrictions on the mysql system schema in particular are useful as part of a strategy for preventing regular accounts from modifying system accounts. See Protecting System Accounts Against Manipulation by Regular Accounts.

Partial revoke operations are subject to these conditions:

  • It is possible to use partial revokes to place restrictions on nonexistent schemas, but only if the revoked privilege is granted globally. If a privilege is not granted globally, revoking it for a nonexistent schema produces an error.

  • Partial revokes apply at the schema level only. You cannot use partial revokes for privileges that apply only globally (such as FILE or BINLOG_ADMIN), or for table, column, or routine privileges.

  • In privilege assignments, enabling partial_revokes causes MySQL to interpret occurrences of unescaped _ and % SQL wildcard characters in schema names as literal characters, just as if they had been escaped as \_ and \%. Because this changes how MySQL interprets privileges, it may be advisable to avoid unescaped wildcard characters in privilege assignments for installations where partial_revokes may be enabled.

As mentioned previously, partial revokes of schema-level privileges appear in SHOW GRANTS output as REVOKE statements. This differs from how SHOW GRANTS represents “plain” schema-level privileges:

  • When granted, schema-level privileges are represented by their own GRANT statements in the output: 

    mysql> CREATE USER u1;
mysql> GRANT UPDATE ON mysql.* TO u1;
mysql> GRANT DELETE ON world.* TO u1;
mysql> SHOW GRANTS FOR u1;
+---------------------------------------+
| Grants for u1@%                       |
+---------------------------------------+
| GRANT USAGE ON *.* TO `u1`@`%`        |
| GRANT UPDATE ON `mysql`.* TO `u1`@`%` |
| GRANT DELETE ON `world`.* TO `u1`@`%` |
+---------------------------------------+

  • When revoked, schema-level privileges simply disappear from the output. They do not appear as REVOKE statements: 

    mysql> REVOKE UPDATE ON mysql.* FROM u1;
mysql> REVOKE DELETE ON world.* FROM u1;
mysql> SHOW GRANTS FOR u1;
+--------------------------------+
| Grants for u1@%                |
+--------------------------------+
| GRANT USAGE ON *.* TO `u1`@`%` |
+--------------------------------+

When a user grants a privilege, any restriction the grantor has on the privilege is inherited by the grantee, unless the grantee already has the privilege without the restriction. Consider the following two users, one of whom has the global SELECT privilege: 

CREATE USER u1, u2;
GRANT SELECT ON *.* TO u2;

Suppose that an administrative user admin has a global but partially revoked SELECT privilege: 

mysql> CREATE USER admin;
mysql> GRANT SELECT ON *.* TO admin WITH GRANT OPTION;
mysql> REVOKE SELECT ON mysql.* FROM admin;
mysql> SHOW GRANTS FOR admin;
+------------------------------------------------------+
| Grants for admin@%                                   |
+------------------------------------------------------+
| GRANT SELECT ON *.* TO `admin`@`%` WITH GRANT OPTION |
| REVOKE SELECT ON `mysql`.* FROM `admin`@`%`          |
+------------------------------------------------------+

If admin grants SELECT globally to u1 and u2, the result differs for each user:

  • If admin grants SELECT globally to u1, who has no SELECT privilege to begin with, u1 inherits the admin privilege restriction: 

    mysql> GRANT SELECT ON *.* TO u1;
mysql> SHOW GRANTS FOR u1;
+------------------------------------------+
| Grants for u1@%                          |
+------------------------------------------+
| GRANT SELECT ON *.* TO `u1`@`%`          |
| REVOKE SELECT ON `mysql`.* FROM `u1`@`%` |
+------------------------------------------+

  • On the other hand, u2 already holds a global SELECT privilege without restriction. GRANT can only add to a grantee's existing privileges, not reduce them, so if admin grants SELECT globally to u2, u2 does not inherit the admin restriction: 

    mysql> GRANT SELECT ON *.* TO u2;
mysql> SHOW GRANTS FOR u2;
+---------------------------------+
| Grants for u2@%                 |
+---------------------------------+
| GRANT SELECT ON *.* TO `u2`@`%` |
+---------------------------------+

If a GRANT statement includes an AS user clause, the privilege restrictions applied are those on the user/role combination specified by the clause, rather than those on the user who executes the statement. For information about the AS clause, see Section 15.7.1.6, “GRANT Statement”.

Restrictions on new privileges granted to an account are added to any existing restrictions for that account: 

mysql> CREATE USER u1;
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO u1;
mysql> REVOKE INSERT ON mysql.* FROM u1;
mysql> SHOW GRANTS FOR u1;
+---------------------------------------------------------+
| Grants for u1@%                                         |
+---------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `u1`@`%` |
| REVOKE INSERT ON `mysql`.* FROM `u1`@`%`                |
+---------------------------------------------------------+
mysql> REVOKE DELETE, UPDATE ON db2.* FROM u1;
mysql> SHOW GRANTS FOR u1;
+---------------------------------------------------------+
| Grants for u1@%                                         |
+---------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `u1`@`%` |
| REVOKE UPDATE, DELETE ON `db2`.* FROM `u1`@`%`          |
| REVOKE INSERT ON `mysql`.* FROM `u1`@`%`                |
+---------------------------------------------------------+

Aggregation of privilege restrictions applies both when privileges are partially revoked explicitly (as just shown) and when restrictions are inherited implicitly from the user who executes the statement or the user mentioned in an AS user clause.

If an account has a privilege restriction on a schema:

  • The account cannot grant to other accounts a privilege on the restricted schema or any object within it.

  • Another account that does not have the restriction can grant privileges to the restricted account for the restricted schema or objects within it. Suppose that an unrestricted user executes these statements: 

    CREATE USER u1;
GRANT SELECT, INSERT, UPDATE ON *.* TO u1;
REVOKE SELECT, INSERT, UPDATE ON mysql.* FROM u1;
GRANT SELECT ON mysql.user TO u1;          -- grant table privilege
GRANT SELECT(Host,User) ON mysql.db TO u1; -- grant column privileges

    The resulting account has these privileges, with the ability to perform limited operations within the restricted schema: 

    mysql> SHOW GRANTS FOR u1;
+-----------------------------------------------------------+
| Grants for u1@%                                           |
+-----------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE ON *.* TO `u1`@`%`           |
| REVOKE SELECT, INSERT, UPDATE ON `mysql`.* FROM `u1`@`%`  |
| GRANT SELECT (`Host`, `User`) ON `mysql`.`db` TO `u1`@`%` |
| GRANT SELECT ON `mysql`.`user` TO `u1`@`%`                |
+-----------------------------------------------------------+

If an account has a restriction on a global privilege, the restriction is removed by any of these actions:

  • Granting the privilege globally to the account by an account that has no restriction on the privilege.

  • Granting the privilege at the schema level.

  • Revoking the privilege globally.

Consider a user u1 who holds several privileges globally, but with restrictions on INSERT, UPDATE and DELETE: 

mysql> CREATE USER u1;
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO u1;
mysql> REVOKE INSERT, UPDATE, DELETE ON mysql.* FROM u1;
mysql> SHOW GRANTS FOR u1;
+----------------------------------------------------------+
| Grants for u1@%                                          |
+----------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `u1`@`%`  |
| REVOKE INSERT, UPDATE, DELETE ON `mysql`.* FROM `u1`@`%` |
+----------------------------------------------------------+

Granting a privilege globally to u1 from an account with no restriction removes the privilege restriction. For example, to remove the INSERT restriction: 

mysql> GRANT INSERT ON *.* TO u1;
mysql> SHOW GRANTS FOR u1;
+---------------------------------------------------------+
| Grants for u1@%                                         |
+---------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `u1`@`%` |
| REVOKE UPDATE, DELETE ON `mysql`.* FROM `u1`@`%`        |
+---------------------------------------------------------+

Granting a privilege at the schema level to u1 removes the privilege restriction. For example, to remove the UPDATE restriction: 

mysql> GRANT UPDATE ON mysql.* TO u1;
mysql> SHOW GRANTS FOR u1;
+---------------------------------------------------------+
| Grants for u1@%                                         |
+---------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `u1`@`%` |
| REVOKE DELETE ON `mysql`.* FROM `u1`@`%`                |
+---------------------------------------------------------+

Revoking a global privilege removes the privilege, including any restrictions on it. For example, to remove the DELETE restriction (at the cost of removing all DELETE access): 

mysql> REVOKE DELETE ON *.* FROM u1;
mysql> SHOW GRANTS FOR u1;
+-------------------------------------------------+
| Grants for u1@%                                 |
+-------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE ON *.* TO `u1`@`%` |
+-------------------------------------------------+

If an account has a privilege at both the global and schema levels, you must revoke it at the schema level twice to effect a partial revoke. Suppose that u1 has these privileges, where INSERT is held both globally and on the world schema: 

mysql> CREATE USER u1;
mysql> GRANT SELECT, INSERT ON *.* TO u1;
mysql> GRANT INSERT ON world.* TO u1;
mysql> SHOW GRANTS FOR u1;
+-----------------------------------------+
| Grants for u1@%                         |
+-----------------------------------------+
| GRANT SELECT, INSERT ON *.* TO `u1`@`%` |
| GRANT INSERT ON `world`.* TO `u1`@`%`   |
+-----------------------------------------+

Revoking INSERT on world revokes the schema-level privilege (SHOW GRANTS no longer displays the schema-level GRANT statement): 

mysql> REVOKE INSERT ON world.* FROM u1;
mysql> SHOW GRANTS FOR u1;
+-----------------------------------------+
| Grants for u1@%                         |
+-----------------------------------------+
| GRANT SELECT, INSERT ON *.* TO `u1`@`%` |
+-----------------------------------------+

Revoking INSERT on world again performs a partial revoke of the global privilege (SHOW GRANTS now includes a schema-level REVOKE statement): 

mysql> REVOKE INSERT ON world.* FROM u1;
mysql> SHOW GRANTS FOR u1;
+------------------------------------------+
| Grants for u1@%                          |
+------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO `u1`@`%`  |
| REVOKE INSERT ON `world`.* FROM `u1`@`%` |
+------------------------------------------+

Partial Revokes Versus Explicit Schema Grants

To provide access to accounts for some schemas but not others, partial revokes provide an alternative to the approach of explicitly granting schema-level access without granting global privileges. The two approaches have different advantages and disadvantages.

Granting schema-level privileges and not global privileges:

  • Adding a new schema: The schema is inaccessible to existing accounts by default. For any account to which the schema should be accessible, the DBA must grant schema-level access.

  • Adding a new account: The DBA must grant schema-level access for each schema to which the account should have access.

Granting global privileges in conjunction with partial revokes:

  • Adding a new schema: The schema is accessible to existing accounts that have global privileges. For any such account to which the schema should be inaccessible, the DBA must add a partial revoke.

  • Adding a new account: The DBA must grant the global privileges, plus a partial revoke on each restricted schema.

The approach that uses explicit schema-level grant is more convenient for accounts for which access is limited to a few schemas. The approach that uses partial revokes is more convenient for accounts with broad access to all schemas except a few.

Disabling Partial Revokes

Once enabled, partial_revokes cannot be disabled if any account has privilege restrictions. If any such account exists, disabling partial_revokes fails:

To disable partial_revokes when restrictions exist, the restrictions first must be removed:

  1. Determine which accounts have partial revokes: 

    SELECT User, Host, User_attributes->>'$.Restrictions'
FROM mysql.user WHERE User_attributes->>'$.Restrictions' <> '';

  2. For each such account, remove its privilege restrictions. Suppose that the previous step shows account u1 to have these restrictions: 

    [{"Database": "world", "Privileges": ["INSERT", "DELETE"]

    Restriction removal can be done various ways:

    • Grant the privileges globally, without restrictions: 

      GRANT INSERT, DELETE ON *.* TO u1;

    • Grant the privileges at the schema level: 

      GRANT INSERT, DELETE ON world.* TO u1;

    • Revoke the privileges globally (assuming that they are no longer needed): 

      REVOKE INSERT, DELETE ON *.* FROM u1;

    • Remove the account itself (assuming that it is no longer needed): 

      DROP USER u1;

After all privilege restrictions are removed, it is possible to disable partial revokes: 

SET PERSIST partial_revokes = OFF;

Partial Revokes and Replication

In replication scenarios, if partial_revokes is enabled on any host, it must be enabled on all hosts. Otherwise, REVOKE statements to partially revoke a global privilege do not have the same effect for all hosts on which replication occurs, potentially resulting in replication inconsistencies or errors.

When partial_revokes is enabled, an extended syntax is recorded in the binary log for GRANT statements, including the current user that issued the statement and their currently active roles. If a user or a role recorded in this way does not exist on the replica, the replication applier thread stops at the GRANT statement with an error. Ensure that all user accounts that issue or might issue GRANT statements on the replication source server also exist on the replica, and have the same set of roles as they have on the source.

MySQL Documentation Library