MySQL权限系统的主要功能是证实连接到一台给定主机的用户，并且赋予该用户在数据库上的相关DML,DQL权限。MySQL存取控制包含2个阶段，一是服务器检查是否允许你连接；二是假定你能连接，服务器检查你发出的每个请求。看你是否有足够的权限实施它。本文主要描述MySQL权限系统相关的用户创建、授权、撤销权限等等。
root@localhost[(none)]> help Account Management
For more information, type 'help - ', where
- 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 | |
+-----------+-------+-------------------------------------------+
3、使用grant授予权限
--grant命令语法
root@localhost[mysql]> help grant
Name: 'GRANT'
Description:
Syntax:
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user_specification [, user_specification] ...
[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
[WITH with_option ...]
GRANT PROXY ON user_specification
TO user_specification [, user_specification] ...
[WITH GRANT OPTION]
object_type:
TABLE
| FUNCTION
| PROCEDURE
priv_level:
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
user_specification:
user
[
| IDENTIFIED WITH auth_plugin [AS 'auth_string']
IDENTIFIED BY [PASSWORD] 'password'
]
如何授权
a、需要指定授予哪些权限
b、权限应用在那些对象上(全局，特定对象等)
c、授予给哪个帐户
d、可以指定密码(可选项,用此方式会自动创建用户)
授权权限的范围：
ON *.*
ON db_name.*
ON db_name.table_name
ON db_name.table_name.column_name
ON db_name.routine_name
--权限一览表，我们直接查询root账户所有的权限，如下
--mysql的权限相对于oracle而言，相对简单，而且也没有涉及到角色方面的定义与配置
root@localhost[(none)]> select * from mysql.user where user='root' and host='localhost'\G
*************************** 1. row ***************************
Host: localhost
User: root
Password:
Select_priv: Y
Insert_priv: Y
Update_priv: Y