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

Re: Grant Update (Possible bug)?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Ilir Gashi <I(dot)Gashi(at)city(dot)ac(dot)uk>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Grant Update (Possible bug)?
Date: 2004-07-02 14:04:57
Message-ID: 13985.1088777097@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

pgsql-bugs by date

Next:From: Stephan SzaboDate: 2004-07-02 14:12:12
Subject: Re: timestamp arithmetic (a possible bug?)
Previous:From: Peter EisentrautDate: 2004-07-02 14:01:26
Subject: Re: Grant Update (Possible bug)?

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