Shulou(Shulou.com)05/31 Report--

How to understand the MySQL management foundation of security, access control and permissions, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

The MySQL permissions system ensures that all users only perform allowed actions. When connecting to a MySQL server, the identity of the user is determined by the host on which the user resides and the user name used. When a user makes a request after a connection, the system grants permissions based on their identity and the action to be performed.

1.MySQL Authorization form

1) user table

Mysql > select host, user, password from mysql.user

+-- +

| | host | user | password | |

+-- +

| | localhost | root | * 25B0A985780CE19647D8E41C2B50E7FB300EA6E8 |

| |% | root | * 25B0A985780CE19647D8E41C2B50E7FB300EA6E8 |

+-- +

2 rows in set (0.00 sec)

Note: "%" is used as a wildcard, which means that access is allowed as root from any host. In other authorization tables except the db table, the empty host value is the same as "%".

[@ more@] MySQL authorization tables generally contain two types of fields: scope fields and permission fields. The host, user, and password of the user table seen above are scope fields, as well as some permission fields whose names end with "_ priv" to specify the permissions the user has. The permissions given to users here apply to every database in the system, so they are generally set to N, while the host and db tables are used for more elaborate permission settings. The remaining fields are related to SSL encryption and user resource restrictions.

2) db and host tables

The db table has three range fields, host, db, and user, which specify that the record is for a user to connect to a database from a host; the remaining fields are permission fields.

Host has two scope fields, host and db, and the rest are permission fields. If the host field recorded in the db table is left blank, the MySQL server obtains the corresponding hostname from the host table when verifying user permissions. The host table is not affected by GRANT and REVOKE statements. Most MySQL installations do not need to use this table at all.

3) tables_priv and columns_ private tables

These two tables can restrict access to specific tables in the database and specific columns in the tables, respectively.

When MySQL needs to decide whether to allow a user to perform a database operation, first check whether the user has sufficient permissions in the user table, and if not, look at the db and host tables. Some administrative operations, such as RELOAD and PROCESS, involve the entire system, and only the user table has corresponding columns.

You can manually modify the contents of the authorization table for permission settings (but this is not recommended for the tables_priv and columns_priv tables), and you need to reload the authorization table using the FLUSH PRIVILEGES statement, mysqladmin flush-privileges, or mysqladmin reload command.

two。 Grant and reclaim permissions

You typically use GRANT and REVOKE commands to grant and reclaim user permissions, and here are some examples to demonstrate their use.

First, create a ggyy user that connects to the MySQL server locally:

Mysql > create user ggyy@localhost identified by 'password'

Query OK, 0 rows affected (0.00 sec)

Mysql > select host, user from mysql.user

+-+ +

| | host | user |

+-+ +

| |% | root |

| | localhost | ggyy |

| | localhost | root |

+-+ +

3 rows in set (0.00 sec)

You can now use the ggyy user to connect to the server, but you don't have access to the database yet.

C: > mysql-u ggyy-p

Enter password: *

Welcome to the MySQL monitor. Commands end with; or g.

Your MySQL connection id is 18

Server version: 5.1.34-community MySQL Community Server (GPL)

Type 'help;' or' h' for help. Type 'c'to clear the current input statement.

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

+-+

1 row in set (0.00 sec)

The ggyy user is granted query permissions on the ggyy database as follows:

Mysql > select current_user ()

+-+

| | current_user () |

+-+

| | root@localhost |

+-+

1 row in set (0.00 sec)

Mysql > grant select on ggyy.* to ggyy@localhost

Query OK, 0 rows affected (0.00 sec)

Mysql > show grants for ggyy@localhost

+-+

| | Grants for ggyy@localhost |

+-+

| | GRANT USAGE ON *. * TO 'ggyy'@'localhost' IDENTIFIED BY PASSWORD' * 484FFAA42C12F40931C794D33A11B7F075B91467' |

| | GRANT SELECT ON `ggyy`. * TO 'ggyy'@'localhost' |

+-+

2 rows in set (0.00 sec)

Mysql > select host, db, user, select_priv, insert_priv, update_priv, delete_priv from mysql.db

+-+

| | host | db | user | select_priv | insert_priv | update_priv | delete_priv | |

+-+

| | localhost | ggyy | ggyy | Y | N | N | N | N |

+-+

1 row in set (0.00 sec)

Mysql > select * from mysql.tables_priv

Empty set (0.00 sec)

