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

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 Received on Mon Apr 16 2007 - 02:34:07 CDT