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

The "at" symbol (@) in user names breaks access to schemas

Details

    Description

      Default roles is set.

      MariaDB [(none)]> show grants for foo;
      +---------------------------------------------+
      | Grants for foo@%                            |
      +---------------------------------------------+
      | GRANT all_ph TO 'foo'@'%'                   |
      | GRANT USAGE ON *.* TO 'foo'@'%' REQUIRE SSL |
      +---------------------------------------------+
      2 rows in set (0.00 sec)
       
      MariaDB [(none)]> show grants for 'foo@bar';
      +-------------------------------------+
      | Grants for foo@bar@%                |
      +-------------------------------------+
      | GRANT all_ph TO 'foo@bar'@'%'       |
      | GRANT USAGE ON *.* TO 'foo@bar'@'%' |
      +-------------------------------------+
      2 rows in set (0.00 sec)
      

      1. You can't set default database (unless you have some DB-level privilege):

      (Wed, 23 Mar 2016 20:59:54 +0800)
      [pashev@cat:~]
      # mysql -ufoo bob_live_ph
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
       
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 156
      Server version: 10.1.11-MariaDB MariaDB Server
       
      Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [bob_live_ph]> Bye
       
      (Wed, 23 Mar 2016 21:00:32 +0800)
      [pashev@cat:~]
      # mysql -u'foo@bar' bob_live_ph
      ERROR 1044 (42000): Access denied for user 'foo@bar'@'%' to database 'bob_live_ph'
      

      2. You can't list databases you have any privilege on:

      (Wed, 23 Mar 2016 21:00:40 +0800)
      [pashev@cat:~]
      # mysql -u'foo@bar' -e 'show databases'
      +--------------------+
      | Database           |
      +--------------------+
      | information_schema |
      +--------------------+
       
      (Wed, 23 Mar 2016 21:01:43 +0800)
      [pashev@cat:~]
      # mysql -u'foo' -e 'show databases'
      +--------------------+
      | Database           |
      +--------------------+
      | bob_live_au        |
      | bob_live_hk        |
      | bob_live_id        |
      | bob_live_ph        |
      | bob_live_sg        |
      | bob_live_tw        |
      | information_schema |
      | oms_live_id        |
      | oms_live_ph        |
      | oms_live_sg        |
      +--------------------+
      

      According to https://mariadb.com/kb/en/mariadb/identifier-names/, @ (U+0040) is a valid character.

      Use case: email addresses as logins

      Attachments

        Issue Links

          Activity

            ip1981 Igor Pashev added a comment -

            Now I'm not sure the problem is in @:

            mysql> show databases;
            +--------------------+
            | Database           |
            +--------------------+
            | information_schema |
            +--------------------+
            1 row in set (0,26 sec)
             
            mysql> select count(1) from `bob_live_th`.`catalog_simple`;
            +----------+
            | count(1) |
            +----------+
            |   911170 |
            +----------+
            1 row in set (0,25 sec)
             
            mysql> use bob_live_th;
            ERROR 1044 (42000): Access denied for user 'gideon_voucher_retreival'@'%' to database 'bob_live_th'
            mysql> show tables in bob_live_th;
            ERROR 1044 (42000): Access denied for user 'gideon_voucher_retreival'@'%' to database 'bob_live_th'
            mysql> 
            

            ip1981 Igor Pashev added a comment - Now I'm not sure the problem is in @ : mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0,26 sec)   mysql> select count(1) from `bob_live_th`.`catalog_simple`; +----------+ | count(1) | +----------+ | 911170 | +----------+ 1 row in set (0,25 sec)   mysql> use bob_live_th; ERROR 1044 (42000): Access denied for user 'gideon_voucher_retreival'@'%' to database 'bob_live_th' mysql> show tables in bob_live_th; ERROR 1044 (42000): Access denied for user 'gideon_voucher_retreival'@'%' to database 'bob_live_th' mysql>
            ip1981 Igor Pashev added a comment -

            It's MDEV-9614!

            ip1981 Igor Pashev added a comment - It's MDEV-9614 !

            People

              Unassigned Unassigned
              ip1981 Igor Pashev
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.