MySQL用户与权限管理

1）查看mysql中所有用户信息

select host, user from mysql.user;

2）添加用户

Ⅰ）MySQL中的用户分为两种：root用户和普通用户。使用root用户创建普通用户。

Ⅱ）使用create user’语句创建普通用户

语法：CREATE USER '用户名1'@'主机名' IDENTIFIED by '密码' [,'用户名2'@'主机名' IDENTIFIED by '密码'];可同时定义多个用户。

主机名，用于指定该用户在哪个主机上可以登录MySQL服务器，localhost表明该用户只能只能在本地登录，若想远程登录将主机名的值改为%或者具体的主机名，%表明在任何电脑上否可以登录。

CREATE USER 'test001'@'localhost' IDENTIFIED by 'test001';

注意：使用‘create user’ 语句创建的用户没有任何权限，如果想要该用户拥有某些权限，需要使用授予权限的SQL语句来实现

3）删除用户

Ⅰ）使用drop user语句删除普通用户

语法：DROP uesr '用户名'@'主机名'[, '用户名'@'主机名'];

DROP USER 'test001'@'localhost';

Ⅱ）使用DELETE语句删除普通用户

语法：delete from mysql.user where user = '用户名' and host = '主机名';

delete from mysql.user where user = 'test001' and host = 'localhost';

4）设置（添加）权限

Ⅰ）查看指定用户的权限，需要具有对MySQL数据库的select权限 ，show grants for '用户名'@'主机名';

CREATE USER 'u1'@'localhost' IDENTIFIED by 'u1';/*创建用户'u1'@'localhost'*/
SHOW grants for 'u1'@'localhost';  /*查看'u1'@'localhost'的权限，USAGE 是指没有任何权限*/

Ⅱ）使用root超级用户来授予其他已经存在的用户权限

grant 权限列表 on (* | 数据库) . (*|数据表名) to ‘用户名’@‘访问主机’ ；

权限列表：create alter drop insert update delete select等，分配所有权限：all privileges。

/*把查询student表的权限授给用户U1*/
GRANT select ON TABLE teststudent.student TO 'u1'@'localhost';
​
/*创建用户U2，U3*/
CREATE USER 'u2'@'localhost' IDENTIFIED by 'u2','u3'@'localhost' IDENTIFIED by 'u3';
/*把对student表和course表的全部操作权限授予用户U2和U3 */
GRANT ALL PRIVILEGES ON TABLE teststudent.student TO 'u2'@'localhost','u3'@'localhost';
GRANT ALL PRIVILEGES ON TABLE teststudent.course TO 'u2'@'localhost','u3'@'localhost';

5）删除（回收权限）

语法：revoke 权限列表 on (* | 数据库) . (*|数据表名) from ‘用户名’@‘访问主机’ ；

/*收回u2，u3对student表的全部权限*/
REVOKE ALL PRIVILEGES ON TABLE student FROM 'u2'@'localhost';
REVOKE ALL PRIVILEGES ON TABLE student FROM 'u3'@'localhost';

6）创建和收回角色

/*为一组具有相同权限的用户创建一个角色，然后使用角色为为这些用户授权 */
/* 首先创建一个角色 r1*/
CREATE role r1;
​
/* 使用grant语句，使r1拥有student表的select、UPDATE、INSERT权限*/
GRANT SELECT,UPDATE,INSERT on TABLE teststudent.course TO r1;
​
/* 将r1这个角色授予用户u1、u2、u3*/
GRANT r1 TO 'u1'@'localhost','u2'@'localhost','u3'@'localhost';
SHOW grants for 'u1'@'localhost'; 
/* 可以一次性地通过r1收回用户u1、u2、u3的这三个权限*/
REVOKE r1 from 'u1'@'localhost';
​
/* 角色的权限修改，增加student表的delete 权限*/
GRANT UPDATE on TABLE teststudent.course TO r1;
​
/* 减少r1的的select权限*/
REVOKE SELECT on TABLE teststudent.course FROM r1;