Give non-root user access to log into phpMyAdmin

Morfie asked:

I’m attempting to give a non-root mysql user access to log into phpMyAdmin and only see the DB they have access to.

I’ve already looked at the following articles and tried their advice:
Configuring phpmyadmin for Multiple Users

I’ve granted the mysql user access with password to their DB and to select items in mysql db (as stated in one of the articles). I have configed phpmyadmin to use cookie auth_type, I have a pma control user, I’ve granted the user from not just ‘%’ but from localhost and IPs, etc.

When the user logs in, they get #1045 Cannot log in to the MySQL server

My root user can log in fine to phpmyadmin. The user that needs access to phpmyadmin can log in from the command line fine as well.

The phpmyadmin directory has chmod -R 777 on it, except for the config file, which isn’t world writable.

The applicable lines from the mysql logfile are:

130507 14:40:00 [Warning] 'user' entry 'someusername@mydomain' ignored in --skip-name-resolve mode.ve mode.
130507 14:40:00 [Warning] 'db' entry 'somedbname someusername@mydomain' ignored in --skip-name-resolve mode.
130507 14:40:00 [Warning] 'tables_priv' entry 'db someusername@mydomain' ignored in --skip-name-resolve mode.
130507 14:40:00 [Warning] 'tables_priv' entry 'host someusername@mydomain' ignored in --skip-name-resolve mode.
130507 14:40:00 [Warning] 'tables_priv' entry 'tables_priv someusername@mydomain' ignored in --skip-name-resolve mode.
130507 14:40:00 [Warning] 'tables_priv' entry 'user someusername@mydomain' ignored in --skip-name-resolve mode.

Just as a note, the actual someusername@mydomain does was granted privs with password.

My phpmyadmin config looks like this:

$cfg['blowfish_secret'] = '*******************';
$i = 0;
$i++;
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['AllowNoPassword'] = false;
$cfg['Servers'][$i]['controluser'] = 'pma';
$cfg['Servers'][$i]['controlpass'] = '**********';
$cfg['Servers'][$i]['hide_db'] = 'information_schema';
$cfg['UploadDir'] = '';
$cfg['SaveDir'] = '';
$cfg['MaxRows'] = 500;
$cfg['AllowUserDropDatabase'] = true;

Any ideas on how to solve this issue?

Thanks.

My answer:


You started MySQL with the skip-name-resolve option, either on the command line or in /etc/my.cnf, but you have MySQL users whose names include a hostname, rather than IP address. Since MySQL isn’t resolving IP addresses to hostnames, it can’t authenticate those users.

To resolve the issue, do one or both of:

  1. Remove the skip-name-resolve option.
  2. Create MySQL users with IP addresses rather than host names.

View the full question and answer on Server Fault.

Creative Commons License
This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.