Mysql 管理权限

创建用户

create user 'username'@'host' identified by 'password';

• username：创建用户的用户名称
• host：localhost 表示该用户只能本地登录，% 表示该用户可以远程登录
• password：该用户的登录密码

create user 'zenghr'@'%' identified by '123456';

上面的命令创建了一个用户名为 zenghr，并且密码为 123456的用户

修改密码

在使用数据库的同时，难免会遇到修改密码的需求，密码太简单、忘记登陆密码等，记录一下 mysql 修改密码的几种方式：

1. update user表

其实 MySQL 所以的账号信息都存储在 mysql.user 表里面，我们也可以直接通过 update user 表来修改密码

提示

mysql从5.7.6 开始删除了password字段 (opens new window)需要换另一种方式修改密码

# 5.6及之前版本
update mysql.user set password=password('admin') where user='root';
# 5.7及之后版本
update mysql.user set authentication_string=password('admin') where user = 'root';

2. alter user 修改

# 修改密码
alter user 'zenghr'@'%' identified by 'admin';
# 刷新权限
flush privileges;

3. set password 命令

# 修改密码
SET password for 'zenghr'@'%' = password('admin');
# 刷新权限
flush privileges;

忘记 root 密码

忘记 root 密码的场景还是比较常见的，很容易记不得当时设置的密码。这个时候一般常用的方法是跳过权限验证，然后更改 root 密码，之后再启用权限验证。

首先修改配置文件，在 [mysqld] 部分加上一句：skip-grant-tables ，加上此参数的目的是跳过权限验证。

[mysqld]
character_set_server=utf8mb4
# 跳过权限
skip-grant-tables
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4

然后重启 mysql 服务后，就可以不输入密码登录，然后执行修改密码的sql语句

[root@host ~]# mysql

mysql> update mysql.user set authentication_string = password ('admin') where user = 'root' and host = '%';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

修改完 root 密码后，再次去除 skip-grant-tables 参数，然后重启下数据库即可

查看用户权限

使用show grants for 'test'@'%';或者show grants for test;都可以

mysql> show grants for test;
+----------------------------------+
| Grants for test@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' |
+----------------------------------+
1 row in set (0.00 sec)

我们可以看到新创建的用户的默认权限是 usage，表示是无权限

用户授权命令 - grant

Mysql 赋予用户权限命令的简单格式可概括为：

grant 权限 on 数据库对象 to 用户;

1. 授权用户：增删改查数据中所有表的权利

grant select on testdb.* to test@'%';
grant insert on testdb.* to test@'%';
grant update on testdb.* to test@'%'; 
grant delete on testdb.* to test@'%';

授权多个权限时，可以用逗号分隔

grant select, insert, update, delete on testdb.* to test@'%';

2. 授权用户：创建、删除、视图等权限

grant create on testdb.* to test@'%';
grant alter on testdb.* to test@'%';
grant drop on testdb.* to test@'%';

3. 授权用户所有权限

grant all on *.* to test@'%';

上面的 sql 语句表示 授权 test 用户管理 mysql 中所有数据库的所有权限

授权某个数据库的权限

grant all on *testdb.* to test@'%';

4. grant 可用的权限列表

权限名称	对应user表中的字段	说明
SELECT	Select_priv	表示授予用户可以使用 SELECT 语句访问特定数据库中所有表和视图的权限。
INSERT	Insert_priv	表示授予用户可以使用 INSERT 语句向特定数据库中所有表添加数据行的权限。
DELETE	Delete_priv	表示授予用户可以使用 DELETE 语句删除特定数据库中所有表的数据行的权限。
UPDATE	Update_priv	表示授予用户可以使用 UPDATE 语句更新特定数据库中所有数据表的值的权限。
REFERENCES	References_priv	表示授予用户可以创建指向特定的数据库中的表外键的权限。
CREATE	Create_priv	表示授权用户可以使用 CREATE TABLE 语句在特定数据库中创建新表的权限。
ALTER	Alter_priv	表示授予用户可以使用 ALTER TABLE 语句修改特定数据库中所有数据表的权限。
SHOW VIEW	Show_view_priv	表示授予用户可以查看特定数据库中已有视图的视图定义的权限。
CREATE ROUTINE	Create_routine_priv	表示授予用户可以为特定的数据库创建存储过程和存储函数的权限。
ALTER ROUTINE	Alter_routine_priv	表示授予用户可以更新和删除数据库中已有的存储过程和存储函数的权限。
INDEX	Index_priv	表示授予用户可以在特定数据库中的所有数据表上定义和删除索引的权限。
DROP	Drop_priv	表示授予用户可以删除特定数据库中所有表和视图的权限。
CREATE TEMPORARY TABLES	Create_tmp_table_priv	表示授予用户可以在特定数据库中创建临时表的权限。
CREATE VIEW	Create_view_priv	表示授予用户可以在特定数据库中创建新的视图的权限。
EXECUTE ROUTINE	Execute_priv	表示授予用户可以调用特定数据库的存储过程和存储函数的权限。
LOCK TABLES	Lock_tables_priv	表示授予用户可以锁定特定数据库的已有数据表的权限。
ALL 或 ALL PRIVILEGES 或 SUPER	Super_priv	表示以上所有权限/超级权限

取消授权命令 - revoke

撤销已经赋予给 MySQL 用户权限的权限，revoke 跟 grant 的语法差不多，只需要把关键字 to 换成 from 即可：

revoke all on "*.*" from test@'%';

删除用户

drop user test@'%';

drop user命令会删除用户以及对应的权限，执行命令后你会发现mysql.user表和mysql.db表的相应记录都消失了

常用命令组

创建用户并授予指定数据库全部权限：适用于 Web 应用创建 Mysql 用户

create user test@'%' identified by '123456';
grant all on testdb.* to test@'%';
flush  privileges;