db2 8.2.7
win 2000

I have to give creatin,dropin priv. on a schema to a user so that they can
debug SP .
Now by Giving these priv. they can also drop Tables in that schema which i want to avoid.
What is the way out, so that they can drop/create the procedures but wont be able to Drop tables in the schema

The only thing I can think of is to make them put their routines in a separate schema and grant the rights there.

Andy

First of all why you have to give privilege on Schema..?
Cant you grant it only for that particular SP..?

How do you grant create rights to a SP that does not exist?

That is why you need creatin right for the schema.

Andy

ohh OK..i was thinking if you have to alter the existing one.

No , i have to keep SP and tables in the Same Schema.
Recently a important Table was dropped by some user because of the schema priv.
It seems to me a big security concern.
I believe there must be a way out ,which we are missing

Revoke the DROPIN privilege on the schemas and manage things via CONTROL privileges on the single objects or the DEFINER.

http://publib.boulder.ibm.com/infoce...c/r0000945.htm

Hi Knut
Thanks for your reply.
1 more solution that i have got
Revoking alterin from schema
and then altering all tables in the schema to restrict drop.

alter table eone.pTest124 add restrict on drop ;