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

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

2. How to allow a user to create a specific table in a database

3. How to allow a user to delete a specific table in a database

4. How to allow a user to delete records in a table

5. How to allow a user to add records to a table

6. How to allow a user to view records in a table

7. How to allow the user to update records in a table

8. How to allow the user to change the table structure

9. What other table-level privileges exist

10. How to assign multiple privileges to a user to manage a specific table at once

11. How to assign all table management privileges to a user at once

12. How to allow a user to transfer table management privileges to another user

13. How to check current user privileges

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

Is it possible in MySQL (MariaDB) to allow user access only to certain tables in specified databases? Is it possible to set different permissions for tables in databases? The answer to all these questions is yes, you can. This is what this article is about.

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 setting up table-level access in MySQL (MariaDB).

All queries in this article will look like this:

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

In this query:

_PRIVILEGE_ - 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
_TestDB_ - the database in which the table you want to grant privileges to the user is located
_TestTABLE_ is the table on which you want to grant privileges to the user. If you specify “*” (asterisk) instead of the table name, then the granted privileges will be at the database level, that is, they will apply to all tables at once
_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 table, the containing database becomes visible to the user when a query is executed:

SHOW DATABASES;

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

Also granting privileges on the table allows the use of the query:

SHOW TABLES;

But in this case, only tables to which the user has any privileges will be shown.

See also:

How to allow a user to create a specific table in a database

CREATE – allows the user to create new tables in the database.

Since permission is given to a specific table, this table may not exist at the time the permission is given – such a setting is not prohibited. If the table already exists, then the user will be able to exercise their privilege to create a table with the specified name if that table is dropped in the future.

So, to allow the user _USERNAME_ to create a table _TestTABLE_ in the database _TestDB_, the following query must be executed:

GRANT CREATE ON _TestDB_._TestTABLE_ TO '_USERNAME_'@'localhost';

Note that the CREATE privilege does not disappear after the table has been created. Therefore, if the table has been deleted, the specified user can re-create it.

How to allow a user to delete a specific table in a database

DROP – allows the user to drop tables in the database.

So, to allow the _USERNAME_ user to delete the _TestTABLE_ table in the _TestDB_ database, you need to execute the following query:

GRANT DROP ON _TestDB_._TestTABLE_ TO '_USERNAME_'@'localhost';

Note that the DROP privilege does not disappear when a table is dropped. Therefore, if a table with the same name was created again, then the specified user can delete it again.

How to allow a user to delete records in a table

DELETE – allows the user to delete rows in tables.

So, to allow the user _USERNAME_ to delete records in the _TestDB_ table, which is located in the _TestDB_ database, you need to execute the following query:

GRANT DELETE ON _TestDB_._TestTABLE_ TO '_USERNAME_'@'localhost';

How to allow a user to add records to a table

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

So, to allow the user _USERNAME_ to insert new records into the _TestTABLE_ table, which is located in the _TestDB_ database, the following query should be executed:

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

How to allow a user to view records in a table

SELECT – allows the user to read the records in the table.

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

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

How to allow the user to update records in a table

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

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

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

How to allow the user to change the table structure

ALTER – allows the user to use “ALTER TABLE” to change the structure of a table.

To allow the user _USERNAME_ to modify the structure of the _TestTABLE_ table in the _TestDB_ database (i.e. add and modify data columns, change the data type of columns), the following query must be executed:

GRANT ALTER ON _TestDB_._TestTABLE_ TO '_USERNAME_'@'localhost';

What other table-level privileges exist

In addition to those already discussed, there are also the following privileges that you can assign to a user at the database level:

REFERENCES – allows the user to create a foreign key
INDEX – allows the user to include or remove indexes
CREATE VIEW – enable creating or modifying views

How to assign multiple privileges to a user to manage a specific table at once

An example of a command that assigns a set of privileges to the _USERNAME_ user over the _TestTABLE_ table in the _TestDB_ database:

GRANT CREATE, DROP, DELETE, INSERT, SELECT, UPDATE, ALTER, REFERENCES ON _TestDB_._TestTABLE_ TO '_USERNAME_'@'localhost';

How to assign all table management privileges to a user at once

If you want to grant a user all possible privileges on a table, then use a query like this (replace _TestTABLE_ with the name of the table, also replace _TestDB_ with the name of your database, and replace _USERNAME_ with the username):

GRANT ALL PRIVILEGES ON _TestDB_._TestTABLE_ TO '_USERNAME_'@'localhost';

How to allow a user to transfer table 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 SELECT (view) privilege on the _TestTABLE_ table in the _TestDB_ database to the user _USERNAME_ with the right to transfer to any other user:

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

And this query gives all possible privileges on the _TestTABLE_ table in the _TestDB_ database to the user _USERNAME_ with the right to transfer to any other user:

GRANT ALL PRIVILEGES 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 tables, run the following query (replace _USERNAME_ with your username):

SHOW GRANTS FOR '_USERNAME_'@'localhost';