mysql同一帐户远程连接和本地连接权限不一致
来源:互联网 发布:mac卸载 编辑:程序博客网 时间:2024/05/16 11:19
背景:
在本地装了一个mysql,在远程客户端连接时候,同样是root帐户,本地可以创库,远程无法创库且本地能看到数据库比远程客户端的多
mysql> select version();+-------------------------+| version() |+-------------------------+| 5.7.18-0ubuntu0.16.04.1 |+-------------------------+
原因分析->问题解决:
了解mysql的权限用户表在mysql库中的user表中
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys |+--------------------+
mysql> user mysql;mysql> select User,Host from user where User = 'root'\G;*************************** 1. row ***************************User: rootHost: %*************************** 2. row ***************************User: rootHost: localhost2 rows in set (0.00 sec)
对比发现user表以User字段和Host为联合主键,并且Host='%'权限都为N,这就解释同一帐户在本地和远程连接时候权限的差异
我们update user set xxx='Y' where User = 'root' and Host='%';
然后刷新权限
flush privileges
重启mysql 服务
---至此问题得意解决
附一张user权限字段表结构,权限的命名比较明朗
mysql> desc user;+------------------------+-----------------------------------+------+-----+-----------------------+-------+| Field | Type | Null | Key | Default | Extra |+------------------------+-----------------------------------+------+-----+-----------------------+-------+| Host | char(60) | NO | PRI | | || User | char(32) | NO | PRI | | || Select_priv | enum('N','Y') | NO | | N | || Insert_priv | enum('N','Y') | NO | | N | || Update_priv | enum('N','Y') | NO | | N | || Delete_priv | enum('N','Y') | NO | | N | || Create_priv | enum('N','Y') | NO | | N | || Drop_priv | enum('N','Y') | NO | | N | || Reload_priv | enum('N','Y') | NO | | N | || Shutdown_priv | enum('N','Y') | NO | | N | || Process_priv | enum('N','Y') | NO | | N | || File_priv | enum('N','Y') | NO | | N | || Grant_priv | enum('N','Y') | NO | | N | || References_priv | enum('N','Y') | NO | | N | || Index_priv | enum('N','Y') | NO | | N | || Alter_priv | enum('N','Y') | NO | | N | || Show_db_priv | enum('N','Y') | NO | | N | || Super_priv | enum('N','Y') | NO | | N | || Create_tmp_table_priv | enum('N','Y') | NO | | N | || Lock_tables_priv | enum('N','Y') | NO | | N | || Execute_priv | enum('N','Y') | NO | | N | || Repl_slave_priv | enum('N','Y') | NO | | N | || Repl_client_priv | enum('N','Y') | NO | | N | || Create_view_priv | enum('N','Y') | NO | | N | || Show_view_priv | enum('N','Y') | NO | | N | || Create_routine_priv | enum('N','Y') | NO | | N | || Alter_routine_priv | enum('N','Y') | NO | | N | || Create_user_priv | enum('N','Y') | NO | | N | || Event_priv | enum('N','Y') | NO | | N | || Trigger_priv | enum('N','Y') | NO | | N | || Create_tablespace_priv | enum('N','Y') | NO | | N | || ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | || ssl_cipher | blob | NO | | NULL | || x509_issuer | blob | NO | | NULL | || x509_subject | blob | NO | | NULL | || max_questions | int(11) unsigned | NO | | 0 | || max_updates | int(11) unsigned | NO | | 0 | || max_connections | int(11) unsigned | NO | | 0 | || max_user_connections | int(11) unsigned | NO | | 0 | || plugin | char(64) | NO | | mysql_native_password | || authentication_string | text | YES | | NULL | || password_expired | enum('N','Y') | NO | | N | || password_last_changed | timestamp | YES | | NULL | || password_lifetime | smallint(5) unsigned | YES | | NULL | || account_locked | enum('N','Y') | NO | | N | |+------------------------+-----------------------------------+------+-----+-----------------------+-------+
阅读全文
0 0
- mysql同一帐户远程连接和本地连接权限不一致
- MySQL远程访问权限,同时允许远程连接和本地连接
- ros本地连接和远程连接
- mysql 远程连接可以,本地连接报错
- MYSQL远程连接正常,本地连接失败
- mysql无法远程连接,但可以本地连接
- db2远程连接服务器和本地连接数据库
- MySql开通远程连接和权限设置
- 本地连接远程mysql数据库
- Oracle 12c 数据库的本地连接和远程连接
- mysql 开启远程连接权限
- Mysql远程连接赋予权限
- mysql 开启远程连接权限
- MYSQL远程连接,权限设定
- MySql开启远程连接权限
- 设置mysql远程连接权限
- 开启MySql远程连接权限
- MYSQL远程连接无权限
- laravel:服务提供者的实际应用
- HDP 2.4 离线安装
- Doubango RTP包传输使用UDT可靠传输协议,解决RTP丢包问题
- PHP: 深入pack/unpack
- 【备忘】尚观教育2017最新版Oracle数据库零基础到入门视频教程
- mysql同一帐户远程连接和本地连接权限不一致
- unity kinect开发电脑配置
- git命令备忘
- Spring 之AOP AspectJ切入点语法详解
- Android轮播图控件之Banner学习使用
- JSONObject.fromObject(map) 执行报错.
- Java并发之线程池简介
- shell基础巩固第二天
- tomcat服务器通过web.xml映射发布文件