You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Manjula G Kutty <ma...@gmail.com> on 2006/08/31 01:15:19 UTC
Question on Grant/Revoke
Hi,
I was testing the new grant/revoke feature and now have this question.
Here is what I did
ij> connect 'jdbc:derby:testdb;create=true' user 'DBADMIN' password 'admin';
ij> create table t1 (id int, name char(100));
0 rows inserted/updated/deleted
ij> insert into t1 values (1,'sss'),(2, 'bbb'),(3, 'kkk');
3 rows inserted/updated/deleted
ij> grant update on t1 to DBUSER;
0 rows inserted/updated/deleted
ij> connect 'jdbc:derby:testdb' user 'DBUSER' password 'user';
ij(CONNECTION1)> update DBADMIN.t1 set id =1 where id=3;
ERROR 28508: User 'DBUSER' does not have select permission on column
'ID' of tab
le 'DBADMIN'.'T1'.
ij(CONNECTION1)>
So now the question is why the DBUSER need a select permission here??
Thanks
Manjula
Re: Question on Grant/Revoke
Posted by Yip Ng <yi...@gmail.com>.
Another example, it looks like the following error message is misleading
since I am only reading column j and not updating j.
...
ij> create table t1 (i int, j int);
0 rows inserted/updated/deleted
ij> insert into t1 values (1,10);
1 row inserted/updated/deleted
ij> grant update (i) on t1 to user2;
0 rows inserted/updated/deleted
ij> connect 'jdbc:derby:wombat;create=true' user 'user2' as user2;
...
ij(USER2)> update user1.t1 set i=j;
ERROR 28508: User 'USER2' does not have update permission on column 'J' of
table
'USER1'.'T1'.
Yip
On 8/31/06, Daniel John Debrunner <dj...@apache.org> wrote:
>
> Rajesh Kartha wrote:
>
> >>Mamta Satoor wrote:
> >>Manjula, I haven't looked at the SQL spec but it looks like that the
> > update statement is doing a select operation on the
> >>column id with "where id=3" and that is what is causing Derby to send
> > an error.
> >
> > More questions:
> > If a separate Select privilege is indeed required then I think the
> > 'grant update on t1 to DBUSER' statement should not be succesful.
>
> No, you need the SELECT privilege because you are reading the id column,
> not because you are updating the column.
>
> I assume if this update is executed, then no select privilege would be
> needed:
>
> UPDATE T SET ID = ?
>
>
> Or with this UPDATE
>
> UPDATE T SET ID = ? WHERE NAME = ?
>
> that one needs UPDATE on T(ID) and SELECT on T(NAME).
>
> Dan.
>
>
>
>
>
Re: Question on Grant/Revoke
Posted by Rajesh Kartha <ka...@gmail.com>.
Thanks a lot Mamta !! really appreciate it.
Phew !! that was a lot of traversing within the spec to get specific
information.
-Rajesh
Mamta Satoor wrote:
> I spent some time going through the SQL 2003 specification and
> verified that the original update statement specified by Manjula does
> require a SELECT privilege and that Derby is behaving SQL complaint.
>
> It's little convoluted to find this information in the SQL spec, but
> here is how one can find that information.
>
> Update statement with search condition is covered in 14.11 <update
> statement: searched>. In this section, going further into <set clause
> list> shows that columns referenced in the <set clause list> fall into
> <object column> category. For <object column> section 14.11, Access
> Rules 1)b)1) says that we need UPDATE privilege for <object column>.
> So, this covers the columns used in the set clause of the update
> statement.
>
> As for the columns in [WHERE <search condition>] in section 14.11
> <update statement: searched>, click on <search condition>
> 1)This will take you to section 8.19 <search condition> and click on
> <boolean value expression>
> 2)This will take you to section 6.34 <boolean value expression> and
> click on <boolean term>, <boolean factor>, <boolean test>, <boolean
> primary>, <prediate>
> 3)This will take you to 8.1 <predicate> and click on <comparison
> predicate>
> 4)This will take you to 8.2 <comparison predicate> and click on <row
> value predicant>
> 5)This will take you to 7.2 <row value expression> and click on <<row
> value special case>, <nonparenthesized value expression primary>
> 6)This will take you to 6.3 <value expression primary> which referes
> to the columns in the where clause as <column reference>. Click on
> <column reference>
> 7)Here, Section 6.7 <column reference> Access Rules 2)b)ii) says that
> we need SELECT privilege on the column reference.
>
> Thanks for bearing with me through the SQL specification maze but
> based on this, Derby behavior is SQL compliant for the update sql
> statement provided by Manjula.
>
> Mamta
>
> On 8/31/06, *Rajesh Kartha* <kartha02@gmail.com
> <ma...@gmail.com>> wrote:
>
> Daniel John Debrunner wrote:
>
> >Rajesh Kartha wrote:
> >
> >
> >
> >>>Mamta Satoor wrote:
> >>>Manjula, I haven't looked at the SQL spec but it looks like
> that the
> >>>
> >>>
> >>update statement is doing a select operation on the
> >>
> >>
> >>>column id with "where id=3" and that is what is causing Derby
> to send
> >>>
> >>>
> >>an error.
> >>
> >>More questions:
> >>If a separate Select privilege is indeed required then I think the
> >>'grant update on t1 to DBUSER' statement should not be succesful.
> >>
> >>
> >
> >No, you need the SELECT privilege because you are reading the id
> column,
> >not because you are updating the column.
> >
> >I assume if this update is executed, then no select privilege
> would be
> >needed:
> >
> >UPDATE T SET ID = ?
> >
> >
> >Or with this UPDATE
> >
> >UPDATE T SET ID = ? WHERE NAME = ?
> >
> >that one needs UPDATE on T(ID) and SELECT on T(NAME).
> >
> >Dan.
> >
> >
> >
> >
> >
> >
> >
> Thanks Dan, I do see for the
>
> UPDATE T SET ID = ? statement, the update works fine (even without
> the select permission,
> a bit strange though given that the user still cannot select and
> view the updated data).
>
> In the UPDATE T SET ID = ? WHERE NAME = ? case it fails because an
> explicit select on T was not given.
>
> I expected an update permission would have an implicit select also
> on that table. In which case
> the behaviour would have been consistent and both scenarios will
> work fine plus the user would
> have been able to select and view the new data.
>
> But if the current working is as per the SQL spec, I am
> fine. Anyways, would be better to get
> this confirmed.
>
> Rajesh
>
>
Re: Question on Grant/Revoke
Posted by Mamta Satoor <ms...@gmail.com>.
I spent some time going through the SQL 2003 specification and verified that
the original update statement specified by Manjula does require a SELECT
privilege and that Derby is behaving SQL complaint.
It's little convoluted to find this information in the SQL spec, but here is
how one can find that information.
Update statement with search condition is covered in 14.11 <update
statement: searched>. In this section, going further into <set clause list>
shows that columns referenced in the <set clause list> fall into <object
column> category. For <object column> section 14.11, Access Rules 1)b)1)
says that we need UPDATE privilege for <object column>. So, this covers the
columns used in the set clause of the update statement.
As for the columns in [WHERE <search condition>] in section 14.11 <update
statement: searched>, click on <search condition>
1)This will take you to section 8.19 <search condition> and click on
<boolean value expression>
2)This will take you to section 6.34 <boolean value expression> and click on
<boolean term>, <boolean factor>, <boolean test>, <boolean primary>,
<prediate>
3)This will take you to 8.1 <predicate> and click on <comparison predicate>
4)This will take you to 8.2 <comparison predicate> and click on <row value
predicant>
5)This will take you to 7.2 <row value expression> and click on <<row value
special case>, <nonparenthesized value expression primary>
6)This will take you to 6.3 <value expression primary> which referes to the
columns in the where clause as <column reference>. Click on <column
reference>
7)Here, Section 6.7 <column reference> Access Rules 2)b)ii) says that we
need SELECT privilege on the column reference.
Thanks for bearing with me through the SQL specification maze but based on
this, Derby behavior is SQL compliant for the update sql statement provided
by Manjula.
Mamta
On 8/31/06, Rajesh Kartha <ka...@gmail.com> wrote:
>
> Daniel John Debrunner wrote:
>
> >Rajesh Kartha wrote:
> >
> >
> >
> >>>Mamta Satoor wrote:
> >>>Manjula, I haven't looked at the SQL spec but it looks like that the
> >>>
> >>>
> >>update statement is doing a select operation on the
> >>
> >>
> >>>column id with "where id=3" and that is what is causing Derby to send
> >>>
> >>>
> >>an error.
> >>
> >>More questions:
> >>If a separate Select privilege is indeed required then I think the
> >>'grant update on t1 to DBUSER' statement should not be succesful.
> >>
> >>
> >
> >No, you need the SELECT privilege because you are reading the id column,
> >not because you are updating the column.
> >
> >I assume if this update is executed, then no select privilege would be
> >needed:
> >
> >UPDATE T SET ID = ?
> >
> >
> >Or with this UPDATE
> >
> >UPDATE T SET ID = ? WHERE NAME = ?
> >
> >that one needs UPDATE on T(ID) and SELECT on T(NAME).
> >
> >Dan.
> >
> >
> >
> >
> >
> >
> >
> Thanks Dan, I do see for the
>
> UPDATE T SET ID = ? statement, the update works fine (even without the
> select permission,
> a bit strange though given that the user still cannot select and view the
> updated data).
>
> In the UPDATE T SET ID = ? WHERE NAME = ? case it fails because an
> explicit select on T was not given.
>
> I expected an update permission would have an implicit select also on that
> table. In which case
> the behaviour would have been consistent and both scenarios will work fine
> plus the user would
> have been able to select and view the new data.
>
> But if the current working is as per the SQL spec, I am fine. Anyways,
> would be better to get
> this confirmed.
>
> Rajesh
>
>
Re: Question on Grant/Revoke
Posted by Rajesh Kartha <ka...@gmail.com>.
Daniel John Debrunner wrote:
>Rajesh Kartha wrote:
>
>
>
>>>Mamta Satoor wrote:
>>>Manjula, I haven't looked at the SQL spec but it looks like that the
>>>
>>>
>>update statement is doing a select operation on the
>>
>>
>>>column id with "where id=3" and that is what is causing Derby to send
>>>
>>>
>>an error.
>>
>>More questions:
>>If a separate Select privilege is indeed required then I think the
>>'grant update on t1 to DBUSER' statement should not be succesful.
>>
>>
>
>No, you need the SELECT privilege because you are reading the id column,
>not because you are updating the column.
>
>I assume if this update is executed, then no select privilege would be
>needed:
>
>UPDATE T SET ID = ?
>
>
>Or with this UPDATE
>
>UPDATE T SET ID = ? WHERE NAME = ?
>
>that one needs UPDATE on T(ID) and SELECT on T(NAME).
>
>Dan.
>
>
>
>
>
>
>
Thanks Dan, I do see for the
UPDATE T SET ID = ? statement, the update works fine (even without the select permission,
a bit strange though given that the user still cannot select and view the updated data).
In the UPDATE T SET ID = ? WHERE NAME = ? case it fails because an explicit select on T was not given.
I expected an update permission would have an implicit select also on that table. In which case
the behaviour would have been consistent and both scenarios will work fine plus the user would
have been able to select and view the new data.
But if the current working is as per the SQL spec, I am fine. Anyways, would be better to get
this confirmed.
Rajesh
Re: Question on Grant/Revoke
Posted by Daniel John Debrunner <dj...@apache.org>.
Rajesh Kartha wrote:
>>Mamta Satoor wrote:
>>Manjula, I haven't looked at the SQL spec but it looks like that the
> update statement is doing a select operation on the
>>column id with "where id=3" and that is what is causing Derby to send
> an error.
>
> More questions:
> If a separate Select privilege is indeed required then I think the
> 'grant update on t1 to DBUSER' statement should not be succesful.
No, you need the SELECT privilege because you are reading the id column,
not because you are updating the column.
I assume if this update is executed, then no select privilege would be
needed:
UPDATE T SET ID = ?
Or with this UPDATE
UPDATE T SET ID = ? WHERE NAME = ?
that one needs UPDATE on T(ID) and SELECT on T(NAME).
Dan.
Re: Question on Grant/Revoke
Posted by Rajesh Kartha <ka...@gmail.com>.
>Mamta Satoor wrote:
>Manjula, I haven't looked at the SQL spec but it looks like that the
update statement is doing a select operation on the
>column id with "where id=3" and that is what is causing Derby to send
an error.
More questions:
If a separate Select privilege is indeed required then I think the
'grant update on t1 to DBUSER' statement should not be succesful.
However, I am very interested in knowing what the SQL spec mentions
about this and wonder how other databases behave.
On the DB2 (v8.2.2) that I have access to, the user who has the UPDATE (
or DELETE) privilege is not required to have a
SELECT privilege on the table.
db2 => select * from db2inst7.updatetab
SQL0551N "CLOUDTST" does not have the privilege to perform operation
"SELECT"
on object "DB2INST7.UPDATETAB". SQLSTATE=42501
db2 => update db2inst7.updatetab set id =300 where id=40
DB20000I The SQL command completed successfully.
-Rajesh
> Mamta
>
>
> On 8/30/06, *Manjula G Kutty* <manjula.kutty@gmail.com
> <ma...@gmail.com>> wrote:
>
> Hi,
>
> I was testing the new grant/revoke feature and now have this question.
> Here is what I did
> ij> connect 'jdbc:derby:testdb;create=true' user 'DBADMIN'
> password 'admin';
> ij> create table t1 (id int, name char(100));
> 0 rows inserted/updated/deleted
> ij> insert into t1 values (1,'sss'),(2, 'bbb'),(3, 'kkk');
> 3 rows inserted/updated/deleted
> ij> grant update on t1 to DBUSER;
> 0 rows inserted/updated/deleted
> ij> connect 'jdbc:derby:testdb' user 'DBUSER' password 'user';
> ij(CONNECTION1)> update DBADMIN.t1 set id =1 where id=3;
> ERROR 28508: User 'DBUSER' does not have select permission on column
> 'ID' of tab
> le 'DBADMIN'.'T1'.
> ij(CONNECTION1)>
>
> So now the question is why the DBUSER need a select permission here??
>
> Thanks
> Manjula
>
>
>
Re: Question on Grant/Revoke
Posted by Mamta Satoor <ms...@gmail.com>.
Manjula, I haven't looked at the SQL spec but it looks like that the update
statement is doing a select operation on the column id with "where id=3" and
that is what is causing Derby to send an error.
Mamta
On 8/30/06, Manjula G Kutty <ma...@gmail.com> wrote:
>
> Hi,
>
> I was testing the new grant/revoke feature and now have this question.
> Here is what I did
> ij> connect 'jdbc:derby:testdb;create=true' user 'DBADMIN' password
> 'admin';
> ij> create table t1 (id int, name char(100));
> 0 rows inserted/updated/deleted
> ij> insert into t1 values (1,'sss'),(2, 'bbb'),(3, 'kkk');
> 3 rows inserted/updated/deleted
> ij> grant update on t1 to DBUSER;
> 0 rows inserted/updated/deleted
> ij> connect 'jdbc:derby:testdb' user 'DBUSER' password 'user';
> ij(CONNECTION1)> update DBADMIN.t1 set id =1 where id=3;
> ERROR 28508: User 'DBUSER' does not have select permission on column
> 'ID' of tab
> le 'DBADMIN'.'T1'.
> ij(CONNECTION1)>
>
> So now the question is why the DBUSER need a select permission here??
>
> Thanks
> Manjula
>
>
>