Re: Remove all tables for user with one SQl statement?

On Mon, 19 Dec 2005 16:40:36 +0100, "Robert Klemme" <bob.news_at_gmx.net> wrote:

>IMHO there's a problem if the user dropping the table has no permissions
>to drop those constraints.

 The user owning the table that is referenced in a foreign key constraint owned by another user _can_ cause that foreign key constraint to be dropped by dropping the table with cascade constraints, even if it has no other privileges on that table.

SQL> create user user1 identified by user1 quota unlimited on users;

User created.

SQL> create user user2 identified by user2 quota unlimited on users;

User created.

SQL> grant create session, create table to user1;

Grant succeeded.

SQL> grant create session, create table to user2;

Grant succeeded.

SQL> connect user1/user1
Connected.
SQL> create table t1 (id number not null primary key);

Table created.

SQL> grant select, references on t1 to user2;

Grant succeeded.

SQL> connect user2/user2
Connected.
SQL> create table t2 (id number not null primary key);

Table created.

SQL> alter table t2 add constraint t2_fk1            
  2      foreign key (id) 
  3      references user1.t1(id);

Table altered.

SQL> connect user1/user1;
Connected.
SQL> drop table t1 cascade constraints;

Table dropped.

SQL> connect user2/user2
Connected.
SQL> select constraint_name, constraint_type from user_constraints;

CONSTRAINT_NAME                C
------------------------------ -
SYS_C005446                    C
SYS_C005447                    P

-- 
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool