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.
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?
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.
> 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 ----
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.
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?
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."
Please login to leave a reply, or register at first.