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

On Sat, 14 Apr 2007 02:28:47 GMT, "Ana C. Dent" <anacedent_at_hotmail.com> wrote:

>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

thanks for the reply.
I plan on just prompting the user for a username (default workstation user) and password with schema and connect string, and let Oracle db backend handle the validity.

I was planning on using (something what?) maybe the alter session to try to 'force' the default sql to affect a given schema.

I'd appreciate any more insight.
Regards Received on Fri Apr 13 2007 - 23:40:35 CDT