mariadbMySQL
I know the general advice is never to use utf8, but instead to use utf8mb4. If that's the advice, then why do the system tables themselves use utf8?
You can see this in the MariaDB test suite, and and the MySQL test suite; or, by using SHOW CREATE TABLE mysql.db;
Please note what MySQL 5.0 Certification Study Guide
say in its bulletpoints on Page 498 Paragraph 6:
On Unix, MySQL comes with a mysql_secure_installation script that can perform several helpful security-related operations on your installation. The script has the following capabilities:
- Set a password for the root accounts
- Remove any remotely accessible root accounts.
- Remove the anonymous user accounts. This improves security because it prevents the possibility of anyone connecting to the MySQL server as root from a remote host. The results is that anyone who wants to connect as root must first be able to log in on the server host, which provides an additional barrier against attack.
- Remove the test database (If you remove the anonymous accounts, you might also want to remove the test database to which they have access).
To get rid of those bad entries, run this please:
DELETE FROM mysql.db WHERE SUBSTR(db,4) = 'test';
FLUSH PRIVILEGES;
As @DTest mentioned in his comment to the question, you can also run mysql_secure_installation to this for you.
If an anonymous user can login to MySQL remotely, a simply disk attack can be launched to hurt the mysql installation. Here is an example:
USE test
CREATE TABLE rolando_tb (a int);
INSERT INTO rolando_tb VALUES (1);
INSERT INTO rolando_tb SELECT a FROM rolando_tb;
INSERT INTO rolando_tb SELECT a FROM rolando_tb;
INSERT INTO rolando_tb SELECT a FROM rolando_tb;
INSERT INTO rolando_tb SELECT a FROM rolando_tb;
Run insert 30 times and you get a 7GB table
mysql.dbThe seriousness of securing the mysql installation has not been fully documented by MySQL AB, and I don't think Oracle is interested in doing so today.
It was suggested by @atxdba's comment that just running 'DROP DATABASE test;' should be the preferred method over touching mysql.db. Dropping the database named test simply removes the database that opens a conduit to a potential security hole.
Please take note of this query:
mysql> select user,host,db from mysql.db;
+------+------+---------+
| user | host | db |
+------+------+---------+
| | % | test |
| | % | test\_% |
+------+------+---------+
2 rows in set (0.09 sec)
Based on this, the following databases can be accessed fully by anonymous users:
While the following databases cannot be accessed fully by anonymous users:
Test is different from test in Linux-based systems, but it is still problem for MySQL running in Windows)You will have to remember this subtle rule based on the mysql.db table. If you do not remember this, creating a test database named test or a database name whose first 5 characters is test_ will reopen the same type of security hole.
The most secure way around having to remember these things is to run these lines after an initial installation:
DELETE FROM mysql.db WHERE SUBSTR(db,4) = 'test' AND user='';
FLUSH PRIVILEGES;
then any database with any name can have a proper authentication setup. You can still run these two lines anytime.
To openly demonstrate the danger of having anonymous users in mysql.db,
I would like to create a user that has only the usage privilege.
I will be using MySQL 5.5.12 on my Desktop
First, look at the mysql.db
mysql> select user,host,db from mysql.db;
+------+------+---------+
| user | host | db |
+------+------+---------+
| | % | test |
| | % | test\_% |
+------+------+---------+
2 rows in set (0.05 sec)
mysql>
According to this, any anonymous Joe can reach these databases.
I'll create a database test_mysqldb
mysql> create database test_mysqldb;
Query OK, 1 row affected (0.00 sec)
mysql> use test_mysqldb
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql>
Let's create an plain vanilla user called vanilla@localhost (no password)
mysql> CREATE USER vanilla@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR vanilla@localhost;
+---------------------------------------------+
| Grants for vanilla@localhost |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'vanilla'@'localhost' |
+---------------------------------------------+
1 row in set (0.00 sec)
mysql>
Next, from the DOS Command Line, let's connect to the mysql schema
C:\>mysql -uvanilla -Dmysql
ERROR 1044 (42000): Access denied for user 'vanilla'@'localhost' to database 'mysql'
C:\>
OK great. That's what I expected.
Next, from the DOS Command Line, let's connect to the test_mysqldb schema, create a table, and load it with numbers
C:\>mysql -uvanilla -Dtest_mysqldb
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.12-log MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE rolando_tb (a bigint unsigned);
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO rolando_tb VALUES (1);
Query OK, 1 row affected (0.06 sec)
mysql> INSERT INTO rolando_tb SELECT * FROM rolando_tb;
Query OK, 1 row affected (0.06 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> INSERT INTO rolando_tb SELECT * FROM rolando_tb;
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO rolando_tb SELECT * FROM rolando_tb;
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> INSERT INTO rolando_tb SELECT * FROM rolando_tb;
Query OK, 8 rows affected (0.06 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM rolando_tb;
+------+
| a |
+------+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
+------+
16 rows in set (0.00 sec)
mysql> SELECT database();
+--------------+
| database() |
+--------------+
| test_mysqldb |
+--------------+
1 row in set (0.00 sec)
mysql>
Did you see that? A user with USAGE privilege can create a table in a test database and fill it with data. This a clear and present danger. This is why I strongly recommend deleteing those test entries out of mysql.db to deter anonymous users from reaching test databases or accessing newly created test databases (via making a subfolder under the default datadir).
As a reminder, this is how you do it:
DELETE FROM mysql.db WHERE SUBSTR(db,4) = 'test' AND user='';
FLUSH PRIVILEGES;
To demonstrate that the DELETE FROM mysql.db WHERE SUBSTR(db,4) = 'test' AND user=''; actually works, I ran this on MySQL 5.6.13 today:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.13-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select db,user,host from mysql.db where LEFT(db,4)='test';
+---------+------+------+
| db | user | host |
+---------+------+------+
| test | | % |
| test\_% | | % |
+---------+------+------+
2 rows in set (0.43 sec)
mysql> delete from mysql.db where LEFT(db,4)='test';
Query OK, 2 rows affected (0.04 sec)
mysql> select db,user,host from mysql.db2 where LEFT(db,4)='test';
Empty set (0.00 sec)
mysql>
Just as a public service announcement, please run
DELETE FROM mysql.db WHERE SUBSTR(db,4) = 'test' AND user='';
FLUSH PRIVILEGES;
or just run mysql-secure-installation and put this potential danger to bed.
So it looks like when the server variables are set, they don't change the database that had already been created before. So what I did was to:
Best Answer
Since, utf8mb3 (also known as UTF8) can not store all languages like Chinese or Emoticons, it is recommended to use utf8mb4 when it comes to store application data.
For simple example, You want to extend your existing business into China. if you have used utf8mb4, your application is always ready to adopt your business extension while in case of utf8mb3(UTF-8), you need an character set upgrade to your application.
While storing system's data MySQL does not store information (supplementary character) which requires utf8mb4, this could be the reason, why MySQL itself is not using utf8mb4.