SP2-0544: Command "drop" disabled in Product User Profile

Anand Reddy pinged me today saying that he is getting the following error in a cloned instance while dropping database links:

SP2-0544: Command "drop" disabled in Product User Profile

This is coming as Production instance is configured to disallow DROP command for preventing its use by any developer code.

This is done through product_user_profile table. Metalink Note 197611.1 has an example:

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 DISABLED

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.