[Webdev] mysql grant permissions

[Dave Burke]

I came across this while working on some of the DB security for a database
this afternoon. I have a user that will be connecting to the database from
a php script and I only want them to have insert & update rights onto
certain tables. Basically these tables contain sensitive information and I
don't want anything to be able to retrive this info thru the website.

Anyways, I ran into a problem with the grant statement which I'll
illustrate below...............



// Create a table called weirdness with only one field
mysql> create table weirdness ( field1 int ) ;

// Insert some blank data
mysql> insert into weirdness values (42);

// Only give user dave insert & update rights on this table
mysql> grant insert,update on test.weirdness to dave;

// Reload the privileges from mysql.tables_priv
mysql> flush PRIVILEGES;

// Now Quit + log back on as user dave
mysql> quit
Bye                     
[dave at batmobile ~]$ ~mysql/bin/mysql -u dave -p
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> use test

Database changed
mysql> select * from weirdness;
+--------+
| field1 |
+--------+
|     42 |
+--------+
1 row in set (0.00 sec)


So the question now, is how user "dave" was able to execute that select
query when all he _should_ have is insert/update access? Do granting
insert or update access automatically grant select access as well ?

I just checked and mysql.tables_priv contains......
| %    | test    | dave  | weirdness           | root at localhost |
20000626191015 | Insert,Update


Does anyone know how to stop a user from having select access but still
have insert & update access ?

Cheers,
Dave