6.2.12 Privilege Restriction 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:

To permit the REVOKE operation, enable partial_revokes:

SET PERSIST sets the value for the running MySQL instance. It also saves the value, causing it to be used for subsequent server restarts. To change the value for the running MySQL instance without saving it for subsequent restarts, use the GLOBAL keyword rather than PERSIST. See Section 13.7.5.1, “SET Syntax for Variable Assignment”.

With partial_revokes enabled, the partial revoke succeeds:

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:

Partial revoke operations are subject to these conditions:

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 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:

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

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

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 13.7.1.6, “GRANT Syntax”.

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

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:

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

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

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

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

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

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:

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

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

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:

Granting global privileges in conjunction with partial revokes:

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.

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:

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

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.