Error when using ‘USE’ statement in MySQL (Mariadb): “ERROR 1044 (42000): Access denied for user” (SOLVED)

Table of contents

1. The “USE” statement causes the error “ERROR 1044 (42000)”

2. How to fix MySQL (MariaDB) “ERROR 1044 (42000): Access denied for user” with USE statement

3. How to grant a user read access to a database and its contents

3.1 How to give a user read access to a specific database in MySQL (MariaDB)

3.2 How to give user read access to all databases in MySQL (MariaDB)

3.3 How to grant a user read access to a specific table in a specific database in MySQL (MariaDB)

4. How to grant a user certain privileges on a database in MySQL (MariaDB)

5. How to grant a user all database privileges in MySQL (MariaDB)

6. How to give a user database privileges that can be transferred to other users in MySQL (MariaDB)

7. How to check current user privileges

The “USE” statement causes the error “ERROR 1044 (42000)”

This note is about the error that occurs when trying to select a database to use, that is, to execute an expression like:

USE YOUR-DB;

But the request fails with an error:

ERROR 1044 (42000): Access denied for user 'YOUR-USERNAME'@'localhost' to database 'YOUR-DB'

For example:

USE TestDB;
ERROR 1044 (42000): Access denied for user 'mial'@'localhost' to database 'TestDB'

There may be several reasons for this error. For example:

1. The user does not have rights to any actions with the specified database
2. The specified database does not exist

As for the second option (the specified database does not exist), in this case the errors may be different – it depends on the user's rights to the database. Namely:

• if the database does not exist and the user does not have read privileges, the above error will be thrown
• if the database does not exist and the user has read privileges (this combination is possible, for example, if the database existed and then was deleted), then the following error message will be displayed:

ERROR 1049 (42000): Unknown database 'TestDB'

Let's consider a situation when the database exists, but the user has no rights to read it.

How to fix MySQL (MariaDB) “ERROR 1044 (42000): Access denied for user” with USE statement

Note: By the way, the exact same “ERROR 1044 (42000)” can occur when trying to create a database. If you are trying to create a database, see the article: ERROR 1044 (42000): Access denied for user ‘mial’@’localhost’ to database ‘TestDB’. Can’t create MySQL database (SOLVED)

This article is about the “ERROR 1044 (42000)” that occurs when trying to select a database by the “USE” statement.

To fix the “ERROR 1044 (42000)”, you need to give the user privileges to work with the database.

The user can be granted both a full set of privileges for working with the database (including creating tables, adding and modifying records in tables), and limited to the right to read the database.

How to grant a user read access to a database and its contents

How to give a user read access to a specific database in MySQL (MariaDB)

To allow the user to use the “USE” and “SELECT” statements, run the following query (replace “TestDB” with your database name and “YOUR-USERNAME” with your username):

GRANT SELECT ON TestDB.* TO 'YOUR-USERNAME'@'localhost';

Note: This query, as well as all the queries below, must be run as the root user or another user who has sufficient privileges on the databases and at the same time has the right to transfer privileges to other users.

For example:

GRANT SELECT ON TestDB.* TO 'mial'@'localhost';

After this query, the “mial” user can view the contents of the “TestDB” database, that is, in relation to this database, he can execute queries with the “USE” and “SELECT” statements.

Changes take effect immediately, even if the user “mial” is connected to the DBMS server at the time of granting him these privileges.

When executing the query “SHOW DATABASES;” the database “TestDB” will appear in the list of databases displayed for the user “mial”.

How to give user read access to all databases in MySQL (MariaDB)

If you want the user to be able to use the “USE” and “SELECT” statements against all databases, that is, to allow the user to view the contents of the databases, use the following query (replace “YOUR-USERNAME” with your username):

GRANT SELECT ON *.* TO 'YOUR-USERNAME'@'localhost';

For example, to give the user "mial" read access to all databases, you would run the following query:

