Linux-Noob Forums - PHP/MySQL Permissions problem

I am at the moment building a PHP/MySQL-powered CMS, and recently I have had some trouble when moving the latest versions of the source code to other servers. What seemed to be happening was an error 'No database selected' despite the fact that I always used mysql_select_db() before any database queries. So I did some diagnostics into the problem and I've discovered that MySQL is actually denying access to the database. So on my home system, I tried to recreate the problem (running MySQL as root):

Code:
mysql> revoke all privileges on pkbeta.* from pkbetauser@localhost;

Query OK, 0 rows affected (0.00 sec)

mysql> grant usage on pkbeta.* to pkbetauser@localhost identified by "xxxxxxx";

Query OK, 0 rows affected (0.00 sec)

On my home system, MySQL is now denying privileges to select the database. :( But if I revoke usage this happens:

Code:
mysql> revoke all privileges on pkbeta.* from pkbetauser@localhost;

ERROR 1141 (42000): There is no such grant defined for user 'pkbetauser' on host 'localhost'

Which means the usage privileges were never granted. :(

Adding privilieges by name (e.g. select, insert, update...) works. So why is it not granting usage privilieges on my home system, but most importantly why can't I connect to a database on a different server? Eventually this will not be hosted on a machine I have direct control over (no SSH access or anything), so this is a big issue. :(

Any help would be great, thanks. :)

Hrmm, weird.. only thing I can suggest it to try *notice the quoting*:

Code:
mysql> grant usage on pkbeta.* to 'pkbetauser'@'localhost';

By rights this isn't needed with GRANTs .. but:

Code:
mysql> flush privileges;

Quote:Adding privilieges by name (e.g. select, insert, update...) works.

If you try a manual select of the users table after the grant does it even exist as an entry? or is the command not even doing that..

Code:
mysql> grant usage on *.* to 'test'@'localhost'; 

Query OK, 0 rows affected (0.05 sec)

mysql> select user,host from user where user = 'test';

+------+-----------+

| user | host      |

+------+-----------+

| test | localhost |

+------+-----------+

1 row in set (0.00 sec)

similar on your end?

I can do it with and without quotes and the results are the same.. I am still using v4

Hrmm give it a go anyway..

Quote:most importantly why can't I connect to a database on a different server?

eeek! missed that :)

Its down the user@host restrictions.

For instance at work we have web and mysql on different servers therefore:

user@localhost -> means nothing as no user will ever connect from localhost

user@webserver -> this is the important one, now the user can access the db from that host

You can do (with CARE!) a wildcard entry:

user@%.myhost.com -> user has access from all subdomains of myhost.

Don't do:

user@% -> user can access from anywhere! (obviously the password can still protect but it allows bruting).

OK here's my complete log of trying your stuff (with my comments added afterwards)

Code:
mysql> revoke all privileges on pkbeta.* from 'pkbetauser'@'localhost';

Query OK, 0 rows affected (0.00 sec)

Obviously, now it fails.

Code:
mysql> grant usage on pkbeta.* to 'pkbetauser'@'localhost';

Query OK, 0 rows affected (0.00 sec)

Access is denied :(

Code:
mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

Access is still denied :(

Code:
mysql> connect mysql

Connection id:    8

Current database: mysql

mysql> select user,host from user where user = 'pkbetauser';

+------------+-----------+

| user       | host      |

+------------+-----------+

| pkbetauser | localhost |

+------------+-----------+

1 row in set (0.00 sec)

So, adding it did work then?? o_O

Code:
mysql> grant select, insert, update, delete on pkbeta.* to 'pkbetauser'@'localhost' identified by "xxxxxx";

Query OK, 0 rows affected (0.00 sec)

Now it works :)

What I meant by on another server is when the whole code is moved over as well, so I FTP all the source code to a remote server and also use that remote server's database (so still using localhost). This makes it unlikely to be something wrong with just my server as the permissions are already set up on that server. Interestingly enough, earlier versions of my source code work and can access the database correctly, but newer versions seem to fail with the database not selected error. Does that potentially mean that I have a very big bug somewhere in my code? I don't know how or why this would happen, but here is how I connect to my database in the script.

I have an include file called include.php which does this:

Code:
// MySQL connection data. Switch for a different server.

$conn = mysql_connect(localhost, "pkbetauser", "xxxxxxx");

// Define the DB to connect do

$connectto = "pkbeta";

and then in each script, when needed the following is called (after having included include.php):

Code:
mysql_select_db($connectto, $conn) or die (customMySQLError(mysql_error(), "{Database connect}"));

Sorry, this is a bit long-winded ;)

