MySQL 用户与权限管理
MySQL权限系统的主要功能是证实连接到一台给定主机的用户，并且赋予该用户在数据库上的相关DML,DQL权限。MySQL存取控制包含2个阶段，一是服务器检查是否允许你连接；二是假定你能连接，服务器检查你发出的每个请求。看你是否有足够的权限实施它。本文主要描述MySQL权限系统相关的用户创建、授权、撤销权限等等。
--------------------------------------分割线 --------------------------------------
--------------------------------------分割线 --------------------------------------
1、获取有关权限管理的帮助
root@localhost[(none)]> help Account Management
For more information, type 'help <item>', where <item> is one of the following
topics:
You asked for help about help category: "Account Management"
CREATE USER
DROP USER
GRANT
RENAME USER
REVOKE
SET PASSWORD
2、创建mysql数据库用户
--创建用户的语法
root@localhost[(none)]> help create user;
Name: 'CREATE USER'
Description:
Syntax:
CREATE USER user_specification [, user_specification] ...
user_specification:
user
[
| IDENTIFIED WITH auth_plugin [AS 'auth_string']
IDENTIFIED BY [PASSWORD] 'password'
]
create user命令会创建一个新帐户，同时也可以为其指定密码。该命令将添加一条记录到user表。
该命令仅仅授予usage权限。需要再使用grant命令进行进一步授权。也可以使用grant命令直接来创建账户见后续的相关演示。
下面是mysql官方手册对usage的解释。
The USAGE privilege specifier stands for “no privileges.” It is used at the global level with
GRANT to modify account attributes such as resource limits or SSL characteristics without affecting
existing account privileges.
--当前演示环境
root@localhost[(none)]> show variables like 'version';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| version | 5.5.39-log |
+---------------+------------+
--创建新用户(未指定密码)
root@localhost[(none)]> create user 'fred'@'localhost';
Query OK, 0 rows affected (0.00 sec)
--指定密码创建新用户,%表示任意，即frank可以从任意主机访问数据库
root@localhost[(none)]> create user 'frank'@'%' identified by 'frank';
Query OK, 0 rows affected (0.00 sec)
--查看刚刚添加的账户
root@localhost[(none)]> select host,user,password from mysql.user where user like 'fr%';
+-----------+-------+-------------------------------------------+
| host | user | password |
+-----------+-------+-------------------------------------------+
| % | frank | *63DAA25989C7E01EB96570FA4DBE154711BEB361 |
| localhost | fred | |
+-----------+-------+-------------------------------------------+