Re: Prevent table delete

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Fri, 17 Aug 2007 14:15:16 -0500
Message-ID: <46c5e7b9$0$31271$88260bb3@free.teranews.com>

Thomas Kellerer wrote:
> Brian Peasland wrote on 17.08.2007 20:39:
>

>>> I have *no* idea what you mean with "table file", but revoking the 
>>> DROP privilege from all users that are able to access the table 
>>> should be enough.
>>
>> Did you mean the DROP ANY TABLE system privilege? There is no DROP 
>> TABLE system privilege.

> Interesting. I thought there was a DROP TABLE privilege (which is
> implicitely granted to the table owner).
> Well, if there isn't then my post was obviously rubbish ;)
>
> Thanks for the hint
> Thomas

You can grant CREATE TABLE, CREATE VIEW, etc to a user. There is no associated DROP system priv (excluding the DROP ANY privs). The owner will be able to drop their objects, even if they no longer have the CREATE privilege for that object type as evidenced below:

SQL> connect scott/tiger
Connected.
SQL> create table test (id number);

Table created.

SQL> connect system
Enter password:
Connected.
SQL> revoke create table from scott;

Revoke succeeded.

SQL> connect scott/tiger
Connected.
SQL> drop table test;

Table dropped.

Just owning the object is sufficient to be able to drop that object.

Cheers!
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

-- 
Posted via a free Usenet account from http://www.teranews.com

Received on Fri Aug 17 2007 - 14:15:16 CDT