MySQL系列(十)--用户权限及远程访问

秒速五厘米 2021-11-05 12:44 250阅读 0赞

本文基于MySQL8.0，记录一下完整的远程访问的过程，以及这个过程中可能遇到的问题，MySQL运行在阿里云服务器，操作系统：CentOS 7.6 64位

顺便说下，买服务器还是要双十二这种拉新活动再买，用一个新的支付宝账号购买，能便宜一大半，不然双核4G就两千多块了。

这里默认防火墙已经向外暴露3306端口/关闭防火墙了，以及阿里云安全组配置暴露3306端口，在Windows通过Navicat测试连接

#### 随便创建一个用户： ####

创建用户之后，默认加密类型为caching\_sha2\_password

Navicat登录结果：

![1351999-20190710101527705-1606600979.png][]

#### 修改用户加密方式： ####

因为可视化工具只支持旧的加密方式mysql\_native\_password，如果是caching\_sha2\_password是无法远程连接登录的

Navicat登录结果：

![1351999-20190710103002912-1101343751.png][]

因为dev用户支持的是localhost登录，我们需要把他修改为远程访问机器的IP或者%，%代表任意地址

#### 修改用户登录授权地址： ####

mysql> rename user 'dev'@'localhost' to 'dev'@'%';
    Query OK, 0 rows affected (0.00 sec)

Navicat登录结果：

![1351999-20190710103308469-394107751.png][]

到这里我们实现访问登录，但是登录dev用户，我们发现只有一个数据库

![1351999-20190710103606128-6129826.png][]

#### 查看dev用户权限： ####

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

dev用户的权限为USAGE，MySQL用户权限介绍如下：

-- 权限列表
    ALL [PRIVILEGES]    -- 设置除GRANT OPTION之外的所有简单权限
    ALTER    -- 允许使用ALTER TABLE
    ALTER ROUTINE    -- 更改或取消已存储的子程序
    CREATE    -- 允许使用CREATE TABLE
    CREATE ROUTINE    -- 创建已存储的子程序
    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        -- 允许用户运行已存储的子程序
    FILE    -- 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
    INDEX     -- 允许使用CREATE INDEX和DROP INDEX
    INSERT    -- 允许使用INSERT
    LOCK TABLES        -- 允许对您拥有SELECT权限的表使用LOCK TABLES
    PROCESS     -- 允许使用SHOW FULL PROCESSLIST
    REFERENCES    -- 未被实施
    RELOAD    -- 允许使用FLUSH
    REPLICATION CLIENT    -- 允许用户询问从属服务器或主服务器的地址
    REPLICATION SLAVE    -- 用于复制型从属服务器（从主服务器中读取二进制日志事件）
    SELECT    -- 允许使用SELECT
    SHOW DATABASES    -- 显示所有数据库
    SHOW VIEW    -- 允许使用SHOW CREATE VIEW
    SHUTDOWN    -- 允许使用mysqladmin shutdown
    SUPER    -- 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。
    UPDATE    -- 允许使用UPDATE
    USAGE    -- “无权限”的同义词
    GRANT OPTION    -- 允许授予权限

USAGE就是无权限，我们现在给dev用户添加select权限，但是只针对数据库mysql的test表，查看权限

mysql> grant select on mysql.test to 'dev'@'%' with grant option;
    Query OK, 0 rows affected (0.01 sec)
    mysql> show grants for 'dev'@'%';
    +---------------------------------------------------------------+
    | Grants for dev@%                                              |
    +---------------------------------------------------------------+
    | GRANT USAGE ON *.* TO `dev`@`%`                               |
    | GRANT SELECT ON `mysql`.`test` TO `dev`@`%` WITH GRANT OPTION |
    +---------------------------------------------------------------+
    2 rows in set (0.00 sec)

Navicat登录结果：只显示一张表

![1351999-20190710105242615-2110413927.png][]

修改用户加密方式：执行select语句是可以的，

SELECT * FROM test;

但是如果执行delete，或者其他DML以及DDL语句，是没有权限的

[SQL]DELETE FROM test WHERE Id=100;
    [Err] 1142 - DELETE command denied to user 'dev'@'202.101.0.2' for table 'test'

后序为了测试方便，把权限赋予所有的数据库的所有表，以及添加update，select权限

mysql> grant select,update, delete on *.* to 'dev'@'%' with grant option;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show grants for 'dev'@'%';
    +--------------------------------------------------------------------+
    | Grants for dev@%                                                   |
    +--------------------------------------------------------------------+
    | GRANT SELECT, insert, UPDATE, DELETE ON *.* TO `dev`@`%` WITH GRANT OPTION |
    | GRANT SELECT ON `mysql`.`test` TO `dev`@`%` WITH GRANT OPTION      |
    +--------------------------------------------------------------------+
    2 rows in set (0.00 sec)

撤销dev用户delete权限：

mysql> revoke delete on *.* from 'dev'@'%';
    Query OK, 0 rows affected (0.01 sec)
    mysql> show grants for 'dev'@'%';
    +---------------------------------------------------------------------------+
    | Grants for dev@%                                                          |
    +---------------------------------------------------------------------------+
    | GRANT SELECT, INSERT, UPDATE, ALTER ON *.* TO `dev`@`%` WITH GRANT OPTION |
    | GRANT SELECT ON `mysql`.`test` TO `dev`@`%` WITH GRANT OPTION             |
    +---------------------------------------------------------------------------+
    2 rows in set (0.00 sec)

撤销dev用户所有权限：

mysql> revoke all privileges on *.* from 'dev'@'%';
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)

**　　PS：flush privileges命令只刷新你MySQL所在机器的权限，如果远程访问，你需要在远程操作所在的机器使用flush privileges进行**

**刷新权限，或者断开连接，并重新连接也会刷新**

到此为止，关于一般用户权限控制的操作讲完了，包括远程访问可能遇到的问题，算是一个入门吧，希望对新手有所帮助。

转载于:https://www.cnblogs.com/huigelaile/p/11162817.html

[1351999-20190710101527705-1606600979.png]: /images/20211105/429236d34bd04b73bffc68d244810317.png
[1351999-20190710103002912-1101343751.png]: /images/20211105/e8b7c9d1e5e440358fc7437a2f02690d.png
[1351999-20190710103308469-394107751.png]: /images/20211105/f27dfb9b883a4d0cb62c16b522852bf8.png
[1351999-20190710103606128-6129826.png]: /images/20211105/1238fb9932a248a3b1c09f07c5a3f165.png
[1351999-20190710105242615-2110413927.png]: /images/20211105/efc894bcdafc4fa28932a4160f7c7e81.png