This content has been marked as final. Show 3 replies
you could achieve this by granting the needed privileges (e.g. select, insert, update, delete) on each table in the #OWNER#-schema to your applications parsing schema.
If you can't use a direct mapping (e.g., you can't assign the schema name of a user as usernames for his application user), you probably need a mapping table in your parsing schema. Define an application item (e.g. "F_OWNER") and create a application computation that executes "On New Instance" and does something like
If you aren't sure you always have a mapping, you might think about an application process that not only computes the value but also handles the action to be performed in case there is no mapping for a user.
SELECT schema FROM mapping_table WHERE owner=v('APP_USER');
But I suppose it could be easier to have a copy of you application for each user and assign the appropriate schema to each copy. That way, you don't need the mapping, you don't need to care about always using the #OWNER# when developing your application, and of course, no user can (not even accidently) access another users (or your default) schema.
Thanks for the reply.
How can i grant privileges from one schema to another?In my workspace i have 2 schemas, both will all the privileges.
I don't think that each user should have an application because this will make modifying something impossible.Imagine that i have 30 users each with his own application. If i find a bug i must modify all applications one by one .
well, you could automate distribution of application updates, as you'll have to find a way to distribute update for the data model of you application.
But of course, there are different aspects, and if you decide to use the mapping-solution, it will work as well.
You can grant privileges with the corresponding SQL statement, which you can issue in the "SQL Commands" (SQL Workshop in your Workspace) or by creating a script. In both cases, you can choose the schema where the statement should be executed. The easiest way would probably be to include the grants in the skript you use to create the schemas with, e.g.
For details on grants, I recommend reading the [url http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9013.htm]Oracle SQL Reference.
CREATE TABLE tableYZ ( ... ); GRANT SELECT ON TABLE tableYZ TO <yourdefaultschema>.