content_image/674491.jpg
modification of system accounts by a regular account, do not grant modification privileges for the mysql schema to the account (or any roles granted to the account). If a regular account must have global privileges that apply to all schemas, mysql  schema modifications can be prevented using privilege restrictions imposed using partial revokes. See Section 6.2.12, “Privilege Restriction Using Partial Revokes” . 

<img src='content_image/674491.jpg'>

## Note 

Unlike withholding the SYSTEM_USER  privilege, which prevents an account from modifying system accounts but not regular accounts, withholding mysql  schema privileges prevents an account from modifying system accounts as well as regular accounts. This should not be an issue because, as mentioned, direct grant-table modification is discouraged. 

Suppose that you want to create a user u1  who has all privileges on all schemas, except that u1  should be a regular user without the ability to modify system accounts. Assuming that the partial_revokes system variable is enabled, configure u1  as follows: 

CREATE USER u1 IDENTIFIED BY ’ password ’; GRANT ALL ON *.* TO u1 WITH GRANT OPTION; -- GRANT ALL includes SYSTEM_USER, so at this point -- u1 can manipulate system or regular accounts REVOKE SYSTEM_USER ON *.* FROM u1; -- Revoking SYSTEM_USER makes u1 a regular user; -- now u1 can use account-management statements -- to manipulate only regular accounts REVOKE ALL ON mysql.* FROM u1; -- This partial revoke prevents u1 from directly -- modifying grant tables to manipulate accounts 

To prevent all mysql  system schema access by an account, revoke all its privileges on the mysql schema, as just shown. It is also possible to permit partial mysql  schema access, such as read-only access. The following example creates an account that has SELECT , INSERT , UPDATE , and DELETE privileges globally for all schemas, but only SELECT  for the mysql  schema: 

CREATE USER u2 IDENTIFIED BY ’ password ’; GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO u2; REVOKE INSERT, UPDATE, DELETE ON mysql.* FROM u2; 

Another possibility is to revoke all mysql  schema privileges but grant access to specific mysql  tables or columns. This can be done even with a partial revoke on mysql . The following statements enable read- only access to u1  within the mysql  schema, but only for the db  table and the Host  and User  columns of the user  table: 

## 6.2.12 Privilege Restriction Using Partial Revokes 

CREATE USER u3 IDENTIFIED BY ’ password ’; GRANT ALL ON *.* TO u3; REVOKE ALL ON mysql.* FROM u3; GRANT SELECT ON mysql.db TO u3; GRANT SELECT(Host,User) ON mysql.user TO u3; 

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 

