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

On Sat, 14 Apr 2007 09:54:41 +0100, Jim Smith <usenet01_at_ponder-stibbons.com> wrote:

>In message <clc0235qcj5noof331a1ds7t53rs52eqh2_at_4ax.com>, Jeff Kish
><jeff.kish_at_mro.com> writes
>>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?
>>
>>thanks
>>Jeff
>>Jeff Kish
>
>More or less right.
>
>Your step 3 is
>for each user
> for each schema
> for each object
> grant <privileges> on schema.object to user;
>
>where <privileges> are select,update,insert,delete for tables
>and various others for other types of object.
>
>set current_schema does let you reference tables without a schema
>qualifier but if the session issuing the command has any objects of its
>own, things can get complicated. Your other option is to create
>synonyms for the schema objects.
>
>As part of developing the ddl for the schemas, you should also create
>scripts to grant the necessary privileges and create the necessary
>synonyms.
>
>You should also look into roles. In general create roles, grant
>privileges to the role and then grant the role to the user.
good.
Ok.. so I create a use for each schema (not optional.. I guess you can't create schema (i) without creating user (i), i.e. I guess I can't just login as a user and create 4 different schemas.

Then create a role that has all the rights and accessed to interact with the database.

Finally add each user to the database and grant them that role.

When each user logs in have the application find out the schema of interest and use the "ALTER SESSION SET CURENT_SCHEMA" statement to point them to the correct place.

Regards and thanks again. Received on Sat Apr 14 2007 - 07:50:05 CDT