Re: permissions, exclusive row locks, and delete

There is no difference in the lock used for an update vs a delete.
A SELECT FOR UPDATE doesn't know ahead of time whether the user is going
to do a DELETE or an UPDATE.

IMO I would think that a SELECT FOR UPDATE should be allowed if the user
has either UPDATE "OR" DELETE permission. If you don't have either of
these permissions then I don't think you should be able to get locks, as
this could be used to prevent users who do have these permission from
being able to execute these operations.

See:
http://blog.tanelpoder.com/2007/11/19/oracle-security-part-2-your-read-only-accounts-arent-that-read-only/
for why this is a bad thing. Postgres seems to not have this security
hole although practically I believe it should be allowed for delete. On
the other hand, why would you allow deletes but not updates?

However, I don't know what the standard says nor have I tested what
other databases have implemented. It may be implementation defined.

- Dan

On 06/03/2013 04:35 PM, Christian Lawrence wrote:
> Hi!
>
> I have a question, which I'm certain may be a bug, but I thought I'd
> check with the community first before submitting it as one.
>
> I have the following SQL pseudo code which obtains an exclusive row lock
> before deleting the row:
>
> SELECT * FROM sometable WHERE (id = :id) FOR UPDATE;
> DELETE FROM sometable WHERE (id = :id);
>
> The user account has SELECT and DELETE permissions on the table.
> However, the SELECT FOR UPDATE will succeed only if the user account has
> UPDATE permissions.
>
> Is this a bug, or is there another way of obtaining an exclusive lock on
> rows for delete without escalating permissions?
>