3 Replies Latest reply: Nov 21, 2010 10:14 AM by Udo RSS

    SQL Report From Another  Schema

    752054
      Is there anyway to select a table from another schema?I have a default schema for my application but i want each user to use their own schema.
      I'm looking for something like #OWNER#.table_name but replacing #OWNER# with the name of another schema in the workspace.

      Kind Regards,
      BgUrsea
      APEX 4.0 10g XE
        • 1. Re: SQL Report From Another  Schema
          Udo
          Hello BgUrsea,

          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
          SELECT schema
            FROM mapping_table
           WHERE owner=v('APP_USER');
          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.

          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.

          -Udo
          • 2. Re: SQL Report From Another  Schema
            752054
            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 .

            Kind Regards
            BgUrsea
            • 3. Re: SQL Report From Another  Schema
              Udo
              Hello,

              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.
              CREATE TABLE tableYZ (
              ...
              );
              GRANT SELECT ON TABLE tableYZ TO <yourdefaultschema>.
              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.

              -Udo