Search code examples
mysqlpermission-denieduser-permissionsheidisql

“Error (1142): SHOW command denied to user…” when trying to restrict access to columns

I ran the following query to create a user with restrictions on what columns they can view/edit in a certain table. The table has foreign keys to other tables that I haven't given any access to. I want the user to be able to edit the columns under INSERT and just have read-only access to the columns under SELECT.

CREATE USER 'user'@'hostname';
GRANT SELECT (`Status`,`Number`,`Location`), INSERT (`Name`,`Address`,`Email Address`,_
`Home Number`,`Work Number`,`Mobile Number`,`Date Available`) ON `project1`.`table1`_
 TO  'user'@'hostname' IDENTIFIED BY 'password';

The query runs and creates the user. I am getting users to use HeidiSQL to edit data in this table. When I login as the user, the only table visible is table1, as expected, however when I click on the table I get the error:

/* SQL Error (1142): SHOW command denied to user 'user'@'<IP address different to hostname IP address>' for table 'table1' */

and cannot see any data at all under 'Data'

The following question was asked on Stack Overflow, however none of the suggested solutions worked for me:

'SHOW command denied to user' when setting up user permissions

If I run the query 

SHOW GRANTS FOR CURRENT_USER;

I get:

GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD '*<password different to one set originally>'
GRANT SELECT (Status,Number,Location), INSERT (Name,Address,Email Address,Home Number,Work Number,Mobile Number,Date Available) ON `project1`.`table1` TO 'user'@'%'

I get a view of the columns in the table if I run (but still can't view any data):

SHOW COLUMNS FROM table1;

Does anyone know why this user is not getting a view of the data in table1?

Solution

  • I managed to find a solution.

    I needed to first of all 'GRANT SHOW VIEW' to the user so they could view the data in the table.

    Next, I changed my query so that I granted the SELECT permission to ALL the columns, and granted the UPDATE permission to all the columns EXCEPT the colulms I wanted to leave as Read-Only to the user.

    The following is the query I ran, which worked successfully:

    CREATE USER 'user'@'localhost';

GRANT SHOW VIEW ON `project1`.`table1` TO  'user'@'localhost' IDENTIFIED BY 'password';

GRANT SELECT (`Status`,`Number`,`Location`,`Name`,`Address`,`Email Address`,`Home Number`,_
`Work Number`,`Mobile Number`,`Date Available`), UPDATE (`Name`,`Address`,`Email Address`,_
`Home Number`,`Work Number`,`Mobile Number`,`Date Available`) ON `project1`.`table1` TO _
'user'@'localhost' IDENTIFIED BY 'password';