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

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 Received on Fri Apr 13 2007 - 20:53:45 CDT