This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Jan 24, 2013 11:21 PM by fac586 Go to original post RSS
  • 15. Re: How to specify application schema in a table select
    fac586 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points