MySQL | MySQL 数据库系统（三）- 数据库的用户授权

Original JackTian 杰哥的IT之旅 2022-06-06

收录于合集

点击上方“杰哥的IT之旅”，选择“置顶公众号”

干货、福利第一时间送达！

前言

MySQL 数据库系统的 root 用户账号其实都是拥有对所有库、所有表的全部权限，频繁使用 root 账户，同时也会给数据库服务器带来一定的安全隐患。因此，我们在实际工作中，通常会建立一些权限低的用户，来负责某一部分库及表的管理与维护，甚至对查询、修改、删除等操作在详细做进一步的限制，从而将数据库服务器的安全降至最低。

1.授予权限grant语句：用来设置数据库用户的访问权限。当指定的用户名不存在时，grant 语句将会创建新的用户。否则，将用于修改用户信息。

语句基本格式

grant 权限列表 on 库名.表名 to 用户名@来源地址 [ identified by '密码']

权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔。例如：select、insert、update，使用all表示所有权限，可授权执行任何操作。
库名.表名：用于指定授权操作的库和表的名称，其中可使用通配符*，使用auth.*表示授权操作的对象为auth库中的所有表。
用户名@来源地址：用于指定用户名称和允许访问的客户机地址。来源地址可以是域名 IP 地址 %表示某个区域或网段中的所有地址。例如：%.google.com和172.16.123.%等；
IDENTIFIED BY：用于设置用户连接数据库时所使用的密码字符串。新建用户时，可省略IDENTIFIED BY部分，用户密码将为空。

例如：你要执行以下操作添加一个名为xiaojack的数据库用户，并允许从本机进行访问，对auth库中的所有表具有查询权限，密码为123.com。

使用grant语句授权的用户记录，会保存到mysql库的user、db、host、tables_priv等相关表中，无须刷新即可生效。

mysql> grant select on auth.* TO 'xiaojack'@'localhost' identified by '123.com';
Query OK, 0 rows affected (0.10 sec)

切换到其他shell终端，可以将用户xiaojack的身份来连接数据库。

当执行授权的数据库操作时将被允许，而执行非授权的数据库操作时将被拒绝。

允许用户xiaojack查看auth库中的users表数据，但禁止查询其他库中的数据。

[root@localhost ~]# mysql -u xiaojack -p
Enter password: 
······ // 省略部分内容
mysql> select * from auth.users;
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| jacktian  | *B2B366CA5C4697F31D4C55D61F0B17E70E5664EC |
+-----------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'xiaojack'@'localhost' for table 'user'

在企业，通常情况下，数据库服务器与网站服务器是独立的。当我们在使用MySQL数据库时，根据实际情况，应当建立新用户授权，允许授权用户从网站服务器来进行访问数据库服务器。创建一个库，并授予所有权限，来限制访问的来源IP地址。

新建一个名为：mongodb库，并授权从IP地址为192.168.3.123的主机连接，用户名为：dbuser、密码为：pwd@123.com，允许在mongodb库中执行所有操作。

mysql> create database mongodb;
Query OK, 1 row affected (0.15 sec)

mysql> grant all on mongodb.* to 'dbuser'@'192.168.3.123' identified by 'pwd@123.com';
Query OK, 0 rows affected (0.05 sec)

2.查看权限

show grant语句：用于查看数据库用户的授权信息。

通过for子句指定查看的用户对象，且指定的用户对象必须与授权使用的用户对象是一致的。

语句基本格式

show grants for 用户名@来源地址

例如：执行以下操作，可查看用户dbuser从主机192.168.3.123访问数据库时的授权信息。

USAGE权限对应的授权记录中包含了用户的连接密码字符。

mysql> show grants for 'dbuser'@'192.168.3.123';
+-------------------------------------------------------------------------------------------------------------------+
| Grants for dbuser@192.168.3.123                                                                                   |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dbuser'@'192.168.3.123' IDENTIFIED BY PASSWORD '*30D9E97027C9D62E7840B4BABB3F3E55D59C0B0E' |
| GRANT ALL PRIVILEGES ON `mongodb`.* TO 'dbuser'@'192.168.3.123'                                                   |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

3.撤销权限

REVOKE语句：用于撤销指定用户的数据库权限。

撤销权限以后，用户依然可以连接到MySQL数据库，但禁止执行对应的数据库相关操作。

语句基本格式

revoke 权限列表 on 数据库名.表名 from 用户名@来源地址

执行如下操作，可撤销用户xiaojack从本机访问数据库auth的所有权限。

mysql> revoke all on auth.* from 'xiaojack'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'xiaojack'@'localhost';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for xiaojack@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'xiaojack'@'localhost' IDENTIFIED BY PASSWORD '*AC241830FFDDC8943AB31CBD47D758E79F7953EA' |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)