MySQL8.0.16のリリースノートにアカウントに広い範囲で権限を付与した後に、特定の権限だけREVOKEする方法についての機能追加が実装されていたので確認してみました。
Previously, it was not possible to grant privileges that apply globally except for certain schemas. This is now possible if the new partial_revokes system variable is enabled. For example, the following statements enable an account to select from or insert into any table except those in the mysql system schema:
Changes in MySQL 8.0.16 (2019-04-25, General Availability)
- MySQL5.7.20では特定の権限だけREVOKE出来ないので、細かく設定する必要がある場合はGRANTを細かく分けて作成する必要があった。以下の例では、全てのDBにSELECT、INSERT権限を付与した後に、mysqlデータベースからだけSELECTとINSERT権限をREVOKEしようとしている。
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.20 |
+-----------+
1 row in set (0.00 sec)
mysql> CREATE USER u1@'%' identified by 'Password2022';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT user,host from mysql.user where user = 'u1';
+------+------+
| user | host |
+------+------+
| u1 | % |
+------+------+
1 row in set (0.00 sec)
mysql> GRANT SELECT, INSERT ON *.* TO u1@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> REVOKE SELECT, INSERT ON mysql.* FROM u1@'%';
ERROR 1141 (42000): There is no such grant defined for user 'u1' on host '%'
mysql> SET PERSIST partial_revokes = ON;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'partial_revokes = ON' at line 1
mysql> show grants for u1@'%'\G
*************************** 1. row ***************************
Grants for u1@%: GRANT SELECT, INSERT ON *.* TO 'u1'@'%'
1 row in set (0.00 sec)
mysql>
MySQL8.0.16以降では以下の様に、特定権限のみをREVOKE出来る様になっている。
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.27 |
+-----------+
1 row in set (0.00 sec)
mysql> CREATE USER u1@'%' identified by 'Password2022';
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT user,host from mysql.user where user = 'u1';
+------+------+
| user | host |
+------+------+
| u1 | % |
+------+------+
1 row in set (0.00 sec)
mysql> GRANT SELECT, INSERT ON *.* TO u1@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> REVOKE SELECT, INSERT ON mysql.* FROM u1@'%';
ERROR 1141 (42000): There is no such grant defined for user 'u1' on host '%'
mysql> SET PERSIST partial_revokes = ON;
Query OK, 0 rows affected (0.00 sec)
mysql> REVOKE SELECT, INSERT ON mysql.* FROM u1@'%';
Query OK, 0 rows affected (0.02 sec)
mysql> show grants for u1@'%'\G
*************************** 1. row ***************************
Grants for u1@%: GRANT SELECT, INSERT ON *.* TO `u1`@`%`
*************************** 2. row ***************************
Grants for u1@%: REVOKE SELECT, INSERT ON `mysql`.* FROM `u1`@`%`
2 rows in set (0.00 sec)
mysql>
この変数を有効にすると、権限を部分的に取り消すことができます。 具体的には、グローバルレベルの権限を持つユーザーの場合、partial_revokes では、特定のスキーマの権限を取り消しながら、他のスキーマの権限をそのままにすることができます。 たとえば、グローバル UPDATE 権限を持つユーザーは、mysql システムスキーマに対するこの権限の実行を制限できます。 (または、別の方法で、ユーザーは mysql スキーマを除くすべてのスキーマに対して UPDATE 権限を実行できます。) この意味では、ユーザーのグローバル UPDATE 権限は部分的に取り消されます。
一度有効にすると、アカウントに権限制限がある場合は partial_revokes を無効にできません。 そのようなアカウントが存在する場合、partial_revokes の無効化は失敗します:
起動時に partial_revokes を無効にしようとすると、サーバーはエラーメッセージをログに記録し、partial_revokes を有効にします。
実行時に partial_revokes を無効にしようとすると、エラーが発生し、partial_revokes 値は変更されません。
この場合に partial_revokes を無効にするには、まず権限を再付与するか、アカウントを削除して、権限が部分的に取り消された各アカウントを変更します。
partial_revokes
上記の様に仕様が補足されているので、アカウントを残した状態で変数設定を変更してみる。
mysql> show variables like 'partial_revokes';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| partial_revokes | ON |
+-----------------+-------+
1 row in set (0.01 sec)
mysql> SET PERSIST partial_revokes = OFF;
ERROR 3896 (HY000): At least one partial revoke exists on a database. The system variable '@@partial_revokes' must be set to ON.
mysql>
mysql> drop user u1@'%';
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT user,host from mysql.user where user = 'u1';
Empty set (0.01 sec)
mysql> show variables like 'partial_revokes';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| partial_revokes | ON |
+-----------------+-------+
1 row in set (0.01 sec)
mysql> SET PERSIST partial_revokes = OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'partial_revokes';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| partial_revokes | OFF |
+-----------------+-------+
1 row in set (0.00 sec)
mysql>
- partial_revokesのOFF(アカウント有り)
- partial_revokesのOFF(アカウント無し)
NOTE:
Enabling partial_revokes causes MySQL to treat unescaped _ and % SQL wildcard characters in schema names as literal characters, just as if they had been escaped as \_ and \%. Because this changes the interpretation of privilege assignments, it may be advisable to avoid unescaped wildcard characters in schema privilege assignments for MySQL installations where partial_revokes may be enabled.