Solved

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

Posted on 2014-11-06
7
262 Views
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.
Thanks
0
Comment
Question by:E43509
7 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 125 total points
Comment Utility
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.
0
 
LVL 23

Assisted Solution

by:David
David earned 125 total points
Comment Utility
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.
0
 

Author Comment

by:E43509
Comment Utility
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).
Thx
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 125 total points
Comment Utility
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.

Reference

http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_1041.htm
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 125 total points
Comment Utility
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.
0
 
LVL 22

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now