Re: corret approach/sql to grant differnt logins rights to add/mod/del schema elements for differnt schemas

On 15 Apr 2007 23:34:07 -0800, yf110_at_vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote:

>Jeff Kish (jeff.kish_at_mro.com) wrote:
>: Hi.
>
>: I need to change an app so that different users can use a single login to
>: update different schemas.
>
>: I assume that the 'correct' or 'normal' way to create a schema with tables is
>: to create a default user and use ddl to create the tables, triggers etc.
>
>: so if I want 4 schemas and 3 users to be able to access each of them I'd need
>: to
>: 1 - create 4 default users and create the tables etc with ddl
>: 2 - add 3 users (that I plan on corresponding to the workstation login that
>: the apps will supply via interaction with the OS)
>: 3 - then logged in as dba do something(?what) to grant the 3 users rights to
>: add/mod/delete each of the 4 schemas.
>
>: I am hoping that after my app logs in and prompts the user for a password and
>: a schema, that I can use after login a "ALTER SESSION SET CURENT_SCHEMA"
>: statement to point to the correct schema.
>
>Another possibility is to define a set of user synonyms so that accessing
>a table accesses which ever version of the table is the desired version.
>
>Switching databases would consist of running a set of user synonym
>definitions.
>
>It's more commands to run to switch databases than the CURENT_SCHEMA
>approach, but I don't think defining a synonym is an expensive operation,
>and has the potential advantage that the user is always running as
>themselves.
>
>I can't say for sure if this is a dumb idea or not.
>
>$0.10

Geez, I'm really not sure.

I have a followup question in general, regarding the alter schema approach.

Can anyone comment on any additional drawbacks? I am planning on using alter session set current_schema="XXX"; from some c++ code to point an application to the proper schema.

I realize now after some more research that this won't change any user related queries (I have a handful of places that get column length/type information from user_tab_columns) which I think won't be affected by the alter session command.

So I plan on digging into that precambrian code and changing it to use all_tab_columns instead and qualifying with the schema name.

Are there any other similar things I should be watching out for?

thanks
Jeff
Jeff Kish Received on Mon Apr 16 2007 - 14:00:35 CDT