mysql授权客户端访问权限_MySQL用户与权限管理

一、用户与权限管理

(一)grant

1、help grant

和权限相关的命令关键字grant可通过help查看其用法：

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 ...]

...

CREATE USER'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

GRANT ALL ON db1.* TO 'jeffrey'@'localhost';

GRANT SELECT ON db2.invoice TO'jeffrey'@'localhost';

GRANT USAGE ON*.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

...

2、grant授权

对于上述：

GRANT ALL ON db1.* TO 'jeffrey'@'localhost';

它实际包含了两条命令，先是创建用户jeffrey，然后才是对这个用户进行授权。如下：

mysql> create user 'jeffrey'@'localhost' identified by 'mypass';

mysql> grant all on db1.* to 'jeffrey'@'localhost' identified by 'mypass';

对于授权语句的一些关键字解释如下：

grant

all privileges

on dbname.*

to username@localhost

Identified by ‘mypass’

授权命令

对应权限

目标：库和表

用户名和客户端主机

用户密码

3、实战练习

查看当前数据库用户情况

mysql> select user,host frommysql.user;+--------+---------------+

| user | host |

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

| root | 127.0.0.1