ITPub博客

首页 > 数据库 > MySQL > mysql用户创建、修改、删除及授权操作的总结

mysql用户创建、修改、删除及授权操作的总结

原创 MySQL 作者:lusklusklusk 时间:2018-09-28 11:23:24 0 删除 编辑

总结

1、mysql命令进行连接如果不带-h,表示user@localhost连接本地localhost数据库服务器,-h后面的ip是数据库服务器的IP

2、要让所有ip都能连,create user时用户名后面加@'%',或不加@(不加@默认就是@'%')

3、mysql执行grant进行授权,权限是追加的,不用担心会覆盖之前的

4、 grant可以同时创建用户、授权、修改密码

5、创建用户建议使用create,因为 create只能创建用户,不能授权 mysql没有类似oracle这样的connect create session权限,mysql只要create了用户就自动拥有了connect create session权限

6、修改密码建议使用alter user比较好,因为alter不会涉及到权限,grant虽然可以修改密码,但是grant命令要带权限选项。

7、如果用户名相同,host对应的网段有相同,哪个用户先生成,就以哪个用户为准

即以下两条,谁先执行,谁先生效,就是说mysql -u test1 -p后面使用谁的密码才能登陆

grant select on test1.* to test123@'192.168.0.0/255.255.0.0' identified by "12345678";

grant select on test1.* to test123@'192.168.10.0/255.255.255.0' identified by "1234567";

8、grant执行的授权,不需要flush privileges就可以生效;update mysql.user表执行的授权,必须要flush privileges才能生效




创建用户

mysql> help create user;

mysql> help grant;

mysql> create user 'user1'@'192.168.10.0/255.255.255.0' identified by '123456';

mysql> grant select,update on db1.* to 'user2'@'192.168.10.0/255.255.255.0' identified by '123456';--创建用户的同时进行了授权


修改密码

mysql> help alter user;

mysql> help grant;

mysql> help set password;

mysql> alter user 'user1'@'192.168.10.0/255.255.255.0' identified by '666666';

mysql> grant select on mysql.user to 'user1'@'192.168.10.0/255.255.255.0' identified by '777777';--修改密码的同时进行了授权

mysql> set password for 'user1'@'192.168.10.0/255.255.255.0'=password('888888');

SET PASSWORD is deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release. ALTER USER is now the preferred statement for assigning passwords.

从MySQL 5.7.6开始,不推荐使用SET PASSWORD,这个用法在未来的MySQL版本中会被抛弃。 ALTER USER现在是首选修改密码的声明。


授权

mysql> help grant;

mysql> grant select,insert,update on *.* to 'user1'@'192.168.10.0/255.255.255.0';--只授权,没有涉及修改密码


删除用户

mysql> help drop user;

mysql> drop user 'user1'@'192.168.10.0/255.255.255.0';




密码相关的参数

mysql> SHOW VARIABLES LIKE '%password%';

mysql> CREATE USER 'mytest'@'192.168.20.0/255.255.255.0' identified by '123456';

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql> show variables like '%validate_password_policy%';

+--------------------------+--------+

| Variable_name            | Value  |

+--------------------------+--------+

| validate_password_policy | MEDIUM |

+--------------------------+--------+

1 row in set (0.01 sec)

mysql> set global validate_password_policy=0;

mysql> show variables like '%validate_password_length%';

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| validate_password_length | 8     |

+--------------------------+-------+

1 row in set (0.00 sec)

mysql> set global validate_password_length=1;




如下实验,DB服务器ip是192.168.10.101,在服务器本地使用mysql登录不加-h表示使用user@localhost登录,-h后面的必须是DB服务器ip

允许所有ip登录,则create user时后面的用户名不加@或create user时后面的用户名加@'%'


[root@mydb ~]# ifconfig |grep 'inet addr'

          inet addr:192.168.10.101  Bcast:192.168.10.255  Mask:255.255.255.0

          inet addr:127.0.0.1  Mask:255.0.0.0

          inet addr:192.168.122.1  Bcast:192.168.122.255  Mask:255.255.255.0


