Changing permissions on tables

This is probably one of those Duh! novice questions, but my knowledge of MySQL is very limited and without HeidiSQL to inspect my databases I'd be lost.

THE PROBLEM:

This concerns a WordPress database and a very popular plug-in called ReviewAZON that adds tables of its own to the WordPress database. Although thousands of other users don't seem to have a problem with it, it's not working for me, apparently because of a MySQL permissions problem.

The developer of the plug-in says that he can see all the necessary ReviewAZON tables have been added to the WordPress database, but, in his words...

"What happens is that all of the inserts, updates and deletes are not being processed after the tables are created. The tables are there, but all of the initial data is not added and any attempt to update the tables results in this error:

UPDATE command denied to user 'xxxxx_wp'@'localhost' for table 'wp_reviewazon_settings'

He has asked me to check the permissions and make sure that the db user has rights to the ReviewAZON tables?

Unfortunately, I don't know how to do that. I can't see anything relating to "permissions" when I look at the database in HeidiSQL. Maybe I just don't know where to look?

The domain in question is hosted by a provider so I only have limited server access, but if access by a root user is necessary I can contact them -- if I know what to ask for.

Can anyone give me guidance here please?

Permissions are managed in HeidiSQL in Tools > User manager. You should definitely check your "xxxxx_wp" user and, if possible, give him the UPDATE, INSERT and DELETE permissions. Should be easy, if not ask again how to use that dialog:

Sounds good Anse -- for a moment there I thought I was on to a solution.

However...

HeidiSQL then reports: "You have no access to the priviliges database."

Is this something I need to have the host technicians fix for me?

Yes, in that case you need some administrator guy to do that. Just forward him your cite

UPDATE command denied to user 'xxxxx_wp'@'localhost' for table 'wp_reviewazon_settings

Hosting services normally provide a web control panel to set MySQL permissions.

With regard to the HeidiSQL User Manager error: "You have no access to the privileges database", a support rep at my hosting company had this to say:

----
You cannot use that section from HeidiSQL. Users should only be managed via your control panel. Only the server admin can view permissions with an administrative command via inside the server itself. Your user simply does not have that permission. You just cannot see the permissions, GRANT privileges cannot be given to users on shared servers.
----

I'm definitely no expert on server management, so I'm wondering what the more experienced people here think of those restrictions.

Are they reasonable?

A shared server it may be, but the individual MySQL databases aren't shared, so surely there shouldn't be a problem with my granting any permissions I wish to my users on my databases?

When trying to troubleshoot what appears to be a permissions problem, how else does one check the permissions that a given user has for specific tables? I tried looking in phpMyAdmin (ugh!) but couldn't see any way to view permissions with it either.

The control panel used by this host is DotNetPanel.

Sounds ok to me if they don't allow the "mysql" db to be accessed by everyone. More important for you: What did he say with regard to "UPDATE command denied" ?

> Sounds ok to me if they don't allow the "mysql" db
> to be accessed by everyone.

Fair enough then. But the question remains: How do you view permissions?

> More important for you: What did he say with regard
> to "UPDATE command denied" ?

Although I haven't tested it yet, the developer of the plug-in claims to have fixed the problem. Here is his explanation:

---- BEGIN QUOTE ----
Once I found out you were running InnoDB instead of MyISAM I realized what the issue was.

For anyone else who maybe hosting on windows and running Wordpress on MySQL, you may find this fix helpful. This particular server is running InnoDB as the default SQL engine instead of MyISAM.

When the plug-in creates the tables necessary to hold the product settings and other data, it uses the default engine which is MyISAM. The fix was to alter the create script for the tables so that they were created with the default engine for this server which is InnoDB.
---- END QUOTE ----

That has nothing to do with missing permissions. I'm sure you still cannot update your tables, and you will still get "UPDATE command denied". They need to allow your user account to update either everything in your database or at least these specific tables.

Hmmm, that's interesting.

If that's the case he may be getting misled into thinking that the problem is fixed because he managed to get it working on my site (I gave him access to the host account Control Panel and WordPress admin to help sort it out).

If so, then the reason it worked on my site may be because in an earlier exchange with the host tech support about getting HeidiSQL User Manager working, I told them I wanted to ensure that UPDATE, INSERT and DELETE permissions were applied to all the plug-in-related tables in the database (as you had suggested). Those tables are easily identified by their names.

Although (as explained above) they won't give me the privileges necessary to use HeidiSQL User Manager, they did say they had made the permission changes I requested.

In other words, after the permissions were modified by the host, maybe it would have worked anyway.

Likely?

Anse, do you think my assumptions in the previous message correct?

I'd like to be sure I'm on the right track before I take that back to the plug-in developer.

TIA

I'm starting to get confused about what you need. Initially I thought you just need UPDATE privs for your table wp_reviewazon_settings. Now you say you'd like to use the user manager - which is very ok if you don't get it from your hoster.

OK, in logical order...

1. Initially it appeared that I didn't have the necessary permissions to update the tables associated with the plug-in.

2. I was looking for a way to view existing permissions and possibly modify them, and I turned to HeidiSQL for that ability, but I couldn't see how to do it.

3. You pointed me towards HeidiSQL's User Manager, which I hadn't attempted to use previously and of which I wasn't aware .

4. In trying to use User Manager I encountered the error message: "You have no access to the privileges database."

5. Hosting support subsequently advised that I could not use HeidiSQL User Manager because the host did not permit that sort of access to MySQL. However the host tech said that he modified permissions on all tables to allow UPDATE, INSERT and DELETE.

6. The plug-in developer then made his comment about creating his tables with InnoDB instead of MyISAM.

7. You said this would not be relevant to any permissions issues regarding ability to update tables.

8. However after the developer made his InnoDB/MyISAM changes, the plug-in did then work correctly as far as allowing the tables to be updated.

9. I then proposed the assumption that, after the permissions were modified by the host tech, it would have worked anyway regardless of any InnoDB/MyISAM considerations.

So would you consider that to be a valid assumption?


I realize that a plug-in developer is not necessarily a MySQL expert, whereas you are. It's a promising product, so I'd like to be able to put him on the right track towards a correct and permanent fix by referring him to this thread.

5. ... However the host tech said that he modified permissions on all tables to allow UPDATE, INSERT and DELETE.

You didn't say so earlier - sounds like he solved your problem then.

I realize that a plug-in developer is not necessarily a MySQL expert, whereas you are.

No, sounds like he also is one, or at least he has fixed your problem, found the cause of it in some engine stuff.

Is your initial Wordpress problem fixed?

> You didn't say so earlier

Yes I did -- message above Posted: Sun, 16 May 10 01:27:
ME: "Although (as explained above) they won't give me the privileges necessary to use HeidiSQL User Manager, they did say they had made the permission changes I requested."

And when I mentioned the "engine stuff" changes made by the plug-in developer, you said:
"That has nothing to do with missing permissions. I'm sure you still cannot update your tables, and you will still get "UPDATE command denied".

So I'm trying to figure out what made the difference?

The host Tech changing the permissions on all tables?

-OR-

The plug-in developer changing the database engine?

I can't go back and ask the host technicians to put the table permissions back the way they were, just so I can find out if it was the database engine change or not.

I'm asking your opinion based on your statement: "That has nothing to do with missing permissions."