Details
-
Task
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
None
-
None
Description
I'm used to declarative granting to users:
GRANT USAGE...
|
REVOKE ALL PRIVILEGES, GRANT OPTION FROM ...
|
GRANT <only these> ...
|
Roles make it difficult. There seems to be no way to revoke all roles from a user or a role, without dropping the grantee and recreating it. Dropping, AFAIK, has unwanted effects, like clearing a password (for a user) or cascade revoke from other roles and users (see below).
I tried to work around with a procedure and prepared statement (reading roles_mapping), but got ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet.
REVOKE ALL PRIVILEGES, GRANT OPTION FROM foo; seems revoke roles, but also fails on non-existing grants (Inherited from the role, I think. Might be MDEV-5228.).
So, I'm out of options. The last straw is that REVOKE role1 FROM role2 is not idempotent 
MariaDB [(none)]> CREATE ROLE foo;
|
Query OK, 0 rows affected (0.01 sec)
|
|
|
MariaDB [(none)]> GRANT _bob_live_sg TO foo;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [(none)]> REVOKE ALL PRIVILEGES, GRANT OPTION FROM foo;
|
ERROR 1147 (42000): There is no such grant defined for user 'foo' on host '' on table 'catalog_attribute_option_shoes_shaft_width'
|
MariaDB [(none)]> REVOKE _bob_live_sg FROM foo;
|
ERROR 1962 (HY000): Cannot revoke role '_bob_live_sg' from: 'foo'.
|
MariaDB [(none)]> show grants for foo;
|
+-----------------------------+
|
| Grants for foo |
|
+-----------------------------+
|
| GRANT USAGE ON *.* TO 'foo' |
|
+-----------------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [(none)]> GRANT _bob_live_sg TO foo;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [(none)]> GRANT _bob_live_sg TO foo;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [(none)]> REVOKE _bob_live_sg FROM foo;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [(none)]> REVOKE _bob_live_sg FROM foo;
|
ERROR 1962 (HY000): Cannot revoke role '_bob_live_sg' from: 'foo'.
|
|
|
MariaDB [(none)]> GRANT foo TO pashev@localhost;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [(none)]> show grants for pashev@localhost;
|
+------------------------------------------------------------------------------------------------------------------------+
|
| Grants for pashev@localhost |
|
+------------------------------------------------------------------------------------------------------------------------+
|
| GRANT ops_sg TO 'pashev'@'localhost' |
|
| GRANT foo TO 'pashev'@'localhost' |
|
| GRANT PROCESS, SHOW DATABASES, REPLICATION CLIENT, SHOW VIEW ON *.* TO 'pashev'@'localhost' IDENTIFIED VIA unix_socket |
|
| GRANT SELECT, EXECUTE ON `mysql`.* TO 'pashev'@'localhost' |
|
+------------------------------------------------------------------------------------------------------------------------+
|
4 rows in set (0.00 sec)
|
|
|
MariaDB [(none)]> drop role foo;
|
Query OK, 0 rows affected (0.01 sec)
|
|
|
MariaDB [(none)]> show grants for pashev@localhost;
|
+------------------------------------------------------------------------------------------------------------------------+
|
| Grants for pashev@localhost |
|
+------------------------------------------------------------------------------------------------------------------------+
|
| GRANT ops_sg TO 'pashev'@'localhost' |
|
| GRANT PROCESS, SHOW DATABASES, REPLICATION CLIENT, SHOW VIEW ON *.* TO 'pashev'@'localhost' IDENTIFIED VIA unix_socket |
|
| GRANT SELECT, EXECUTE ON `mysql`.* TO 'pashev'@'localhost' |
|
+------------------------------------------------------------------------------------------------------------------------+
|
3 rows in set (0.00 sec)
|
|
|
MariaDB [(none)]> CREATE ROLE foo;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [(none)]> GRANT _bob_live_sg TO foo;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [(none)]> show grants for pashev@localhost;
|
+------------------------------------------------------------------------------------------------------------------------+
|
| Grants for pashev@localhost |
|
+------------------------------------------------------------------------------------------------------------------------+
|
| GRANT ops_sg TO 'pashev'@'localhost' |
|
| GRANT PROCESS, SHOW DATABASES, REPLICATION CLIENT, SHOW VIEW ON *.* TO 'pashev'@'localhost' IDENTIFIED VIA unix_socket |
|
| GRANT SELECT, EXECUTE ON `mysql`.* TO 'pashev'@'localhost' |
|
+------------------------------------------------------------------------------------------------------------------------+
|
3 rows in set (0.00 sec)
|
|
|
MariaDB [(none)]> PREPARE s FROM 'REVOKE _bob_live_sg FROM foo';
|
ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet
|
|