mysql> CREATE USER 'mytest01'@'192.168.20.0/255.255.255.0' identified by '123456';

Query OK, 0 rows affected (0.01 sec)


mysql> CREATE USER 'mytest02'@'192.168.10.0/255.255.255.0' identified by '123456';

Query OK, 0 rows affected (0.00 sec)


mysql> CREATE USER 'mytest03'@'*' identified by '123456';

Query OK, 0 rows affected (0.00 sec)


[root@mydb ~]# mysql -u mytest01 -p123456

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'mytest01'@'localhost' (using password: YES)

--无法登录,因为没有加-h表示使用user@localhost


[root@mydb ~]# mysql -u mytest02 -p123456

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'mytest02'@'localhost' (using password: YES)

--无法登录,因为没有加-h表示使用user@localhost


[root@mydb ~]# mysql -u mytest03 -p123456

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'mytest03'@'localhost' (using password: YES)

--无法登录,因为没有加-h表示使用user@localhost

--'mytest03'@'*',用户名后面加@'*'不是表示所有ip可以登录,应该加@'%'


[root@mydb ~]# mysql -u mytest01 -p123456 -h 192.168.10.101

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'mytest01'@'192.168.10.101' (using password: YES)


[root@mydb ~]# mysql -u mytest02 -p123456 -h 192.168.10.101

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> exit


[root@mydb ~]# mysql -u mytest03 -p123456 -h 192.168.10.101

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'mytest03'@'192.168.10.101' (using password: YES)


[root@mydb ~]# mysql -u mytest02 -p123456 -h 192.168.10.102

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.10.102' (113)

--无法登录,-h后面必须是DB服务器ip


mysql> CREATE USER 'mytest04' identified by '123456';

Query OK, 0 rows affected (0.00 sec)


[root@mydb ~]# mysql -u mytest04 -p123456

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> exit


[root@mydb ~]# mysql -u mytest04 -p123456 -h 192.168.10.101

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql>


[root@mydb ~]# mysql -u mytest04 -p123456 -h 192.168.10.102

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.10.102' (113)


mysql>  create user mytest06@'%' identified by '123456';

Query OK, 0 rows affected (0.01 sec)


[root@mydb ~]# mysql -u mytest06 -p123456

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql>


[root@mydb ~]# mysql -u mytest06 -p123456 -h 192.168.10.101

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql>


[root@mydb ~]# mysql -u mytest06 -p123456 -h 192.168.10.102

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.10.102' (113)




如下实验,用户存在的情况下,grant后面可以不加密码,用户不存在的情况下,grant后面必须加密码

多次执行grant,权限都是追加的。


mytest02@'192.168.10.0/255.255.255.0'已经存在的情况下

mysql> grant select on test1.* to mytest02@'192.168.10.0/255.255.255.0';

Query OK, 0 rows affected (0.03 sec)


mytest03@'192.168.10.0/255.255.255.0'还不存在的情况下

1、第一步报错了,因为没有这个用户

2、第二步,创建了用户密码并授了test1数据库的select权限,见表mysql.user和mysql.db

3、第三步,用户存在的情况下,追加了全局权限并修改了密码,见mysql.user

4、第四步,用户存在的情况下,追加了全局权限并修改了密码,见mysql.user

5、第五步,用户存在的情况下,追加了test1数据库的select权限并修改了密码,见mysql.db


mysql> grant select on test1.* to mytest03@'192.168.10.0/255.255.255.0';

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements


mysql> grant select on test1.* to mytest03@'192.168.10.0/255.255.255.0' identified by "123_Tn_123";

Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> grant select,update on *.* to mytest03@'192.168.10.0/255.255.255.0'  identified by "123_Tn123";

Query OK, 0 rows affected, 1 warning (0.02 sec)


mysql> grant select,insert on *.* to mytest03@'192.168.10.0/255.255.255.0'  identified by "123_Tr99";

