MySQL - Users and Permissions

  1. 1. 1.1 查看用户
    1. 1.1. > Host 中有两种情况 localhost, %
  2. 2. 1.2 创建用户
  3. 3. 2.1 查看 root 的权限
  4. 4. 2.2 自定义用户的权限
    1. 4.1. 给 dbman 用户配置权限。
  5. 5. 2.3 验证
  6. 6. 2.4 删除用户
  7. 7. 2.5 Disable remote root logins into MySQL

MySQL 用户、权限基本操作


## 1. MySQL 用户管理

MySQL 的用户管理是在一张系统数据库 mysql > user 里进行管理的。

用户的操作，都是对该库该表进行操作。

1.1 查看用户

1
SELECT User, Host FROM mysql.user;
1
2
3
4
5
6
7
+--------+-----------+
| User   | Host      |
+--------+-----------+
| dbman  | %         |
| root   | %         |
| root   | localhost |
+--------+-----------+

> Host 中有两种情况 localhost, %

  • localhost 表示只能本地登录
  • % 表示支持远程登录

这两种登录模式是分别单独配置的，所以表里会有两条记录。

1.2 创建用户

1
CREATE USER 'dbman'@'%' IDENTIFIED BY 'password';
  • dbman 为用户名
  • % 为远程登录，可配置为 localhost
  • password 为登录密码

## 2. 权限基本操作

因为一个用户在远程和本地登录时可以有不同的权限，所以这里查看权限时，要加上 Host 条件的。

2.1 查看 root 的权限

1
SHOW GRANTS FOR 'root'@'%';

显示 root 在远程访问中有着所有的权限。

1
2
3
4
5
+-------------------------------------------------------------+
| Grants for root@%                                           |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+

2.2 自定义用户的权限

给 dbman 用户配置权限。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 远程连接，所有数据库，所有权限
GRANT ALL PRIVILEGES ON *.* TO 'dbman'@'%';

-- 本地连接，所有数据库，所有权限
GRANT ALL PRIVILEGES ON *.* TO 'dbman'@'localhost';

-- 远程连接，所有数据库，所有权限
GRANT ALL PRIVILEGES ON *.* TO 'dbman'@'%';

-- 本地连接，指定数据库 (db_test)，所有权限，并设连接密码
GRANT ALL PRIVILEGES ON db_test.* To 'dbman'@'localhost' IDENTIFIED BY 'password';

-- 远程连接，指定数据库，指定权限，并设连接密码
GRANT SELECT,UPDATE ON db_test.* To 'dbman'@'%' IDENTIFIED BY 'password';

-- After making changes to permissions/user accounts, make sure you flush the provilege tables using the following command:
FLUSH PRIVILEGES;

2.3 验证

1
SHOW GRANTS FOR 'dbman'@'%';
1
2
3
4
5
+------------------------------------+
| Grants for d2team@%                |
+------------------------------------+
| GRANT USAGE ON *.* TO 'd2team'@'%' |
+------------------------------------+

2.4 删除用户

Just as you can delete databases with DROP, you can use DROP to delete a user altogether:

1
DROP USER ‘demo’@‘localhost’;

2.5 Disable remote root logins into MySQL

新的 DB 里都会给两个 root 的，一个是 % 另一个是 localhost，而 remote 是最危险的，别人可以通过该用户来进行攻击。

1
2
3
4
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

-- After making changes to permissions/user accounts, make sure you flush the provilege tables using the following command:
FLUSH PRIVILEGES;

## 3. 权限的级别定义

Here is a short list of other common possible permissions that users can enjoy.

  • ALL PRIVILEGES - as we saw previously, this would allow a MySQL user all access to a designated database (or if no database is selected, across the system)
  • CREATE - allows them to create new tables or databases
  • DROP - allows them to them to delete tables or databases
  • DELETE - allows them to delete rows from tables
  • INSERT - allows them to insert rows into tables
  • SELECT - allows them to use the Select command to read through databases
  • UPDATE - allow them to update table rows
  • GRANT OPTION - allows them to grant or remove other users’ privileges. To provide a specific user with a permission, you can use this framework:
1
GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;

## REF::