Loading...
X

ERROR 1044 (42000): Access denied for user ‘mial’@’localhost’ to database ‘TestDB’. Can’t create MySQL database (SOLVED)

Why can't user create MySQL (MariaDB) database

When trying to create a database, you may encounter an error:

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

The reason for the error is that the specified user (username “mial” in this example) does not have the authority to create databases.

The solution to the problem depends on the situation in which you are faced with it.

1. Error “ERROR 1044 (42000)” on shared hosting

If you want to create a new database on the hosting, for example, through the phpMyAdmin web interface or in a PHP script, and you get the error “ERROR 1044 (42000)”, then this is a common situation. Usually hosting provides another way to create databases and users, for example, in the control panel on the corresponding tab.

2. Error on the local computer or virtual private server

This error may occur when you have full root access to the MySQL (MariaDB) server, but you have created an additional user to execute SQL queries from a PHP script or for other purposes.

There are several solutions in this case.

1) Create a database as a root user and assign database privileges to the specified user

For example, we log in as the root user:

mysql -u root -p

Or:

mariadb -u root -p

Create a database (replace “TestDB” with the name of the database):

CREATE DATABASE TestDB;

Grant privileges to work with the specified database to the desired user (replace “TestDB” with the name of the database, and “YOUR-USERNAME” with the username):

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

Check granted privileges:

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

For example, the username is “mial”, then the queries are:

 

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

In the “Grants for mial@localhost” column, the following line repeats the granting of privileges to the user and at the same time means that everything worked:

GRANT ALL PRIVILEGES ON `TestDB`.* TO `mial`@`localhost`

So we have the following result:

  • database created
  • normal user did not get the rights to create databases and access other databases
  • a normal user can create and edit tables and records in the database for which he has been granted privileges

2) Allow normal user to create databases

The following request allows the user to create databases (replace “YOUR-USERNAME” with your username):

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

For example, for the “mial” user, the commands are:

Log in as root user:

mysql -u root -p

Or:

mariadb -u root -p

We give the specified user privileges to create databases:

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

Checking the granted privileges:

SHOW GRANTS FOR 'mial'@'localhost';

Now you can log in as a normal user who was previously unable to create the database due to an error:

mysql -u mial -p

Or:

mariadb -u mial -p

We create a database:

CREATE DATABASE TestDB;

As you can see, the error “ERROR 1044 (42000): Access denied for user” has been fixed.

The following result is obtained:

  • normal user can create databases
  • but at the same time, a regular user gets access to other databases, for example, can view their contents, can create other databases

Leave Your Observation

Your email address will not be published. Required fields are marked *