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

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

2. How to allow a user to create tables in a database

3. How to allow a user to delete tables in a database

4. How to allow user to delete records in database tables

5. How to allow user to add records in database tables

6. How to allow a user to view records in database tables

7. How to allow user to update records in database tables

8. How to allow a user to change the structure of tables in a database

9. What other database level privileges exist

10. How to assign multiple database management privileges to a user at once

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

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

13. How to check current user privileges

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

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

All queries in this article will look like this:

GRANT _PRIVILEGE_ ON _TestDB_.* 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
_TestDB_ is the database on which you want to grant privileges to the user. If you specify “*” (asterisk) instead of the database name, then the granted privileges will have a global level, that is, they will apply to all databases 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 database, that 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 database, he can execute queries with the “USE” statement in relation to this database.

Also, granting privileges on the database allows the use of the query:

SHOW TABLES;

See also:

How to allow a user to create tables in a database

CREATE – allows the user to create new tables in the database (if issued for a single database), or allows the user to create new databases (if no specific database is specified when granting the privilege).

So, to allow the _USERNAME_ user to create tables in the _TestDB_ database, you need to execute the following query:

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

How to allow a user to delete tables in a database

DROP – allows the user to drop tables in the database (if issued for one database), or allows the database itself to be dropped (if a specific database is not specified when granting the privilege).

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

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

How to allow user to delete records in database tables

DELETE – allows the user to delete rows in tables.

So, to allow the user _USERNAME_ to delete records in the tables of the _TestDB_ database, you need to execute the following query:

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

How to allow user to add records in database tables

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

So, to allow the user _USERNAME_ to insert new records in the tables of the _TestDB_ database, you need to execute the following query:

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

How to allow a user to view records in database tables

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

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

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

How to allow user to update records in database tables

UPDATE – allows the user to update existing database rows.

To allow the user _USERNAME_ to update records in the tables of the _TestDB_ database, the following query must be executed:

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

How to allow a user to change the structure of tables in a database

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

To allow the user _USERNAME_ to change the structure of the tables in the _TestDB_ database (that is, add and modify data columns, change the data type of columns), the following query must be executed:

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

What other database 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 – enables creating or modifying views

How to assign multiple database management privileges to a user at once

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

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

How to assign a user all database management privileges at once

If you want to give a user all possible database privileges, then use a query like this (replace _TestDB_ with your database name and replace _USERNAME_ with your username):

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

How to allow a user to transfer database 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 _TestDB_ database to the user _USERNAME_ with the right to transfer to any other user:

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

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

GRANT ALL PRIVILEGES ON _TestDB_.* TO '_USERNAME_'@'localhost'; WITH GRANT OPTION;

How to check current user privileges

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

SHOW GRANTS FOR '_USERNAME_'@'localhost';