[MDEV-25688] Access denied on newly created user until anonymous user dropped Created: 2021-05-17  Updated: 2021-05-20  Resolved: 2021-05-20

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System
Affects Version/s: 10.2.38, 10.3.29
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Daniel Black Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None


 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

 Comments   
Comment by Sergei Golubchik [ 2021-05-20 ]

https://mariadb.com/kb/en/troubleshooting-connection-issues/#localhost-and

Generated at Thu Feb 08 09:39:35 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.