Re: Schema management

I had the same issue. If you create a stored procedure that uses dbms_sql you can create a synonym for the truncate procedure and grant them execute and maintain the security.

Dave Turner

On Fri, Oct 06, 2000 at 12:45:54PM -0800, Ron Rogers wrote:
> 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 ª¿ª
>
> >>> beckerb_at_mfldclin.edu 10/06/00 03:35PM >>>
> Hello,
>
> 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.
> beckerb_at_dgabby.mfldclin.edu
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Bill Becker
> INET: beckerb_at_mfldclin.edu
>
> 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: ListGuru_at_fatcity.com (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).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Ron Rogers
> INET: RROGERS_at_galottery.org
>
> 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: ListGuru_at_fatcity.com (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 - 16:33:33 CDT