Use the ggyy user to view the contents of the ggyy database:

Mysql > select current_user ()

+-+

| | current_user () |

+-+

| | ggyy@localhost |

+-+

1 row in set (0.00 sec)

Mysql > use ggyy

Database changed

Mysql > show tables

+-+

| | Tables_in_ggyy |

+-+

| | blob_text_test |

| | char_test |

| | contact |

| | date_time_test |

| | float_test |

| | groups1 |

| | groups2 |

| | int_test |

| | members |

| | members_temp |

| | processes1 |

| | test_crttb |

| | test_crttb2 |

| | test_crttb3 |

| | test_crttb4 |

| | test_crttb5 |

| | ts_dt_test |

| | users1 |

+-+

18 rows in set (0.00 sec)

Mysql > select * from processes1 limit 1

+-+

| | pid | pname | ppid | |

+-+

| | 1 | init | 0 | |

+-+

1 row in set (0.00 sec)

Next, create a ggyy user that connects to the MySQL server from the remote host 192.168.7.100, granting permission to query the user1 table in the ggyy database. Use the GRANT command to create authorized users at authorization time:

Mysql > select current_user ()

+-+

| | current_user () |

+-+

| | root@localhost |

+-+

1 row in set (0.00 sec)

Mysql > grant select on ggyy.users1 to ggyy@192.168.7.100 identified by 'ggyy'

Query OK, 0 rows affected (0.00 sec)

Mysql > show grants for ggyy@192.168.7.100

+-+

| | Grants for ggyy@192.168.7.100 |

+-+

| | GRANT USAGE ON *. * TO 'ggyy'@'192.168.7.100' IDENTIFIED BY PASSWORD' * 484FFAA42C12F40931C794D33A11B7F075B91467' |

| | GRANT SELECT ON `ggyy`.`users1`TO 'ggyy'@'192.168.7.100' |

+-+

2 rows in set (0.00 sec)

Mysql > select host, user from mysql.user

+-+ +

| | host | user |

+-+ +

| |% | root |

| | 192.168.7.100 | ggyy | |

| | localhost | ggyy |

| | localhost | root |

+-+ +

4 rows in set (0.00 sec)

Mysql > select host, db, user, select_priv, insert_priv, update_priv, delete_priv from mysql.db where host = '192.168.7.1

00'

Empty set (0.00 sec)

Mysql > select * from mysql.tables_priv where host = '192.168.7.100'

+-+ +

| | Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv |

+-+ +

| | 192.168.7.100 | ggyy | ggyy | users1 | root@localhost | 2010-01-17 14:02:42 | Select |

+-+ +

1 row in set (0.00 sec)

As you can see, because the permissions granted to the table are granted, there is no corresponding record in the host table, so you need to check it in the tables_ private table.

Access the database on the remote host:

C: > mysql-h 192.168.7.101-u ggyy-p

Enter password: *

Welcome to the MySQL monitor. Commands end with; or g.

Your MySQL connection id is 20

Server version: 5.1.34-community MySQL Community Server (GPL)

Type 'help;' or' h' for help. Type 'c'to clear the current input statement.

Mysql > select current_user ()

+-+

| | current_user () |

+-+

| | ggyy@192.168.7.100 |

+-+

