Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Drop any table ?? for user in different schema

Ratzie
Super Advisor

Drop any table ?? for user in different schema

Can I specifically allow a user to drop a table in a schema (specific schema) that he does not own.

I would perfer not to give him this drop any table that can be very nasty on any other table...

Oracle 10.2.0.2
5 REPLIES
Paul Sperry
Honored Contributor

Re: Drop any table ?? for user in different schema

DROP TABLE needs ALTER permission on the schema to which the table belongs, CONTROL permission on the table, or

membership in the db_ddladmin fixed database role.



USE dbname

GRANT ALTER ON OBJECT :: tablename TO username;

GO;

Yogeeraj_1
Honored Contributor

Re: Drop any table ?? for user in different schema

hi,

You may also try implementing Proxy User.

SQL> show user
USER is "SCOTT"
SQL> alter user yogeeraj grant connect through scott;

User altered.

SQL> connect scott[yogeeraj]/tiger
Connected.
SQL> show user
USER is "SW1"
SQL>

hope this helps!
kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Srikanth Arunachalam
Trusted Contributor

Re: Drop any table ?? for user in different schema

Hi Ratzie,

Is your flashback feature is set on. If you have FLASHBACK feature on, you need not worry about user droping table. It will be placed in recyclebin from where you can restore it.

Plz let me know if you want more information on this.

Thanks,
Srikanth
Yogeeraj_1
Honored Contributor

Re: Drop any table ?? for user in different schema

hi again,

sorry for the typing mistake in my previous post which should instead read as follows:

SQL> show user
USER is "SCOTT"
SQL> alter user yogeeraj grant connect through scott;

User altered.

SQL> connect scott[yogeeraj]/tiger
Connected.
SQL> show user
USER is "YOGEERAJ"
SQL>

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Ratzie
Super Advisor

Re: Drop any table ?? for user in different schema

THanks guys, we have forced the user to log in with the appropriate user account