MySQL basic operation (ii)

Vi. Permissions

1 , build the user first, and then authorize

mysql> create user [email protected] ' localhost ' identified by ' [email protected] ';

Query OK, 0 rows affected (0.22 sec)

Mysql> Show grants for ' benet ' @ ' localhost ';

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

| Grants for [email protected] localhost |

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

| GRANT USAGE on *. benet ' @ ' localhost ' identified by PASSWORD ' *760f60073fd235571a5260444301db22136ed604 ' |

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

1 row in Set (0.00 sec)

Mysql> Grant all on benet_gbk.* to [email protected] ' localhost ';

Query OK, 0 rows affected (0.22 sec)

Mysql> Show grants for ' benet ' @ ' localhost ';

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

| Grants for [email protected] localhost |

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

| GRANT USAGE on *. benet ' @ ' localhost ' identified by PASSWORD ' *760f60073fd235571a5260444301db22136ed604 ' |

| GRANT all Privileges "BENET_GBK". * to ' benet ' @ ' localhost ' |

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

2 rows in Set (0.00 sec)

mysql> flush Privileges;

Query OK, 0 rows affected (0.01 sec)

Mysql>

2 , direct authorization Law

Mysql> Grant all on benet_gbk.* to [email protected] ' localhost ' identified by ' [email protected] ';

Query OK, 0 rows affected (0.01 sec)

Mysql> Show grants for ' Benet1 ' @ ' Localhos ';

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

| Grants for [email protected] |

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

| GRANT USAGE on *. benet1 ' @ ' 127.0.0.1 ' identified by PASSWORD ' *760f60073fd235571a5260444301db22136ed604 ' |

| GRANT all Privileges "BENET_GBK". * to ' benet1 ' @ ' Localhos ' |

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

2 rows in Set (0.00 sec)

3 , Authorization BDQN users in 192.168.1.0/24 network segment can connect to database remotely

Mysql> Grant All on * * to [e-mail protected] ' 192.168.1.% ' identified by ' [email protected] ';

Query OK, 0 rows Affected (0.00 sec)

mysql> flush Privileges;

Query OK, 0 rows affected (0.01 sec)

Mysql> Select User,host

from user;

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

| user | Host |

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

| Benet | 127.0.0.1 |

| Benet1 | 127.0.0.1 |

| Root | 127.0.0.1 |

| BDQN | 192.168.1.% |

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

4 rows in Set (0.00 sec)

[Email protected] ~]# mysql-ubdqn [email protected]-H 192.168.1.101

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 2

Server Version:5.5.38-log Source Distribution

Copyright (c) and/or, Oracle, its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

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

View All What permissions are included in the permission, take back one first, and then view

mysql>mysql> revoke insert on benet_gbk.* from [email protected] ' localhost ';

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show grants for [email protected] ' localhost ';

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

| Grants for [email protected] localhost                                                                                                                                                                                                                    |

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

| GRANT USAGE on *. benet ' @ ' localhost ' identified by PASSWORD ' *760f60073fd235571a5260444301db22136ed604 '    ;                                                                                                                           |

| GRANT SELECT, UPDATE, DELETE, create, DROP, REFERENCES, INDEX, ALTER, CREATE temporary TABLES, LOCK TABLES, EXECUTE, C reate view, SHOW view, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER on ' benet_gbk '. * to ' benet ' @ ' 127.0.0.1 ' |

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

2 rows in Set (0.00 sec)

All Privileges permissions include:

SELECT, Insert,update, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE temporary TABLES, LOCK TABLES, EXECUTE, CREA TE view, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER

This article is from Dr. R's free space blog, so be sure to keep this source http://zhangxiaoguang.blog.51cto.com/79856/1626291

MySQL basic operation (ii)