推荐点击下面图片，通过本站淘宝优惠价购买:
mysql中grant all privileges on赋给用户远程权限
改表法。
当你的帐号不允许从远程登陆，只能在localhost连接时。这个时候只要在mysql服务器上，更改 mysql 数据库里的 user 表里的 host 项，从localhost"改成%即可实现用户远程登录
在安装mysql的机器上运行：
1. mysql -u root -p
2. select host,user from user where user='root';
3. update user set host = '%' where user='root' and host='localhost';
4. select host, user from user where user='root';
授权法
[root@aaa-server ~]# mysql -u root -p
MariaDB [(none)]> grant all privileges on *.* to root@'%' identified by '123' with grant option;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> exit
Bye
授权法。
例如，你想user使用mypwd从任何主机连接到mysql服务器的话。
在安装mysql的机器上运行：
1. GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'mypwd' WITH
GRANT OPTION;
2.FLUSH PRIVILEGES;
模板：
grant all privileges on 库名.表名 to '用户名'@'IP地址' identified by '密码' with grant option;
flush privileges;
如果你想允许用户user从ip为192.168.1.4的主机连接到mysql服务器，并使用mypwd作为密码
在安装mysql的机器上运行：
GRANT ALL PRIVILEGES ON *.* TO 'user'@'192.168.1.3' IDENTIFIED BY 'mypwd' WITH GRANT OPTION;
FLUSH PRIVILEGES;
注意授权后必须FLUSH PRIVILEGES;否则无法立即生效。
高版本数据库不能按照grant all privileges on *.* to "root"@"%" identified by "xxxx";去修改用户权限
mysql> SELECT @@VERSION;
+-----------+
| @@VERSION |
+-----------+
| 8.0.14 |
+-----------+
1 row in set (0.00 sec)
高版本修改用户权限方法：
# 先创建远程用户，再授权
mysql> create user 'root'@'%' identified by 'password';
Query OK, 0 rows affected (0.03 sec)
mysql> grant all privileges on *.* to 'root'@'%' with grant option;
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
再次查看发现有了root %
mysql> select User,Host from user;
+------------------+-----------+
| User | Host |
+------------------+-----------+
| root | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
————————————————
本文链接：http://www.hqyman.cn/post/4009.html 非本站原创文章欢迎转载，原创文章需保留本站地址！
休息一下，本站随机推荐观看栏目:
