Postgres Pro
Downloads
Home   >   mailing lists

Thread: Grant Update (Possible bug)?

Grant Update (Possible bug)?

From
Ilir Gashi
Date:
Hi,

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.


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.)

In Oracle 8.0.5 both updates execute sucessfully.

Best regards,

Ilir

____________________________________________

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

Re: Grant Update (Possible bug)?

From
Peter Eisentraut
Date:
Am Freitag, 2. Juli 2004 13:20 schrieb Ilir Gashi:
> 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.

According to the letter of the SQL standard, this behavior is not conforming.
But PostgreSQL enforces that you need SELECT privilege for columns that you
read for the purpose of performing an UPDATE.  The reason is that otherwise
you could infer a great deal about the data in the table by just looking at
the update count.

Re: Grant Update (Possible bug)?

From
Tom Lane
Date:
Ilir Gashi <I.Gashi@city.ac.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

Re: Grant Update (Possible bug)?

From
Ilir Gashi
Date:
On Jul 2 2004, Tom Lane wrote:

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

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

Best regards,

Ilir

=20

____________________________________________

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

Re: Grant Update (Possible bug)?

From
Peter Eisentraut
Date:
Me wrote:
> Am Freitag, 2. Juli 2004 13:20 schrieb Ilir Gashi:
> > 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.
>
> According to the letter of the SQL standard, this behavior is not
> conforming.

OK, I was wrong, the behavior is correct, but the specification was hidden
somewhere in the subclauses.

Re: Grant Update (Possible bug)?

From
Tom Lane
Date:
Peter Eisentraut <peter_e@gmx.net> writes:
> According to the letter of the SQL standard, this behavior is not conforming.
> But PostgreSQL enforces that you need SELECT privilege for columns that you
> read for the purpose of performing an UPDATE.

Why do you think it's non-conformant?  AFAICS SQL92 section 6.4 <column
reference> Access Rule 1(a) requires exactly this behavior.

I notice that Annex E item 5 points this out as an incompatibility with
SQL89 ... maybe Oracle is still on SQL89 ...

            regards, tom lane

Re: Grant Update (Possible bug)?

From
Stephen Frost
Date:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > According to the letter of the SQL standard, this behavior is not confo=
rming.
> > But PostgreSQL enforces that you need SELECT privilege for columns that=
 you=20
> > read for the purpose of performing an UPDATE.
>=20
> Why do you think it's non-conformant?  AFAICS SQL92 section 6.4 <column
> reference> Access Rule 1(a) requires exactly this behavior.
>=20
> I notice that Annex E item 5 points this out as an incompatibility with
> SQL89 ... maybe Oracle is still on SQL89 ...

It would seem reasonable, then, that a grant of update privilege would
imply a grant of select privilege.  Currently this isn't the case.

    Stephen