3 Replies Latest reply: Jul 19, 2013 4:18 AM by kron777 RSS

    APEX DB Schemas

    kron777

      Application Express 4.2.2.00.11

       

      I have a custom schema "XX1" that contains all of my tables.

      I have a custom schema "XX2". My workspace is referencing "XX2".

       

      For various reasons, I can't set my workspace to XX1 (standards, security etc). I grant select, insert on XX1.table1 to XX2 and created a synonym for XX1.table in XX2's schema but I can't reference this table in any of the wizard drop downs.

       

      What's the recommended way of giving access to XX1 schema without actually putting the workspace on top of it?

        • 1. Re: APEX DB Schemas
          Mike Kutz

          kron,

          In order for the wizards in APEX Developer to 'see' the data and code schemas, you have to "link" them to the workspace.

           

          This is done within the APEX Admin portal:

          "manage workspace" -> "Manage workspace to schema assignment"

          Add your other schema(s) there.

          As far as I can tell, APEX does not do any grants.  it just adds a row to the table that the wizards use for their LOVs.

           

          Interesting enough, if you write the SQL or PL/SQL yourself, you don't need to 'link' the workspaces.

          (but you still need to have the correct 'grants')

          • 2. Re: APEX DB Schemas
            InoL

            The wizard won't pick up synonyms (at least, that is what I noticed too). The usual way around this is to create a view in XX2:

             

            create view my_table

            as select * from XX1.my_table

             

            There was a good blog by Scott Spendolini about this approach (Secure Apex Development), but a link I had for this is now invalid.

             

            Oh, here is a copy:

            http://eastcoastoracle.org/PDF_files/Presentations/SpendoliniSecure%20APEX%20Development.pdf

            • 3. Re: APEX DB Schemas
              kron777

              Thanks Inol, that's a good pdf - pages 37-40 are pretty much spot on to what I need although I'm keen to avoid having to write the DML myself as the latest tabular form wizard seems to do a good job of it. I'll go down the path of views at this point and if there's a complex requirement, I guess i'll write the DML.

               

              Cheers!