Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Schema management

Re: Schema management

From: Ron Rogers <>
Date: Fri, 06 Oct 2000 15:41:16 -0400
Message-Id: <>

One problem with one owner and roles that are granted is the application = can NOT truncate any table. Deletes must be used and that eats up space. = What I did here is to create the tables that need to be completely emptied = by the application under their own scheme so they can truncate the table = and then the table owner grants all privs to the dba. ROR =AA=BF=AA=20
>>> 10/06/00 03:35PM >>>

I am seeking advice on how to organize schema security within an Oracle instance. Currently, we tend to divide schemas by application usage (all of appA's objects are owned by appA_owner, appB's objects are owned by appB_owner, etc.) The main problem with this is that there is a frequent need to cross schema boundaries for applications or processes that affect data owned by several different owners.

The suggestion has been made to have a single owner that owns everything, and to control access to developers and users entirely thru roles. System-wide processes/applications would be run under this owner's privs; other proceses/applications more narrow in scope would have their privileges determined entirely thru roles.

I am wondering what other solutions exist. Is there an accepted practice which most people follow? Can anyone point me to some documentation describing a good solution for managing schemas?

Oracle 8.1.6 on Solaris 2.7

Thanks to any who respond.
Please see the official ORACLE-L FAQ: --=20
Author: Bill Becker

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message to: (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Oct 06 2000 - 14:41:16 CDT

Original text of this message