MySQL操作之用户管理权限管理：(DC)（五）

一、简介

MySQL中用户分为root用户和普通用户。root用户为超级管理员，具有所有权限(创建用户、删除用户、管理用户)。

二、用户管理

2.1 User表

• 新建MySQL时，会自动安装一个mysql的数据库，该数据库下面的表都是权限表。
  
• 其中：user表时最重要权限表。记录了允许连接到服务器的账号信息以及一些全局权限信息。
  
• user表有42个字段，大致分为4类。
  

1. 用户列

• 包括Host、User、Password，分别代表主机名、用户名和密码。
• Host、User：为联合主键。
• Host、User、Password：三个字段都匹配时，才会允许建立连接。
• 修改密码：只需要修改User表中Password即可。

2. 权限列

• 包括Select_priv、Insert_priv、Update_priv等以priv结尾的字段，这些字段决定了用户的权限。
• Select_priv：查询权限。
• Insert_priv：插入权限。
• Update_priv：更新权限。
• 数据类型都是枚举类型Enum。
• 默认值为：N(没有权限)。

3. 安全列

• 用户管理用户的安全信息，其中包含6个字段。
• ssl_type和ssl_cipher：用户加密。
• x509_issuer和x509_subject标准：用来标识用户。
• plugin和authentication_string：用于存储于授权相关的插件。

4. 资源控制列

• 用于限制用户使用的资源。其中包括4个字段。
• max_questions：每小时允许用户执行查询操作的次数。
• max_updates：每小时允许用户执行更新操作的次数。
• max_connection：每小时允许用户建立连接的次数。
• max_user_connection：每小时允许用户同时建立连接的次数。

2.2 创建普通用户

有中方式创建：

• 使用GRANT语句创建用户
• 使用CREATE USER语句创建用户
• 使用Insert语句创建用户

1. 使用GRANT语句创建用户

• GRANT语句不仅可以创建新用户，还可以对用户进行授权。
  
• 该语句会自动加载权限表，不需要手动刷新。
  
• 而且安全、准确、错误少。
  
• 使用GRANT语句创建用户是最常用的方法。
  
• 创建用户时，MySQL会对用户的密码自动加密，以提高安全性。
  

语法：

GRANT privileges ON database.table
  TO `username`@`hostname` [IDENTIFIED BY[PASSWORD]`password`]

• privileges：表示该用户具有的权限信息。
• database.table：表示新用户的权限范围表，可以在指定的库、表上使用权限。
• username：新用户名称。
• hostname：主机名。
• Password：新用户的密码。

创建新用户：用户名user01，密码1581145，授予对test.student有查询权限。

GRANT SELECT ON test.student TO `user01`@`1581145` IDENTIFIED BY `123`

2. 使用CREATE USER语句创建用户

• 使用CREATE USER语句创建新用户，服务器会自动修改授权表。不需要手动刷新。
• 但，新建的用户是没有权限的。

CREATE USER `username`@`hostname` [IDENTIFIED BY [PASSWORD]`password`]

• username：表示新创建的用户名。
• hostname：表示主机名。
• password：表示用户的密码。
• [PASSWORD]：该参数是可选的，假如密码为普通字符串就不需要使用。

语句：

CREATE USER `user02`@`127.0.0.1` IDENTIFIED BU `123`

3. 使用Insert语句创建用户

• 在User表添加数据。
• 通常只需要添加Host、User、Password这三个字段即可，其它字段取默认值。
• ssl_cipher、x509_issuer、x509_subject字段没有默认值，也需要添加。
• 需要手动刷新权限表或者重启MySQL服务。

新建用户

INSERT INTO mysql.`user` ( `host`, `user`, `Password`, ssl_cipher, x509_issuer, x509_subject )
  VALUES( 'localhost', 'user03', PASSWORD ( '123' ), '', '', '' );

刷新权限：

FLUSH PRIVILEGES;

2.3 删除普通用户

1. 使用DROP User语句删除用户

语法

DROP USER `username`@`hostname` [,`username`@`hostname`];

DROP USER `user01`@`localhost`;

2. 使用DELETE语句删除用户

• DELETE不仅可以删除普通表数据，还可以删除user表数据。

语法

DELETE FROM mysql.user WHERE Host='hostname' AND User='username';

sql

DELETE FROM mysql.user WHERE Host = 'localhost' AND User = 'user02';

2.4 修改用户密码

1. 使用mysqladmin修改密码

dd

mysqladmin -u username [-h hostname] -p password new_password

