Cannot manage users on a remote database server

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Install Apache and MySQL on separate boxes, and SQL Buddy on the Apache box.
2. Create a user in MySQL with full privileges with a host or IP of the Apache 
box.
3. Log in with SQL Buddy and navigate to the Users screen.

What is the expected output? What do you see instead?
Expected output is the full user management console. Instead, I see a list of 
existing users but 
the bottom panel says "You do not have enough permissions to create new users."

What version of the product are you using? On what operating system?
The latest version from the web site (cannot find any screens in SQL Buddy that 
mention the 
current version number - this probably ought to be added to the home screen 
somewhere). 
Running on CentOS 5.3, PHP 5.3.0, MySQL 5.1.39.

Please provide any additional information below.
It appears that SQL Buddy is querying the mysql.users table by logged-in user 
name and 
database server name to check permissions, rather than using the user name and 
local server 
name for which a grant was created. So, for example, my web server ads-web-01 
runs SQL 
Buddy to connect to ads-db-01. SQL Buddy is looking in the users table for a 
record for 
sqlbuddy@ads-db-01, when it should be looking for sqlbuddy@ads-web-01.

I've been able to work around this bug by modifying the query at users.php:440 
to read thusly:
        $checkSql = $conn->query("SELECT `Grant_priv` FROM `user` WHERE `Host`=(SELECT 
SUBSTRING_INDEX(USER(), '@', -1)) AND `User`=(SELECT SUBSTRING_INDEX(USER(), 
'@', 1)) LIMIT 
1");

This takes advantage of the built-in MySQL function USER(), which returns the 
current user name 
and originating hostname in a single-column result (i.e., 
'sqlbuddy@ads-web-01.domain.com'). 

After applying this fix, user management still appears to work fine when 
connecting to a local 
MySQL instance, as USER() returns 'sqlbuddy@localhost'. So if you were to make 
the same fix to 
the repo, then the majority of your users ought not notice any difference as I 
imagine most of 
them run MySQL and SQL Buddy on the same machine.

Original issue reported on code.google.com by brian.cline on 19 Oct 2009 at 6:26

[GoogleCodeExporter]

Thanks for reporting this. I will make the change in the repo.

Original comment by calvinlo...@gmail.com on 22 Oct 2009 at 6:35

• Changed state: Accepted

[GoogleCodeExporter]

I have an issue with the latest sqlbuddy where my user is 'user'@'%' with all 
privileges.  sqlbuddy is logging my on as 'user'@'localhost' which is fine, but 
is 
not giving create user privilege.

Is this the same issue or should I report it separately?

Using mysql remotely allows my user to create other users so privileges are 
correct.  

This is with SQLBuddy-1.3.2 and MySQL-5.0.90 on WinXP.

Original comment by russell....@gmail.com on 12 Apr 2010 at 8:02

[GoogleCodeExporter]

Just tried your fix (at 251 in my users.php) and it doesn't work for the '%' 
case.  Any 
suggestions I can try to fix this?

Original comment by russell....@gmail.com on 12 Apr 2010 at 8:10

[GoogleCodeExporter]

I've changed the query to

    $checkSql = $conn->query("SELECT `Grant_priv` FROM `user` WHERE (`Host`=(SELECT 
SUBSTRING_INDEX(USER(), '@', -1)) OR `Host`='%') AND `User`=(SELECT 
SUBSTRING_INDEX(USER(), '@', 1)) LIMIT 1");

And that seems to work for me now.

Original comment by russell....@gmail.com on 12 Apr 2010 at 8:31