Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-9691

Want REVOKE ALL ROLES

Details

    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

          People

            cvicentiu Vicențiu Ciorbaru
            ip1981 Igor Pashev
            Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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