深入理解 MySQL 用户和权限

原创

白墨的博客 2022-03-08 14:14:24 博主文章分类：数据库开发 ©著作权

©著作权归作者所有：来自51CTO博客作者白墨的博客的原创作品，请联系作者获取转载授权，否则将追究法律责任

一. 用户

只创建用户
CREATE USER username IDENTIFIED BY 'password';
创建用户的同时授予权限
GRANT ALL ON databasename.* TO username@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

DROP USER username@localhost;

SET PASSWORD FOR username@localhost = password('newpassword');

use mysql;
UPDATE USER SET password=password('newpassword') WHERE user='username' and host='localhost';
FLUSH PRIVILEGES;

mysqladmin -uroot -pold_password password new_password

打开新终端，进入mysql/bin 目录
打开mysql命令行
use mysql; UPDATE USER SET password=password('newpassword') WHERE user='username' and host='localhost'; FLUSH PRIVILEGES;

！！！注意：在每次执行完分配权限命令后，需要刷新权限
FLUSH PRIVILEGES;

服务器
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';
数据库
GRANT ALL PRIVILEGES ON databasename.* TO 'username'@'localhost';
数据表
GRANT ALL PRIVILEGES ON databasename.tablename TO 'username'@'localhost';
数据表的列
GRANT SELECT(id, col1, col2) ON databasename.tablename TO 'username'@'localhost';
存储过程
GRANT EXECUTE ON PROCEDURE databasename.tablename TO 'username'@'localhost';
函数
GRANT EXECUTE ON FUNCTION databasename.tablename TO 'username'@'localhost';

为普通用户添加权限
GRANT SELECT, INSERT, UPDATE, DELETE ON databasename.* TO 'username'@'%';
为开发者添加权限
GRANT CREATE, ALTER, DROP, REFERENCES ON databasename.* TO 'username'@'192.168.0.%';
为普通 DBA 添加权限
GRANT ALL PRIVILEGES ON databasename.* TO 'username'@'localhost';
为高级 DBA 添加权限
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';

SHOW GRANTS FOR username;

REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'localhost';

GRANT ：执行命令词，一般为动词
ALL：赋予权限名，参考下面的权限列表
*.*：前者表示数据库名，后者表示数据表名
- databasename.*：表示在databasename中的所有表
- databasename.tablename：表示在databasename中的tablename表

'username'@'localhost'：前者为用户名，后者为接入的IP

localhost 通过UNIXsocket连接，不会被解析为IP地址
127.0.0.1 通过TCP/IP协议连接，只能本机访问
::1 ::1 支持ipv6，等同于ipv4的127.0.0.1