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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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).
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.