4

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.

Kara
  • 6,115
  • 16
  • 50
  • 57
Tyn
  • 555
  • 7
  • 22
  • 2
    You do realise that UNLIMITED TABLESPACE is not a good thing to grant a user? Because it grants rights on any tablespace, including ones like SYSTEM and SYSAUX which should not be used by applications. If you're going to lock things down you should explicitly grant quota on *named tablespaces* to the user. – APC Jan 12 '12 at 17:36
  • Thanks for your suggestion APC. Yes, UNLIMITED TABLESPACE is not best privilege to have on a production system. But all db users were granted RESOURCE role (which Oracle silently grants UNLIMITED TABLESPACE without telling you). Now I removed RESOURCE role I have to grant this privilege for temporary purpose. But will go further with grant quota later. – Tyn Jan 13 '12 at 11:07

1 Answers1

10

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.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • 4
    +1 Basically schema owners should not be able to connect to the production database. – APC Jan 12 '12 at 17:38
  • 1
    If you have a bunch of SQL queries that currently don't specify schema names, you could make this virtually pain-free by creating synonyms for the new user for all the objects in the old schema they'd need. That way there's no need to change the queries. – ivanatpr Jan 12 '12 at 18:30
  • Unless @invantpr you take into account the hassle of creating and maintaining a load of synonyms. – Ben Jan 12 '12 at 18:41
  • Thanks a lot Justin. That's make my life a lot easier. Having DDL trigger is not the best solution as having app user but for a 20 year old system with tons of legacy code, this should have to do ;-) – Tyn Jan 13 '12 at 10:26