Phpmyadmin: "You do not have privileges to manipulate with the users!" on root superadmin
Steps to reproduce
Created a local server of MariaDB via home-brew
Installed latest version of phpmyadmin
logged in as root with password
pma control user set up
No functionality to add users
Expected behaviour
Should see "add user account" button
Actual behaviour
No button and error:
"You do not have privileges to manipulate with the users!"
Server configuration
Operating system:
MacOS X High Sierra 10.13.5 (17F77)
Web server:
Apache/2.4.33 (Unix) OpenSSL/1.0.2o PHP/7.1.18
Database client version: libmysql - mysqlnd 5.0.12-dev - 20150407 - $Id: 38fea24f2847fa7519001be390c98ae0acafe387 $
PHP extension: mysqliDocumentation curlDocumentation mbstringDocumentation
PHP version: 7.1.18
Database:
Server: 127.0.0.1 via TCP/IP
Server type: MariaDB
Server connection: SSL is not being used Documentation
Server version: 10.3.7-MariaDB - Homebrew
Protocol version: 10
User: root@localhost
Server charset: UTF-8 Unicode (utf8)
PHP version:
phpMyAdmin version:
Version information: 4.8.2 (up to date)
Client configuration
Browser:
Safari: Version 11.1.1 (13605.2.8)
Chrome: Version 67.0.3396.99 (Official Build) (64-bit)
Operating system:
MacOS X High Sierra 10.13.5 (17F77)
freedmandil
All 50 comments
Could you please post here the output of this SQL command (which you can run from the "SQL" tab anywhere in phpMyAdmin)?
SHOW GRANTS;
ibennetch
on 30 Jun 2018
Grants for root@localhost | 聽
-- | --
GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*' WITH GRANT OPTION
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
freedmandil
on 1 Jul 2018
I have the same issue running MySQL 5.6.39.
The issue is gone after downgrading PMA from 4.8.1 to 4.7.8.
Note: I did not try any pma releases in between.
sabl0r
on 2 Jul 2018
I can not reproduce this issue, post output of (ADJUST the '''root''@''%''' part):
SELECT * FROM `INFORMATION_SCHEMA`.`USER_PRIVILEGES` WHERE `PRIVILEGE_TYPE` = 'CREATE USER' AND '''root''@''%''' LIKE `GRANTEE` LIMIT 1
AND
SELECT * FROM (SELECT `GRANTEE`, `IS_GRANTABLE` FROM `INFORMATION_SCHEMA`.`COLUMN_PRIVILEGES` UNION SELECT `GRANTEE`, `IS_GRANTABLE` FROM `INFORMATION_SCHEMA`.`TABLE_PRIVILEGES` UNION SELECT `GRANTEE`, `IS_GRANTABLE` FROM `INFORMATION_SCHEMA`.`SCHEMA_PRIVILEGES` UNION SELECT `GRANTEE`, `IS_GRANTABLE` FROM `INFORMATION_SCHEMA`.`USER_PRIVILEGES`) t WHERE `IS_GRANTABLE` = 'YES' AND '''root''@''%''' LIKE `GRANTEE` LIMIT 1
Source https://github.com/phpmyadmin/phpmyadmin/blob/e816e45c183f49e770b4c36714c509e913d2043c/libraries/classes/DatabaseInterface.php#L2291
Tests
I tried 4.8.2 and 5.0.0-dev
SHOW GRANTS;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' WITH GRANT OPTION
docker run --hostname=mariadb-10.3.7 --publish=3307:3306 --rm --name=mariadb-10.3.7-pwd-root -e MYSQL_ROOT_PASSWORD=root -d mariadb:10.3.7
Port on local machine: 3307
williamdes
on 2 Jul 2018
I upgraded to 4.8.2 and the issue is back again.
SELECT * FROM `INFORMATION_SCHEMA`.`USER_PRIVILEGES` WHERE `PRIVILEGE_TYPE` = 'CREATE USER' AND '''root''@''localhost''' LIKE `GRANTEE` LIMIT 1
GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE
-- | -- | -- | --
'root'@'localhost' | def | CREATE USER | YES
SELECT * FROM (SELECT `GRANTEE`, `IS_GRANTABLE` FROM `INFORMATION_SCHEMA`.`COLUMN_PRIVILEGES` UNION SELECT `GRANTEE`, `IS_GRANTABLE` FROM `INFORMATION_SCHEMA`.`TABLE_PRIVILEGES` UNION SELECT `GRANTEE`, `IS_GRANTABLE` FROM `INFORMATION_SCHEMA`.`SCHEMA_PRIVILEGES` UNION SELECT `GRANTEE`, `IS_GRANTABLE` FROM `INFORMATION_SCHEMA`.`USER_PRIVILEGES`) t WHERE `IS_GRANTABLE` = 'YES' AND '''root''@''localhost''' LIKE `GRANTEE` LIMIT 1
GRANTEE | IS_GRANTABLE
-- | --
'root'@'localhost' | YES
sabl0r
on 3 Jul 2018
I had the same issue and I fixed it by setting following option in config.inc.php:
$cfg['Servers'][$i]['DisableIS'] = true;
I guess something is messed up somewhere here:
LogIN-
on 5 Jul 2018
adding:
$cfg['Servers'][$i]['DisableIS'] = true;
Fixed the issue for me as well
freedmandil
on 8 Jul 2018
To answer:
SELECT * FROM `INFORMATION_SCHEMA`.`USER_PRIVILEGES` WHERE `PRIVILEGE_TYPE` = 'CREATE USER' LIKE `GRANTEE` LIMIT 1
GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE
-- | -- | -- | --
'root'@'localhost' | def | SELECT | YES
SELECT * FROM (SELECT `GRANTEE`, `IS_GRANTABLE` FROM `INFORMATION_SCHEMA`.`COLUMN_PRIVILEGES` UNION SELECT `GRANTEE`, `IS_GRANTABLE` FROM `INFORMATION_SCHEMA`.`TABLE_PRIVILEGES` UNION SELECT `GRANTEE`, `IS_GRANTABLE` FROM `INFORMATION_SCHEMA`.`SCHEMA_PRIVILEGES` UNION SELECT `GRANTEE`, `IS_GRANTABLE` FROM `INFORMATION_SCHEMA`.`USER_PRIVILEGES`) t WHERE `IS_GRANTABLE` = 'YES' AND '''root''@''localhost''' LIKE `GRANTEE` LIMIT 1
Produces:
GRANTEE | IS_GRANTABLE
-- | --
'root'@'localhost' | YES
freedmandil
on 8 Jul 2018
I'm getting the same error: "You do not have privileges to manipulate with the users!"
with PhpMyAdmin Version 4.8.0, MariaDB 10.1.31, XAMPP version 7.2.2, PHP 7.2.4. I am on a Windows 10 64 bit system.
adding "$cfg['Servers'][$i]['DisableIS'] = true;" to the config.inc.php file did not work for me. Altho the PhpMyAdmin 4.8.0 changelog indicates (issue #13722) DisableIS is not fully honored.
feralreason
on 22 Sep 2018
Per sabl0r, downgrading to PhpMyAdmin Version 4.7.8 removes the "You do not have privileges to manipulate with the users!" error. However, I _still have no button_ to add users.
Ran:
SELECT `User`,`Host`,`Create_user_priv` FROM `user` WHERE `User` = 'root' AND Create_user_priv = 'Y'
This produces:
User Host Create_user_priv
root localhost Y
root 127.0.0.1 Y
Ran:
SELECT * FROM `INFORMATION_SCHEMA`.`USER_PRIVILEGES` WHERE `PRIVILEGE_TYPE` = 'CREATE USER' AND '''root''@''localhost''' LIKE `GRANTEE` LIMIT 1
This produces an Empty Set
Ran:
SELECT * FROM (SELECT GRANTEE,IS_GRANTABLE FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES UNION SELECT GRANTEE,IS_GRANTABLE FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES UNION SELECT GRANTEE,IS_GRANTABLE FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES UNION SELECT GRANTEE,IS_GRANTABLE FROM INFORMATION_SCHEMA.USER_PRIVILEGES) t WHERE IS_GRANTABLE= 'YES' AND '''root''@''localhost''' LIKE GRANTEE LIMIT 1
This also produces an Empty Set
MySQL documentation indicates that the INFORMATION_SCHEMA pulls the Create User privilege info from the USER table -- so what's happening here?
Ran:
SHOW GRANTS
This produced this message:
1290 - The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement
feralreason
on 27 Sep 2018
@feralreason: I'm sorry but I'm going to use your post as a 'template' for mine. Details and versions differ but I've taken the exact same steps.
I'm getting the same error: "You do not have privileges to manipulate with the users!"
with PhpMyAdmin Version 4.8.3, MariaDB 10.1.26, nginx/1.15.5, PHP 7.2.10. I am on a Debian 64 bit system.
Adding $cfg['Servers'][$i]['DisableIS'] = true; to the config.inc.php file did also not work for me.
Ran: SELECT User,Host,Create_user_priv FROM user WHERE User = 'root' AND Create_user_priv = 'Y'
This produces:
User Host Create_user_priv
root localhost Y
root <hostname> Y
root 127.0.0.1 Y
root ::1 Y
root % Y
Ran:
SELECT * FROM INFORMATION_SCHEMA.USER_PRIVILEGES WHERE PRIVILEGE_TYPE= 'CREATE USER' LIKE GRANTEE LIMIT 1
This produces:
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
'root'@'localhost' def SELECT YES
Ran:
SELECT * FROM (SELECT GRANTEE,IS_GRANTABLE FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES UNION SELECT GRANTEE,IS_GRANTABLE FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES UNION SELECT GRANTEE,IS_GRANTABLE FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES UNION SELECT GRANTEE,IS_GRANTABLE FROM INFORMATION_SCHEMA.USER_PRIVILEGES) t WHERE IS_GRANTABLE= 'YES' AND '''root''@''localhost''' LIKE GRANTEE LIMIT 1
This produces:
GRANTEE IS_GRANTABLE
'root'@'localhost' YES
Ran: SHOW GRANTS
This produces:
Grants for root@localhost
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' ...
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH G...
RobThree
on 25 Oct 2018
No problem. I never tracked down the cause -- ended up doing a complete reinstall of XAMPP. That solved my problem.
feralreason
on 25 Oct 2018
I've been granting, flushing, restarting and even rebooting my * off but still the same. I can manage with MySQL WorkBench but it is quite disappointing to see PHPMyAdmin fail on this.
RobThree
on 26 Oct 2018
Just a heads up: In my case it was related to the control-user having faulty configuration.
GingerAdonis
on 9 Nov 2018
Any other fix? Getting this :/
CharlieJ1911
on 4 Dec 2018
Can you show your current config? (with masked credentials of course)
GingerAdonis
on 4 Dec 2018
I can confirm what @JarnoVgr said: this is an issue with a misconfigured control-user. In my case, I had to
GRANT ALL PRIVILEGES ON `phpmyadmin`.* TO 'MyControlUser'@'MyHost' and FLUSH PRIVILEGES.
cdoggyd
on 5 Dec 2018
Hello,
i have this issue on verrsion 4.8.5. Any news on this if it should be fixed?
The PMA user has full privileges on the PMA database. I login as superuser and cannot add new users. It shows "You do not have privileges to manipulate with the users!".
regards
Kai
balticer
on 28 Feb 2019
The issue is still there using phpMyAdmin-4.8.5. Root user is not allowed to add users.
- MariaDB 10.1.38
- Ubuntu 18.04.1
- Login as root on localhost with password
MariaDB [(none)]> SHOW GRANTS;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*redacted' WITH GRANT OPTION
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
MariaDB [(none)]> SELECT * FROM `INFORMATION_SCHEMA`.`USER_PRIVILEGES` WHERE `PRIVILEGE_TYPE` = 'CREATE USER' AND '''root''@''localhost''' LIKE `GRANTEE` LIMIT 1;
+--------------------+---------------+----------------+--------------+
| GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+--------------------+---------------+----------------+--------------+
| 'root'@'localhost' | def | CREATE USER | YES |
+--------------------+---------------+----------------+--------------+
1 row in set (0.01 sec)
MariaDB [(none)]> SELECT * FROM (SELECT `GRANTEE`, `IS_GRANTABLE` FROM `INFORMATION_SCHEMA`.`COLUMN_PRIVILEGES` UNION SELECT `GRANTEE`, `IS_GRANTABLE` FROM `INFORMATION_SCHEMA`.`TABLE_PRIVILEGES` UNION SELECT `GRANTEE`, `IS_GRANTABLE` FROM `INFORMATION_SCHEMA`.`SCHEMA_PRIVILEGES` UNION SELECT `GRANTEE`, `IS_GRANTABLE` FROM `INFORMATION_SCHEMA`.`USER_PRIVILEGES`) t WHERE `IS_GRANTABLE` = 'YES' AND '''root''@''localhost''' LIKE `GRANTEE` LIMIT 1;
+--------------------+--------------+
| GRANTEE | IS_GRANTABLE |
+--------------------+--------------+
| 'root'@'localhost' | YES |
+--------------------+--------------+
1 row in set (0.00 sec)
Setting "$cfg['Servers'][$i]['DisableIS'] = true;" enables creation of new users, but then I do not see all databases anymore. They are still there and applications can use them, they are just not shown in phpMyAdmin. So I need to set this option to manage users and then unset it again to manage the databases. This is really no suitable workaround.
sleeksorrow
on 29 Apr 2019
I just started having it happen, and 'DisableIS' allowed user creation, but some of my schemas disappeared in the list (command line says they're still there, though).
Also: wondering if this might be related to the PMA service schema, since this didn't really show up until I enabled that. But after enabling, there was a single entry in the entire PMA schema in the pma__recent schema:
[{"db":"phpmyadmin","table":"pma__recent"},{"db":"phpmyadmin","table":"pma__table_info"},{"db":"phpmyadmin","table":"pma__table_uiprefs"},{"db":"phpmyadmin","table":"pma__usergroups"},{"db":"phpmyadmin","table":"pma__userconfig"},{"db":"phpmyadmin","table":"pma__users"}]
None of my accessing of any other tables appears to be logged, and no other table in the entire schema has any entries... which is weird. I've been actively developing a web site for 2 weeks.
This is on a personal Windows-based dev machine running WAMPServer64 (as of this post, the latest version, which includes PMA 4.8.4 ).
VishwaJay
on 4 May 2019
The DisableIS option did not work for me on one of our VPSes.
If it's any use I found this to be caused by incorrect phpMyAdmin (SQL) configuration. Turned out that the user I had created for this did not have database privileges assigned. As I was signed in as a super user I manually added the database privileges via the table directly, flushed privileges via the CLI, re-logged into phpMyAdmin then this was resolved.
AdamReece-WebBox
on 8 May 2019
@AdamReece-WebBox And can you start a database server and recreate the issue from scratch ?
It would be awesome if you could help us reproduce this issue :)
williamdes
on 8 May 2019
I can't remove/recreate the MySQL server from scratch as it's on one of our production VPSes (currently hosting a pretty highly visited client website), though I can verify on both this and other similarly built VPSes that simply denying the assigned PMA user privileges it needs to the assigned configuration database causes this, after flushing privileges and re-logging into PMA of course.
Perhaps when PMA has invalid configuration to use SQL based setting storage it blocks it from being able to determine the current user's privileges correctly? I also think that in my case it was definitely our fault for not giving the assigned PMA user the privileges it needed to work with the assigned database. (Not necessarily a PMA fault.)
The only thing that led me on to this cause was the red error about configuration storage at the bottom of PMA's dashboard. (I should have noticed this earlier.) When I clicked through to find out why it wanted the database to be created, though it was very visible on the sidebar. However clicking into the "phpmyadmin" database then Privileges tab was also unavailable, so this needed to be done manually. Clicking through to the "mysql" database and "db" table the expected row for PMA's privileges was clearly missing. To resolve this I just inserted the intended row and flushed privileges, then when I re-logged into PMA as a super user it correctly let me manage user privileges.
The VPS in question, and the 2 others I tried to reproduce it on are Debian (Stretch) based using MariaDB 10.1.38. They obtain PMA via Git using the "STABLE" tag, pulling it for updates on a daily basis. -- I also tried this on one of our Ubuntu (Bionic) VPSes running MySQL 5.7.26, and this issue occurred in the exact same circumstances.
AdamReece-WebBox
on 8 May 2019
This is on a personal Windows-based dev machine running WAMPServer64 (as of this post, the latest version, which includes PMA 4.8.4 ).
I should also add: my machine runs MariaDB on port 3306 and MySQL Community Ed on port 3308 (whichever ones shipped with Wampserver64 3.1.3, if that helps). All install options were the defaults.
The issue cleared up for me with DisableIS and then returned again intermittently on MariaDB (and hasn't returned on MySQL, that I can see). However, there are a few schemas I can't see with DisableIS true.
VishwaJay
on 10 May 2019
In my case phpMyAdmin 4.8.5 did not have write access to the default tmp folder at ./tmp. Once I gave apache2 write access to that folder I am able to manipulate with the users again.
iaasen
on 13 May 2019
Can someone give me temporary access to a phpMyAdmin instance and a way to edit the code in order to find a fix ? (please email me : [email protected])
I still can not reproduce this issue..
Or can someone create a step by step way to reproduce this using a docker image of mysql/mariadb ?
williamdes
on 14 May 2019
Nobody ?
williamdes
on 18 May 2019
Of the servers I work on, all belong to other people.
Codenvy ?
VishwaJay
on 19 May 2019
My affected installation has ./tmp writable for the webserver and phpmyadmin is using it thoroughly. So this is not the culprit for me.
My installation is not publicly reachable and it's holding confidential data, so I cannot provide access to it. Nevertheless I could try setting debug options (hints please) or installing special debug versions or adding debug patches for gaining more info and provide that output after reviewing and redaction if needed.
sleeksorrow
on 20 May 2019
Has this perhaps been fixed already in the head? I'm using the stable branch on all of our VPSes.
I obviously can't hand out access to them as they're all holding client business data.
AdamReece-WebBox
on 21 May 2019
Using the bandaid fix $cfg['Servers'][$i]['DisableIS'] = true; produces no change for me on phpmyamdin 4.8.5 with Apache2.4.29/ubuntu and MySQL Ver 14.14 Distrib 5.7.26
nate2014jatc
on 23 May 2019
Try to delete/comment those lines:
$cfg['Servers'][$i]['controluser'] = 'USERNAME';
$cfg['Servers'][$i]['controlpass'] = 'PASSWORD';
dev-subhojit
on 9 Jun 2019
I have had this issue for a while. Reverted back phpMyAdmin 4.7.8 where I don't see the issue. None of the suggested workaround helped for me. @williamdes I can give you access to a server were the issue is seen, will PM you.
hoxi
on 11 Jun 2019
I would never do this in production, but I ran into this problem on my personal development machine under XAMPP 7.3.0 with MariaDB 10.1.37. My solution was to set:
$cfg['Servers'][$i]['controluser'] = '<root>';
$cfg['Servers'][$i]['controlpass'] = '<root_password>';
in the phpmyadmin config file: config.inc.php
After forcing a page reload, I was able to manipulate users again. I think there is definitely something wrong with the control user implementation in this release, but for now, at least I have visual control of user management. Hope this helps somebody out there until this issue is resolved upstream or a better workaround becomes available.
jnorrid
on 13 Jun 2019
I had the same issue and I fixed it by setting following option in
config.inc.php:
$cfg['Servers'][$i]['DisableIS'] = true;
This resolved my issue as well. I had the same thing with Grants being correct but not having access within phpMyAdmin to grant privileges. I'd give you temporary access to troubleshoot it, but this phpMyAdmin instance is only accessible via VPN and it's on a production box. I'm running MySQL 5.7.26, PHP 7.3.6 and phpMyAdmin 4.9.0.1 with apache 2.4 on FreeBSD 11.2.
objecttothis
on 8 Jul 2019
I have phpMyAdmin 4.9.0.1 apache2 on old Ubuntu14_04 with php5 working with mysql 5.5 and 5.7
$cfg['Servers'][$i]['DisableIS'] = true;
did _NOT_ resolve the problem.
johnh530
on 11 Jul 2019
I have phpMyAdmin 4.9.0.1 apache2 on old Ubuntu14_04 with php5 working with mysql 5.5 and 5.7
$cfg['Servers'][$i]['DisableIS'] = true;
did _NOT_ resolve the problem.
If you have the PMA configuration database/tables defined in your config.inc.php check see https://github.com/phpmyadmin/phpmyadmin/issues/14433#issuecomment-490385669
AdamReece-WebBox
on 11 Jul 2019
To be clear, I need a way to reproduce this issue.
It would be awesome if someone could send the phpmyadmin config file and the '/var/lib/mysql' folder in a zip (please remove sensitive data but be sure the issue still exists)
Also send the exact version you are using so I can start a docker container
I think this issue is maybe some mysql data being corrupt
:ping_pong: @hoxi
williamdes
on 11 Jul 2019
@williamdes I'd like to help out, but I'm going to need some assistance locating the files you need. the config.inc.php is easy. I'm running this mysql/phpmyadmin from a Jail in FreeBSD 11.2 and there is no /var/lib/mysql. I have a /usr/local/var/lib/ but no mysql folder there. Are you just needing the my.cnf file? I can provide that to you (with data redacted).
objecttothis
on 11 Jul 2019
@williamdes I'd like to help out
@objecttothis Thanks
From what I see on https://forums.freebsd.org/threads/moving-mysql-data-dir.3711/ the data folder would be /var/db/mysql or /usr/mysql
Are you just needing the my.cnf file?
I would like to have everything (data and config) from the mysql server + config.inc.php
williamdes
on 11 Jul 2019
@williamdes Found the directory. The problem I have now is that I have to remove all key pairs, certs and all the directories with database content other than the phpMyAdmin folder to maintain GDPR compliance. That doesn't leave a whole lot. Will that be enough for you? It does leave some error logs, etc. Also, tell me where you want me to send it if it will be enough. I'm not going to upload it here. Too much of a security risk.
objecttothis
on 11 Jul 2019
@objecttothis you can remove logs since they are not part of the state of the server
Remove everything that is confidential
If you can boot the server in another folder and then remove all the data and still have the bug It will be okay for me
I really want to be able to have the same issue when starting the server
you can email me, and if you have a GPG key you can protect the data you send me :)
williamdes
on 11 Jul 2019
@williamdes email sent.
objecttothis
on 13 Jul 2019
Well the way I continually reproduced the issue in 4.9.1 is to setup the phpmyadmin's controluser and pmadb. Grant the controluser all rights to pmadb but do NOT run the sql file to populate the pmadb with the tables required by phpmyadmin; leave it as a completely empty db.
Then log into phpmyadmin from the web interface as the root user. Once logged in everything seems to work correctly (despite all the warnings about the pmadb) but for whatever reason since pmadb is an empty db you will be told you don't have privileges to manipulate users.
Running the proper sql to populate the pmadb with the correct tables and then relogging fixes the problem. Almost seems like there is a query in the wrong place making the privilege check fail in a way that it shouldn't.
credomane
on 26 Sep 2019
Yep, I concur with @credomane. That exact situation happened to me only a couple of weeks ago when spinning up a Debian (9.9) VPS for a client. I just forgot to run the SQL script to import PMA DB. (Privileges were set correctly.)
AdamReece-WebBox
on 26 Sep 2019
That fixed my problem, too. I already had a PMA DB and no warnings about it at all, but nevertheless I first tried dropping this DB and recreating it using the script. Sadly this did not fix the problem. Then I dropped the controluser (pma) and recreated it, and then the problem disappeared.
sleeksorrow
on 26 Sep 2019
Thank you @objecttothis the server you sent me has the issue.
I am trying to find a fix
I apologise for making everybody here wait for a fix for a so long time but life goes so quickly :)
williamdes
on 20 Oct 2019
If everyone where can try my patch and comment or add a reaction to say it works ?
The patch: https://github.com/phpmyadmin/phpmyadmin/commit/b8650115be7cc2d79d231723b7a6f1961e834d27 and https://github.com/phpmyadmin/phpmyadmin/commit/af344bb4c1456fec5382a535733a2e3dddddbb8d
https://github.com/phpmyadmin/phpmyadmin/commit/b8650115be7cc2d79d231723b7a6f1961e834d27.patch and https://github.com/phpmyadmin/phpmyadmin/commit/af344bb4c1456fec5382a535733a2e3dddddbb8d.patch
Note
Everybody who added $cfg['Servers'][$i]['DisableIS'] = true; can remove it because it was just a hack so that the other permission detection system is used.
Explained
The bug:
In the code base a line calls getCurrentUserAndHost() and it gets cached.
But the request did not succeed for a unknown reason and still the request was cached.
it cached the user as '@' and not 'root@localhost' for example.
So the queries that used information schema used a LIKE '@' instead of 'root@localhost' for example.
They did no find any rows for '@' and so the user "did not have the privileges"
williamdes
on 20 Oct 2019
This problem is still existing together
mysqli::real_connect(): (HY000/1698): Access denied for user 'root'@'localhost'
System: Ubuntu 20.04 MariaDB 10.5.3 PHPMyadmin Version information: 5.0.2 (up to date)
and the behavior is like the original poster has already mentioned.
adding
/* ADDED MANUALLY */
$cfg['Servers'][$i]['DisableIS'] = true;
solved the problem for now
But the root user can't edit user priviledges
You do not have the privileges to administrate the users!
https://mariadb.com/kb/en/rename-user/
The RENAME USER statement renames existing MariaDB accounts. To use it, you must have the global CREATE USER privilege or the UPDATE privilege for the mysql database. Each account is named using the same format as for the CREATE USER statement;
CREATE USER 'donald', 'mickey';
RENAME USER 'donald' TO 'duck'@'localhost', 'mickey' TO 'mouse'@'localhost';
is working from shell but not in phpMyAdmin
I can't even access the administration of a user in PHPMyadmin
+--------------------+--------------+
| GRANTEE | IS_GRANTABLE |
+--------------------+--------------+
| 'root'@'localhost' | YES |
+--------------------+--------------+
| 'root'@'localhost' | def | SELECT | YES |
| 'root'@'localhost' | def | INSERT | YES |
| 'root'@'localhost' | def | UPDATE | YES |
| 'root'@'localhost' | def | DELETE | YES |
| 'root'@'localhost' | def | CREATE | YES |
| 'root'@'localhost' | def | DROP | YES |
| 'root'@'localhost' | def | RELOAD | YES |
| 'root'@'localhost' | def | SHUTDOWN | YES |
| 'root'@'localhost' | def | PROCESS | YES |
| 'root'@'localhost' | def | FILE | YES |
| 'root'@'localhost' | def | REFERENCES | YES |
| 'root'@'localhost' | def | INDEX | YES |
| 'root'@'localhost' | def | ALTER | YES |
| 'root'@'localhost' | def | SHOW DATABASES | YES |
| 'root'@'localhost' | def | SUPER | YES |
| 'root'@'localhost' | def | CREATE TEMPORARY TABLES | YES |
| 'root'@'localhost' | def | LOCK TABLES | YES |
| 'root'@'localhost' | def | EXECUTE | YES |
| 'root'@'localhost' | def | REPLICATION SLAVE | YES |
| 'root'@'localhost' | def | BINLOG MONITOR | YES |
| 'root'@'localhost' | def | CREATE VIEW | YES |
| 'root'@'localhost' | def | SHOW VIEW | YES |
| 'root'@'localhost' | def | CREATE ROUTINE | YES |
| 'root'@'localhost' | def | ALTER ROUTINE | YES |
| 'root'@'localhost' | def | CREATE USER | YES |
| 'root'@'localhost' | def | EVENT | YES |
| 'root'@'localhost' | def | TRIGGER | YES |
| 'root'@'localhost' | def | CREATE TABLESPACE | YES |
| 'root'@'localhost' | def | DELETE HISTORY | YES |
| 'root'@'localhost' | def | SET USER | YES |
| 'root'@'localhost' | def | FEDERATED ADMIN | YES |
| 'root'@'localhost' | def | CONNECTION ADMIN | YES |
| 'root'@'localhost' | def | READ_ONLY ADMIN | YES |
| 'root'@'localhost' | def | REPLICATION SLAVE ADMIN | YES |
| 'root'@'localhost' | def | REPLICATION MASTER ADMIN | YES |
| 'root'@'localhost' | def | BINLOG ADMIN | YES |
| 'root'@'localhost' | def | BINLOG REPLAY | YES |
lisandi
on 27 May 2020
I encountered a similar issue on upgrading MySQL, despite convincing myself that I'd uninstalled and re-installed a later version. The issue was that MySQL kindly retained the databases in e.g. /var/lib/mysql, notably the mysql schema containing the user table. With the upgrade, new columns are added to this table: it seems that as a safety measure, the permissions are then reset, and need to be manually changed to 'Y' in the database from the mysql command-line console.
I hope this helps someone.
jvanschalkwyk
on 19 Dec 2020
Related issues
aschuch247
路
41Comments
adjerbetian
路
34Comments
linjialiang
路
36Comments
ibennetch
路
50Comments
smallufo
路
52Comments
Most helpful comment
adding:
$cfg['Servers'][$i]['DisableIS'] = true;Fixed the issue for me as well