mysql用户和权限管理（Linux系统下）

在mysql自带的库中有一个mysql，这个库包含了太多的东西，其中有一张表user，这张表存储了所有的用户信息。

mysql> select user,host,password from user;
+------+-----------------------+-------------------------------------------+
| user | host                  | password                                  |
+------+-----------------------+-------------------------------------------+
| root | localhost             | *E01F5425C006D5AF5E3E22EEF9D6AF6BD19FDFED |
| root | localhost.localdomain |                                           |
| root | 127.0.0.1             |                                           |
| root | ::1                   |                                           |
|      | localhost             |                                           |
|      | localhost.localdomain |                                           |
+------+-----------------------+-------------------------------------------+
6 rows in set (0.00 sec)

一、重置密码

首先来解决，如果root密码忘记了，该怎么解决？

 找回密码有前提，前提就是你能够停止mysql服务。
1）停服务
# /etc/init.d/mysqld stop
2）使用跳过授权表的方式启动数据库
# mysqld_safe --skip-grant-tables --user=mysql &

关闭：mysqladmin   shutdown

3）匿名登录
# mysql
4）修改user表
mysql> update user set password='' where user='root' and host='localhost';
5）重启mysql服务
# /etc/init.d/mysqld restart

6）重设密码

 

二、用户管理

1.创建用户

  语法：create user 用户名@主机 identified by 密码;

mysql> create user kebi@localhost identified by '123';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password from user;
+------+-----------------------+-------------------------------------------+
| user | host                  | password                                  |
+------+-----------------------+-------------------------------------------+
| root | localhost             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | localhost.localdomain |                                           |
| root | 127.0.0.1             |                                           |
| root | ::1                   |                                           |
|      | localhost             |                                           |
|      | localhost.localdomain |                                           |
| kebi | localhost             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+------+-----------------------+-------------------------------------------+
7 rows in set (0.00 sec)

 

2.删除用户

语法：drop user 用户名@主机;

mysql> select user,host,password from user;
+------+-----------------------+-------------------------------------------+
| user | host                  | password                                  |
+------+-----------------------+-------------------------------------------+
| root | localhost             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | localhost.localdomain |                                           |
| root | 127.0.0.1             |                                           |
| root | ::1                   |                                           |
|      | localhost             |                                           |
|      | localhost.localdomain |                                           |
+------+-----------------------+-------------------------------------------+
6 rows in set (0.00 sec)

 

3.授权

grant不仅可以用来授权，还可以用来创建用户；

授权的语法：grant 权限列表 on 库名.表名 to 用户@主机 identified by '密码';

刷新权限：flush privileges;

mysql> grant all on *.* to kebi@'192.168.%.%' identified by '123';   #*.*：所有库上的所有表； 192.168.%.%：192.168内的所有Ip；123：密码
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password from user;
+------+-----------------------+-------------------------------------------+
| user | host                  | password                                  |
+------+-----------------------+-------------------------------------------+
| root | localhost             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | localhost.localdomain |                                           |
| root | 127.0.0.1             |                                           |
| root | ::1                   |                                           |
|      | localhost             |                                           |
|      | localhost.localdomain |                                           |
| kebi | 192.168.%.%           | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+------+-----------------------+-------------------------------------------+
7 rows in set (0.00 sec)

mysql> grant select,insert on class_7.* to maoxian@localhost identified by '123';
授权 读，写 在库 class_7上的所有表 对于 毛线 用123登陆
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password from user;
+---------+-----------------------+-------------------------------------------+
| user    | host                  | password                                  |
+---------+-----------------------+-------------------------------------------+
| root    | localhost             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root    | localhost.localdomain |                                           |
| root    | 127.0.0.1             |                                           |
| root    | ::1                   |                                           |
|         | localhost             |                                           |
|         | localhost.localdomain |                                           |
| kebi    | 192.168.%.%           | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| maoxian | localhost             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+---------+-----------------------+-------------------------------------------+
8 rows in set (0.00 sec)

 

4.查看用户权限

回收语法：show grants for 用户@主机;

mysql> show grants for maoxian@localhost;
+----------------------------------------------------------------------------------------------------------------+
| Grants for maoxian@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'maoxian'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT SELECT, INSERT ON `class_7`.* TO 'maoxian'@'localhost'                                                   |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

 

5.回收权限

回收权限有两种方法：删除用户和revoke

回收语法：  revoke 权限 on 库名.表名 from 用户@主机；

mysql> revoke insert,select on *.* from maoxian@localhost;
Query OK, 0 rows affected (0.00 sec)

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