关于mysql数据库的用户权限问题总结

按照我的步骤，你一定可以学会如何设置mysql用户权限问题，我是敢于打包票的，下面的每一条命令我都给解释清楚！

这里不仅列出所有权限问题，还给列出了很多常用命令，可以学习一下的。

查看mysql的版本：
mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.60-log |
+------------+
1 row in set (0.00 sec)

 

查看所有用户的权限及用户分配情况

mysql> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | %         |
| root | 127.0.0.1 |
| root | ::1       |
+------+-----------+
3 rows in set (0.00 sec)o

插入这个表一个用户test,  密码：123456, 登录的IP主机号不限

mysql> insert into mysql.user(Host,User,Password) values('%','test',password('123456'));
Query OK, 1 row affected, 3 warnings (0.00 sec)

再次查询所有用户及权限

mysql> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | %         |
| test | %         |
| root | 127.0.0.1 |
| root | ::1       |
+------+-----------+
4 rows in set (0.00 sec)

刷新缓存，使刚才的设置生效

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

退出mysql查询命令行

mysql> exit
Bye

测试一下，刚刚设置的用户是否可以登录成功。

[root@izm5ed10hr1juhchtamtojz java]# mysql -utest -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 60
Server version: 5.5.60-log Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

上面的显示信息说明是可以登录成功的，那么退出即可，我们现在要给用户赋权限了，必须使用最高的权限用户root给刚才新增的用户赋予权限！

mysql> exit
Bye

登录root用户
[root@izm5ed10hr1juhchtamtojz java]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 61
Server version: 5.5.60-log Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

赋予test用户增加、修改、查询的权限

mysql> grant select,update,insert on schedule.* to test@localhost identified by '123456';
Query OK, 0 rows affected (0.00 sec)

展示test用户的权限情况。<!--糟糕，居然是USAGE,我查了一下，貌似这个意思是相当于什么权限没有-->

mysql>  show grants for 'test';
+-----------------------------------------------------------------------------------------------------+
| Grants for test@%                                                                                   |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

重新查询一下，试试这种方式，@后面是当前系统的主机号（IP）。

mysql> show grants for 'test'@'120.27.17.31';
ERROR 1141 (42000): There is no such grant defined for user 'test' on host '120.27.17.31'

上面居然报错了，说明没有这种命令啊，后面的翻译是：没有这种权限定义在这个IP地址下给test用户。没办法，我换了下面的命令，还是和前面那一条是一样的结果啊，哎，重新弄吧
mysql> show grants for 'test'@'%';
+-----------------------------------------------------------------------------------------------------+
| Grants for test@%                                                                                   |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

只能删除这个test用户了，重新来吧！

mysql> drop user test;
Query OK, 0 rows affected (0.00 sec)

查询用户情况

mysql> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | %         |
| root | 127.0.0.1 |
| root | ::1       |
| test | localhost |
+------+-----------+
4 rows in set (0.00 sec)

赋予权限给liuniu这个用户，<!--注意：此时还没创建这个用户，居然给我成功了，这个mysql也是真会自作多情啊-->

mysql>  grant select,update,insert  on schedule.*  to liuniu;
Query OK, 0 rows affected (0.00 sec)

再次查询用户情况

mysql> select user,host from mysql.user;
+--------+-----------+
| user   | host      |
+--------+-----------+
| liuniu | %         |
| root   | %         |
| root   | 127.0.0.1 |
| root   | ::1       |
| test   | localhost |
+--------+-----------+
5 rows in set (0.00 sec)

展示用户权限，结果不错，居然是增加、修改、查询都成功赋予给liuniu这个用户了

mysql> show grants for 'liuniu'@'%';
+--------------------------------------------------------------+
| Grants for liuniu@%                                          |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'liuniu'@'%'                           |
| GRANT SELECT, INSERT, UPDATE ON `schedule`.* TO 'liuniu'@'%' |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

退出mysql命令行编辑模式

mysql> exit
Bye

登录liuniu用户
[root@izm5ed10hr1juhchtamtojz java]# mysql -uliuniu -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'liuniu'@'localhost' (using password: YES)
[root@izm5ed10hr1juhchtamtojz java]# mysql -uliuniu -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'liuniu'@'localhost' (using password: YES)

我靠，上面居然都失败了，原来我忘记设置密码了，不用使用密码就能进来了啊
[root@izm5ed10hr1juhchtamtojz java]# mysql -uliuniu 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 69
Server version: 5.5.60-log Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

没有密码，这个用户我要了也没有什么用，还是删除吧！

居然error,  删除失败了，我当前登录的是liuniu用户，居然想自己删除自己，这不是扯淡嘛，自杀不成功啊！

mysql> drop user liuniu;
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation

先看下权限吧，居然自己看自己啊，哎，这也是正常的。
mysql> show grants for 'liuniu'@'%';
+--------------------------------------------------------------+
| Grants for liuniu@%                                          |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'liuniu'@'%'                           |
| GRANT SELECT, INSERT, UPDATE ON `schedule`.* TO 'liuniu'@'%' |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

退出吧，重新登录root吧
mysql> exit
Bye

前奏结束了，下面开始好戏了哦！

1.首先登录root用户
[root@izm5ed10hr1juhchtamtojz java]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 70
Server version: 5.5.60-log Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

2.删除用户liuniu,这个用户忘记设置密码，没有实际作用。（提示：没有这个用户可以忽略这个步骤）

mysql> drop user liuniu;
Query OK, 0 rows affected (0.00 sec)

3.赋予liuniu这个用户查询、修改、插入的权限

mysql> grant select,update,insert on schedule.* to liuniu  identified by '123456';
Query OK, 0 rows affected (0.00 sec)

4.显示用户权限

mysql> show grants for 'liuniu'@'%';
+-------------------------------------------------------------------------------------------------------+
| Grants for liuniu@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'liuniu'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT, INSERT, UPDATE ON `schedule`.* TO 'liuniu'@'%'                                          |
+-------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
5.补充一个删除权限给liuniu这个用户吧

mysql> grant delete on schedule.* to liuniu  identified by '123456';
Query OK, 0 rows affected (0.00 sec)

6.刷新缓存

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

7.查询liuniu用户的权限

mysql> show grants for 'liuniu'@'%';
+-------------------------------------------------------------------------------------------------------+
| Grants for liuniu@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'liuniu'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `schedule`.* TO 'liuniu'@'%'                                  |
+-------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

ok，现在新增的这个用户就具有了增删改查的权限了！