Error “ERROR 1143 (42000)”: command denied to user for column in table in MySQL (MariaDB) (SOLVED)

How to fix “ERROR 1143 (42000)” in MySQL (MariaDB)

Error number 1143 (42000) occurs when a user has privileges on some columns of a table, but he tries to exercise his privileges on those columns for which he does not have permissions.

If the user tries to perform actions for which he does not have privileges on any column in the table, then an error with a different number occurs: 1142 (42000), “command denied to user for table”.

So, if you see error 1143 (42000), then you can fix it by specifying in the query only those columns for which the user has privileges. If you get error 1142 (42000), then this means that the user is trying to execute the command, but he does not have privileges on any column.

Simply put, the user can fix error 1143 (42000) on his own, but to resolve error 1142 (42000), additional privileges must be granted and the user himself can no longer fix it.

Let's consider specific examples.

The next query:

SELECT * FROM TestTABLE;

Throws an error:

ERROR 1143 (42000): SELECT command denied to user 'mial'@'localhost' for column 'id' in table 'TestTABLE'

The error itself already says that the user does not have the SELECT privilege on the “id” column.

How to find out which privileges and which columns a user has privileges in MySQL (MariaDB)

To find out which columns in a particular table a user has access to, run a command like this (replace _TestTABLE_ with the name of the table you are interested in):

SHOW COLUMNS FROM _TestTABLE_;

As a result, only those columns for which the user has any privileges will be shown.

For example:

SHOW COLUMNS FROM TestTABLE;

Query execution result:

Only one field is shown, “test_column”, even though we know from the error message that the table also has an “id” field. Conclusion: this user has privileges on the “test_column” field, but no privileges on the “id” field.

But what exactly are the privileges the user has on the “test_column” column? The previous query cannot answer this.

To check what privileges a user has to read and modify tables and columns, run the following query (replace _USERNAME_ with your username):

SHOW GRANTS FOR '_USERNAME_'@'localhost';

For example:

SHOW GRANTS FOR 'mial'@'localhost';

All the necessary information is contained in the following line:

GRANT SELECT (`test_column`), INSERT (`test_column`) ON `TestDB`.`TestTABLE` TO `mial`@`localhost`

This line means the following:

the user can execute a query with a SELECT statement against the “test_column” column, which belongs to the TestTABLE table, which is located in the TestDB database
the user can execute query with INSERT statement against column “test_column” which belongs to table TestTABLE which is in database TestDB

How to execute a query if the user only has access to certain columns of a table in MySQL (MariaDB)

As we remember, the following query throws an error because the user is trying to display the contents of all columns:

SELECT * FROM TestTABLE;

Replace “*” (asterisk) with the column on which the user has the SELECT privilege. For example:

SELECT test_column FROM TestTABLE;

If there are privileges for several columns, then list them separated by commas, for example:

SELECT test_column1, test_column2, test_column3 FROM TestTABLE;

The user also has the INSERT privilege, but only on one “test_column” column. Let's try to insert new data into the table:

INSERT INTO TestTABLE (`id`, `test_column`) VALUES ("4", "more data");

We get an error:

ERROR 1143 (42000): INSERT command denied to user 'mial'@'localhost' for column 'id' in table 'TestTABLE'

The user does not have the INSERT privilege on the “id” column. So let's change our query:

INSERT INTO TestTABLE (`test_column`) VALUES ("more data");

As you can see in the screenshot, the request was successful.

Let's check if the data is actually inserted in the table:

SELECT test_column FROM TestTABLE;

Yes, new data has been added to a column on which the user has the INSERT privilege.

Conclusion

So, for each MySQL and MariaDB user, you can individually configure database access rights. Including limiting its privileges to only viewing certain columns. Or you can allow the user to add and modify data only in certain tables and columns.

For details on fine-grained configuration of MySQL and MariaDB user permissions, see the following articles: