db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta Satoor" <msat...@gmail.com>
Subject In Derby SQL Authorization, is drop view the only way to drop a view created by a dba? Will revoke privilege ever drop such a view?
Date Mon, 31 Jul 2006 01:28:26 GMT

With Derby SQL Authorization, there are 2 ways a view can get dropped
automatically provided it is created by a non-dba user
1)drop view command - issued either by the dba or the view owner
2)revoke privilege - if the privilege being revoked is required by the view,
then view will be dropped automatically

I have been thinking about some view testing and wrote a test case where the
dba creates a view in a schema owned by some other user and the view is
accessing objects from couple different schemas on which the view's schema
owner doesn't have access to. The view creation from dba doesn't fail
because dba can access any object in any schema and can create any object in
any schema. For such a view created by the dba, it looks like the only way
to drop that view will be through drop view either by dba or the schema
owner. No revoke privilege can cause the view created by dba to drop
automatically. This logically sounds correct but I wanted to run it by the
community to see if this is the correct behavior. Any Comments on the

Here is an example demonstrating the case above
-- View tests
--  Let the dba(satConnection) create a view in schema mamta2 (owned by user
mamta2). The view's definition accesses
--    objects from schema mamta1. The owner of schema mamta2 does not have
access to objects in schema mamta1
--    but the create view by dba does not fail because dba has access to all
the objects.
--  mamta2 will have access to the view created by the dba because mamta2 is
owner of the schema "mamta2" and
--    it has access to all the objects created in it's schema, whether they
were created by mamta2 or the dba.
--  Any other user (except the dba) will need to get explicit select
privileges on the view in order to access it
set connection mamta1;
-- Note that mamta1 is creating couple tables but has not granted
permissions on those tables to anyone
create table t11ViewTest (c111 int not null primary key);
create table t12ViewTest (c121 int, c122 char);
set connection mamta2;
-- create an object so that user mamta2 will become owner of the schema
mamta2. If I don't do this, dba will
--   become owner of schema mamta2 with the create view sql below
create table t21ViewTest (c211 int, c212 char);
-- connect as dba
set connection satConnection;
-- dba is creating a view in schema owned by another user. dba can create
objects anywhere and access objects from anywhere
--   Notice that user mamta2 does not have access to objects accessed by
view below
create view mamta2.v21ViewTest as select t1.c111, t2.c122 from
mamta1.t11ViewTest as t1, mamta1.t12ViewTest as t2;
-- dba can do select from that view
select * from mamta2.v21ViewTest;
set connection mamta2;
-- the schema owner can do a select from an object that is part of it's
schema even though it was created by the dba
select * from v21ViewTest;

Note that the only way to drop view v21ViewTest will be via drop view issued
either by user mamta2 or the dba.


View raw message