[MySQL] Insufficient permissions to add new user

[las3rtje]

Hi all,

After a morning of hardship I've finally transferred my hosting package, WITH SSL, to my VPS with plesk.

I am running into some weird problems.
- When I create a new database there's no problem at all - database created under the right account.
- When I try to create a user for this database, the following message pops up :

Error: Connection to the database server has failed because the supplied account does not possess administrative privileges: Access denied for user 'admin'@'localhost' to database 'prestashop'

this is odd, since I'm logged in as plesk admin, and when I go to the serevr settings -> server database settings and click "view databases" I can see all databases.

The odd thing here is that on neither of my accounts I can create database users.

mysql> select * from user where user='admin' \G
************************* 1. row *************************
Host: localhost
User: admin
Password: 423efac2621ce68b
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string: NULL
1 row in set (0.00 sec)

AND

mysql> SHOW GRANTS FOR 'admin'@'localhost';

| Grants for admin@localhost
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, CREATE TABLESPACE ON . TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '423efac2621ce68b' WITH GRANT OPTION |

I tried restarting the VPS, restarting the mysql daemon, no luck.

Anyone has any idea what can be wrong this time ?

Note: I have not touched the server in any way, I've just created and deleted some accounts, and uploaded site files.

 

[IgorG]

Did you check it? http://kb.parallels.com/1784

 

[las3rtje]

Hello Igor,

Yes, as you can see in my first post all the right GRANTS have been set, also from the plesk cp I can see all the databases in the system.

Today I'll try manually adding a user outside of plesk (straight from mysql commandline), that will probably work, i have no idea what can be wrong here.

 

[las3rtje]

I have fixed this together with my system administrator:

It seems that the mysql "admin" user (same as plesk login) didn't have access to some grants, but it shows "y" on those grants.

We have removed mysql, reinstalled it completely with a proper "root" user, and now everything works again. Nevertheless this shouldn't be possible!