Query OK, 0 rows affected, 1 warning (0.02 sec)


mysql> grant select,update on test1.* to mytest03@'192.168.10.0/255.255.255.0'  identified by "123_TR00";

Query OK, 0 rows affected, 1 warning (0.00 sec)




如下实验:如果用户名相同,host对应的网段有相同,哪个用户先生成,就以哪个用户为准


数据库服务器的IP是192.168.10.101

客户端的IP是192.168.10.1

mysql> grant select on test1.* to test123 identified by "123456";

mysql> grant select on test1.* to test123@'192.168.10.0/255.255.255.0' identified by "1234567";

mysql> grant select on test1.* to test123@'192.168.0.0/255.255.0.0' identified by "12345678";

mysql> grant select on test1.* to test123@'192.168.10.1' identified by "123456789";

mysql> grant select on test1.* to test123@'192.168.10.101' identified by "12345678910";

mysql> select host,user,password_last_changed from mysql.user where user='test123' order by 3;

+----------------------------+---------+-----------------------+

| host                       | user    | password_last_changed |

+----------------------------+---------+-----------------------+

| %                          | test123 | 2018-09-26 19:21:23   |

| 192.168.10.0/255.255.255.0 | test123 | 2018-09-26 19:24:08   |

| 192.168.0.0/255.255.0.0    | test123 | 2018-09-26 19:24:30   |

| 192.168.10.1               | test123 | 2018-09-26 19:31:00   |

| 192.168.10.101             | test123 | 2018-09-26 19:31:16   |

+----------------------------+---------+-----------------------+

不管在192.168.10.1还是192.168.10.101上执行mysql命令连接,居然发现只有下面的可以连接,即test123@'192.168.10.0/255.255.255.0'有效

mysql -u test123 -p1234567 -h 192.168.10.101


mysql> drop user test123@'192.168.10.0/255.255.255.0';

mysql> select host,user,password_last_changed from mysql.user where user='test123' order by 3;

+-------------------------+---------+-----------------------+

| host                    | user    | password_last_changed |

+-------------------------+---------+-----------------------+

| %                       | test123 | 2018-09-26 19:21:23   |

| 192.168.0.0/255.255.0.0 | test123 | 2018-09-26 19:24:30   |

| 192.168.10.1            | test123 | 2018-09-26 19:31:00   |

| 192.168.10.101          | test123 | 2018-09-26 19:31:16   |

+-------------------------+---------+-----------------------+

再次测试发现不管在192.168.10.1还是192.168.10.101上执行mysql命令连接,只有下面的可以连接,即test123@'192.168.0.0/255.255.0.0'有效

mysql -u test123 -p12345678 -h 192.168.10.101


mysql> drop user test123@'192.168.0.0/255.255.0.0';

mysql> select host,user,password_last_changed from mysql.user where user='test123' order by 3;

+----------------+---------+-----------------------+

| host           | user    | password_last_changed |

+----------------+---------+-----------------------+

| %              | test123 | 2018-09-26 19:21:23   |

| 192.168.10.1   | test123 | 2018-09-26 19:31:00   |

| 192.168.10.101 | test123 | 2018-09-26 19:31:16   |

+----------------+---------+-----------------------+

再次测试

发现在192.168.10.1执行mysql命令连接,只有下面的可以连接,即test123@'192.168.10.1'有效

mysql -u test123 -p123456789 -h 192.168.10.101

发现在192.168.10.101执行mysql命令连接,只有下面的可以连接,即test123@'192.168.10.101'有效

mysql -u test123 -p12345678910 -h 192.168.10.101


mysql> drop user test123@'192.168.10.1';

mysql> drop user test123@'192.168.10.101';

mysql> select host,user,password_last_changed from mysql.user where user='test123' order by 3;

+------+---------+-----------------------+

| host | user    | password_last_changed |

+------+---------+-----------------------+

| %    | test123 | 2018-09-26 19:21:23   |

+------+---------+-----------------------+

再次测试发现不管在192.168.10.1还是192.168.10.101上执行mysql命令连接,下面的可以连接,即test123有效了

mysql -u test123 -p123456 -h 192.168.10.101


mysql> grant select on test1.* to test123@'192.168.0.0/255.255.0.0' identified by "12345678";

mysql> grant select on test1.* to test123@'192.168.10.0/255.255.255.0' identified by "1234567";

mysql> select host,user,password_last_changed from mysql.user where user='test123' order by 3;

+----------------------------+---------+-----------------------+

| host                       | user    | password_last_changed |

+----------------------------+---------+-----------------------+

| %                          | test123 | 2018-09-26 19:21:23   |

| 192.168.0.0/255.255.0.0    | test123 | 2018-09-26 19:45:45   |

| 192.168.10.0/255.255.255.0 | test123 | 2018-09-26 19:45:58   |

+----------------------------+---------+-----------------------+

再次测试发现不管在192.168.10.1还是192.168.10.101上执行mysql命令连接,下面的可以连接,即test123@'192.168.0.0/255.255.0.0'生效了

mysql -u test123 -p12345678 -h 192.168.10.101

得出结论,以下两条,谁先执行,谁先生效

grant select on test1.* to test123@'192.168.0.0/255.255.0.0' identified by "12345678";

grant select on test1.* to test123@'192.168.10.0/255.255.255.0' identified by "1234567";


mysql> drop user test123@'192.168.0.0/255.255.0.0';

mysql> drop user test123@'192.168.10.0/255.255.255.0';

mysql> grant select on test1.* to test123@'192.168.10.1' identified by "123456789";

mysql> grant select on test1.* to test123@'192.168.10.101' identified by "12345678910";

mysql> grant select on test1.* to test123@'192.168.10.0/255.255.255.0' identified by "1234567";

mysql> grant select on test1.* to test123@'192.168.0.0/255.255.0.0' identified by "12345678";

mysql> select host,user,password_last_changed from mysql.user where user='test123' order by 3;

+----------------------------+---------+-----------------------+

| host                       | user    | password_last_changed |

+----------------------------+---------+-----------------------+

| %                          | test123 | 2018-09-26 19:21:23   |

| 192.168.10.1               | test123 | 2018-09-26 19:48:59   |

| 192.168.10.101             | test123 | 2018-09-26 19:49:05   |

| 192.168.10.0/255.255.255.0 | test123 | 2018-09-26 19:49:21   |

| 192.168.0.0/255.255.0.0    | test123 | 2018-09-26 19:49:28   |

+----------------------------+---------+-----------------------+

再次测试

发现在192.168.10.1执行mysql命令连接,只有下面的可以连接,即test123@'192.168.10.1'有效

mysql -u test123 -p123456789 -h 192.168.10.101

发现在192.168.10.101执行mysql命令连接,只有下面的可以连接,即test123@'192.168.10.101'有效

 mysql -u test123 -p12345678910 -h 192.168.10.101

得出结论,以下三条,谁先执行,192.168.10.1上以谁先生效

grant select on test1.* to test123@'192.168.10.1' identified by "123456789";

grant select on test1.* to test123@'192.168.0.0/255.255.0.0' identified by "12345678";

grant select on test1.* to test123@'192.168.10.0/255.255.255.0' identified by "1234567";

得出结论,以下三条,谁先执行,192.168.10.101上以谁先生效

grant select on test1.* to test123@'192.168.10.101' identified by "12345678910";

grant select on test1.* to test123@'192.168.0.0/255.255.0.0' identified by "12345678";

grant select on test1.* to test123@'192.168.10.0/255.255.255.0' identified by "1234567";

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30126024/viewspace-2215129/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Welcome to Lukes DB HOME。 10G OCP、11G OCM, 8年以上DBA工作经验,博客仅记录自己的一个学习过程,不代表完全准确,如有需要,欢迎转载。

注册时间:2015-02-02

  • 博文量
    396
  • 访问量
    580225