MySQL用户与权限管理

用户管理

MySQL数据库的user表

查看表信息

use mysql;
select * from INFORMATION_SCHEMA.TABLES WHERE table_name = 'user' AND table_schema = 'mysql';
select * from INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'user' AND table_schema = 'mysql';

表信息

字段名	含义
Table_catalog	数据表登记目录
Table_schema	数据表所属的数据库名
Table_name	表名称
Table_type	表类型[system view|base table]
Engine	使用的数据库引擎[MyISAM|CSV|InnoDB]
Version	版本，默认值10
Row_format	行格式[Compact|Dynamic|Fixed]
Table_rows	表里所存多少行数据
Avg_row_length	平均行长度
Data_length	数据长度
Max_data_length	最大数据长度
Index_length	索引长度
Data_free	空间碎片
Auto_increment	做自增主键的自动增量当前值
Create_time	表的创建时间
Update_time	表的更新时间
Check_time	表的检查时间
Table_collation	表的字符校验编码集
Checksum	校验和
Create_options	创建选项
Table_comment	表的注释、备注

字段信息

字段名	说明
Host	客户端登录主机名限制。%表示不作限制
User	用户名
*_priv	以_priv结尾的字段都是权限表示字段，Y表示拥有该权限，N反之
ssl_type	支持ssl标准加密安全字段
ssl_cipher	支持ssl标准加密安全字段
x509_issuer	支持x509标准字段
x509_subject	支持x509标准字段
max_questions	每小时允许执行多少次查询
max_updates	每小时可以执行多少次更新 ：0表示无限制
max_connections	每小时可以建立的多少次连接：0表示无限制
max_user_connections	单用户可以同时具有的连接数：0表示无限制
plugin	5.5.7开始,mysql引入plugins以进行用户连接时的密码验证,plugin创建外部/代理用户
authentication_string	通过authentication_string可以控制两者的映射关系,(PAM plugin等,PAM可以支持多个服务名)尤其是在使用代理用户时，并须声明这一点
password_expired	密码过期 Y,说明该用户密码已过期 N相反
password_last_changed	最后一次的密码修改时间
password_lifetime	密码有效期
account_locked	账户是否已锁定

创建用户

方式1：推荐
CREATE USER ['用户名'[@'登录域'] [IDENTIFIED BY [PASSWORD] '密码']][, [用户名 [IDENTIFIED BY [PASSWORD] '密码']]]

方式2：
GRANT SELECT ON database.table TO '用户名'[@'登录域'] [IDENTIFIED BY [PASSWORD]]
'密码'; -- database.table 为 *.* 时表示所有数据库和所有表
FLUSH PRIVILEGES; -- 刷新权限信息


方式3：
手动向user表插入数据行。

删除用户

方式1：推荐
DROP user 用户名;

方式2：
DELETE FROM user WHERE user='用户名';
FLUSH PRIVILEGES; -- 刷新用户信息

修改用户

修改用户名

方式1：
RENAME USER 旧用户名 TO 新用户名;

方式2：
UPDATE user SET user='新用户名' where user = '旧用户名';
FLUSH PRIVILEGES;

修改密码

方式1：
SET PASSWORD [FOR '目标用户名'[@'登录域']] = PASSWORD('新密码'); -- 未指定用户名时默认修改当前用户的密码
                          
方式2：
UPDATE user SET password = PASSWORD('新密码') where user = '目标用户名';

权限管理

权限标识

权限标识	说明
ALL PRIVILEGES	除GRANT OPTION外的所有权限
ALTER	使用ALTER TABLE
ALTER ROUTINE	使用ALTER PROCEDURE和DROP PROCEDURE
CREATE	使用CREATE TABLE
CREATE ROUTINE	使用CREATE PROCEDURE
CREATE TEMPORARY TABLES	使用CREATE TEMPORARY TABLE
CREATE USER	使用CREATE USER、DROP USER、RENAME USER和REVOKE ALL PRIVILEGES
CREATE VIEW	使用CREATE VIEW
DELETE	使用DELETE
DROP	使用DROP TABLE
EXECUTE	使用CALL和存储过程
FILE	使用SELECT INTO OUTFILE和LOAD DATA INFILE
GRANT OPTION	使用GRANT和REVOKE
INDEX	使用CREATE INDEX和DROP INDEX
INSERT	使用INSERT
LOCK TABLES	使用LOCK TABLES
PROCESS	使用SHOW FULL PROCESSLIST
RELOAD	使用FLUSH
REPLICATION CLIENT	服务器位置的访问
REPLICATION SLAVE	由复制从属使用
SELECT	使用SELECT
SHOW DATABASES	使用SHOW DATABASES
SHOW VIEW	使用SHOW CREATE VIEW
SHUTDOWN	使用mysqladmin shutdown（用来关闭MySQL）
SUPER	使用CHANGE MASTER、KILL、LOGS、PURGE、MASTER和SET GLOBAL。还允许mysqladmin调试登录
UPDATE	使用UPDATE
USAGE	无访问权限

查看权限

-- 查看权限变更履历
SHOW GRANTS [FOR 目标用户名[@'登录域']];	-- 不指定用户名则表示当前用户

设置权限

GRANT [权限,[权限]] ON database.table TO '用户名'[@'登录域'] [IDENTIFIED BY [PASSWORD] '密码'] [WITH GRANT OPTION];
-- WITH GRANT OPTION 表示该用户可以将自身的权限赋予给别人。
-- 重复授权会进行权限的叠加

刷新权限

FLUSH PRIVILEGES;	-- 刷新权限，加载最新的权限信息
-- 使用这个命令使权限生效，尤其是你对那些权限表user、db、host等做了update或者delete更新的时候。以前遇到过使用grant后权限没有更新的情况，只要对权限做了更改就使用FLUSH PRIVILEGES命令来刷新权限。

删除权限

REVOKE [权限,[权限]] ON database.table TO '用户名'[@'登录域'] [IDENTIFIED BY [PASSWORD] '密码'];	-- 移除相关权限
FLUSH PRIVILEGES;

注意

1. 在使用GRANT和REVOKE时，用户账号必须存在，但对所涉及的对象没有这个要求。这允许管理员在创建数据库和表之前设计和实现安全措施。
2. 当某个数据库或表被删除时（用DROP语句），相关的访问权限仍然存在。而且，如果将来重新创建该数据库或表，这些权限仍然起作用。

其他

MySQL权限经验原则

1、只授予能满足需要的最小权限，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限。

2、创建用户的时候限制用户的登录主机，一般是限制成指定IP或者内网IP段。

3、初始化数据库的时候删除没有密码的用户。安装完数据库的时候会自动创建一些用户，这些用户默认没有密码。

4、为每个用户设置满足密码复杂度的密码。

5、定期清理不需要的用户。回收权限或者删除用户。

参考资料

https://www.cnblogs.com/Richardzhu/p/3318595.html

https://blog.csdn.net/weixin_40918067/article/details/116868906