Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (asf.osuosl.org: domain of kartha02@gmail.com designates
 64.233.162.195 as permitted sender)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
        s=beta; d=gmail.com;
        h=received:message-id:date:from:user-agent:x-accept-language:mime-version:to:subject:references:in-reply-to:content-type:content-transfer-encoding;
        b=rR0WKdbIiqfqZF5f4XiodovMfYDz+YCp0vncbkQs0j5G++OirAzuFiiUxT0WpzwOawEAG+nmzHTPDChFWkfZkOiExKPnGGbPLu6IVMBu8gMHP0sRKsOzbFsd/dPFn7jj0VD4qQYxKsE7Y+RwNJQ+LhZzV6bY3sEO8IHxszt41vQ=
Message-ID: <44F7108A.6030800@gmail.com>
Date: Thu, 31 Aug 2006 09:38:34 -0700
From: Rajesh Kartha <kartha02@gmail.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
 rv:1.7.12) Gecko/20050915
MIME-Version: 1.0
To: derby-dev@db.apache.org
Subject: Re: Question on Grant/Revoke
References: <44F61C07.4020609@gmail.com>
 <d9619e4a0608310745v7a978820kd3a37e39a9ff9271@mail.gmail.com>
In-Reply-To: <d9619e4a0608310745v7a978820kd3a37e39a9ff9271@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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