MySql基本操作（二）

標籤：mysql許可權

六、許可權

1、先建使用者，然後再授權

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 *.* TO ‘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 *.* 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 [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 *.* 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 [email 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) 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 [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 *.* 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

MySql基本操作（二）