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

Access denied on newly created user until anonymous user dropped

    XMLWordPrintable

    Details

      Description

      Create a default install locally:

      initialization 10.3-1447b39475d2900066e0d058cee9b3fa6aec5016

      alias mariadblocal='mkdir -p /tmp/${PWD##*/}-datadir && scripts/mysql_install_db --no-defaults --srcdir=$OLDPWD --builddir=$PWD --datadir=/tmp/${PWD##*/}-datadir --verbose && sql/mysqld --no-defaults --skip-networking --datadir=/tmp/${PWD##*/}-datadir --socket=/tmp/${PWD##*/}.sock --plugin-dir=${PWD}/mysql-test/var/plugins/ --verbose'
      alias mc='client/mysql --socket=/tmp/${PWD##*/}.sock'
      $ mariadblocal 
      Installing MariaDB/MySQL system tables in '/tmp/build-mariadb-server-10.3-datadir' ...
      2021-05-17 16:18:23 0 [Note] /home/dan/repos/build-mariadb-server-10.3/sql/mysqld (mysqld 10.3.30-MariaDB) starting as process 93705 ...
      2021-05-17 16:18:23 0 [Note] InnoDB: Using Linux native AIO
      2021-05-17 16:18:23 0 [Note] InnoDB: The first innodb_system data file 'ibdata1' did not exist. A new tablespace will be created!
      2021-05-17 16:18:23 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
      2021-05-17 16:18:23 0 [Note] InnoDB: Uses event mutexes
      2021-05-17 16:18:23 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
      2021-05-17 16:18:23 0 [Note] InnoDB: Number of pools: 1
      2021-05-17 16:18:23 0 [Note] InnoDB: Using SSE2 crc32 instructions
      2021-05-17 16:18:23 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
      2021-05-17 16:18:23 0 [Note] InnoDB: Completed initialization of buffer pool
      2021-05-17 16:18:23 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
      2021-05-17 16:18:23 0 [Note] InnoDB: Setting file './ibdata1' size to 12 MB. Physically writing the file full; Please wait ...
      2021-05-17 16:18:23 0 [Note] InnoDB: File './ibdata1' size is now 12 MB.
      2021-05-17 16:18:23 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 50331648 bytes
      2021-05-17 16:18:23 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 50331648 bytes
      2021-05-17 16:18:23 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
      2021-05-17 16:18:23 0 [Note] InnoDB: New log files created, LSN=44897
      2021-05-17 16:18:23 0 [Note] InnoDB: Doublewrite buffer not found: creating new
      2021-05-17 16:18:23 0 [Note] InnoDB: Doublewrite buffer created
      2021-05-17 16:18:23 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
      2021-05-17 16:18:23 0 [Note] InnoDB: Creating foreign key constraint system tables.
      2021-05-17 16:18:23 0 [Note] InnoDB: Creating tablespace and datafile system tables.
      2021-05-17 16:18:23 0 [Note] InnoDB: Creating sys_virtual system tables.
      2021-05-17 16:18:23 0 [Note] InnoDB: Creating shared tablespace for temporary tables
      2021-05-17 16:18:23 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
      2021-05-17 16:18:23 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
      2021-05-17 16:18:23 0 [Note] InnoDB: Waiting for purge to start
      2021-05-17 16:18:23 0 [Note] InnoDB: 10.3.30 started; log sequence number 0; transaction id 7
      OK
      2021-05-17 16:18:25 0 [Note] sql/mysqld (mysqld 10.3.30-MariaDB) starting as process 93737 ...
      2021-05-17 16:18:25 0 [Note] InnoDB: Using Linux native AIO
      2021-05-17 16:18:25 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
      2021-05-17 16:18:25 0 [Note] InnoDB: Uses event mutexes
      2021-05-17 16:18:25 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
      2021-05-17 16:18:25 0 [Note] InnoDB: Number of pools: 1
      2021-05-17 16:18:25 0 [Note] InnoDB: Using SSE2 crc32 instructions
      2021-05-17 16:18:25 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
      2021-05-17 16:18:25 0 [Note] InnoDB: Completed initialization of buffer pool
      2021-05-17 16:18:25 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
      2021-05-17 16:18:25 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
      2021-05-17 16:18:25 0 [Note] InnoDB: Creating shared tablespace for temporary tables
      2021-05-17 16:18:25 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
      2021-05-17 16:18:25 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
      2021-05-17 16:18:25 0 [Note] InnoDB: Waiting for purge to start
      2021-05-17 16:18:25 0 [Note] InnoDB: 10.3.30 started; log sequence number 1625443; transaction id 20
      2021-05-17 16:18:25 0 [Note] InnoDB: Loading buffer pool(s) from /tmp/build-mariadb-server-10.3-datadir/ib_buffer_pool
      2021-05-17 16:18:25 0 [Note] Plugin 'FEEDBACK' is disabled.
      2021-05-17 16:18:25 0 [Note] InnoDB: Buffer pool(s) load completed at 210517 16:18:25
      2021-05-17 16:18:25 0 [Note] Reading of all Master_info entries succeeded
      2021-05-17 16:18:25 0 [Note] Added new Master_info '' to hash table
      2021-05-17 16:18:25 0 [Note] sql/mysqld: ready for connections.
      Version: '10.3.30-MariaDB'  socket: '/tmp/build-mariadb-server-10.3.sock'  port: 0  Source distribution
      2021-05-17 16:19:04 9 [Warning] Access denied for user 'theuser'@'localhost' (using password: YES)
      2021-05-17 16:19:24 10 [Warning] Access denied for user 'theuser'@'localhost' (using password: YES)
      2021-05-17 16:19:41 11 [Warning] Access denied for user 'theuser'@'localhost' (using password: YES)
      

      create a user

       
      +------+-----------------------+
      | user | host                  |
      +------+-----------------------+
      | root | 127.0.0.1             |
      | root | ::1                   |
      |      | localhost             |
      | root | localhost             |
      |      | localhost.localdomain |
      | root | localhost.localdomain |
      +------+-----------------------+
      6 rows in set (0.003 sec)
       
      MariaDB [(none)]>  CREATE USER 'theuser'@'%' IDENTIFIED BY 'mypass';
      Query OK, 0 rows affected (0.001 sec)
       
      MariaDB [(none)]> select user,host from mysql.user;
      +---------+-----------------------+
      | user    | host                  |
      +---------+-----------------------+
      | theuser | %                     |
      | root    | 127.0.0.1             |
      | root    | ::1                   |
      |         | localhost             |
      | root    | localhost             |
      |         | localhost.localdomain |
      | root    | localhost.localdomain |
      +---------+-----------------------+
      7 rows in set (0.001 sec)
      

      attempt access

      $ mc -u theuser -pmypass
      ERROR 1045 (28000): Access denied for user 'theuser'@'localhost' (using password: YES)
       
      ~/repos/build-mariadb-server-10.3 
      $ mc -u theuser -pmypass
      ERROR 1045 (28000): Access denied for user 'theuser'@'localhost' (using password: YES)
       
      ~/repos/build-mariadb-server-10.3 
      $ mc -u theuser -pmypass
      ERROR 1045 (28000): Access denied for user 'theuser'@'localhost' (using password: YES)
       
      ~/repos/build-mariadb-server-10.3 
      $ mc -u theuser -pmypass
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 12
      Server version: 10.3.30-MariaDB Source distribution
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [(none)]> \s
      --------------
      client/mysql  Ver 15.1 Distrib 10.3.30-MariaDB, for Linux (x86_64) using readline 5.1
       
      Connection id:		12
      Current database:	
      Current user:		theuser@localhost
      SSL:			Not in use
      Current pager:		stdout
      Using outfile:		''
      Using delimiter:	;
      Server:			MariaDB
      Server version:		10.3.30-MariaDB Source distribution
      Protocol version:	10
      Connection:		Localhost via UNIX socket
      Server characterset:	latin1
      Db     characterset:	latin1
      Client characterset:	utf8
      Conn.  characterset:	utf8
      UNIX socket:		/tmp/build-mariadb-server-10.3.sock
      Uptime:			6 min 24 sec
       
      Threads: 7  Questions: 12  Slow queries: 0  Opens: 18  Flush tables: 1  Open tables: 11  Queries per second avg: 0.031
      

      The following was executed one at a time after the first denial:

      remediation

      MariaDB [(none)]> drop user root@'localhost.localdomain';
      Query OK, 0 rows affected (0.001 sec)
       
      MariaDB [(none)]> drop user ''@'localhost.localdomain';
      Query OK, 0 rows affected (0.001 sec)
       
      MariaDB [(none)]> drop user ''@'localhost';
      Query OK, 0 rows affected (0.001 sec)
      
      

      Also tested 10.2-1447b39475d2900066e0d058cee9b3fa6aec5016

        Attachments

          Activity

            People

            Assignee:
            serg Sergei Golubchik
            Reporter:
            danblack Daniel Black
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration