1 2 Previous Next 15 Replies Latest reply: Jan 25, 2013 1:21 AM by fac586 Go to original post RSS
      • 15. Re: How to specify application schema in a table select
        fac586
        kvsinfo wrote:
        Hi,
        I think I know what is happening.

        1. I created a workspace "WS1" and application "app1" with schema "dbuser1" (this is my parsing schema)

        2. I then created a new schema "dbuser2" which is my custom application schema where all my tables are and assign this to workspace "ws1".

        workspace "ws1" is now assigned to 2 schemas, "dbuser1" and dbuser2".

        3. for my lov select I put
        (code)
        RETURN 'select ename,empno FROM ' || 'dbuser2' || '.emp'
        (code)

        please note I hard coded the schema name for now to be sure all is OK.

        If I run my app I get an error that the table does not exists.

        Do I need to grant the tables from "dbuser2" to my APEX app1 or to my parsing schema "dbuser1"?
        The required privileges need to be granted to the parsing schema. It's not possible to "grant the tables from 'dbuser2' to my APEX app1". Security works in the standard Oracle way, privileges are granted to schemas, not applications.
        2. I then created a new schema "dbuser2" which is my custom application schema where all my tables are and assign this to workspace "ws1".
        All this does is enable apps in workspace "ws1" to use either "dbuser1" or "dbuser2" as the parsing schema. It's not necessary to add "dbuser2" to the workspace if "dbuser1" has the required privileges granted in the database. The best security practice dictates that "dbuser2" should +not+ be assigned to the workspace if application security is based on the privileges granted to "dbuser1".
        1 2 Previous Next