Shulou(Shulou.com)06/01 Report--

By default, the rows in the mysql.db table indicate that any user can access the test database and the database at the beginning of test_. The value of the User field for these rows is blank, indicating that any user is matched. This means that these databases (test databases and databases at the beginning of test_) can be used by any user by default (even those who do not have permissions).

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 a lot of permissions on the test database and the database at the beginning of test_ (the above permission is Y)

The following verifies the above 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 read-only users to connect to mysql

Mysql > select user ()

+-+

| | user () |

+-+

| | select@localhost |

+-+

1 row in set (0.00 sec)

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

+-

| | Grants for select@localhost |

+-

| | GRANT USAGE ON *. * TO 'select'@'localhost' IDENTIFIED BY PASSWORD' * 852200EDF18814F8BFC1F1DC816AAC4152D8262E'|

| | GRANT SELECT ON `yujx`.`t`TO 'select'@'localhost' |

+-

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 library

Mysql > use test

Database changed

# you can create tables

Mysql > create table t (x int)

Query OK, 0 rows affected (0.01 sec)

# you can use insert table

Mysql > insert into t select 1

Query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

# you can drop database

Mysql > drop database test

Query OK, 1 row affected (0.01sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | test_a |

| | yujx |

+-+

3 rows in set (0.00 sec)

# also applies to libraries at the beginning of 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 a

Query OK, 0 rows affected (0.01 sec)

Mysql > drop database test_a

Query OK, 0 rows affected (0.00 sec)

# create 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 a

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

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

If you don't want users with arbitrary permissions (even read-only permissions) to manipulate test databases or databases that start with test_, you can delete the test-related rows in its mysql.db table, as follows:

Shell > mysql-u root-p

Enter password: (enter root password here)

Mysql > DELETE FROM mysql.db WHERE Db LIKE 'test%'

Mysql > FLUSH PRIVILEGES

# use read-only user actions again

# it is no longer possible to manipulate test-related databases as follows

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_a'

So far, you can see that by default, the mysql.db table in the initialized mysql environment contains 2 rows of test database-related configuration by default, so that any user can manipulate the database at the beginning of test or test_ at will. If you want to avoid this problem, you can directly drop test the database.

With regard to this phenomenon, you may need to pay attention to:

1. Never use a test database or create a database at the beginning of test_ to store business data in a formal environment.

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

3. If you want to avoid the above problems completely, you can delete the data related to test in mysql.db. Refer to the above.

Reference link:

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.