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)
