Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Monday, December 22, 2008

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
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)

--------- ------- ---------- ----------------

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.

No comments: