[MDEV-9691] Want REVOKE ALL ROLES - Jira

Details

Type: Task
Status: Open (View Workflow)
Priority: Minor
Resolution: Unresolved
Fix Version/s: None
Component/s: Authentication and Privilege System
Labels:
- foundation

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

Ascending order - Click to sort in descending order

Igor Pashev created issue - 2016-03-07 07:15

Igor Pashev made changes - 2016-03-07 07:15

Field	Original Value	New Value
Component/s		Authentication and Privilege System [ 13101 ]
Affects Version/s		10.1.11 [ 21202 ]

Igor Pashev made changes - 2016-03-07 07:18

Description

I'm used to declarative granting to users:

{noformat}
GRANT USAGE...
REVOKE ALL PRIVILEGES, GRANT OPTION FROM ...
GRANT <only these> ...
{noformat}

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).

So, I'm out of options. The last straw is that REVOKE role1 FROM role2 is not idempotent :-)

{noformat}
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)]> 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

{noformat}

I'm used to declarative granting to users:

{noformat}
GRANT USAGE...
REVOKE ALL PRIVILEGES, GRANT OPTION FROM ...
GRANT <only these> ...
{noformat}

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).

So, I'm out of options. The last straw is that REVOKE role1 FROM role2 is not idempotent :-)

{noformat}
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

{noformat}

Igor Pashev made changes - 2016-03-07 07:23

Description

I'm used to declarative granting to users:

{noformat}
GRANT USAGE...
REVOKE ALL PRIVILEGES, GRANT OPTION FROM ...
GRANT <only these> ...
{noformat}

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).

So, I'm out of options. The last straw is that REVOKE role1 FROM role2 is not idempotent :-)

{noformat}
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

{noformat}

I'm used to declarative granting to users:

{noformat}
GRANT USAGE...
REVOKE ALL PRIVILEGES, GRANT OPTION FROM ...
GRANT <only these> ...
{noformat}

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 :-)

{noformat}
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

{noformat}

Igor Pashev added a comment - 2016-03-07 09:21

Seems I can use

delete from roles_mapping where user='ops_sg';

flush privileges;

Igor Pashev added a comment - 2016-03-07 09:21 Seems I can use delete from roles_mapping where user='ops_sg'; flush privileges;

Elena Stepanova added a comment - 2016-03-07 13:16

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?

Elena Stepanova added a comment - 2016-03-07 13:16 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?

Elena Stepanova made changes - 2016-03-07 13:16

Affects Version/s	10.1.11 [ 21202 ]
Issue Type	Bug [ 1 ]	Task [ 3 ]

Sergei Golubchik added a comment - 2016-03-07 14:56

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.

Sergei Golubchik added a comment - 2016-03-07 14:56 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.

Igor Pashev added a comment - 2016-03-20 11:04 - edited

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)

Igor Pashev added a comment - 2016-03-20 20:27

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)

Igor Pashev added a comment - 2016-03-20 20:27 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)

Vicențiu Ciorbaru added a comment - 2016-03-21 20:20

Will work on this as soon as the next 10.1 sprint starts.

Vicențiu Ciorbaru added a comment - 2016-03-21 20:20 Will work on this as soon as the next 10.1 sprint starts.

Vicențiu Ciorbaru made changes - 2016-03-21 20:20

Assignee

Vicentiu Ciorbaru [ cvicentiu ]

Sergei Golubchik made changes - 2021-12-06 21:20

Workflow

MariaDB v3 [ 74485 ]

MariaDB v4 [ 130444 ]

Vlad Radu made changes - 2025-01-24 10:30

Epic Link

MDBF-920 [ 132513 ]

Vlad Radu made changes - 2025-01-24 10:31

Epic Link

MDBF-920 [ 132513 ]

Vlad Radu made changes - 2025-02-06 07:19

Labels

foundation

People

Assignee:: Vicențiu Ciorbaru

Reporter:: Igor Pashev

Votes:: 1 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2016-03-07 07:15

Updated:: 2025-02-06 07:19

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration