Re: Is a role the way to go with this?

From: Robert W. Swisshelm <swisshelm_at_lilly.com>
Date: 1996/07/20
Message-ID: <31F10C74.D8D_at_lilly.com>#1/1


Scott Mattes wrote:
>
> We just moved to an Oracle 7 platform, but this site has only one set
> of books and is a long drive, so here is my question.
>
> The app we have moved over has one Oracle user that owns all the
> tables/views. Mostly we have users viewing and updating tables via
> forms and COBOL programs. But this one little piece of the app needs
> to allow a user to create tables (copies of existing tables) that are
> owned
> by the app master id.
>
> Before when we controlled the horizontal and vertical we used a SQL
> to do this and had the app master id's password in it. Now that we
> don't control much of anything we would like a better way.
>
> Can a role be written to allow this one user to create tables and
> synonyms that are really owned by the app master id?
>
> I got this idea from browsing through the on-line version of Oracle
> Unleashed at http://www.mcp.com/sams/
> ----------------------
> Scott Mattes
> I-Net, Inc

I don't think that roles would work, but you could use a stored procedure and dynamic sql.

When a user executes a stored procedure, they run under the privilege domain of the owner of the procedure, not their own. So, they would have the ability to create objects owned by the owner of the procedure.

Normally, PL/SQL does not allow 'dynamic' sql. However, with version 7.1 and about, Oracle provides us with the DBMS_SQL package. There are a number of procedures in that package that you can use to build your CREATE TABLE command on the fly.

The DBMS_SQL package is documented in the 7.1 addendum, or in Chapter 11 of the 7.2 Application Developers Guide.

FYI, Oracle ships is documentation online. You can put the CD on the network and make it available to everyone. It comes with a runtime version of Oracle Book for Windows. It isn't the greatest tool, but it is better than nothing.

-- 
Bob Swisshelm
Eli Lilly and Company
swisshelm_at_lilly.com
Received on Sat Jul 20 1996 - 00:00:00 CEST

Original text of this message