本文主要介绍mysql如何创建、修改、删除和授权用户，具有一定的参考价值。有兴趣的朋友可以参考一下。希望大家看完这篇文章后收获多多。让边肖带你去了解一下。
1.连接的mysql命令。如果不包括-h，则意味着user@localhost连接到本地localhost数据库服务器，而-h之后的ip就是数据库服务器的IP。
2.若要建立所有ip连接，请在创建用户时在用户名后添加@“%”，或者不添加@(不带@，默认值为@“%”)。
3.mysql执行grant进行授权，权限是添加的，所以不用担心覆盖前面的权限。
4.grant可以同时创建用户、授权和修改密码。
5.建议创建用户时使用create，因为create只能创建用户，但不能授权用户。mysql不像oracle那样拥有connect create session权限，只要创建用户，mysql就会自动拥有connect create session权限。
6.建议使用alter user更改密码，因为alter不会涉及权限。虽然grant可以更改密码，但grant命令应该有权限选项。
7.如果用户名相同，主机对应的网段也相同，以哪个用户成为老师为准。
也就是下面两项，谁先执行谁教，也就是说mysql -u test1 -p登录后用谁的密码。
测试1上的授权选择。* to test 123 @ ' 192 . 168 . 0 . 0/255 . 255 . 0 . 0 '由' 12345678 '标识；
测试1上的授权选择。* to test 123 @ ' 192 . 168 . 10 . 0/255 . 255 . 255 . 0 '由' 1234567 '标识；
8.授予的授权可以在没有刷新权限的情况下生效；更新mysql.user表执行的授权必须是刷新权限才能生效。
创造用户
mysql帮助创建用户；
mysql帮助授予；
mysql创建由“123456”标识的用户“user 1”@“192 . 168 . 10 . 0/255 . 255 . 255 . 0”；
mysql授权选择，在db1上更新。*至由“123456”标识的“user 2”@“192 . 168 . 10 . 0/255 . 255 . 255 . 0”；-用户是经过授权创建的。
修改密码
mysql帮助改变用户；
mysql帮助授予；
mysql帮助设置密码；
MySQL alter user ' user 1 ' @ ' 192 . 168 . 10 . 0/255 . 255 . 255 . 0 '由' 666666 '标识；
mysql将mysql.user上的select授予由' 777777 '标识的' user 1 ' @ ' 192 . 168 . 10 . 0/255 . 255 . 255 . 0 '；-在更改密码的同时授权。
mysql为' user 1 ' @ ' 192 . 168 . 10 . 0/255 . 255 . 255 . 0 '设置密码=password(' 888888 ')；
从MySQL 5.7.6开始，SET PASSWORD被弃用，并将在未来的MySQL版本中删除。ALTER USER现在是分配密码的首选语句。
自从MySQL 5.7.6以来，不建议设置密码，这种用法在未来的MySQL版本中将被丢弃。ALTER USER现在是更改密码的首选语句。
批准
mysql帮助授予；
mysql授权选择、插入、更新*。*至“user 1”@“192 . 168 . 10 . 0/255 . 255 . 255 . 0”；-仅授权，不修改密码。
删除用户
mysql帮助删除用户；
mysql删除用户“user 1”@“192 . 168 . 10 . 0/255 . 255 . 255 . 0”；
密码相关参数
/p>
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如何实现用户创建、修改、删除及授权操作”这篇文章对大家有帮助，同时也希望大家多多支持，关注行业资讯频道，更多相关知识等着你来学习!
内容来源网络，如有侵权，联系删除，本文地址：https://www.230890.com/zhan/57897.html