Details
-
Task
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
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
|
|
Attachments
Activity
Roles were developed as close to SQL standard as possible, and from what I can see, there is no such thing as REVOKE ALL ROLES there:
https://mariadb.com/kb/en/sql-99/revoke-statement/
On the other hand, it would be definitely helpful if prepared statements could deal with roles, we have a task for that, MDEV-9443, I've added a note about REVOKE specifically.
Regarding the REVOKE statement not being idempotent, it's the same for regular privileges. I couldn't find SQL standard specifying it one way or another, but from safety reasons it seems to me it makes more sense to produce error – if you want to revoke a privilege/role and make a typo in the privilege/role name, it's better to get an error than stay under the impression that the statement did the job.
serg, could you please see the request and decide if there is anything that needs to be done here?
This needs more careful analysis, but perhaps "cannot revoke role" should be a warning, not an error. At least it seems that the standard says that revoking a not-granted privilege is a warning, not an error.
It doesn't seem like there is any standard way to revoke all roles, though. We can still implement it as a non-standard extension, but, I'd rather extend REVOKE ALL PRIVILEGES, GRANT OPTION to revoke all roles too.
Actually (as I noted in the description and just discovered it again )
REVOKE ALL PRIVILEGES, GRANT OPTION FROM foo; does revoke roles, but fails on non-existing grants (MDEV-5228).
MariaDB [mysql]> create role role1;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [mysql]> create role role2;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [mysql]> grant select on mysql.user to role1;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [mysql]> grant role1 to role2;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [mysql]> show grants for role2;
|
+-------------------------------------------+
|
| Grants for role2 |
|
+-------------------------------------------+
|
| GRANT role1 TO 'role2' |
|
| GRANT USAGE ON *.* TO 'role2' |
|
| GRANT USAGE ON *.* TO 'role1' |
|
| GRANT SELECT ON `mysql`.`user` TO 'role1' |
|
+-------------------------------------------+
|
4 rows in set (0.00 sec)
|
|
|
MariaDB [mysql]> select * from roles_mapping;
|
+-----------+---------+-------------+--------------+
|
| Host | User | Role | Admin_option |
|
+-----------+---------+-------------+--------------+
|
| localhost | mariadb | catalog_hk | Y |
|
| localhost | mariadb | role2 | Y |
|
| localhost | mariadb | catalog_all | Y |
|
| localhost | mariadb | catalog_sg | Y |
|
| localhost | mariadb | role1 | Y |
|
| | role2 | role1 | N |
|
+-----------+---------+-------------+--------------+
|
6 rows in set (0.00 sec)
|
|
|
MariaDB [mysql]> revoke all, grant option from role2;
|
ERROR 1147 (42000): There is no such grant defined for user 'role2' on host '' on table 'user'
|
Error (Code 1147): There is no such grant defined for user 'role2' on host '' on table 'user'
|
Error (Code 1269): Can't revoke all privileges for one or more of the requested users
|
MariaDB [mysql]> show grants for role2;
|
+-------------------------------+
|
| Grants for role2 |
|
+-------------------------------+
|
| GRANT USAGE ON *.* TO 'role2' |
|
+-------------------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [mysql]> select * from roles_mapping;
|
+-----------+---------+-------------+--------------+
|
| Host | User | Role | Admin_option |
|
+-----------+---------+-------------+--------------+
|
| localhost | mariadb | catalog_hk | Y |
|
| localhost | mariadb | role2 | Y |
|
| localhost | mariadb | catalog_all | Y |
|
| localhost | mariadb | catalog_sg | Y |
|
| localhost | mariadb | role1 | Y |
|
+-----------+---------+-------------+--------------+
|
5 rows in set (0.00 sec)
|
Finally, it turned out that REVOKE ALL PRIVILEGES, GRANT OPTION revokes all roles from users
and from roles, but also fails on roles as in MDEV-5228.
So the real issue is "REVOKE ALL PRIVILEGES, GRANT OPTION fails on roles".
MariaDB [mysql]> show grants for imagesync@'%';
|
+----------------------------------------------------------------------------------------------------------------------+
|
| Grants for imagesync@% |
|
+----------------------------------------------------------------------------------------------------------------------+
|
| GRANT catalog_all TO 'imagesync'@'%' |
|
| GRANT USAGE ON *.* TO 'imagesync'@'%' IDENTIFIED BY PASSWORD '*3F830F44A9487B772B695819F125EB0C7B4B921F' REQUIRE SSL |
|
+----------------------------------------------------------------------------------------------------------------------+
|
2 rows in set (0.00 sec)
|
|
|
MariaDB [mysql]> select * from roles_mapping where user='imagesync';
|
+------+-----------+-------------+--------------+
|
| Host | User | Role | Admin_option |
|
+------+-----------+-------------+--------------+
|
| % | imagesync | catalog_all | N |
|
+------+-----------+-------------+--------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [mysql]> revoke all,grant option from imagesync@'%';
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [mysql]> show grants for imagesync@'%';
|
+----------------------------------------------------------------------------------------------------------------------+
|
| Grants for imagesync@% |
|
+----------------------------------------------------------------------------------------------------------------------+
|
| GRANT USAGE ON *.* TO 'imagesync'@'%' IDENTIFIED BY PASSWORD '*3F830F44A9487B772B695819F125EB0C7B4B921F' REQUIRE SSL |
|
+----------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [mysql]> select * from roles_mapping where user='imagesync';
|
Empty set (0.00 sec)
|
|
Seems I can use
delete from roles_mapping where user='ops_sg';flush privileges;