Oracle Security - how to prevent a User from DROP TABLE its own tables Oracle Security - how to prevent a User from DROP TABLE its own tables oracle oracle

Oracle Security - how to prevent a User from DROP TABLE its own tables


A user will always have permissions to drop objects that they own. You can't prevent that by revoking privileges.

Since you're looking at tightening security, creating a new user and granting that user whatever privileges they need to manipulate the data is the right answer. The only people that ought to be logging in to a production database as a user that owns application objects are DBAs and then only when they are in the process of deploying changes to the schema. Everyone else should be logging in to the database as users other than the schema owner.

That being said, if the right solution is more work than you're prepared to undertake right now, a potential stopgap would be to create a DDL trigger on the database that throws an exception if a DROP is issued against an object in the specified schema. This is less secure than the proper solution. You may miss something when implementing the trigger, you or someone else may drop or disable the trigger and forget to re-enable it, etc. And it makes security reporting much more difficult because you've got a custom solution that isn't going to be obvious in the various security related data dictionary views which may create problems for auditors.