Solved

Insert command denied

Posted on 2014-01-17
3
341 Views
Last Modified: 2014-01-22
Hello

I am getting this error from a webpage when trying to insert into a table in the 'databasename'

INSERT command denied to user 'theuser'@'localhost' for table 'thetable'

However when i do a show grants on the database i get this


GRANT USAGE ON *.* TO 'theuser'@'%' IDENTIFIED BY PASSWORD 'xxxx'
GRANT ALL PRIVILEGES ON `databasename`.* TO 'theuser'@'%'

I'm not sure what the first line means but surely the second line means the user has all privileges on that database and should be able to insert?

thanks
0
Comment
Question by:andieje
3 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39788685
if those permissions where given in some session, but no "FLUSH PRIVILEGES" was issued, and/or the user did not reconnect yet, it might be "missing" still in the session.

or you are just simply looking at the wrong mysql instance,. ...
0
 
LVL 34

Assisted Solution

by:Dan Craciun
Dan Craciun earned 250 total points
ID: 39788892
Create a user "theuser" with the host "localhost".

For some reason, the rights for theuser@% don't apply when connecting from localhost

https://dev.mysql.com/doc/refman/5.5/en/adding-users.html

HTH,
Dan
0
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 250 total points
ID: 39794279
The rights for user@% DO apply when connecting from localhost. The problem is that MySQL ships with an anonymous user, ''@localhost, which has access only to the `test` database. The privilege system matches the most exact host name first - ''@localhost matches all localhost users better than user@%.

So, you can create user@localhost, but then you have two separate users to maintain, and this problem will appear again when you want different_user@%.

The best thing to do is to DROP USER ''@localhost;

This is in the manual at http://dev.mysql.com/doc/refman/5.6/en/access-denied.html - find the text starting, "If you cannot figure out why"
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MySQL Select latest stock count records 11 70
[MYSQL]: Delete is very slow 4 72
FrontEnd tools to create web database application 7 62
MySQL Query Using Up Memory 6 32
Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question