3

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

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'

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..?

Jim
  • 1,337
  • 5
  • 20
  • 29

2 Answers2

1

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

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

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

SELECT * FROM table1

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;

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

Luke
  • 13,678
  • 7
  • 45
  • 79
-1

Try this:

GRANT SELECT (col1,col2....), INSERT (col1,col2...) ON mydb.mytbl TO 'someuser'@'somehost'; 
flush privileges;
Ajitha Ms
  • 545
  • 5
  • 18
  • try this.. http://stackoverflow.com/questions/5016505/mysql-grant-all-privileges-on-database – Ajitha Ms May 22 '14 at 10:04
  • Query is almost identical to the question, and the linked post grants global privileges. Original question is asking how to get SHOW to work when granting privileges to specific columns. – Luke Sep 08 '17 at 18:59