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

SET ROLE returning ERROR 1959 Invalid role specification for valid role

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.12
    • 10.0.13
    • None
    • None
    • Centos 6.5

    Description

      Cannot "SET ROLE" to a correctly-defined role that has been granted to an account. Seems to affect any account not defined as "@localhost"

      Attachments

        Activity

          mnewcomb Michael Newcomb added a comment - - edited

          Executed as root, statements like:

          create role WB_CLIENT;
           
          grant execute on procedure badges.p_hubspotter_lookup to WB_CLIENT;
          grant execute on procedure badges.p_wristband_lookup to WB_CLIENT;
          grant execute on procedure badges.p_login to WB_CLIENT;
          grant execute on procedure badges.p_hubspotter_list to WB_CLIENT;
          grant execute on procedure badges.p_token_list to WB_CLIENT;
           
          ...
           
          grant WB_CLIENT to 'wristband_client'@'10.%';

          Log in as "wristband_client:"

          mysql --user=wristband_client '--password=[password]' --host=[host]
          Welcome to the MariaDB monitor.  Commands end with ; or \g.
          Your MariaDB connection id is 164
          Server version: 10.0.12-MariaDB MariaDB Server
           
          Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.
           
          Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
           
          MariaDB [(none)]> use badges;
          Database changed
          MariaDB [badges]> show grants;
          +--------------------------------------------------------------------------------------------------------------------------------------+
          | Grants for wristband_client@10.%                                                                                                     |
          +--------------------------------------------------------------------------------------------------------------------------------------+
          | GRANT WB_CLIENT TO 'wristband_client'@'10.%'                                                                                         |
          | GRANT CREATE TEMPORARY TABLES ON *.* TO 'wristband_client'@'10.%' IDENTIFIED BY PASSWORD '...' |
          | GRANT SELECT ON `mysql`.`proc` TO 'wristband_client'@'10.%'                                                                          |
          +--------------------------------------------------------------------------------------------------------------------------------------+
          3 rows in set (0.02 sec)
           
          MariaDB [badges]> set role WB_CLIENT;
          ERROR 1959 (OP000): Invalid role specification `WB_CLIENT`.
          MariaDB [badges]> select current_user();
          +-----------------------+
          | current_user()        |
          +-----------------------+
          | wristband_client@10.% |
          +-----------------------+
          1 row in set (0.03 sec)
           
          MariaDB [badges]> SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES;
          +-----------------------+-----------+--------------+
          | GRANTEE               | ROLE_NAME | IS_GRANTABLE |
          +-----------------------+-----------+--------------+
          | wristband_client@10.% | WB_CLIENT | NO           |
          +-----------------------+-----------+--------------+
          1 row in set (0.03 sec)

          I have only ever been able to get SET ROLE to work if the account is defined as 'wristband_client'@'localhost'. Seen on two separate instances of MariaDB 10. This came from a freshly-installed instance (one week ago).

          mnewcomb Michael Newcomb added a comment - - edited Executed as root, statements like: create role WB_CLIENT;   grant execute on procedure badges.p_hubspotter_lookup to WB_CLIENT; grant execute on procedure badges.p_wristband_lookup to WB_CLIENT; grant execute on procedure badges.p_login to WB_CLIENT; grant execute on procedure badges.p_hubspotter_list to WB_CLIENT; grant execute on procedure badges.p_token_list to WB_CLIENT;   ...   grant WB_CLIENT to 'wristband_client'@'10.%'; Log in as "wristband_client:" mysql --user=wristband_client '--password=[password]' --host=[host] Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 164 Server version: 10.0.12-MariaDB MariaDB Server   Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.   MariaDB [(none)]> use badges; Database changed MariaDB [badges]> show grants; +--------------------------------------------------------------------------------------------------------------------------------------+ | Grants for wristband_client@10.% | +--------------------------------------------------------------------------------------------------------------------------------------+ | GRANT WB_CLIENT TO 'wristband_client'@'10.%' | | GRANT CREATE TEMPORARY TABLES ON *.* TO 'wristband_client'@'10.%' IDENTIFIED BY PASSWORD '...' | | GRANT SELECT ON `mysql`.`proc` TO 'wristband_client'@'10.%' | +--------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.02 sec)   MariaDB [badges]> set role WB_CLIENT; ERROR 1959 (OP000): Invalid role specification `WB_CLIENT`. MariaDB [badges]> select current_user(); +-----------------------+ | current_user() | +-----------------------+ | wristband_client@10.% | +-----------------------+ 1 row in set (0.03 sec)   MariaDB [badges]> SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES; +-----------------------+-----------+--------------+ | GRANTEE | ROLE_NAME | IS_GRANTABLE | +-----------------------+-----------+--------------+ | wristband_client@10.% | WB_CLIENT | NO | +-----------------------+-----------+--------------+ 1 row in set (0.03 sec) I have only ever been able to get SET ROLE to work if the account is defined as 'wristband_client'@'localhost'. Seen on two separate instances of MariaDB 10. This came from a freshly-installed instance (one week ago).

          MTR test case:

          create role r1;
          create user foo@'127.0.0.1';
          grant r1 to foo@'127.0.0.1';
           
          --connect (con1,127.0.0.1,foo,,)
          show grants;
          set role r1;

          elenst Elena Stepanova added a comment - MTR test case: create role r1; create user foo@ '127.0.0.1' ; grant r1 to foo@ '127.0.0.1' ;   --connect (con1,127.0.0.1,foo,,) show grants; set role r1;

          People

            serg Sergei Golubchik
            mnewcomb Michael Newcomb
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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