设置用户权限时“拒绝用户显示命令" [英] 'SHOW command denied to user' when setting up user permissions

问题描述

我需要使用 MYSQL 向数据库中的两个特定列授予用户 UPDATE 和 SELECT 权限

I need to grant a user UPDATE and SELECT to two specific column in a db using MYSQL

我使用下面的命令来做到这一点

I used the below command to do this

GRANT SELECT (title,new_title,catagory,description,runtime), UPDATE    (title,new_title,catagory,description,runtime) ON database.table1 TO 'user1'@'%' IDENTIFIED  BY    'xxx';
FLUSH PRIVILEGES

但是，当我尝试使用 Sequal Pro 查看表/字段时，出现以下错误

However when I try to view the table/fields using Sequal Pro I get the following error

MySQL said: SHOW command denied to user 'suer1'@'host.x.x.x' for table 'table1'

我可以看到数据库列表中的 DB 以及我已授予访问权限的一个表，但我无法从中读取任何数据.. 有谁知道我如何修复此错误并且只能查看/编辑我想要的表/列...?

I can see the DB in the database list along with the one table I have granted access to, but I can't read any of the data from it.. Would anyone know how I can fix this error and only view/edit the tables/column I want..?

推荐答案

如果您选择有问题的特定列，您的资助实际上会起作用:

Your grant will actually work, if you select the specific columns in question:

SELECT title, new_title, catagory, description, runtime FROM table1

但是，像这样的更一般的查询将失败，SELECT command denied to user:

However, a more general query like this will fail with SELECT command denied to user:

SELECT * FROM table1

我认为这可能是 Sequal Pro 尝试获取有关表的信息的方式的问题.它说 MySQL said: SHOW command denied to user，但尽管有以下效果:

I believe this may be an issue with the way Sequal Pro tries to get information about the table. It says MySQL said: SHOW command denied to user, but despite that the following works:

SHOW COLUMNS FROM table;

我不确定哪个SHOW 命令对于 Sequal Pro 失败.

I'm not sure which SHOW command is failing for Sequal Pro.

这篇关于设置用户权限时“拒绝用户显示命令"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！