1 row in set (0.00 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | ggyy |

+-+

2 rows in set (0.00 sec)

Mysql > use ggyy

Database changed

Mysql > show tables

+-+

| | Tables_in_ggyy |

+-+

| | users1 |

+-+

1 row in set (0.00 sec)

Mysql > select * from users1 limit 1

+-+

| | uid | uname | gid | |

+-+

| | 202 | fengsong | 200 | |

+-+

1 row in set (0.00 sec)

To reclaim permissions, use the REVOKE command, for example:

Mysql > select current_user ()

+-+

| | current_user () |

+-+

| | root@localhost |

+-+

1 row in set (0.00 sec)

Mysql > revoke select on ggyy.* from ggyy@localhost

Query OK, 0 rows affected (0.00 sec)

Mysql > revoke select on ggyy.users1 from ggyy@192.168.7.100

Query OK, 0 rows affected (0.00 sec)

You can also use a combination of db and host tables to manage database permissions, such as:

Mysql > select current_user ()

+-+

| | current_user () |

+-+

| | root@localhost |

+-+

1 row in set (0.00 sec)

Mysql > insert into mysql.db (db, user, select_priv, delete_priv) values ('ggyy',' ggyy', 'Yothers,' Y')

Query OK, 1 row affected (0.00 sec)

Mysql > insert into mysql.host (db, host, select_priv, insert_priv) values ('ggyy',' localhost', 'Yothers,' Y')

Query OK, 1 row affected (0.00 sec)

Mysql > select host, db, user, select_priv, insert_priv, delete_priv from mysql.db where db = 'ggyy'

+-+ +

| | host | db | user | select_priv | insert_priv | delete_priv | |

+-+ +

| | ggyy | ggyy | Y | N | Y |

+-+ +

1 row in set (0.00 sec)

Mysql > select host, db, select_priv, insert_priv, delete_priv from mysql.host where db = 'ggyy'

+-+

| | host | db | select_priv | insert_priv | delete_priv | |

+-+

| | localhost | ggyy | Y | Y | N | |

+-+

1 row in set (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Use ggyy users to access the database on the local machine:

Mysql > select current_user ()

+-+

| | current_user () |

+-+

| | ggyy@localhost |

+-+

1 row in set (0.00 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | ggyy |

+-+

2 rows in set (0.00 sec)

Mysql > use ggyy

Database changed

Mysql > show tables

+-+

| | Tables_in_ggyy |

+-+

| | blob_text_test |

| | char_test |

| | contact |

| | date_time_test |

| | float_test |

| | groups1 |

| | groups2 |

| | int_test |

| | members |

| | members_temp |

| | processes1 |

| | test_crttb |

| | test_crttb2 |

| | test_crttb3 |

| | test_crttb4 |

| | test_crttb5 |

| | ts_dt_test |

| | users1 |

+-+

18 rows in set (0.00 sec)

Mysql > select * from groups1 limit 1

+-+ +

| | gid | gname |

+-+ +

| | 0 | root |

+-+ +

1 row in set (0.00 sec)

Mysql > insert into groups1 values (255, 'test')

ERROR 1142 (42000): INSERT command denied to user 'ggyy'@'localhost' for table' groups1'

Mysql > delete from groups1 where gname = 'root'

ERROR 1142 (42000): DELETE command denied to user 'ggyy'@'localhost' for table' groups1'

The permission column in the db table is equivalent to the "master switch", and the permission column in the host table is equivalent to "separate off". Only when both "switches" are turned on, the user has the right to operate accordingly.

Mysql > select current_user ()

+-+

| | current_user () |

+-+

| | root@localhost |

+-+

1 row in set (0.00 sec)

Mysql > update mysql.db set insert_priv ='Y 'where db =' ggyy'

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > update mysql.host set delete_priv ='Y 'where db =' ggyy'

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > select host, db, user, select_priv, insert_priv, delete_priv from mysql.db where db = 'ggyy'

+-+ +

| | host | db | user | select_priv | insert_priv | delete_priv | |

+-+ +

| | ggyy | ggyy | Y | Y | Y | Y |

+-+ +

1 row in set (0.00 sec)

Mysql > select host, db, select_priv, insert_priv, delete_priv from mysql.host where db = 'ggyy'

+-+

| | host | db | select_priv | insert_priv | delete_priv | |

+-+

| | localhost | ggyy | Y | Y | Y |

+-+

1 row in set (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Use the ggyy user to try the insert and delete operations again:

Mysql > select current_user ()

+-+

| | current_user () |

+-+

| | ggyy@localhost |

+-+

1 row in set (0.00 sec)

Mysql > insert into groups1 values (255, 'test')

Query OK, 1 row affected (0.00 sec)

Mysql > delete from groups1 where gname = 'root'

Query OK, 1 rows affected (0.00 sec)

This permission, determined by both the db table and the host table, cannot be seen with the SHOW GRANTS command and cannot be reclaimed with the REVOKE command.

Mysql > select current_user ()

+-+

| | current_user () |

+-+

| | root@localhost |

+-+

1 row in set (0.00 sec)

Mysql > show grants for ggyy@localhost

+-+

| | Grants for ggyy@localhost |

+-+

| | GRANT USAGE ON *. * TO 'ggyy'@'localhost' IDENTIFIED BY PASSWORD' * 484FFAA42C12F40931C794D33A11B7F075B91467' |

+-+

1 row in set (0.00 sec)

Mysql > revoke select on ggyy.* from ggyy@localhost

ERROR 1141 (42000): There is no such grant defined for user 'ggyy' on host' localhost'

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.