MySQL and BlueDragon permission problem

[Tormod]

I wonder if I'm doing something wrong in CP+.

I am trying to let a test user (blabla) access a test database (blabla_net).

I have given this user all privileges in CP+, under the MySQL Databases page. When I created my user (via the Create website link) an entry was created under "Database Permissions". I have added the user manually to the User permissions list (it makes no difference whether the user is listed there or not AFAIK).

I can login to CP+:20000 as the blabla user and administer the mysql database there.

But no matter what I do in the BlueDragon admin, I am unable to verify a datasource pointing to blabla using that user (BTW I'm using the JDBC driver).

I keep getting this:
Access denied for user 'blabla'@'redbud.viviotech.net' (using password: YES)

I even went to console and did a GRANT ALL for this user but to no avail, BlueDragon still refused to verify the datasource.

The only user I am able to verify it for, is root WITHOUT a password. If I use my default root password the verification fails.

You can see for yourself that the output actually works for root on my extremely simple test page:
http://redbud.viviotech.net/index.cfm

Is there something special about CP+ and MySQL database permissions? It seems to me I may have made a mistake when I made that user or something.

I am trying to follow the CP+ user docs and it works fine within CP+, but the problem is getting access from BlueDragon to users that are defined in CP+.

With regards,
Tormod

[jordan]

This is more of a BlueDragon problem in that it's specific to how MySQL sees a BlueDragon connection. In your error message, you can see that MySQL is seeing the connection as coming from "redbud.viviotech.net" instead of localhost. I believe this is true evenif you use "localhost" when you create your DSN.

The best way to fix this is to update your user permissions in MySQL. To do that in CP+ 2.5, take the following steps:

1) Log in to the CP+ admin interface on port 10000
2) Click the "MySQL Database Server" link from the left menu
3) Click the "User Permissions" icon in the bottom left
4) Select the user that you're attempting to connect with
5) In the text box next to "Hosts" the default is to only allow connections from "localhost". Change this to the percent sign: "%" (without the quotes).

This should allow you to connect to your MySQL server from any host. If you wanted to be "ultra secure", you could create a new user entry for each host that the user might connect from. However, using the percent sign is a catch-all for any host.

You may need to Restart MySQL (or Flush the permissions) to refresh the permissions.

Let us know if this helps!

[Tormod]

No luck I'm afraid. I replaced localhost with a % but it made no difference. The user is now listed with "Any" under hosts.

Flushed privileges and restarted MySQL, same issue.

Also, there seems to be a bug in CP+ in that when you click on a username in User privileges, the form that opens up contains the settings for the admin user, not for the selected user.

[Tormod]

(As for the bug, it may be my browser - when I view source the "old" settings are those of the user I selected).

I have tried to set host to % in both user privileges and database privileges but it makes no difference.

I wonder...do I need anything under Host permissions? It is currently empty.

[Tormod]

A ha...progress!

I set the user host to be my server domain name... and did the same in the BlueDragon DNS setup. It now verified my user.

I'll experiment with different variations until I find out exactly what is needed for this to work with new users.

Thanks, Jordan.

Regards,
Tormod

[Tormod]

Ah...but this removed my MySQL access in the CP+ domain admin.

Well, getting closer.

[Tormod]

Setting "redbud.viviotech.net" in the host field makes Bluedragon work but not the CP+ domain admin panel.

Setting % or ANY in the host field makes the CP+ domain admin panel work but not Bluedragon.

[jordan]

To make your life simpler, try removing all permissions for that particular user (db permissions, host permissions, etc) except for what's in your "user permissions" table. That should make the user's access to the server solely dependant on what the user logs in as.

Hope this helps!

[jordan]

Once you have the user account wide open and working, then you can widdle away at the permissions (making them more secure) until you have it locked down tight but still functional for your own purposes.

[Tormod]

Okay, removed absolutely all other entries for my blabla user except in the user permissions. Now it fails to verify in both Bluedragon and CP+ (I can log into the CP+:20000 but MySQL won't accept the password).

[jordan]

Okay, removed absolutely all other entries for my blabla user except in the user permissions. Now it fails to verify in both Bluedragon and CP+ (I can log into the CP+:20000 but MySQL won't accept the password).

Then something is wrong with your user permissions table. Make sure that CP+ uses the "%" symbol instead of just saying "all". I believe there's a bug in CP+ in this regard - CP+ doesn't actually use a % in the user table but leaves it blank - essentially giving that user NO permissions.

Let me know if this works for you!

[Tormod]

Adding an entry in the database permissions with Any and All privileges lets Bluedragon verify the datasource, but I still cannot manage MySQL via the domain control panel.

Maybe I need to erase the entire user and start over...

[jordan]

Adding an entry in the database permissions with Any and All privileges lets Bluedragon verify the datasource, but I still cannot manage MySQL via the domain control panel.

Maybe I need to erase the entire user and start over...

Strange... ... that makes absolutely no sense whatsoever. Did you try the percent symbol instead of the "all" radio button?

How about screaming explatives and making angry gestures toward your computer screen? Have you tried that?

[Tormod]

Okay, removed absolutely all other entries for my blabla user except in the user permissions. Now it fails to verify in both Bluedragon and CP+ (I can log into the CP+:20000 but MySQL won't accept the password).

Then something is wrong with your user permissions table. Make sure that CP+ uses the "%" symbol instead of just saying "all". I believe there's a bug in CP+ in this regard - CP+ doesn't actually use a % in the user table but leaves it blank - essentially giving that user NO permissions.

Let me know if this works for you!

I am entering a % symbol. If I go back to edit it, the form has "Any" selected.

I am still able to get BD to verify the DSN, but my user is still locked out of MySQL admin...

[jordan]

Yay, progress. Okay, I'm wondering if the port 20000 admin is simply looking ad the db permissions table. Try adding the user back to the db permissions table and seeing if that doesn't help your port 20000 admin problem.

At least we're getting somewhere now...