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 behavior?
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.
Thanks,
Mamta
