Default MySQL test Database Permissions

Default MySQL test Database Permissions

By default, the mysql. db table contains rows that allow any user to access databases starting with "test" and "test. The value of the User field in these rows is null, indicating matching any User. This means that these databases (databases starting with test _ and those starting with test _) can be used by any user by default (even if they do not have permissions ).

The default data of the table mysql. db is as follows:

Mysql> select * from mysql. db \ 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)

As you can see, any user has great permissions on the database test and the database test _ (the preceding permission is Y)

Verify the preceding Permissions

# Create a read-only account

Mysql> grant select on yujx. t to 'select' @ 'localhost' identified by 'select ';

Query OK, 0 rows affected (0.00 sec)

Mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

# Use a read-only user to connect to mysql

Mysql> select user ();

+ ------------------ +

| User () |

+ ------------------ +

| Select @ localhost |

+ ------------------ +

1 row in set (0.00 sec)

Mysql> show grants for 'select' @ 'localhost ';

+ Response ---------------------------------------------------------------------------------------------------------

| Grants for select @ localhost |

+ Response ---------------------------------------------------------------------------------------------------------

| Grant usage on *. * TO 'select' @ 'localhost' identified by password' * 852200edf18814f8bfc1f1dc816aac%2d8262e'

| Grant select on 'yujx'. 'T' TO 'select' @ 'localhost' |

+ Response -------------------------------------------------------------------------------------------------

2 rows in set (0.00 sec)

Mysql> show databases;

+ -------------------- +

| Database |

+ -------------------- +

| Information_schema |

| Test |

| Test_a |

| Yujx |

+ -------------------- +

4 rows in set (0.00 sec)

# Operate the test Database

Mysql> use test;

Database changed

# Tables can be created

Mysql> create table t (x int );

Query OK, 0 rows affected (0.01 sec)

# Insert tables

Mysql> insert into t select 1;

Query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

# Drop database

Mysql> drop database test;

Query OK, 1 row affected (0.01 sec)

Mysql> show databases;

+ -------------------- +

| Database |

+ -------------------- +

| Information_schema |

| Test_a |

| Yujx |

+ -------------------- +

3 rows in set (0.00 sec)

# Databases starting with test _

Mysql> use test_a

Database changed

Mysql> create table a (x int );

Query OK, 0 rows affected (0.01 sec)

Mysql> show tables;

+ ------------------ +

| Tables_in_test_a |

+ ------------------ +

| A |

+ ------------------ +

1 row in set (0.00 sec)

Mysql> drop table;

Query OK, 0 rows affected (0.01 sec)

Mysql> drop database test_a;

Query OK, 0 rows affected (0.00 sec)

# Creating a database

# Any dbname that starts with "test" can be created successfully.

Mysql> create database test;

Query OK, 1 row affected (0.00 sec)

Mysql> create database test_a;

Query OK, 1 row affected (0.00 sec)

Mysql> create database test_ B;

Query OK, 1 row affected (0.00 sec)

Mysql> create database;

ERROR 1044 (42000): Access denied for user 'select' @ 'localhost' to database 'A'

# Delete from mysql. db where db like 'test %'

If you do not want users with any permissions (even if they only have read-only permissions) to operate the test database or database named after test _, you can delete its mysql. test-related rows in the db table:

Shell> mysql-u root-p

Enter password: (enter root password here)

Mysql> delete from mysql. db WHERE Db LIKE 'test % ';

Mysql> flush privileges;

# Use a read-only user again

# As follows, test-related databases cannot be operated at will

Mysql> select user ();

+ ------------------ +

| User () |

+ ------------------ +

| Select @ localhost |

+ ------------------ +

Mysql> show databases;

+ -------------------- +

| Database |

+ -------------------- +

| Information_schema |

| Yujx |

+ -------------------- +

2 rows in set (0.00 sec)

Mysql> create database test;

ERROR 1044 (42000): Access denied for user 'select' @ 'localhost' to database 'test'

Mysql> create database test_a;

ERROR 1044 (42000): Access denied for user 'select' @ 'localhost' to database' test _'

Now, we can see that mysql is initialized in the mysql environment by default. by default, the db table contains two rows of test database-related configurations, so that any user can operate the database starting with test or test _ at will. If you want to avoid this problem, you can directly drop the test database.

You may need to pay attention to this issue:

1. In the official environment, never use the test database or create a database starting with test _ to store business data.

2. When testing and verifying user permissions, never go to the test database, which may mislead you

3. If you want to completely avoid the preceding problems, delete the test-related data in mysql. db. Refer to the preceding section.

Reference: https://dev.mysql.com/doc/refman/5.6/en/default-privileges.html

This article permanently updates the link address: