MySQL Bugs: #5165: SELECT ... FOR UPDATE uses 'select' privelege instead 'update'

Bug #5165	SELECT ... FOR UPDATE uses 'select' privelege instead 'update'
Submitted:	23 Aug 2004 11:19	Modified:	23 Aug 2004 16:38
Reporter:	Marat Latypov	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4	OS:	Any (All)
Assigned to:		CPU Architecture:	Any

Description:
SELECT ... FOR UPDATE uses 'select' privelege instead 'update'

So user without 'update' privigege can set exclusive lock for recordset.
I think such users must have only simple selects or selects with shared locks for FK integrity.

If user can't UPDATE 
he must not be able SELECT FOR UPDATE.

How to repeat:
1. Create user

 GRANT select
    ON my_db.*    TO user_name IDENTIFIED BY  'password'

2. Log in  as 'user_name'

SELECT * FROM my_table WHERE something FOR UPDATE;
Ok

UPDATE my_table SET xxx='xxx' WHERE something
Access denied for user 'user_name@***'

Suggested fix:
I think if user can't UPDATE 
he must not be able SELECT FOR UPDATE.

This in my opinion is not a bug, it is a property of the implementation.
SELECT...FOR UPDATE can be used for different reasons. The FOR UPDATE 
clause signals that you intend to insert, update, or delete the rows 
returned by the query, but does not require that you perform one of 
these operations. Note that it is always only a read-only operation. 
Database state will not change. It is true that this clause will set 
exclusive locks for all maching rows and that might decrease 
concurrency and cause deadlocks.

OK!

So I change my opinion:

1) User must not be able SELECT FOR UPDATE some existing records
if he haven't 'UPDATE', 'DELETE' privigege 

2) User must not be able SELECT FOR UPDATE some non-existing records
if he haven't 'INSERT' privigege 

3) User must not be able SELECT FOR UPDATE at all
if he haven't 'UPDATE', 'DELETE' or 'INSERT' privigege 

Seems it's more correct.

Hi!

I am changing the status of this bug report to 'Won't fix'. It can be debated which rights one must have to issue SELECT ... FOR UPDATE. We will look at changing the current behavior if several users see the current behavior as a problem.

Regards,

Heikki

Just a small comment on this. Consider following application:

SELECT * FROM A where a = :value FOR UPDATE;
if EXISTS
    INSERT INTO B VALUES(....); // B contains foreign key to A
    COMMIT;
else
    ROLLBACK
end if

Now if user has SELECT privilege for A and INSERT+SELECT for B. In my opinion we
should allow the user to do SELECT...FOR UPDATE from table A. it would be very slow
to test that has the user INSERT/DELETE/UPDATE privileges to any table if
he has done SELECT...FOR UPDATE to table where he has not INSERT/DELETE/UPDATE
privileges. Therefore, I don't see any good reason to change the way of using
privileges.

1)
IMHO In this example using SHARED LOCK is more correct.
I.e.
SELECT * FROM A where a = :value LOCK IN SHARE MODE;
instead
SELECT * FROM A where a = :value FOR UPDATE;

2)
Yes.
Privelege checking for SELECT FOR UPDATE would be a little bit slower because adding new checks