Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
Description
Currently, mysql_install_db provides default access to the test database by inserting some rows into the mysql.db table for the ''@'%' user account, but it does not insert any rows into the mysql.user table for that user account. For example:
MariaDB [(none)]> SELECT * FROM mysql.user WHERE User='' AND Host='%'\G
|
Empty set (0.00 sec)
|
|
|
MariaDB [(none)]> SELECT * FROM mysql.db WHERE User='' AND Host='%'\G
|
*************************** 1. row ***************************
|
Host: %
|
Db: test
|
User:
|
Select_priv: Y
|
Insert_priv: Y
|
Update_priv: Y
|
Delete_priv: Y
|
Create_priv: Y
|
Drop_priv: Y
|
Grant_priv: N
|
References_priv: Y
|
Index_priv: Y
|
Alter_priv: Y
|
Create_tmp_table_priv: Y
|
Lock_tables_priv: Y
|
Create_view_priv: Y
|
Show_view_priv: Y
|
Create_routine_priv: Y
|
Alter_routine_priv: N
|
Execute_priv: N
|
Event_priv: Y
|
Trigger_priv: Y
|
*************************** 2. row ***************************
|
Host: %
|
Db: test\_%
|
User:
|
Select_priv: Y
|
Insert_priv: Y
|
Update_priv: Y
|
Delete_priv: Y
|
Create_priv: Y
|
Drop_priv: Y
|
Grant_priv: N
|
References_priv: Y
|
Index_priv: Y
|
Alter_priv: Y
|
Create_tmp_table_priv: Y
|
Lock_tables_priv: Y
|
Create_view_priv: Y
|
Show_view_priv: Y
|
Create_routine_priv: Y
|
Alter_routine_priv: N
|
Execute_priv: N
|
Event_priv: Y
|
Trigger_priv: Y
|
2 rows in set (0.00 sec)
|
These rows are currently inserted by the scripts/mysql_test_db.sql script:
https://github.com/MariaDB/server/blob/mariadb-10.4.8/scripts/mysql_test_db.sql#L18
This behavior is apparently an artifact of MySQL 3.22, which implemented privileges prior to the implementation of the GRANT statement.
The effect of this is that mysql_install_db creates privileges for the ''@'%' user account, but the user account doesn't really exist from the perspective of other DCL statements like GRANT, CREATE USER, ALTER USER, and DROP USER.
If someone tries to actually create a ''@'%' user account, then they will see errors that are difficult to interpret. For example:
MariaDB [(none)]> CREATE USER ''@'%';
|
ERROR 1396 (HY000): Operation CREATE USER failed for ''@'%'
|
We should probably fix scripts/mysql_test_db.sql, so that it creates a row in the mysql.user table for the ''@'%' user account.
For now, this can be worked around by deleting the row in the mysql.db table and then executing FLUSH PRIVILEGES. For example:
DELETE FROM mysql.db WHERE User='' AND Host='%';
|
FLUSH PRIVILEGES;
|
And then the account can be created:
MariaDB [(none)]> CREATE USER ''@'%';
|
Query OK, 0 rows affected (0.01 sec)
|
This is documented here:
https://mariadb.com/kb/en/library/create-user/#fixing-a-legacy-default-anonymous-account
Attachments
Issue Links
- blocks
-
MDEV-20259 mysql_secure_installation should use DDL and DCL instead of DML
-
- Open
-
- relates to
-
-
- Closed
-
- links to
Activity
You cannot create a user if there are already some privileges granted to this user.
And in the default setup, ''@'%' has all privileges on test.*.
Oh, interesting. You're right, this works if I delete those privileges:
MariaDB [(none)]> CREATE USER ''@'%';
|
ERROR 1396 (HY000): Operation CREATE USER failed for ''@'%'
|
MariaDB [(none)]> SELECT User, Host FROM mysql.db;
|
+------+------+
|
| User | Host |
|
+------+------+
|
| | % |
|
| | % |
|
+------+------+
|
2 rows in set (0.01 sec)
|
|
|
MariaDB [(none)]> DELETE FROM mysql.db WHERE User='' AND Host='%';
|
Query OK, 2 rows affected (0.00 sec)
|
|
|
MariaDB [(none)]> FLUSH PRIVILEGES;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [(none)]> CREATE USER ''@'%';
|
Query OK, 0 rows affected (0.01 sec)
|
Perhaps we should make it consistent and create a matching row in mysql.user too.
That sounds like a reasonable fix to me.
I have clarified the docs to explain this. I agree a matching row in mysql.user makes sense. This bug can either be renamed, or closed and a new task created.
Hmm. If mysql_install_db will create such a user, it'll have to create it locked, otherwise it'll allow anonymous passwordless remote connections to the server.
But even if locked, it'll be a catch-all account, so instead for an unexistent user name the error will be not "wrong password" anymore, but "account locked". I think that might be quite unexpected.
Two other options:
- not to create test db with everybody's full access to it. This is what Debian is doing for many years already
- use
MDEV-5215(when it's implemented) instead of 3.22 pre-grant method for allowing everyone to access test db
use
MDEV-5215(when it's implemented) instead of 3.22 pre-grant method for allowing everyone to access test db
I created MDEV-20947 to track that idea.
While waiting for MDEV-5215 and MDEV-20947 how about giving access to the test databases to anonymous users only? 10.6?
diff --git a/scripts/mysql_test_db.sql b/scripts/mysql_test_db.sql
|
index 9f8a0cf604c..edf1242c6c5 100644
|
--- a/scripts/mysql_test_db.sql
|
+++ b/scripts/mysql_test_db.sql
|
@@ -15,17 +15,12 @@
|
|
CREATE DATABASE IF NOT EXISTS test CHARACTER SET latin1 COLLATE latin1_swedish_ci;
|
|
--- Fill "db" table with default grants for anyone to
|
--- access database 'test' and 'test_%' if "db" table didn't exist
|
-CREATE TEMPORARY TABLE tmp_db LIKE db;
|
-INSERT INTO tmp_db VALUES ('%','test','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y','Y','Y');
|
-INSERT INTO tmp_db VALUES ('%','test\_%','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y','Y','Y');
|
-INSERT INTO db SELECT * FROM tmp_db WHERE @had_db_table=0;
|
-DROP TABLE tmp_db;
|
-
|
-- Anonymous user with no privileges.
|
CREATE TEMPORARY TABLE tmp_user_anonymous LIKE global_priv;
|
INSERT INTO tmp_user_anonymous (host,user) VALUES ('localhost','');
|
INSERT INTO tmp_user_anonymous (host,user) SELECT @current_hostname,'' FROM dual WHERE @current_hostname != 'localhost';
|
INSERT INTO global_priv SELECT * FROM tmp_user_anonymous WHERE @had_user_table=0;
|
+-- access database 'test' and 'test_%' for anonymous users if "db" table exists
|
+INSERT INTO db SELECT host,'test',user,'Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y','Y','Y' FROM tmp_user_anonymous WHERE @had_db_table=0 AND @had_db_table=0;
|
+INSERT INTO db SELECT host,'test\_%',user,'Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y','Y','Y' FROM tmp_user_anonymous WHERE @had_db_table=0 AND @had_db_table=0;
|
DROP TABLE tmp_user_anonymous;
|
+ mysql_secure_installation changes to cope with old and new versions.
This is neither.
You cannot create a user if there are already some privileges granted to this user.
And in the default setup, ''@'%' has all privileges on test.*.
This is how it always was, even in 3.22, before GRANT statement was implemented. So it's something that is not fully compatible with GRANT and cannot be created with GRANT — there is no row for ''@'%' in mysql.user table, but there is such a row in mysql.db table.
Perhaps we should make it consistent and create a matching row in mysql.user too.