授权命令GRANT 语句的语法如下：
GRANT privileges (columns) ON what TO user IDENTIFIEDBY "password" WITH GRANT OPTION
例1: 创建一个lunardb，并创建用户lunar从本机访问lunardb：
mysql> create database lunardb; Query OK, 1 row affected (0.05 sec) mysql> grant all privileges on lunardb.* to lunar@localhost identified by "lunar"; Query OK, 0 rows affected (0.06 sec) mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> exit Bye [root@lunar mysql5.6]#
使用lunar用户访问lunardb：
[root@lunar mysql5.6]# mysql -ulunar -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.21-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2014, 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. mysql> select database(); +------------+ | database() | +------------+ | NULL | +------------+ 1 row in set (0.00 sec) mysql> use lunardb; Database changed mysql> show database(); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database()' at line 1 mysql> select database(); +------------+ | database() | +------------+ | lunardb | +------------+ 1 row in set (0.00 sec) mysql> select user(); +-----------------+ | user() | +-----------------+ | lunar@localhost | +-----------------+ 1 row in set (0.00 sec) mysql>
例2: 授予lunar从本机访问lunardb，从192.168.56.66访问test数据库，从所有主机访问mysql数据库：
grant select,insert,update,delete,create,drop on lunardb.* to lunar@localhost identified by “lunar”;
grant select,insert,update,delete,create,drop on test.* to lunar@192.168.56.66 identified by “lunar”;
grant select,insert,update,delete,create,drop on mysql.* to lunar@’%’ identified by “lunar”;
查看所有用户以及他们的权限：
mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
+--------------------------------+
| query |
+--------------------------------+
| User: 'lunar'@'%'; |
| User: 'root'@'127.0.0.1'; |
| User: 'lunar'@'192.168.56.66'; |
| User: 'root'@'::1'; |
| User: 'lunar'@'localhost'; |
| User: 'root'@'localhost'; |
| User: 'root'@'lunar'; |
+--------------------------------+
7 rows in set (0.07 sec)
mysql>
–查看lunar和root的用户的授权信息：
mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> show grants for 'lunar'@'%'; +------------------------------------------------------------------------------------------------------+ | Grants for lunar@% | +------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'lunar'@'%' IDENTIFIED BY PASSWORD '*26C772B5D40BC7BC8FDD90CDC5C1100110433463' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `mysql`.* TO 'lunar'@'%' | +------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> show grants for 'root'@'%'; ERROR 1141 (42000): There is no such grant defined for user 'root' on host '%' mysql> show grants; +----------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@localhost | +----------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*8D4165021FB50C23292ACA2C4478CB9688E08607' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +----------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql>
上面的错误表示root用户没有’%’的授权，’%’表示说有主机（从所有主机连接，比如远程登录过来），因此，我来授权一下：
mysql> grant all privileges on *.* to 'root'@'%' with grant option; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'root'@'%'; +-------------------------------------------------------------+ | Grants for root@% | +-------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION | +-------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show grants; +----------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@localhost | +----------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*8D4165021FB50C23292ACA2C4478CB9688E08607' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +----------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql>
mysql的用户权限等信息都记录在mysql.user中，查看user表的定义：
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | YES | | | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+---------+-------+
43 rows in set (0.05 sec)
mysql>
