Re: grant drop table?

Short answer: no, not in the literal way you've described it here.

There are two privileges involved here: 'Create table' (which means a User can create -and drop and stick indexes on- tables created within their own schema only). And there's the 'Create Any Table' privilege, which means the User with it can create a table in anyone else's schema. If you use this privilege, then you must explicitly grant the additional privileges to 'Drop Any Table', 'Create Any Index' and so on.

Trouble is, once someone has the 'Drop Any Table' privilege, Oracle takes it literally: they have the right to drop *anyone's* tables... which tables they may drop is not determined by considerations of which role they possess.

I sense from the way that you phrased the question that what you want is for anyone who has the rights to drop tables to have it limited such that they can only drop tables belonging to other members of that same group. Oracle's roles don't work on group membership principles, so a person either can drop everything, drop nothing, or drop just his/her own objects.

However, I guess you could write code that could trap DDL statements, determine role ownerships, and either throw a wobbly or allow the DDL statement to proceed depending on how the determination worked out. But I wouldn't have a clue how to write it, and the fact that the intrinsic functionality is not there in Oracle makes me think that it would probably be a bad idea even if I did.

Regards
HJR "help desk" <software_at_localhost.localdomain> wrote in message news:8u16ne$8qe$1_at_localhost.localdomain...
> Hello,
>
> I am trying to grant a role the drop table privileges in order to
> enable all users with that role to create and drop any tables created
> by any of those users. Is this possible?
>
> Thanks!
>
>
Received on Sat Nov 04 2000 - 15:50:20 CST