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 in
news:clc0235qcj5noof331a1ds7t53rs52eqh2_at_4ax.com:

> 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.
>
> (the database layer has no schema identifer in its sql and so it works
> on the default.. I'm hoping the alter session code will take care of
> pointing to the correct schema).
>
>
> Could someone comment and possibly point me to the correct/normal way
> of granting a set of users the correct rights?
>

Trade-offs!
IMO, you have one of two choices to make. 1) Either is Oracle's enforce data access & restrictions or 2) have the application manage its own security (within a single schema).

P.S.
I could be wrong, but I suspect you have little working experience with ALTER SESSION SET CURENT_SCHEMA.
IMO, it does not "point" to anything.

HTH & YMMV Received on Fri Apr 13 2007 - 21:28:47 CDT