As security tightening exercise, I'm removing all system privileges from an oracle database user. Now this user ONLY has the following system privileges:
- CREATE SESSION
- UNLIMITED TABLESPACE
I was hoping that the user wont be able to do any DDL commands. But to my surprise, user can DROP TABLE in its own schema even though it can't create one.
Oracle documentation says prerequisite for DROP TABLE is "The table must be in your own schema or you must have the DROP ANY TABLE system privilege". Just that!!! I don't understand the security logic of Oracle but is there any way I can prevent Users from dropping their own tables?
The alternative would be creating another user to run the application and grant object access, which I'd rather like to avoid as there are potential issues.
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
DROPis 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.