Preventing Tables from being dropped

[WILLIEWANKA]

Is there any way to prevent a user from dropping one of his own tables?


We're basically trying to find a way to revoke all but select on a table from public, but still have the ability to grant all access back if needed.


If you revoke all on a table from a user, is there any way to grant it back....if that user was the owner, and no other users have grant option? *whistles innocently*

 

[dnoeth]

If you login as a owner you can grant any right on any owned object to any user. It's not controlled by dbc.accessrights, but by dbc.children.
So if the user is an owner he still can grant himself the drop right and drop the table

Check "Implicit Rights" in the Database Administration manual.

But you probably ment the rights of the creator of an object.
The creator is the user who submitted the Create statement, but maybe he's not an owner.

dbc
- sysdba
- - user1
- - db1

sysdba/dbc: grant create table on db1 to user1;
user1: create table db1.foo;
sysdba/dbc: revoke all on db1.foo from user1;

-> user1 doesn't have any access to that table although he's the creator, but sysdba/dbc still have the implicit owner rights.

sysdba/dbc: grant select on db1.foo to public;

Dieter