How to allow a user to access only certain columns in MySQL (MariaDB)

1. How to set permissions on individual columns in MySQL (MariaDB)

2. How to allow the user to add entries to a column

3. How to allow the user to view entries in a column

4. How to allow the user to update records in a column

5. How to allow the user to create a foreign key for a column

6. How to assign different privileges to different columns in one query

7. How to allow a user to transfer column management privileges to another user

8. How to check current user privileges

How to set permissions on individual columns in MySQL (MariaDB)

Is it possible in MySQL (MariaDB) to allow user access only to certain columns that belong to a specific table in a specified database? Is it possible to set different permissions for table columns in databases? The answer to all these questions is yes, you can. This article is devoted to this – setting permissions for columns in MySQL (MariaDB).

In MySQL (MariaDB), permissions can be configured as follows:

globally (apply to all databases and their contents)
at the database level (apply to specified databases and their contents)
at the table level (applies to specified tables and their contents)
at the column level (applied to the specified columns and their content)

This article is about configuring column-level access in MySQL (MariaDB).

All queries in this article will look like this:

GRANT _PRIVILEGE_ (_COLUMN_) ON _TestDB_._TestTABLE_ TO '_USERNAME_'@'localhost';

In this query:

_PRIVILEGE_ is the privilege (access right) you want to grant to the user. You can specify several privileges at the same time, in which case they must be listed separated by commas
_COLUMN_ is the column for which the privilege is being granted. Note that the columns are listed in brackets after the privilege.
_TestDB_ is the database in which the table with the specified column is located, on which you want to grant privileges to the user
_TestTABLE_ is the table that contains the column you want to grant privileges to the user
_USERNAME_ is the name of the user to whom the privileges are granted

Note: Be aware that granting one privilege may automatically grant other privileges.

When a user is granted any privilege on a column, the containing database becomes visible to the user when the query is executed.

SHOW DATABASES;

Also, when granting any privilege to a user to access or manage a column, he can execute queries with the “USE” statement against the database in which the table with this column is located.

Also granting privileges on the column allows the query to be used:

SHOW TABLES;

But in this case, only tables that have columns on which the user has any privileges will be shown.

See also:

How to allow the user to add entries to a column

INSERT – allows the user to insert new data into columns.

So, to allow the user _USERNAME_ to insert new records into the _COLUMN_ column of the _TestTABLE_ table located in the _TestDB_ database, the following query must be executed:

GRANT INSERT (_COLUMN_) ON _TestDB_._TestTABLE_ TO '_USERNAME_'@'localhost';

How to allow the user to view entries in a column

SELECT – allows the user to read the entries in the column.

To allow the user _USERNAME_ to view records in the _COLUMN_ column of the _TestTABLE_ table in the _TestDB_ database, the following query must be executed:

GRANT SELECT (_COLUMN_) ON _TestDB_._TestTABLE_ TO '_USERNAME_'@'localhost';

How to allow the user to update records in a column

UPDATE – allows the user to update existing rows in the specified column.

To allow the user _USERNAME_ to update records in the _COLUMN_ column of the _TestTABLE_ table located in the _TestDB_ database, the following query must be executed:

GRANT UPDATE (_COLUMN_) ON _TestDB_._TestTABLE_ TO '_USERNAME_'@'localhost';

How to allow the user to create a foreign key for a column

REFERENCES – allows the user to create a foreign key.

To allow the _USERNAME_ user to create a foreign key for the _COLUMN_ column of the _TestTABLE_ table located in the _TestDB_ database, the following query must be executed:

GRANT REFERENCES (_COLUMN_) ON _TestDB_._TestTABLE_ TO '_USERNAME_'@'localhost';

How to assign different privileges to different columns in one query

If you want to assign different privileges to the same column, you must specify the column in parentheses after each privilege, even if it's the same column. The privileges themselves are listed separated by commas. For example:

GRANT INSERT (_COLUMN_), SELECT (_COLUMN_) ON _TestDB_._TestTABLE_ TO '_USERNAME_'@'localhost';

If you want to assign the privilege to control multiple columns, then the columns must also be specified in brackets and listed separated by commas. For example:

GRANT INSERT (_COLUMN_), SELECT (_COLUMN_, _COLUMN2_) ON _TestDB_._TestTABLE_ TO '_USERNAME_'@'localhost';

For example, the following query grants INSERT privilege on the test_column column, and also grants SELECT privilege on the id and test_column columns:

GRANT INSERT (test_column), SELECT (id, test_column) ON TestDB.TestTABLE TO 'mial'@'localhost';

How to allow a user to transfer column management privileges to another user

If you want to give a user the ability to transfer his privileges to other users, then add “WITH GRANT OPTION” to the request.

For example, the following query grants the SELECT (view) privilege on the _COLUMN_ column of the _TestTABLE_ table in the _TestDB_ database to the user _USERNAME_ with the right to transfer to any other user:

GRANT SELECT (_COLUMN_) ON _TestDB_._TestTABLE_ TO '_USERNAME_'@'localhost'; WITH GRANT OPTION;

How to check current user privileges

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

SHOW GRANTS FOR '_USERNAME_'@'localhost';