mysql如何实现用户创建、修改、删除及授权操作
发布时间：2022-01-11 19:23:44 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要介绍了mysql如何实现用户创建、修改、删除及授权操作，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。 1、mysql命令进行连接如果不带-h，表示user@localhost连接本地local
|
这篇文章主要介绍了mysql如何实现用户创建、修改、删除及授权操作，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。 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"; 感谢你能够认真阅读完这篇文章，希望小编分享的“mysql如何实现用户创建、修改、删除及授权操作”这篇文章对大家有帮助。 （编辑：温州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
