Prior to MySQL 8.0.16, it is not possible to grant privileges that
apply globally except for certain schemas. As of MySQL 8.0.16,
that is possible 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.
For brevity, CREATE USER
statements shown here do not include passwords. For production
use, always assign account passwords.
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 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"]}] |
+------+------+------------------------------------------------------+
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
orBINLOG_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 wherepartial_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
grantsSELECT
globally tou1
, who has noSELECT
privilege to begin with,u1
inherits theadmin
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 globalSELECT
privilege without restriction.GRANT
can only add to a grantee's existing privileges, not reduce them, so ifadmin
grantsSELECT
globally tou2
,u2
does not inherit theadmin
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
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
user
AS
clause, see 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
clause.
user
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`@`%` |
+------------------------------------------+
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.
Once enabled, partial_revokes
cannot be disabled if any account has privilege restrictions. If
any such account exists, disabling
partial_revokes
fails:
For attempts to disable
partial_revokes
at startup, the server logs an error message and enablespartial_revokes
.For attempts to disable
partial_revokes
at runtime, an error occurs and thepartial_revokes
value remains unchanged.
To disable partial_revokes
when
restrictions exist, the restrictions first must be removed:
Determine which accounts have partial revokes:
SELECT User, Host, User_attributes->>'$.Restrictions' FROM mysql.user WHERE User_attributes->>'$.Restrictions' <> '';
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;
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.