select command denied to user

[bobb2468]

I am trying trying to update one of my tables using the following code.

@ $db=mysql_connect('home' ,'user1', 'password');

mysql_select_db('users');
$query = "update table1 set col1 = '$var1', col2='$var2' where col3='$var3' "; (there are 17cols and 17 vars)
$result = mysql_query($query) or die(mysql_error());

running the script gives me the following error

select command denied to user: '[e-mail]' for column 'col3' in table 'table1'. (home being my localhost name)

I have granted user1 the update privilege and have echoed the variables to make sure they are all being passed. I have also tried the same using delete and i get the same error message.

Am i missing something? Do i need to do anything different when using update/delete privileges?

thanks
bobb2468

[sid]

did you check if the data was actually modified? this is just a wild guess, but could it be that your code works just fine, but at some point you try to select data with that user which you didnt grant? do you use any db tools like phpmyadmin or so?

[bobb2468]

hi sid,

i dont use any db tools (i grant privilages in dos). Is this something i should look into?

Perhaps it might help if i try and explain what i am trying to achive.

I am trying to devise a way where users who wish to register on my website firstly input there email address and then i send them a randomly generate password and a link on how they can complete the registration. (i then input this information into table1 - this bit works fine). If they follow the link they reach a form with several inputs which they need to complete to register. On this form they are required to input the email address they have already provided and the code which i emailed to them. I also ask them to choose thier own more memorable password. Once they send this form my script should firstly check the required fields have been completed and then check that the email and code match in the databse. if they do match it should then update the entry in the table to include the additional information. Only problem being that i get the error code as i outlined in my previous post.

The first part of the process (where they enter an email address) and i send them a code works fine, and the information is added to my database. Originally i only added two variables into a database with 17 columns, which left 15cols with NULL in them. Thinking that this might be the problem i altered the script to enter info into all cols, but this didnt make any difference.

Apologises if this post is a little long.

cheers
bobb2468

[bobb2468]

I have figured out what the problem is.

In an attempt to try and maximise security i have been giving different users (in my mysql tables) different names and passwords and granting them different privileges.

example

grant INSERT on table1 to USER1 identified by password1

grant SELECT on table1 to USER2 identified by password2

etc

well i had not given the user with the update privilege the select privilege and hence the error message.

thought id post this just in case anyone else has the same problem.

cheers
bobb2468