This content has been marked as final. Show 8 replies
Why would you want to do that? It makes very little sense to have a user that is allowed to create objects but not delete those same objects. It commonly makes sense to create a new user that has access to all the objects in a particular schema but doesn't have the ability to modify that schema.
You can't revoke the ability of a user to drop objects owned by that user. You could, potentially, create a DDL trigger that throws an error if the user issues a DROP statement. But that would mean that you were granting a privilege on one hand and then preventing the user from exercising that privilege on the other. That makes things far more complex to manage. It also tends to be extremely problematic in any sort of audit scenario-- the auditor is almost certainly going to be concerned about the original privilege grant.
run following script
then fire following query that will disable HR schema from dropping its own schema objects
SQL>insert into system.PRODUCT_USER_PROFILE
SQL*Plus uses the PRODUCT_USER_PROFILE (PUP) table, a table in the SYSTEM account, to provide product-level security that supplements the user-level security provided by the SQL GRANT and REVOKE commands and user roles.
DBAs can use the PUP table to disable certain SQL and SQL*Plus commands in the SQL*Plus environment on a per-user basis. SQL*Plus—not Oracle Database—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 the PUP table when a user logs in to SQL*Plus and maintains those restrictions for the duration of the session. Changes to the PUP table will only take effect the next time the affected users log in to SQL*Plus.
When SYSTEM, SYS, or a user authenticating with SYSDBA or SYSOPER privileges connects or logs in, SQL*Plus does not read the PUP table. Therefore, no restrictions apply to these users.
The PUP table applies only to the local database. If accessing objects on a remote database through a database link, the PUP table for the remote database does not apply. The remote database cannot extract the username and password from the database link in order to determine that user's profile and privileges.