GRANT SELECT ON *.* TO 'mial'@'localhost';

Now user “mial” can execute “USE” and “SELECT” queries against all databases, and can also execute “SHOW DATABASES;” query. If the user "mial" was connected to the DBMS server at the time of granting him privileges, then for the changes to take effect, the user must disconnect and reconnect.

How to grant a user read access to a specific table in a specific database in MySQL (MariaDB)

To allow a user to only view the contents of a specific table in the database, use the following query (replace “TestDB” with the name of your database, “TestTABLE” with the name of the table, and “YOUR-USERNAME” with the name of your user):

GRANT SELECT ON TestDB.TestTABLE TO 'YOUR-USERNAME'@'localhost';

For example:

GRANT SELECT ON TestDB.TestTABLE TO 'mial'@'localhost';

After this request, the user “mial” can view the contents of the “TestTABLE” table of the “TestDB” database, that is, in relation to this database, he can execute a query with the “USE” statement, and in relation to the specified table, he will be able to execute a query with the “SELECT” statement. Changes take effect immediately, even if the user “mial” is connected to the DBMS server at the time of granting him these privileges.

When executing the query “SHOW DATABASES;” the database “TestDB” will appear in the list of databases displayed for the user “mial”. And when executing the query “SHOW TABLES;” the table “TestTABLE” will appear in the list of displayed tables (all other tables will remain invisible to this user).

How to grant a user certain privileges on a database in MySQL (MariaDB)

In addition to granting the “SELECT” privilege, you can grant any other privilege from the following list:

• CREATE – allows the user to create new tables in the database (if granted for a single database), or allows the user to create new databases (if no specific database is specified when granting the privilege). If the user receives this privilege, then he also gets the right to use queries with “USE” and “SHOW TABLES;”, although he cannot execute expressions with the “SELECT” statement
• 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). If the user receives this privilege, then he also gains the right to use “USE” and “SHOW TABLES;” queries, although he cannot execute the “SELECT” statement.
• DELETE – allows the user to delete rows in tables
• INSERT – allows the user to insert new data into tables
• SELECT – allows the user to read records in the database
• UPDATE – allows the user to update existing database rows
• ALTER – allows the user to use “ALTER TABLE” to change the structure of tables
• REFERENCES – allows the user to create a foreign key
• INDEX – allows the user to enable or remove indexes
• CREATE VIEW – enables creating or modifying views

In one request, you can grant several privileges at once by listing them separated by commas. For example, the following command applies the listed privileges to a single table (replace “TestDB” with your database name, replace “TestTABLE” with your table name, and replace “YOUR-USERNAME” with your username):

GRANT CREATE, DROP, DELETE, INSERT, SELECT, UPDATE, ALTER, REFERENCES ON TestDB.TestTABLE TO 'YOUR-USERNAME'@'localhost';

And the following command applies the listed privileges to all tables in the specified database (replace “TestDB” with your database name and replace “YOUR-USERNAME” with your username):

GRANT CREATE, DROP, DELETE, INSERT, SELECT, UPDATE, ALTER, REFERENCES ON TestDB.* TO 'YOUR-USERNAME'@'localhost';

How to grant a user all database privileges in MySQL (MariaDB)

If you want to grant a user all possible database privileges, then use a query like this (replace “TestDB” with your database name and replace “YOUR-USERNAME” with your username):

GRANT ALL PRIVILEGES ON TestDB.* TO 'YOUR-USERNAME'@'localhost';

For example:

GRANT ALL PRIVILEGES ON TestDB.* TO 'mial'@'localhost';

How to give a user database privileges that can be transferred to other users in MySQL (MariaDB)

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:

GRANT ALL PRIVILEGES ON TestDB.* TO 'mial'@'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 “YOUR-USERNAME” with the username):

SHOW GRANTS FOR 'YOUR-USERNAME'@'HOST';

For example:

SHOW GRANTS FOR 'mial'@'localhost';