navicat一键收回权限语句_浅谈MySQL权限管理

在我们安装完数据库后，只能在本地登录数据库，当进行远程登录时，会提示我们错误。这时，我们就要进行相应的配置，给特定的用户赋予特定的操作权限。MySQL中使用GRANT命令和REVOKE命令来管理用户的权限。

查看用户的权限：

(1)：查看当前用户的所有权限(示例中使用root用户登录)，使用show grants命令：

mysql> show grants \G

*************************** 1. row ***************************

Grants for root@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION

*************************** 2. row ***************************

Grants for root@localhost: GRANT BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION

*************************** 3. row ***************************

Grants for root@localhost: GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

3 rows in set (0.00 sec)

(2)：查看指定用户的所有权限，使用show grants for 'user'@'ip地址段'命令：

mysql> show grants for 'compony'@'192.168.0.1' \G

*************************** 1. row ***************************

Grants for 'compony'@'192.168.0.1': GRANT USAGE ON *.* TO `compony`@`192.168.0.1`

*************************** 2. row ***************************

Grants for 'compony'@'192.168.0.1': GRANT SELECT ON `noteclass`.* TO `compony`@`192.168.0.1`

2 rows in set (0.00 sec)

上面表示compony用户在192.168.0.1网络地址登录本机MySQL后，对noteclass数据库具有select权限。

使用GRANT命令进行授权：

grant语法

grant 权限 on 数据库.数据表 to 用户名@IP地址 identified by '密码' with grant option;

//例如

grant all privileges on *.* to 'user'@'192.168.0.1' identified by '123456' with grant option;

权限包括select、update、delete、insert等，详情请参考MySQL官方网站，传送门：https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html

on：表示这些权限对哪些数据库和表生效

to：权限授予的对象

identified by：设置用户的登录密码

with grant option：表示允许用户将自己的权限授予给其他用户

grant命令只能对已存在的用户生效，如果授权的用户不存在，则会出现如下错误：

mysql> grant all on noteclass.* to shi@'192.168.0.1' identified by '123456';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by '123456'' at line 1

使用grant命令给用户授予权限，权限会自动叠加而不是覆盖。在授权后，不要忘了执行命令更新权限表。

full privileges;

使用revoke命令收回授权

revoke命令语法跟grant命令差不多，只需把关键字to换成from即可，并且不需要设置密码。revoke可以收回所有权限，也可以收回部分权限，示例代码如下：

mysql> revoke all privileges on *.* from 'compony'@'192.168.1.0';

mysql> revoke insert,select,update,delete,drop，create，alter on huanqiu.* from 'compony'@'192.168.0.1';

mysql> flush privileges

mysql授权表一共涉及到5个表，分别是user、db、host、tables_priv和columns_priv。

这5张表的内容和用途如下：

1)user表

user表列出可以连接服务器的用户及其口令，并且它指定他们有哪种全局(超级用户)权限。在user表启用的任何权限均是全局权限，并适用于所有数据库。例如，如果你启用了DELETE权限，在这里列出的用户可以从任何表中删除记录，所以在你这样做之前要认真考虑。

2)db表

db表列出数据库，而用户有权限访问它们。在这里指定的权限适用于一个数据库中的所有表。

3)host表

host表与db表结合使用在一个较好层次上控制特定主机对数据库的访问权限，这可能比单独使用db好些。这个表不受GRANT和REVOKE语句的影响，所以，你可能发觉你根本不是用它。

4)tables_priv表

tables_priv表指定表级权限，在这里指定的一个权限适用于一个表的所有列。

5)columns_priv表

columns_priv表指定列级权限。这里指定的权限适用于一个表的特定列。

问题解决：

在配置MySQL远程连接的过程中，可能存在Navicat无法连接的状况，造成这种状况的原因很有可能是MySQL的加密规则不一样，检索user表中的信息，如下：

mysql> select User,Host,plugin from user;

+------------------+----------------+-----------------------+

| User | Host | plugin |

+------------------+----------------+-----------------------+

| compony | 221.217.88.214 | mysql_native_password |

| mysql.infoschema | localhost | caching_sha2_password |

| mysql.session | localhost | caching_sha2_password |

| mysql.sys | localhost | caching_sha2_password |

| root | localhost | caching_sha2_password |

+------------------+----------------+-----------------------+

5 rows in set (0.00 sec)

MySQL8默认的加密方式改为了caching_sha2_password，我们需要把这个改为mysql_native_password。修改方法如下：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';

或者在创建用户时直接指定加密规则：

CREATE USER 'user_name'@'192.168.0.1' IDENTIFIED WITH 'mysql_native_password' BY 'password';

也可以升级客户端修复上面的问题。

在授权时，还可能存在下面的情况，提示信息为You are not allowed to create a user with GRANT，造成该问题的的原因是授权语句中的IP地址写成了%，网上很多教程说%是通配所有的Host，但是在MySQL8中引起错误，具体原因不算太清楚，可能是新版本做了一些修改，如果有了解的可以分享一下。解决这个问题的办法就是把这个%换成用户在user表中对应的Host，更改过后使用flush privileges命令刷新一下权限即可。