MySQL中对于用户的权限比较简单,本文主要是分为创建用户、修改用户权限、修改用户密码、删除用户等几部分,介绍MySQL对于用户权限的管理。

1 创建用户

1.1 create user

格式如下:

CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

其中,username为用户名;hostname为主机,localhost指本地用户,通配符%指可以从远程主机登陆用户,如果不指定host,则默认为%;password为密码,可以为空。

例如,创建用户名为user、密码为123456的用户,主机为本地,则

CREATE USER 'user'@'localhost' IDENTIFIED BY '123456';

创建用户名为user、密码为123456的用户,主机为192.168.1.1,则

CREATE USER 'user'@'192.168.1.1' IDENTIFIED BY '123456';

创建用户名为user、密码为123456的用户,则

CREATE USER 'user' IDENTIFIED BY '123456';

创建用户名为user,则

CREATE USER user;

注:这种方式需要登录mysql控制台,且登录用户需要CREATE USER权限。

1.2 insert into mysql.user

格式如下:

INSERT INTO mysql.user(host, user, password) VALUES(hostname, username, PASSWORD(password));
FLUSH PRIVILEGES;

其中,mysql.user是在mysql库中存储用户信息的user表,hostname表示主机名(localhost指本地用户,通配符%指可以从远程主机登陆用户),第二个password是密码(第一个password是字段)。

例如,创建用户名为user、密码为123456的用户,则

INSERT INTO mysql.user(host, user, password) VALUES('localhost', 'user', PASSWORD('123456'));
FLUSH PRIVILEGES;

注:因为这种方式是手动修改数据库,如果想要这种修改立马生效,还需执行FLUSH PRIVILEGES命令刷新用户权限列表,所以需要登录的用户有SELECT、UPDATE、RELOAD三种权限。

2 修改用户权限

2.1 为用户赋予权限

在MySQL中使用GRANT命令给用户授权,如果用户不存在,GRANT会自动创建用户,并进行授权。

格式如下:

GRANT privileges ON databases.tablename TO 'username'@'hostname' [INDENTIFIED BY ‘password’];

其中,privileges表示具体的权限名称,多个权限间通过逗号隔开;databases表示数据库,使用*表示所有数据库;tablename表示表名,使用*表示所有表;username表示用户名;hostname表示主机名;password表示密码,[]中用于创建用户时为用户添加密码。

2.2 常用权限

  • 表数据: select, update, delete, insert
  • 表结构: create, alert, drop
  • 外键: references
  • 创建临时表: create temporary tables
  • 操作索引: index
  • 视图: create view, show view
  • 存储过程: create routine, alert routine, execute
  • 所有权限: all

2.3 权限表

  • 存储过程权限表: mysql.procs_priv
  • 列权限表: mysql.columns_priv
  • 表格权限表: mysql.tables_priv
  • 用户权限表: information_schema.user_privileges(字段grantee为授权者, 字段privilege_type为权限名称)
  • 用户信息及权限: mysql.user(后缀为_priv的字段的值与user_privileges的privilege_type一一对应)

2.4 查看用户权限

查看用户权限可以通过查询上面的权限表的数据进行查询,或者通过show grants命令进行查询,格式如下:

SHOW GRANTS [FOR username@hostname]

其中,username表示用户名,hostname表示主机名,[]中的内容可以省略,是查询当前登录用户的权限。

2.5 撤销权限

可以通过命令撤销某用户的某种权限,格式如下:

REVOKE privileges ON databease.table FROM username@hostname

撤销权限与赋予权限命令相似,区别在于将TO改为FROM。

3 修改用户密码

3.1 mysqladmin命令

格式如下:

mysqladmin -u USER -p password NEW_PASSWORD

其中,USER为用户名,NEW_PASSWORD为新密码。

该命令直接在命令行中执行,回车之后会提示输入原密码,输入正确后即可修改。

例如,设置用户user的密码为123456,原密码为654321,则

mysqladmin -u user -p password 123456
Enter password:******

注:上面的**是自己输入的654321。

3.2 SET PASSWORD 语句

格式如下:

SET PASSWORD FOR username@hostname=PASSWORD('password');

其中,username为用户名,hostname为指定的主机,password为新密码。

这种方式需要先登入mysql控制台,即通过正确用户名、密码登入。

例如:设置在localhost上的用户user的密码为123456,则

SET PASSWORD FOR user@localhost=PASSWORD('123456');

注:通过这种方式修改自己的密码,可以只有基本的USAGE权限;如果修改其他人的密码,需要对mysql数据库存在UPDATE权限。

3.3 UPDATE user 语句

格式如下:

UPDATE mysql.user SET password=PASSWORD('password') WHERE user='username' and host='hostname';
FLUSH PRIVILEGES;

其中,mysql.user表示mysql库中的user表,user表示字段为user,username为用户名,password为新密码,hostname为指定的主机。

这种方式需要先登入mysql控制台,即通过正确用户名、密码登入。

例如:设置用户user的密码为123456,则

UPDATE mysql.user SET password=PASSWORD('123456') WHERE user='user' and host='localhost';
FLUSH PRIVILEGES;

注:因为这种方式是手动修改数据库,如果想要这种修改立马生效,还需执行FLUSH PRIVILEGES命令刷新用户权限列表,所以需要登录的用户有SELECT、UPDATE、RELOAD三种权限。

4 删除用户

在mysql中,可以通过直接删除mysql.user表中的数据删除用户,也可以通过drop命令删除用户,与创建用户的操作类似。

4.1 DELETE FROM mysql.user

格式如下:

DELETE FROM mysql.user WHERE user='username' and host='hostname'

其中,mysql.user表示mysql库中的user表,user表示字段为user,username为用户名,hostname为指定的主机。

4.2 DROP USER username

格式如下:

DROP USER 'username'@'hostname'

其中,username为用户名,hostname为指定的主机。