用户与权限管理

目录

一.用户管理

1.1 登录mysql 服务器

1.2 创建用户

1.3 修改用户

1.4 删除用户

1.5 设置当前用户密码

1.6 修改其它用户密码

二. 权限管理

2.1 授予权限,查看权限,回收权限

三. 角色管理

3.2 创建角色

四. 权限表

4.1 user 表

4.2 db表(显示数据库权限)

4.3 tables_priv表和columns_priv表

4.4 procs_priv表 

五. 访问控制(了解)  

5.1 连接核实阶段

5.2 请求核实阶段

---

---

一.用户管理

1.1 登录mysql 服务器

mysql –h hostname|hostIP –P port –u username –p DatabaseName –e "SQL语句"
 

-h 后面接主机名或者主机IP
-P 后面接端口号 默认3306
-u 后面接用户名
-p  会提示输入密码
DatabaseName参数 指明要用的数据库,可以省略
-e 后面接sql语句直接执行,然后退出数据库

 mysql -h hostname -uroot -P 3306 dbtest -e "select * from emp"

mysql -uroot -p -hlocalhost -P3306 mysql -e "select host,user from user"
一般: mysql -uroot -p;

1.2 创建用户

/* create user 用户名@'主机地址' [密码] */
CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']];

create user 'zhangsan' identified by 'abc123';//默认 host为% 可以修改
create user 'zhangsan'@'localhost' identified by 'abc123';

*用户表在名为mysql的数据库下的user表中;
*查看mysql下的用户
use mysql;
select * from user;  //这样出来的信息很多,可以只查询用户名与主机名
select host,user from user; //host字段表示允许哪台主机连接
例如: localhost 表示只允许主机连接 %代表任何主机连接

1.3 修改用户

修改用户名：(用的很少)
本质上就是修改mysql数据库下的user表的user字段

update mysql.user set user='zs'where user='zhangsan';
FLUSH PRIVILEGES; //刷新

1.4 删除用户

方式1：使用DROP方式删除（推荐） 不需要刷新
/* drop user 用户名@'主机地址'   默认为%    */

必须拥有DROP USER权限

DROP USER user[,user]…;
DROP USER 'zs' ; # 默认删除host为%的用户
drop user 'zs'@'localhost';

方式2：使用DELETE方式删除(需要刷新)
DELETE FROM mysql.user WHERE Host=’hostname’ AND User=’username’;
FLUSH PRIVILEGES;

注意：不推荐通过 DELETE FROM USER u WHERE USER='li4' 进行删除，系统会有残留信息保
留。而drop user命令会删除用户以及对应的权限，执行命令后你会发现mysql.user表和mysql.db表
的相应记录都消失了。

1.5 设置当前用户密码

1. 使用ALTER USER命令来修改当前用户密码

ALTER USER USER() IDENTIFIED BY 'new_password';
alter user user()identified by '123';
# user() 表明当前用户

2. 使用SET语句来修改当前用户密码

SET PASSWORD='new_password';
set password = '123';

密码在mysql存储回会加密处理

1.6 修改其它用户密码

前提:有权限
1. 使用ALTER语句来修改普通用户的密码

ALTER USER user [IDENTIFIED BY '新密码'] [,user[IDENTIFIED BY '新密码']]…;
alter user 'zhangsan'@'%' identified by '123';

2. 使用SET命令来修改普通用户的密码

SET PASSWORD FOR 'username'@'hostname'='new_password';
set password for 'zhangsan'@'localhost'='123';

3. 使用UPDATE语句修改普通用户的密码（不推荐）

UPDATE MySQL.user SET authentication_string=PASSWORD("123456") 
WHERE User = "username" AND Host = "hostname";

二. 权限管理

2.1 授予权限,查看权限,回收权限

GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];
该权限如果发现没有该用户，则会直接新建一个用户。
/* grant .. on .. to [identified by] */
grant all on *.* to 'zhangsan'@'%' identified by 'ABC23';
grant select mysql.emp to 'lisi';

