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* <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> 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
