Database (mysql) access denied

dagaloni

dagaloni

Verified User
Joined
Jul 10, 2020
Messages
5
Location
Netherlands
Hi there!

I'm struggling to find an answer for my problem for I lack the right terminology. The problem: I can't use "Your current login information" when uploading a database via directadmin (see screenshot). When I do, I get the following error:

Code: 
Unable to restore database davxxxxxx : ERROR 1045 (28000): Access denied for user 'davxxxxxx'@'localhost' (using password: YES)

Screenshot 2020-07-10 at 10.56.01.png
> Screenshot with the option "Your current login information"

I already reset the password for da_admin. So I am able to upload a database via "A specified [user] & pass []". I would like to make ""Your current login information" work again.

I think I'm looking for a mysql user, but when I search for that term I get results of the database user and that's not what I want. When I search for 'access denied' all I get are results for resetting da_admin passwords.

Can someone point me in the right direction? Many thanks! ?
 
When you are successful are you using your DA username or the mysql username such as user_mysqlname? Mysql usernames that get created when creating a database have the underscore.
 
floyd said:
Did you do step 3?

Click to expand...

Thanks for the suggestion! Yes, I did step 3. In fact, that is the guide I used.

floyd said:
When you are successful are you using your DA username or the mysql username such as user_mysqlname? Mysql usernames that get created when creating a database have the underscore.
Click to expand...

The credentials created in step 3 are the ones working and the one I use with 'specified user'.

It used to be the case that I could login into phpmyadmin with the same credentials as the reseller-admin. That's not working at the moment and I have no idea how to fix that.
 
It used to be the case if you changed your password in DA it would change the mysql password as well. Try doing that. Then if the database is under myusername then you should be able to login with myusername and the new password.

You can also login to phpmyadmin using the da_admin username and password and check the username you are having a problem with. But be very careful as you can do a lot of damage logged in a da_admin.
 
[Update – See below]
--
Thanks Floyd! I think I headed a step in the right direction. ?

What I did was login into a reseller-admin and changed the password from there (via the password button in the menu). I checked all boxes including 'main database' account. That didn't solve the problem (still the same error when trying to upload a database via directadmin). However, when I tried to login into phpmyadmin it accepted my password and let me login. Then I got a message: You have no privileges.

So I logged into phpmyadmin via da_admin and went to User Accounts and tried to add some privileges to the reseller-admin. But here I ran into some problems which are a little over my head.

When I went to User Accounts, at the bottom of the page there was this notice: Note: phpMyAdmin gets the users’ privileges directly from MySQL’s privilege tables. The content of these tables may differ from the privileges the server uses, if they have been changed manually. In this case, you should reload the privileges before you continue. I looked up mysql.tables_priv and the reseller-admin was not to be found there. Should I add the user there? And if so, how?

Under User Accounts I looked up the reseller-admin and clicked 'edit privileges' but it doesn't matter what I select, the moment I hit Go I get this error message:

Code: 
Error
SQL query: DocumentationEdit Edit
GRANT SELECT, INSERT, UPDATE, DELETE, FILE ON  *.* TO 'davxxxxxxx'@'localhost' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_' at line 1

So I think I'm heading in the right direction and I have to somehow restore the privileges of the reseller-admins.

Update: Via phpmyadmin I added all the databases created in the reseller-admin to the user. When I login with the reseller-admin's credentials in phpmyadmin I can see all the databases and I can import databases! ?

What I did was login via da_Admin > User Accounts > Edit Privileges (of the reseller-admin) > Database (in the secondary menu) > Added all the databases created in the reseller-admin's account of directadmin.
 
Last edited:
The only thing I have to do is figure out why the access is denied when I try to upload/import a database via directadmin and how to fix that.
 
I still don't understand why the "Your current login information" option is still there and the default for normal users anyway, this will never work for 99,99% of the users right?
 
So can you go to SSH and login
Code: 
mysql -uroot -p

OR

mysql -uda_admin -p

If yes can the user assigned to that database login as well?

also what do you have for Backup and Restore settings in the Admin Backup and Transfer screen?

1609944685507.png
Is restore database as admin marked ?

Depending on who is logged in and who is restorring you might have something mismatched

Also did you for sure kill off the
Code: 
service mysqld stop
mysqld_safe --skip-grant-tables &

Might check ps to be sure. I have had to manually go kill off these before and cycle mysqld before.
 
This is not related to admin backups so not sure how that could be related. This is in user level MySQL management -> Upload Backup with the connection method "Your current login information". For me it always results in a:

Code: 
Unable to restore database forum_test : ERROR 1045 (28000): Access denied for user 'forum'@'localhost' (using password: YES)

This was the logged in user forum trying to upload a simple db backup to the database forum_test. When I check manually by logging in with mysql -uforum -p I can list and access all the databases username_* so the username user definitely has access to all these databases I'm trying to upload the backup to:

SQL: 
root@server1:/home/forum# mysql -uforum -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8616351
Server version: 5.7.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use forum_test
Database changed
mysql> CREATE TABLE table1(
    ->  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->  firstname VARCHAR(30) NOT NULL,
    ->  lastname VARCHAR(30) NOT NULL,
    ->  email VARCHAR(50),
    ->  reg_date TIMESTAMP
    ->  );
Query OK, 0 rows affected (0.05 sec)

mysql> show tables;
+----------------------+
| Tables_in_forum_test |
+----------------------+
| table1               |
+----------------------+
1 row in set (0.00 sec)

Also not entirely sure where this upload backup feature would actually get "Your current login information" securely.
 
tristan said:
This is in user level MySQL management -> Upload Backup
Click to expand...
O sorry I got confused by the resetting of the da_admin part.
tristan said:
This was the logged in user forum trying to upload a simple db backup to the database forum_test.
Click to expand...
Must be a Bug then.;)

If the user forum has privilege on the said database it should work. If you go to the a specified user and put in forum and password does it work?

Did you try syncing the user's password in Change your password?

1609947828395.png
 
Yeah already found the problem, it only works when you're really logged in "cleanly" as that user, not when you're using the "login as ..." feature. Which is unfortunate since we do a lot of management as admin users on our servers. So seems like a bug to me since that "Your current login information" shouldn't be there as an option when being "logged in as ...".
 
tristan said:
Yeah already found the problem, it only works when you're really logged in "cleanly" as that user, not when you're using the "login as ..." feature. Which is unfortunate since we do a lot of management as admin users on our servers. So seems like a bug to me since that "Your current login information" shouldn't be there as an option when being "logged in as ...".
Click to expand...
I see. Me too log in as I use a lot as well. Sound like a Feedback request for sure.
 
You must log in or register to reply here.
Back
Top