I thought you said you were collecting required privileges and I was going to store them in SYSDEPENDS and later use them for authorization. I don't see the privileges collected for veiws from the test case above :) So, yes, I will work on authorization for views, triggers and constraints once the required privileges are collected. If you are busy with other things, I don't mind looking into collecting privileges part.
 
Mamta

 
On 6/1/06, Satheesh Bandaram <bandaram@gmail.com> wrote:
Mamta Satoor wrote:

> Hi,
>
> I wrote a simple test for grant/revoke and came across incorrect
> behavior for views. If a create view is issued with select on a
> non-granted table, it does not fail. create trigger and constraints do
> catch access to non-granted tables. Here is the ij session

Work to implement SQL authorization for views, triggers and constraints
is till pending. I thought you were working on it? :)

It seems binding of createView needs to change to set current required
privilege to be SELECT_PRIV.

Satheesh

> $ 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';
> ij> create table t1 (c11 int not null primary key);
> connect 'jdbc:derby:c:/dellater/dbmaintest2;create=true' user 'mamta2';
> 0 rows inserted/updated/deleted
> ij> WARNING 01J01: Database 'c:/dellater/dbmaintest2' not created,
> connection made to existing database instead.
> ij(CONNECTION1)> create table t2 (c21 int);
> 0 rows inserted/updated/deleted
> ij(CONNECTION1)> create trigger tr1t2 after insert on t2 for each row
> mode db2sql select * from mamta1.t1;
> ERROR 28508: User 'MAMTA2' does not have select permission on column
> 'C11' of table 'MAMTA1'.'T1'.
> ij(CONNECTION1)> alter table t2 add constraint fk1t2 foreign key(c21)
> references mamta1.t1;
> ERROR 28508: User 'MAMTA2' does not have references permission on
> column 'C11' of table 'MAMTA1'.'T1'.
> ij(CONNECTION1)> -- view should fail but it is not
> create view v1 as select * from mamta1.t1;
> 0 rows inserted/updated/deleted
>
> Mamta