EDIT: The remote version that I tried to upload is here (though beware it crashes with that error and some pages and files are missing from the installation). It is also meant to be accessed from aurora@66.90.103.17, as I have a running installation using the older source code using these credentials. Thanks for the help :).

Quote:
Code:
mysql> grant select, insert, update, delete on pkbeta.* to 'pkbetauser'@'localhost' identified by "xxxxxx"; Query OK, 0 rows affected (0.00 sec)

<div>

Now it works :)

</div>

awesome.. why it needs the password and the separation ? who knows!

Quote:It is also meant to be accessed from aurora@66.90.103.17, as I have a running installation using the older source code using these credentials. Thanks for the help :).

Quote:<blockquote data-ipsquote="" class="ipsQuote" data-ipsquote-contentcommentid="7531" data-ipsquote-username="hybrid" data-cite="hybrid" data-ipsquote-contentapp="forums" data-ipsquote-contenttype="forums" data-ipsquote-contentid="2094" data-ipsquote-contentclass="forums_Topic"><div>

Code:
mysql> grant select, insert, update, delete on pkbeta.* to 'pkbetauser'@'localhost' identified by "xxxxxx"; Query OK, 0 rows affected (0.00 sec)

<div>

Now it works :)

</div>

awesome.. why it needs the password and the separation ? who knows!

Quote:It is also meant to be accessed from aurora@66.90.103.17, as I have a running installation using the older source code using these credentials. Thanks for the help :).

Yup this is what I was talking about, although its connection to "localhost", its identifying as the IP. MySQL doesn't fix that.. you need to be specific. I remember FluKex had an issue where "localhost" was identifying as "localhost.localdomain" .. So adding an entry for that user@host will solve the issue.

</div></blockquote>

I think you misunderstand here :)The remote copy here is running all on a different server, the code as well (not on my home computer). The reason they use a different mysql setup is just how they do it, they have set all the permissions there and I know for sure that their permissions are right because other, older copies of the same project with the same logon details work fine. Neither do I have any access to change permissions on that server.

So I am now thinking it's something wrong in my code, and I'm doing some additional investigation now.

So confused o_O !

OK, so I revoked everything again and built a really small script just to test to see if it was a bug in the other code:

Code:
<?php

 $conn = mysql_connect(localhost, "pkbetauser", "xxxx");

 $connectto = "pkbeta";

 mysql_select_db("pkbeta", $conn) or die (mysql_error());

 $sql = "select `id`, `shortname` from `romwypk_featurepages`;";

 $query = mysql_query($sql, $conn) or die (mysql_error());

 print_r(mysql_fetch_array($query));

?>

After granting usage:

Code:
mysql> grant usage on pkbeta.* to 'pkbetauser'@'localhost' identified by 'xxxxx';

Query OK, 0 rows affected (0.00 sec)

it fails with Access denied for user 'pkbetauser'@'localhost' to database 'pkbeta'.

On the remote server:

Code:
<?php

 $conn = mysql_connect("mysql", "aurora", "xxxx");

 $connectto = "aurora";

 mysql_select_db("aurora", $conn) or die (mysql_error());

 $sql = "select `id`, `shortname` from `romwypk_featurepages`;";

 $query = mysql_query($sql, $conn) or die (mysql_error());

 print_r(mysql_fetch_array($query));

?>

Note that this works and displays as [/url][url=http://dhost.info/aurora/romwy/testdbdhost.php]http://dhost.info/aurora/romwy/testdbdhost.php.

So why does this test work, but my main code fail?

EDIT: According to the MySQL manual:

Quote:USAGE: Synonym for

:) I've worked out why it doesn't work on a remote server: for some reason I forgot to switch the name of the database in include.php from my local database to the one on the remote server. Now it works perfectly with no problems. :)Such a small mistake can cause so much pain.

It still is a mystery why grant usage doesn't seem to work on my server, apart from the link I posted in the above post saying that according to MySQL, usage means no privileges. The good thing is that it doesn't matter, because I can simply explicitly define which privilieges I used when I have the permissions to do so at home.

The main thing is, I have successfully got the code to work remotely, which means there should be no issues when it's finally moved to its permanent paid-for server at some point in the future. :)

Anyway, thanks znx for your help through this saga of MySQL permissions, I'm so happy that I finally worked out why it wasn't working.

znx, you are a legend anyway [img]<___base_url___>/uploads/emoticons/default_rolleyes.gif[/img]