Skip site navigation (1) Skip section navigation (2)

Re: Grant Update (Possible bug)?

From: Ilir Gashi <I(dot)Gashi(at)city(dot)ac(dot)uk>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Grant Update (Possible bug)?
Date: 2004-07-02 14:34:20
Message-ID: E1BgP7M-0005r0-00@ms2.city.ac.uk (view raw or flat)
Thread:
Lists: pgsql-bugs
On Jul 2 2004, Tom Lane wrote:

> Ilir Gashi <I(dot)Gashi(at)city(dot)ac(dot)uk> writes:
> > This is a another bug reported for the Firebird 1.0 server. I 
> > subsequently ran it in PostgreSQL 7.2, Oracle 8.0.5 anf MSSQL 7.
> 
> > Reproducible script:
> 
> > Connect as pgsql:
> 
> > CREATE TABLE TEST(ID INTEGER,NAME VARCHAR(50));
> 
> > INSERT INTO TEST (ID) VALUES (1);
> 
> > GRANT UPDATE ON TEST TO TESTUSER;
> 
> > Connect as TestUser;
> 
> > UPDATE TEST SET NAME='TEST' WHERE ID=1;
> 
> > ERROR:  test: Permission denied.
> 
> > UPDATE TEST SET NAME='TEST';
> 
> > Executes successfully. 
> 
> This is not a bug.  That UPDATE requires SELECT permission because it
> makes use of the ID field in the where clause.  If you grant someone
> UPDATE but not SELECT, presumably you want them to be able to insert
> data but not learn anything about what is in the table.  If we allowed
> such commands then something like
> 	UPDATE TEST SET NAME = NAME WHERE ID = 1
> could be used to determine whether the table contains a row with ID=1
> (by inspecting the reported row count).  So it would be a security flaw.
> 
> The SQL specification also requires this behavior.  In SQL92 the Access
> Rules for <column reference> say
> 
>          1) The applicable privileges shall include SELECT for T if CR is
>             contained in any of:
> 
>             a) a <search condition> immediately contained in a <delete
>               statement: searched> or an <update statement: searched>; or
> 
>             b) a <value expression> immediately contained in an <update
>               source>.
> 
> > So the user can update the whole table but not specific columns. Is 
> > this a bug or as specified (I read briefly the Reference Guide and I 
> > didn't see this highlited anywhere, but I may have missed it.). 
> > Firebird and MSSQL have the same behaviour as PostgreSQL (Firebird have 
> > acknowledged this as a bug, I haven't checked on MSSQL website yet.)
> 
> It's not a bug.  Please withdraw the complaint against Firebird.
> 
> > In Oracle 8.0.5 both updates execute sucessfully.
> 
> Oracle is a very poor reference for SQL-spec-compliant behavior :-(
> 
> 			regards, tom lane

Thanks for the quick reply. I also suspected that it was not a bug, but was 
confused by The Firebird bug-tracker at SourceForge who had marked it as an 
'Initial bug', and became even more confused from the behaviour of Oracle. 
Thanks for the clarification.

Best regards,

Ilir

 

____________________________________________

Ilir Gashi 
PhD Student 
Centre for Software Reliability 
City University 
Northampton Square, London EC1V 0HB
email: i(dot)gashi(at)city(dot)ac(dot)uk
website: http://www.csr.city.ac.uk/csr_city/staff/gashi/
____________________________________________


pgsql-bugs by date

Next:From: Stephan SzaboDate: 2004-07-02 14:37:13
Subject: Re: timestamp arithmetic (a possible bug?)
Previous:From: Tom LaneDate: 2004-07-02 14:28:46
Subject: Re: timestamp arithmetic (a possible bug?)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group