[SOLVED] No permission to access databases no matter what I do

punt · 12-17-2014, 06:53 PM

Okay, so I've spent a significant chunk of my day on this stressful situation.

The short version is I keep creating a username and password for MySQL (MariaDB) and I can't get in no matter what.

Here's an example of what I'm going through:

Code:

$ /sbin/service mysqld restart
Redirecting to /bin/systemctl restart  mysqld.service
$ mysql_setpermission -u user
Password for user user to connect to MySQL: 
Can't make a connection to the mysql server.
 The error: Access denied for user 'user'@'localhost' (using password: YES) at /usr/bin/mysql_setpermission line 90, <STDIN> line 1.

Here's the appropriate line in mysql.user:

Code:

| %         | user | *hashorsomethinghere | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       | N                | N       |p

I can get in fine as the root user which has a password, but for some reason, everything I do with this other account fails with an error.

I've done every literal grant thing I could think of. Plus, I'm mostly using phpMyAdmin. I can't imagine why this is failing.

Code:

UPDATE `mysql`.`user` SET `Password` = PASSWORD('password') WHERE `user`.`Host` = '%' AND `user`.`User` = 'user'

Code:

GRANT ALL PRIVILEGES ON `database`.* TO 'user'@'%'WITH GRANT OPTION;

How do I find out exactly why this is failing?

Code:

$ mysql -u user -p database
Enter password: 
ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)

vmccord · 12-18-2014, 10:40 AM

If I recall correctly localhost is special. I always set that specifically. Also you have to have "identified by" in the grant option.

Code:

GRANT ALL PRIVILEGES ON `database`.* TO 'user'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

punt · 12-18-2014, 05:26 PM

Thanks! Not sure why that fixed it, but it did

SAbhi · 12-18-2014, 08:35 PM

Quote:

Originally Posted by punt

Thanks! Not sure why that fixed it, but it did

well its very clear why it fixed your issue.. untill your card is enabled by the bank you wont be able to make transactions!! got it..?

punt · 12-20-2014, 06:06 PM

Quote:

Originally Posted by SAbhi

well its very clear why it fixed your issue.. untill your card is enabled by the bank you wont be able to make transactions!! got it..?

Right, but ... I enabled the permissions in phpMyAdmin. Multiple times. So I'm not sure how phpMyAdmin *didn't* enable it.

I even copied the root user thinking something broke. But it didn't work.

astrogeek · 12-20-2014, 06:22 PM

Quote:

Originally Posted by punt

Right, but ... I enabled the permissions in phpMyAdmin. Multiple times. So I'm not sure how phpMyAdmin *didn't* enable it.

I even copied the root user thinking something broke. But it didn't work.

I don't think that you actually understand MySQL grants (i.e., user access permissions), including the root user grant, or phpmyadmin's relationship to MySQL.

And it is not at all clear from your description what permissions you thought you had set, and where.

Phpmyadmin can't enable anything unless the permissions exist in the the MySQL user permissions - i.e., via the GRANT.

I would suggest that you forget phpmyqdmin for the moment and study the MySQL manual for how grants work and how connections are established. Afterward read up on phpmyadmin for how it must be configured to interact with MySQL.

punt · 12-20-2014, 06:29 PM

Quote:

Originally Posted by astrogeek

I don't think that you actually understand MySQL grants (i.e., user access permissions), including the root user grant, or phpmyadmin's relationship to MySQL.

And it is not at all clear from your description what permissions you set, and where.

Phpmyadmin can't enable anything unless the permissions exist in the the MySQL user permissions - i.e., via the GRANT.

I would suggest that you forget phpmyqdmin for the moment and study the MySQL manual for how grants work and how connections are established. Afterward read up on phpmyadmin for how it must be configured to interact with MySQL.

Duly noted. I hate relying on software anyway and have always done it previously from the command line. I thought it'd be more robust after all these years and underestimated the command prompt. (I was also doing this as root.)

Thank you.

astrogeek · 12-20-2014, 06:33 PM

Quote:

Originally Posted by punt

Duly noted. I hate relying on software anyway and have always done it previously from the command line. I thought it'd be more robust after all these years and underestimated the command prompt. (I was also doing this as root.)

Thank you.

You are welcome.

It is easy to assume that the mysql root user is somehow special in the same sense that the system root user is - but it is not. "root" is just another MySQL user name and it has whatever permissions were granted to it.

And phpmyadmin is just another external application that must connect via some mysql user account, with only the permissions granted to that user - it has no special status either.

Good luck!