• username：要修改的用户名。
• -h hostname：可以不写，默认localhost。
• -p password：为关键字。
• new_password：新设置的密码。

修改密码：

mysqladmin -u root -p password 1581145

上述语句执行完成后，会提示输入密码。这个密码是旧密码。

2. 使用Update语句修改密码

语法：

UPDATE mysql.user set Password=PASSWORD('new_password')
  WHERE User = 'username' AND Host='hostname';

sql

UPDATE mysql.user set Password=PASSWORD('1581145')
  WHERE User = 'root' AND Host='localhost';

刷新权限：

FLUSH PRIVILEGES;

3. 使用SET语句修改密码

• 用户登录MySQL服务器以后，可以使用set语句，修改密码。
• 需使用PASSWORD('new_password')进行密码加密。

语法：

SET PASSWORD FOR `username`@'hostname' = PASSWORD('new_password');
SET PASSWORD = PASSWORD('new_password');

SET PASSWORD = PASSWORD('1581145');

4. root使用GRANT修改普通用户密码

语法：

GRANT USAGE ON *.* TO 'usernmae'@'localhost' IDENTIFIED BY [PASSWORD]‘new_password';

2.5 解决root用户密码丢失

• --skip-grant-tables：可以停止MySQL的权限判断。也就是任何用户都可以登录MySQL。

1、停止MySQL服务

net stop mysql;

2、使用--skip-grant-tables启动MySQL服务

mysqld --skip-grant-tables

3、登录MySQL服务器

mysql -u -root

4、使用Update语句设置root用户密码

UPDATE mysql.user SET PASSWORD = PASSWORD ( '158145' ) 
  WHERE User = 'root' AND Host = 'localhot';

5、加载权限表

FLUSH PRIVILEGES;

三、权限管理

3.1 MySQL的权限

MySQL的权限信息，被存储在user、db、host、tables、priv、cloumn_priv和procs_priv中。当MySQL启动时，会加载这些表，并将权限信息读取到内存中。

• Create和Drop权限：可以创建数据库、表、索引，或者删除已有的数据库、表、索引。
• Insert、Delete、Update和Select权限：对数据库表进行增删改查。
• Index权限：可以创建和删除索引。适用于所有表。
• Alter权限：用于修改表的结构或者重命名表。
• Grant权限：允许用户为其它用户授权。可用户数据库和表。
• File权限：能读写MySQL服务器上的所有文件。

3.2 授予权限

语法：

GRANT privileges [(columns)][,privileges [(columns)]...] ON database.table
  TO `username`@`hostname` [IDENTIFIED BY[PASSWORD]`password`]
  [,`username`@`hostname` [IDENTIFIED BY[PASSWORD]`password`]]...
  [WITH with_option [with_option]...]

• privileges：表示权限类型。
• columns：表示权限作用域某一列。
• IDENTIFIED BY：为用户设置密码。

其中WITH关键字有5个参数取值：

1. GRANT OPTION：将自己权限授予其它用户。
2. MAX_QUERIED_PER_HOUR count：设置每小时最多可执行多少次count查询。
3. MAX_UPDATES_PER_HOUR count：设置每小时最多可执行多少次更新。
4. MAX_CONNECTIONS_PER_HOUR count：设置每小时最大连接数。
5. MAX_USER_CONNECTIONS：设置每个用户最多可以同时建立的连接数量。s

授予权限：

GRANT INSERT,SELECT ON *.* TO 'user04'@'localhost' IDENTIFIED BY '123' 
  WITH GRANT OPTION;

3.3 查看权限

SHOW GRANTS：用来查看用户的权限

SHOW GRANTS FOR 'username'@'hostname';

SHOW GRANTS FOR 'root'@'1581145';

结果：

root用户

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' 
IDENTIFIED BY PASSWORD '*B105526BD04216F24C8DC48AF956866AA539F35C' WITH GRANT OPTION

普通用户( SELECT,INSERT)

GRANT SELECT,INSERT ON *.* TO 'user03'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'

3.4 收回权限

REVOKE：用于收回用户的权限。

REVOKE privileges [columns] [,privileges [columns]] ON database.table
  FROM 'username'@'hostname'[,username'@'hostname']...

• privileges：表示参数列表。
• columns：表示作用在哪一列。

收回Insert权限

REVOKE INSERT ON *.* FROM 'user04'@'localhost';

收回全部权限

REVOKE ALL PRIVILEGES,GRANT OPTION ON *.* FROM 'user04'@'localhost';