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 Sun, 03 Sep 2006 06:49:36 GMT
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 
> <mailto:kartha02@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
>
>


Mime
View raw message