db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <banda...@gmail.com>
Subject Re: Question on Grant revoke functional spec
Date Thu, 27 Jul 2006 08:02:55 GMT
Hi Mamta,

While it is OK for 'mamta2' to create the view, in your example,
attempts to GRANT access to other users on view 'v1' should raise an
error. This is because user 'mamta2' has been granted SELECT privilege
and this user is attempting to allow other users to SELECT from table
created by 'mamta1'. User 'mamta1' only granted SELECT to 'mamta2' and
without WITH GRANT OPTION, 'mamta2' can't allow other users access to
the view. If the grant statement to allow SELECT privilege where to be
issued WITH GRANT OPTION, then it would have been possible for 'mamta2'
to GRANT access to view 'v1' here.

Derby currently doesn't support WITH GRANT OPTION. So, while it is OK
for 'mamta2' here to create the view, an error *should be* raised if
this user attempts to grant access to columns derived from user 'mamta1'

So, while the spirit of the statement you high-lighted is correct,
actual text below leads to think an error would be raised during create
view. The error would actually be raised when 'Jane' tries to GRANT
access to any user or even PUBLIC. (Since only owners of a table can
actually grant that privilege in Derby currently)

Not sure if your view authorization work implemented this. If not, it
probably should be addressed... otherwise it would be very easy to
"export" any privileges any user receives to others. This should not be
possible in Derby until WITH GRANT OPTION is implemented.


Mamta Satoor wrote:

> Hi,
> I was going through the grant revoke functional spec which is attached
> to DERBY-464 and was puzzled by following example in there 
> CREATE VIEW s.v(vc1,vc2,vc3)
>     AS SELECT t1.c1,t1.c2,f(t1.c3) FROM t1 JOIN t2 ON t1.c1 = t2.c1
> WHERE t2.c2 = 5
> Jane needs the following permissions in order to create the view:
>    *
>       ownership of schema s (so that she can create something in it),
>    *
>       _*ownership of table t1 (so that she can allow others to see
>       columns in it), *_
>    *
>       select permission on t2.c1 and t2.c1, and
>    *
>       execute permission on f.
> The example says that Jane needs ownerhsip of table t1 so she can
> allow others to see columns in it. Is that right?
> Currently, in Derby SQL Authorization mode, I can create a view based
> on a table in some other schema as long as I have the select privilege
> on it. Following is the ij session showing that behavior
> $ java -Dderby.database.sqlAuthorization=true -Dij.exceptionTrace=true
> org.apache.derby.tools.ij
> ij version 10.2
> ij> connect 'jdbc:derby:c:/dellater/dbmaintest2;create=true' user
> 'mamta1' as mamta1;
> ij> create table t1(c11 int);
> 0 rows inserted/updated/deleted
> ij> grant select on t1 to mamta2;
> 0 rows inserted/updated/deleted
> ij> connect 'jdbc:derby:c:/dellater/dbmaintest2;create=true' user
> 'mamta2' as mamta2;
> WARNING 01J01: Database 'c:/dellater/dbmaintest2' not created,
> connection made to existing database instead.
> ij(MAMTA2)>create view v1 as select * from mamta1.t1;
> 0 rows inserted/updated/deleted
> ij(MAMTA2)>
> thanks,
> Mamta

View raw message