MySql基本操作（二）

六、权限

1、先建用户，然后再授权

mysql> create user benet@‘localhost‘ identified by ‘pwd@123‘;

Query OK, 0 rows affected (0.22 sec)

mysql> show grants for ‘benet‘@‘localhost‘;

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

| Grants for benet@ localhost                                                                                   |

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

| GRANT USAGE ON *.* TO ‘benet‘@‘ localhost ‘ IDENTIFIED BY PASSWORD ‘*760F60073FD235571A5260444301DB22136ED604‘ |

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

1 row in set (0.00 sec)

mysql> grant all on benet_gbk.* to benet@‘ localhost ‘;

Query OK, 0 rows affected (0.22 sec)

mysql> show grants for ‘benet‘@‘ localhost ‘;          

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

| Grants for benet@ localhost                                                                                   |

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

| GRANT USAGE ON *.* TO ‘benet‘@‘ localhost ‘ IDENTIFIED BY PASSWORD ‘*760F60073FD235571A5260444301DB22136ED604‘ |

| GRANT ALL PRIVILEGES ON `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、直接授权法

mysql> grant all on benet_gbk.* to benet1@‘localhost‘ identified by ‘pwd@123‘;

Query OK, 0 rows affected (0.01 sec)

mysql> show grants for ‘benet1‘@‘localhos‘;

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

| Grants for benet1@localhos                                                                                   |

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

| GRANT USAGE ON *.* TO ‘benet1‘@‘127.0.0.1‘ IDENTIFIED BY PASSWORD ‘*760F60073FD235571A5260444301DB22136ED604‘ |

| GRANT ALL PRIVILEGES ON `benet_gbk`.* TO ‘benet1‘@‘localhos‘                                                 |

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

2 rows in set (0.00 sec)

3、授权bdqn用户在192.168.1.0/24网段可以远程连接数据库

mysql> grant all on *.* to bdqn@‘192.168.1.%‘ identified by ‘pwd@123‘;

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)

[root@localhost ~]# mysql -ubdqn -ppwd@123 -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) 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.

查看all权限都包括哪些权限，先收回一个，再查看

mysql>mysql> revoke insert on benet_gbk.* from benet@‘localhost‘;

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for benet@‘ localhost ‘;                   

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

| Grants for benet@ localhost                                                                                                                                                                                                         |

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

| GRANT USAGE ON *.* TO ‘benet‘@‘ localhost ‘ IDENTIFIED BY PASSWORD ‘*760F60073FD235571A5260444301DB22136ED604‘                                                                                                                       |

| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE 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权限包括：

SELECT, INSERT,UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER

本文出自 “R博士的自由空间” 博客，请务必保留此出处http://zhangxiaoguang.blog.51cto.com/79856/1626291