mysql用户和权限管理(Linux系统下)

时间:2022-10-27 09:15:30

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

mysql用户和权限管理(Linux系统下)mysql用户和权限管理(Linux系统下)
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)
查询user表

一、重置密码

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

 找回密码有前提,前提就是你能够停止mysql服务。
1)停服务
# /etc/init.d/mysqld stop
2)使用跳过授权表的方式启动数据库
# mysqld_safe --skip-grant-tables --user=mysql &
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用户和权限管理(Linux系统下)mysql用户和权限管理(Linux系统下)
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用户和权限管理(Linux系统下)mysql用户和权限管理(Linux系统下)
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用户和权限管理(Linux系统下)mysql用户和权限管理(Linux系统下)
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用户和权限管理(Linux系统下)mysql用户和权限管理(Linux系统下)
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用户和权限管理(Linux系统下)mysql用户和权限管理(Linux系统下)
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用户和权限管理(Linux系统下)mysql用户和权限管理(Linux系统下)
mysql> revoke insert,select on *.* from maoxian@localhost;
Query OK, 0 rows affected (0.00 sec)
回收部分权限
mysql用户和权限管理(Linux系统下)mysql用户和权限管理(Linux系统下)
mysql> revoke all on *.* from maoxian@localhost;
Query OK, 0 rows affected (0.00 sec)
回收全部权限