Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

Understanding the limitations of separating the Oracle schema owner versus an oracle user of schema

I am trying to find a list of operations / tasks that an oracle schema owner can do that cannot be done by another oracle user  (not a sysdba) without giving them some super privileges.   I am not just talking about a simple granting select, insert, update, delete, execute on an schema object to a user
For example, the TRUNCATE TABLE command.  Easily done by schema owner, but not available to another oracle user unless you grant that user 'DROP ANY TABLE'.

Our security group is looking at restricting access by this method and I need to give my vendors of various applications an idea where this paradigm may break their app.
4 Solutions
slightwv (䄆 Netminder) Commented:
I'm not sure what you are looking for here.

The basic answer is that as the owner of the objects in a schema you can do ANYTHING you want to them.

As a normal user that just has 'create session' granted, you can do NOTHING to them.  You cannot even see anything about that schema unless permissions have been granted to the PUBLIC role.

You that user to access the objects, they would need to be granted access.

As far as security goes:
You normally separate the base schema from the app users and grant the minimal rights necessary for the app to run.
DavidSenior Oracle Database AdministratorCommented:
If you're allowing non-SYS-level users to drop any table, you've already found something to fix (remove) as soon as possible. Said user could corrupt your production instance in a few seconds, once the SYS-owned tables disappeared.

SlightWV is correct about the object control, provided the schema owner has the object privileges in the first place.  Oracle used to provide pre-populated user roles such as CONNECT or RESOURCES, with which a user could create their own tables, indexes, etc.  

Now, I might create an APP_USER with the SUID object privileges to manipulate data owned by APP_OWNER.  Likewise, APP_READ could SELECT against APP_OWNER tables and views, with an appropriate role and synonyms.  And to complete the basics, the APP_OWNER account is kept locked down until the DBA needs to deploy (tested and documented) object changes.

In my world, the best practice is to "grant the least amount of privilege necessary" for someone to do their job.
E43509Author Commented:
Thanks for the replies above.  I think my question is not quite clear enough as I understand these concepts but don't have a clear line of sight to a simple list of what are the gotchas with the separation of schema owner versus schema user.  
Granting drop any table to non sys level users is not something we would do but just an example of a gotcha.  We have one app that runs a truncate on temporary staging tables (instead of a delete).  Therefore that application will not run as some other oracle user and will continue to attach as the schema owner.
I need to identify other similar operations or conditions to be proactive on these vendor apps and identify which ones may have problems (white box approach).  We don't want to run them in this separation mode and hope testing finds any problems (black box approach).
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Wasim Akram ShaikCommented:
The terms which you used here pose the same meaning: ORACLE_USER and SCHEMA_OWNER are the synonyms to each other.

Every USER is associated to only one SCHEMA
and Every SCHEMA can hold objects of only one USER

>>"Therefore that application will not run as some other oracle user and will continue to attach as the schema owner."

If I interpret correctly, its better to separate applications/modules to have different users/schemas so as to have a track of changes done by one application/module.

You can find what all objects are being used by Other Objects as every object if  it has any dependency can be listed down from data_dictionary views

ALL_DEPENDENCIES is the view which you have to check in order to find that dependency on objects.


Mark GeerlingsDatabase AdministratorCommented:
I think we do understand what you are asking for.  But Oracle doesn't give us the kind of "in-between" level of system privileges that you might like.  Basically, the schema owner can do anything to his tables, and any other user can only do SELECT, INSERT, UPDATE or DELETE on his tables, and that is only if those privileges are specifically granted by the schema owner.

The DBA privileges including "drop any table", "alter any table", etc. are "system" privileges.  That is, they are system-wide and are not limited to any particualr schema.  So they are not a good idea to give to anyone who is not a system administrator.

If you have control over the source code (which you don't have if you are dealing with purchased apps) you could create some procedures as the schema owner that can do specific tasks (like: truncate a table) and you could grant "execute" on these procedures to other users.  This would allow them to do things that you can't give them specific grants to do.

The only other option that I can think of (and I'm not recommending it) that would allow a non-schema owner to truncate a table owned by someone else, would be to grant a system privilege, like "drop any table" to a non-schema owner, but then also create a DDL trigger to watch for this action and check who is attempting to do what, and either allow that action to proceed, or to return an error from the DDL trigger and not allow the action.
Steve WalesSenior Database AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now