content_image/852907.jpg
access certain schemas. For example, it is possible to permit an account to modify any table except those in the mysql  system schema. 

## Using Partial Revokes 

•Using Partial Revokes 

•Partial Revokes Versus Explicit Schema Grants 

•Disabling Partial Revokes 

•Partial Revokes and Replication 

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

## Note 

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 the 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 13.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’ | +------+------+------------------------------------------------------+ 

