深入理解 MySQL 用户和权限

一. 用户

1 创建用户(这里的用户不可使用，在分配权限后可正常登陆)

• 只创建用户

  CREATE USER username IDENTIFIED BY 'password';

• 创建用户的同时授予权限

  GRANT ALL ON databasename.* TO username@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

2 删除用户

DROP USER username@localhost;

3 修改用户密码

• 命令修改

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

• 更新 User 表

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

• mysqladmin

mysqladmin -uroot -pold_password password new_password

4 解决忘记root密码

• 关闭 mysql 服务

• 进入mysql/bin 目录

• 输入mysqld --skip-grant-tables

  • –skip-grant-tables ：启动MySQL服务的时候跳过权限表认证

• 打开新终端，进入mysql/bin 目录

• 打开mysql命令行

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

二. 权限

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

FLUSH PRIVILEGES;

1 在多个层次上授予权限

• 服务器

  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';

2 对不同用户角色的授权（权限可自选，这里只是参考）

• 为普通用户添加权限

  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';

3 查看授权

 SHOW GRANTS FOR username;

4 撤销在所有数据库上的权限

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

5 命令分析

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

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

  • 'username'@'%'：可以从任何地点接入

  • 'username'@'192.168.1.%'：192.168.1 IP下的局域网都可接入

  • 'username'@'%.website.com'：可以从website.com接入

  • 'username'@'localhost': 只可以本机登录

  • localhost 通过UNIXsocket连接，不会被解析为IP地址

    127.0.0.1 通过TCP/IP协议连接，只能本机访问

    ::1 ::1 支持ipv6，等同于ipv4的127.0.0.1

6 权限列表

权限名	含义
ALTER	修改表和索引
CREATE	创建数据库和表
DELETE	删除表中已有的记录
DROP	抛弃(删除)数据库和表
INDEX	创建或抛弃索引
INSERT	向表中插入新行
REFERENCE	外键权限
SELECT	检索表中的记录
UPDATE	修改现存表记录
FILE	读或写服务器上的文件
PROCESS	查看服务器中执行的线程信息或杀死线程
RELOAD	重载授权表或清空日志、主机缓存或表缓存
SHUTDOWN	关闭服务器
ALL	所有权限，ALL PRIVILEGES同义词
USAGE	特殊的 “无权限” 权限