--------查看权限-----------------------
/* show grants for */
#查看所有权限
show privileges;
#查看当前用户拥有的权限
show grants;
# 查看特定用户的权限(加个for)
show grants for '用户名'@'主机地址'; 
---------收回权限-----------------
/* revoke  on from*/
注意：在将用户账户从user表删除之前，应该收回相应用户的所有权限。

REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 用户名@用户地址;
#收回全库全表的所有权限 
REVOKE ALL PRIVILEGES ON *.* FROM joe@'%'; 
#收回mysql库下的所有表的插删改查权限 
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM joe@localhost;
注意： 须用户重新登录后才能生效

补充: 默认普通用户不可以给用户授予权限,
创建用户时加上 with grant option 就可以

三. 角色管理

角色 : 权限的集合

引入角色的目的是 方便管理拥有相同权限的用户

3.2 创建角色

流程: 创建角色->给角色赋予权限 -> 给用户赋予角色 -> 激活角色

/*  1. 创建角色 create role  */
语法: CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...

create role 'manager'@'localhost';

/* 2. 给角色赋予权限(跟给用户授予权限一样)  */
语法:GRANT privileges ON table_name TO 'role_name'[@'host_name'];

grant all on *.* to 'manager'@'localhost';
GRANT SELECT ON demo.invcount TO 'manager';

/*  查看角色的权限 (同查看用户权限)*/
语法: show grants for '';
SHOW GRANTS FOR 'manager';

注意:只要你创建了一个角色，系统就会自动给你一个“ USAGE ”权限，意思是 连接登录数据库的权限 。

/*  回收角色的权限  revoke ... on ... from .. (同收回用户权限)*/

语法:REVOKE privileges ON tablename FROM 'rolename';
REVOKE INSERT, UPDATE, DELETE ON school.* FROM 'school_write';

/*  删除角色  drop user*/
语法: DROP ROLE role [,role2]...
如果你删除了角色，那么用户也就失去了通过这个角色所获得的所有权限
drop role 'manager';

/* 3. 给用户赋予角色 grant .. to... */
语法: GRANT role [,role2,...] TO user [,user2,...];
grant 'manager' to 'zhangsan'@'localhost';

/* 4. 激活角色 */ 
角色创建并授权后，要赋给用户并处于 激活状态 才能发挥作用
SELECT CURRENT_ROLE();  #查看角色是否激活
/*方式1：使用set default role 命令激活角色  */

SET DEFAULT ROLE ALL TO 'zhangsan'@'localhost','lisi';

/*方式2：将activate_all_roles_on_login设置为ON */
SET GLOBAL activate_all_roles_on_login=ON;
(对所有角色永久激活)

/*  撤销用户的角色 revoke ... from*/
语法: REVOKE role FROM user;
revoke'manager' from 'zhangsan'@'localhost';
执行后可以查看下权限
show grants for 'zhangsan'@'localhost';





/* 设置强制角色(mandatory role)
强制角色是给每个创建账户的默认角色，不需要手动设置。强制角色无法被REVOKE或者DROP。
 */
# 方式1：服务启动前设置
[mysqld] mandatory_roles='role1,role2@localhost,r3@%.atguigu.com'

方式2：运行时设置
#系统重启后仍然 有效
SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com';
#系统重启后 失效
SET GLOBAL mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; 

四. 权限表

4.1 user 表

记录用户账号和权限信息

SELECT host,user,authentication_string,select_priv,insert_priv,drop_priv 
FROM mysql.user;
authentication_string 密码字段

4.2 db表(显示数据库权限)

1. 用户列 db 表用户列有 3 个字段，分别是 Host 、 User 、 Db 。这 3 个字段分别表示主机名、用户名和数据库名。表示从某个主机连接某个用户对某个数据库的操作权限，这3 个字段的组合构成了 db 表的主键。

2. 权限列

Create_routine_priv 和 Alter_routine_priv 这两个字段决定用户是否具有创建和修改存储过程的权限。

4.3 tables_priv表和columns_priv表

4.4 procs_priv表 

五. 访问控制(了解)  

5.1 连接核实阶段

5.2 请求核实阶段