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

Posted on 2014-11-06
Medium Priority
Last Modified: 2015-06-23
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.
Question by:E43509
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 77

Accepted Solution

slightwv (䄆 Netminder) earned 500 total points
ID: 40426547
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.
LVL 23

Assisted Solution

David earned 500 total points
ID: 40426777
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.

Author Comment

ID: 40426806
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).
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 500 total points
ID: 40427705
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.


LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 500 total points
ID: 40428388
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.
LVL 22

Expert Comment

by:Steve Wales
ID: 40845982
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question