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

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.

-- 
Jim Smith
Ponder Stibbons Limited <http://oracleandting.blogspot.com/>
RSS <http://oracleandting.blogspot.com/atom.xml>