[MDEV-30685] Possible bug in CREATE USER Created: 2023-02-19  Updated: 2023-02-20  Resolved: 2023-02-20

Status: Closed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.6.12
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: acsfer Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None


 Description    

MariaDB [(none)]> CREATE USER 'blocklists
 
    '> ,
 
    '> ;
 
    '> ;
 
    '> ';
 
Query OK, 0 rows affected (0.011 sec)
 
 
 
MariaDB [(none)]> DROP USER blocklists;
 
ERROR 1396 (HY000): Operation DROP USER failed for 'blocklists'@'%'
 
 
 
MariaDB [(none)]> SELECT user,host FROM mysql.user;
 
+-------------------+-----------+
 
| User              | Host      |
 
+-------------------+-----------+
 
| blocklists
 
,
 
;
 
;
 
 | %         |
 
| mariadb.sys       | localhost |
 
| mysql             | localhost |
 
| root              | localhost |
 
+-------------------+-----------+
 
4 rows in set (0.001 sec)
 
 
 
MariaDB [(none)]> DROP USER 'blocklists%'@'%';
 
ERROR 1396 (HY000): Operation DROP USER failed for 'blocklists%'@'%'
 
 
 
MariaDB [(none)]> DROP USER 'blocklists
 
,
 
;
 
;'@'%';
 
ERROR 1396 (HY000): Operation DROP USER failed for 'blocklists\n,\n;\n;'@'%'
 
 
 
MariaDB [(none)]> DROP USER 'blocklists,;;'@'%';
 
ERROR 1396 (HY000): Operation DROP USER failed for 'blocklists,;;'@'%'



 Comments   
Comment by Daniel Black [ 2023-02-19 ]

The user isn't called blocklist its:

blocklists
 
,
 
;
 
;'@'%'

Newlines, , and ;; are significant.

What did you expect to happen?

Comment by acsfer [ 2023-02-19 ]

1st, user shouldn't be created with this format.
2nd, impossible to delete it (at least i can't find a way).

Comment by Daniel Black [ 2023-02-20 ]

It was a quoted username so was accepted as is.

To cancel a SQL statement rather than execute it use \c:

MariaDB [(none)]> CREATE USER 'blocklist
 
    '> ,
 
    '> ;
 
    '> '\c
 
MariaDB [(none)]> 
 

On deletion the following are options:

Use the same sequence you used to create it.

MariaDB [(none)]>  CREATE USER 'blocklists
 
    '> ,
 
    '> ;
 
    '> ;
 
    '> ';
 
Query OK, 0 rows affected (0.001 sec)
 
 
 
MariaDB [(none)]> DROP USER 'blocklist
 
    '> ,
 
    '> ;
 
    '> ;
 
    '> ';
 
Query OK, 0 rows affected (0.001 sec)

If you get quite stuck on the exact username:

MariaDB [(none)]> select user into @user from mysql.global_priv where user like 'block%';
 
Query OK, 1 row affected (0.001 sec)
 
 
 
MariaDB [(none)]> execute immediate concat('drop user ', quote(@user));
 
Query OK, 0 rows affected (0.001 sec)

Generated at Thu Feb 08 10:18:08 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.