This content has been marked as final. Show 8 replies
Which four digit version? Most questions have version dependent answers!
Also, a best practice would be to lock the account which owns an application!
Senior Oracle DBA
i am using ORACLE 10.2.0.4
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.1 person found this helpful
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.
trigger1 person found this helpful
Check below link, may be of your interest :
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.
You know that only applies to SQL*Plus, right? The same user can log in using any other tool (SQL Developer, TOAD, etc.) and drop the table. I'm hard-pressed to imagine why you would want a user to be able to drop a table if they log in using SQL Developer but not SQL*Plus.