db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rajesh Kartha <karth...@gmail.com>
Subject Re: Question on Grant/Revoke
Date Thu, 31 Aug 2006 16:38:34 GMT
 >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 
on object "DB2INST7.UPDATETAB".  SQLSTATE=42501
db2 => update db2inst7.updatetab set id =300 where id=40
DB20000I  The SQL command completed successfully.


> Mamta
> On 8/30/06, *Manjula G Kutty* <manjula.kutty@gmail.com 
> <mailto:manjula.kutty@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

View raw message