Re: permissions, exclusive row locks, and delete

I agree that a DoS having only SELECT permissions to perform an 
exclusive row lock is a very bad thing.  Thankfully we have PostgreSQL 
as a decent database alternative ;)

 From reading 
http://www.postgresql.org/docs/9.2/static/sql-select.html#SQL-FOR-UPDATE-SHARE 
and 
http://www.postgresql.org/docs/9.2/static/sql-grant.html#SQL-GRANT-DESCRIPTION-OBJECTS 
I could infer that a "low-level UPDATE permission" is required to "flag" 
each row as being locked (i.e.: an in-place modification of row metadata).

But this sounds like an internal/implementation quirk given that 
semantically no SQL UPDATE is expected to be performed by this user 
account at all.

With a SQL DELETE, an in-place modification of row metadata is 
performed, to "flag" the row as deleted.  This doesn't require UPDATE 
permissions though.

I'm not sure about the SQL standard or other database implementations 
either.  Does anyone else have other views on this?



On 4/06/2013 10:24 AM, Daniel Wood wrote:
> 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?
>>
>
>
>