MySQL - Users and Permissions

  1. 1. 1. MySQL 用户管理
    1. 1.1. 1.1 查看用户
      1. 1.1.1. > Host 中有两种情况 localhost, %
    2. 1.2. 1.2 创建用户
  2. 2. 2. 权限基本操作
    1. 2.1. 2.1 查看 root 的权限
    2. 2.2. 2.2 自定义用户的权限
      1. 2.2.1. 给 dbman 用户配置权限。
    3. 2.3. 2.3 验证
    4. 2.4. 2.4 删除用户
    5. 2.5. 2.5 Disable remote root logins into MySQL
  3. 3. 3. 权限的级别定义
  4. 4. REF::

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::