Purpose: 
~~~~~~~~ 
The purpose of the document is to help the DBA's understand how to avoid users 
from dropping their own objects. 
This is sometimes important from users accidental errors 
Note that Oracle does not have any automated feature to prevent a user from 
dropping his own objects. 

Scope & Application: 
~~~~~~~~~~~~~~~~~~~~ 
A user automatically has all object privileges for schema objects contained in 
his or her schema. 
As such you cannot revoke any object privilege from a user for his own schema 
objects. 
Below mentioned are few steps/workarounds to prevent a user from dropping his 
own objects. 

Workarounds: 
~~~~~~~~~~~~ 
Workaround 1: 
------------- 
First you create a user for e.g. scott and have user scott login and create all 
necessary objects, and then revoke create session from user scott so that user 
can no longer login 
This will prevent user scott from logging a session, which in turn will prevent 
from dropping his own objects. 
Everyone else that will be able to drop that table will have to have the 
"drop any table " Privilege given to them. 
This can help in application design wherein your production user scott has all 
the application required objects created. 
You can then create another application user which has only selective 
application privileges been granted on production scott schema. 
This will prevent the application from directly accessing the production user 
and avoid accidental errors. 
Below mentioned are the Steps for Workaround 1. 
SQL> connect sys/****** 
SQL> create user scott identified by tiger 
default tablespace users temporary tablespace temp quota 100m on users; 
SQL> grant connect, resource to scott; 
SQL> connect scott/tiger 
SQL> create table xyz ( a number); 
SQL> create table abc ( d number); 
SQL> connect sys/****** 
SQL> revoke create session from connect; 
SQL> connect scott 
Enter password: ***** 
ERROR: 
ORA-01045: user SCOTT lacks CREATE SESSION privilege; logon denied 
SQL> connect sys/****** 
SQL> drop table scott.xyz; 
Later on when you create another schema you will have to grant create session 
privilege again to connect role or else the new schema will lack it and fail to 
login. 
*** This way you can prevent user scott from dropping his own objects. 

Workaround 2: 
------------- 
To make use of Product_user_profile table. 
A table that resides in the SYSTEM account. 
It provides product level security that supplements the user level security 
provided by SQL commands GRANT and REVOKE, and can be used with one's own 
applications as well as with other Oracle products. 
The table is created by running the command file pupbld.sql under the schema 
SYSTEM. 

Note: 
----- 
This will prevent you to drop any Schema Object using SQL*Plus only. 
This will not work via Svrmgrl or any other Tool. 
Note: The userid, attribute and char_value must be in uppercase. 
For Example: 
SQL> connect system/manager 
SQL> insert into product_user_profile (product,userid,attribute,char_value) 
values('SQL*Plus','SCOTT,'DROP','DISABLED'); 

PRODUCT   USERID  ATTRIBUTE  CHAR_VALUE 
--------- ------- ---------- ---------------- 
SQL*PLUS  SCOTT   DROP       <B>DISABLED</B>

If for example user SCOTT attempts to Drop a object, he would receive this error: 
SQL> drop table xyz; 
SP2-0544: invalid command: drop. 
To re-enable commands, delete the row containing the restriction. 
*** This will prevent user scott from dropping his own objects. 

Suggestion: 
----------- 
The above workarounds/steps can sometimes beneficially help from accidental users 
errors and can save drastic time and energy against recovering from such 
accidental errors. 

PRODUCT_USER_PROFILE is a table in the database that the DBA can modify 
to provide product-level security. 
This table provides security for products of SQL*Plus, SQL*ReportWriter, and 
Oracle Browser.

Various Oracle products use PRODUCT_USER_PROFILE, a table in the SYSTEM 
account, to provide product-level security that supplements user roles 
and the user-level security provided by the SQL GRANT and REVOKE commands. 
DBAs can use PRODUCT_USER_PROFILE to disable certain SQL and SQL*Plus commands 
in the SQL*Plus environment on a per-user basis. SQL*Plus --not Oracle-- 
enforces this security. DBAs can even restrict access to the GRANT, REVOKE, 
and SET ROLE commands to control users' ability to change their database 
privileges. 
SQL*Plus reads restrictions from PRODUCT_USER_PROFILE when a user logs into 
SQL*Plus and maintains those restrictions for the duration of the session. 
Changes to PRODUCT_USER_PROFILE only take effect the next time the affected 
users log into SQL*Plus.