MySQL用户及权限管理

1.查看用户及用户权限

mysql中的用户信息和权限等都存储在一个名为mysql的数据库中。其中主要用到的是user、db、tables_priv、columns_priv、procs_priv这五张表，最重要的是user表。

　　user表存储全局权限，适用于一个给定服务器中的所有数据库，在命令中展现形式为*.*；

　　db表存储数据库权限，适用于一个给定数据库中的所有表，在命令中展现形式为[数据库名].*；

　　tables_priv表存储表权限，适用于一个给定表中的所有列，在命令中展现形式为[数据库名].[表名]；

　　columns_priv表存储列权限，适用于一个给定表中的单一列，在命令中展现形式为；

　　CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT权限，适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级，而且除了CREATE ROUTINE外，这些权限可以被授予为子程序层级，并存储在procs_priv表中。

　　查看用户及使用范围（也叫作用域），注意user表中user+host是复合主键，下面很多地方都是用的这个复合主键确认唯一值。

例1：查看当前数据库的用户及主机信息

use mysql;
select user,host from user;

2.创建用户

MySQL提供了CREATE USER语句，允许您创建一个新的用户帐户。 CREATE USER语句的语法如下：

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

参数说明：

• username：你将创建的用户名
• host：指定该用户在哪个主机上可以登录，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登录，可以使用通配符%，还可以指定的ip地址范围
• password：给用户设置密码，password必须是明文。 在将用户帐户保存到用户表之前，MySQL将加密明文密码

例2：创建一个名为张三的本地账户，账户的密码为123456

create user 'zhangsan'@'localhost' IDENTIFIED by '123456';

说明：如果需要指定主机地址登录的用户，则将localhost改为对应的主机Ip即可；如果需要创建任意地址登录的账户，则将localhost改为%，如create user 'lisi'@'%' IDENTIFIED by '123456'；如果需要创建无密码的用户，则可以省略identified by关键字，如create user 'wangwu'@'%' ;

 3.修改用户

（1）修改用户名语法：rename user '原用户名'@'原主机地址'  to  '修改后的用户名'@'修改后的主机地址'

例3：修改lisi用户的登录主机地址为localhost

rename user 'lisi'@'%' to 'lisi'@'localhost';

（2）修改用户密码

1>使用set password命令

/*修改用户lisi的密码为lisi*/
set password for 'lisi'@'localhost' = password('lisi');

2>更新mysql.user表

/*修改用户wangwu的密码为123456*/
update mysql.user set password = password('123456') where 
user='wangwu';
flush PRIVILEGES; #密码修改生效

 注意：user表的password字段在高版本中改为了authentication_strting。

3>使用mysqladmin命令，在系统命令行执行。

语法：mysqladmin -u用户名 -p旧的密码 password 新密码

 如：mysqladmin -uroot -p123456 password 123456

 4.删除用户

删除用户之前先查看用户名和主机地址

 例4：删除名为lisi的用户

drop user 'lisi'@'localhost';

5.权限管理

（1）查看用户权限

/*查看当前用户权限*/
show grants;

查看用户zhangsan的权限

/*查看用户张三的权限*/
show GRANTS for 'zhangsan'@'localhost';

 权限介绍

权限	权限说明	权限级别
CREATE	创建数据库、表或索引的权限	数据库、表或索引
DROP	删除数据库或表的权限	数据库或表
GRANT OPTION	赋予权限选项	数据库或表
REFERENCES	引用权限	数据库或表
ALTER	更改表的权限	数据表
DELETE	删除表数据的权限	数据表
INDEX	操作索引的权限	数据表
INSERT	添加表数据的权限	数据表
SELECT	查询表数据的权限	数据表
UPDATE	更新表数据的权限	数据表
CREATE VIEW	创建视图的权限	视图
SHOW VIEW	查看视图的权限	视图
ALTER ROUTINE	更改存储过程的权限	存储过程
CREATE ROUTINE	创建存储过程的权限	存储过程
EXECUTE	执行存储过程权限	存储过程
FILE	服务器主机文件的访问权限	文件管理
CREATE TEMPORARY TABLES	创建临时表的权限	服务器管理
LOCK TABLES	锁表的权限	服务器管理
CREATE USER	创建用户的权限	服务器管理
RELOAD	执行 flush privileges, refresh, reload 等刷新命令的权限	服务器管理
PROCESS	查看进程的权限	服务器管理
REPLICATION CLIENT	查看主从服务器状态的权限	服务器管理
REPLICATION SLAVE	主从复制的权限	服务器管理
SHOW DATABASES	查看数据库的权限	服务器管理
SHUTDOWN	关闭数据库的权限	服务器管理
SUPER	超级权限	服务器管理
ALL [PRIVILEGES]	所有权限
USAGE	没有任何权限

 （2）添加用户权限

语法：GRANT 权限名称列表 ON `数据库名`.`表名` TO '用户名'@'%';

• 权限名称列表：用户的操作权限，创建用户时默认是没有权限的 USAGE；
• ON：后面紧跟数据库和数据表名称，表示要对哪个数据库的哪张表进行授权，可以使用通配符 * 表示所有；
• TO：表示授予权限的用户；

例5：创建一个名为york的用户，并给york用户下的test2022数据库中的users表添加insert,delete权限。（如没有test2022数据库和users表，请现在root用户中添加）

 第一步：先创建用户york

create user 'york'@'%' IDENTIFIED by '123456';

第二步：登陆york账户，查看是否能看见root账户下的数据库信息。创建一个名为york的新连接，使用york账户名和123456密码登录即可

 此时看不见数据库信息，此时需要授予york用户select权限，在root用户的连接下，给york用户授予所有数据库和表的select 权限

GRANT select ON *.* TO 'york'@'%';

运行以后关闭york连接，再打开，此时能看见所有的数据库了

 第三步：在root用户下，给york用户下的test2022数据库中的users表添加insert,delete权限。

GRANT insert,delete ON `test2022`.`users` TO 'york'@'%';

此时，查看york用户的权限如下：

 给york用户的test2022数据库下所有表的所有权限：

grant all PRIVILEGES on `test2022`.* to 'york'@'%';

 给york用户添加所有数据库下所有表的所有权限：

grant all PRIVILEGES on *.* to 'york'@'%';

注意：所有的操作进行后，需要关闭连接，再重新连接后才能生效。

 

参考文章：

https://blog.csdn.net/aiwangtingyun/article/details/106011258