This discussion is archived
5 Replies Latest reply: Nov 1, 2013 2:44 PM by Mike Kutz RSS

using application express query tool in a production environment

Guess2 Newbie
Currently Being Moderated

Database: 11.2.0.3

 

another company has the operations contract. We have very limited access to production. We use a special PC that we have virtually no privileges on. it just has a internet explorer and notepad installed. we do not have direct access to the database. right now we are stuck using a home grown web application to run queries to support production. our access to production is through the web application that is stored on a middle tier server. the database is behind a firewall. sql developer wont work even though it only requires an unzip since it wont be able to reach the database. sqlplus wont work (and cant be installled on the pc we use to get to production anyway). isqlplus is long since desupported.

 

this web application we have is pretty bad. I was looking at application express. I have some questions about deploying this. I would just want the 'sql query tool' and thats it.

 

1. we are not allowed to have production database passwords. web apps use the Oracle OID to log into the DB. can we configure application express where to plug in to our OID. we log in as our selves and then application express logs into the appropriate schema?

2. i am supporting several production databases. do i need an application express schema in each db?

3. in order to handle multiple databases , can i configure the query tool screen to have radio buttons or a drop down list of dbs to connect to different DBs? this would work with the OID. im a DBA, not a web developer, Ill have to ask them how the current web application works.

4. any security issues i should know about?

  • 1. Re: using application express query tool in a production environment
    Mike Kutz Expert
    Currently Being Moderated

    Guess2 wrote:

     

    1. we are not allowed to have production database passwords. web apps use the Oracle OID to log into the DB. can we configure application express where to plug in to our OID. we log in as our selves and then application express logs into the appropriate schema?

    2. i am supporting several production databases. do i need an application express schema in each db?

    3. in order to handle multiple databases , can i configure the query tool screen to have radio buttons or a drop down list of dbs to connect to different DBs? this would work with the OID. im a DBA, not a web developer, Ill have to ask them how the current web application works.

    4. any security issues i should know about?

    It would be better to answer these things out of order

     

    Question 2:  APEX on each DB?

    Yes.  You will need to install APEX on each DB

    Unless you have database links, the application(s) can only access data on the database it is on.

     

    Question 1 (and maybe 3)

    Lets say you use the APEX Listener.

    Based on your description of the environment, the Listener will need to run on the other side of the firewall (ie same side as the databases).

    The latest version of the Listener can be configured to 'connect' to multiple Databases....

    so, you will only need one Listener for Production.

    (I haven't messed with the multi-database portion of it...)

    However, each database will need its own (set of) application(s).

     

    The Listener will actually connect as a "Proxy User" (usually, APEX_PUBLIC_USER) which has very limited permissions (eg only CREATE SESSION)

    From there, as you run your application(s) the Listener will "switch users" to the appropriate "parsing schema" (most likely, the same one the current web-app uses)

    The key thing to note:  even if you have individual accounts, all SQL and PL/SQL code will run as that "parsing schema".

    The "parsing schema" can be different than your "workspace [schema]".  And each App can use a different "parsing schema".

    eg APP_NORMAL can use "regular_web_user_schema" as the parsing schema while APP_FOR_SUPERUSER can use "superuser_web_schema" as the parsing schema.

     

    Question 4 Security

    Oracle APEX has an Authentication Plug-in (out-of-the-box) that will work with Oracle's SSO.

    I'm not familiar with OID but it sounds like that the above Authentication Schema is for OID.

     

    Most likely, due to the security of the environment, the "APEX - Runtime Only" will be installed on the production databases.

    This totally removes the ability to use the "SQL Workshop" of APEX.

     

    Now, as far as "query tool" goes, you really need to look at what an Interactive Report [IR] is and what it can do.

    Most likely, your APEX app will have multiple IRs and those IRs will be your new "query tool".

     

    suggestion for you:

    create an account on apex.oracle.com and mess around with it.

  • 2. Re: using application express query tool in a production environment
    TexasApexDeveloper Guru
    Currently Being Moderated

    Mike,

      If they like,  they COULD download the query builder that APEX has installed and try mimicking it, since there are instructions out there on how to find it in the APEX install files.. You can't just load it and try running it due to security constraints that are built in, but they could grab pieces of it and build their own query tool...

     

    Thank you,

     

    Tony Miller

    LuvMuffin Software

  • 3. Re: using application express query tool in a production environment
    Mike Kutz Expert
    Currently Being Moderated

    Tony,

    If that is what the Original Poster is seeking, this would be the 2nd thread that I have seen which has requested such capabilities.

    Again, if true, I suggest that the OP makes a "feature/enhancement request" requesting that the Query Builder be installable in other APEX applications.

    (eg as a Region Plugin)

     

    The primary concern, of course, will be "security".

    The other concern:  will this be taking away sales from other high $$$ Oracle products?

     

    MK

  • 4. Re: using application express query tool in a production environment
    TexasApexDeveloper Guru
    Currently Being Moderated

    Doesn't APEX already do that with the ability to build apps without buying a development tool license??  I know of a few places who have written a tool to do what the user wants, heck if they install APEX 4.2.3, they could use the packaged Reports app that they just released to do some of what they want.. Don't know if it has the EXACT same query tool front-end, but have heard a lot of people LUVING the package and wanting it for users to use..

     

    Thank you,

     

    Tony Miller

    LuvMuffin Software

  • 5. Re: using application express query tool in a production environment
    Mike Kutz Expert
    Currently Being Moderated

    Tony,

    It took me awhile to realize what you said.  I'll rephrase it here:

    "Reports" is the name of a pre-packaged application that comes with APEX 4.2.3.

     

    From the description:

    "Data Reporter" lets end-users easily create and share reports based on available data. Data can be from your own SQL (Structured Query Language) select statements or from Data Sources. Data Sources provide a way for more SQL-savvy users to pre-create queries based on available data including Websheet Data Grid data. Report types include Interactive, Calendar, Dashboard, and PDF and reports can link to each other (providing drill down capabilities) or to outside URLs. Reports can be accessible to all or limited to a specific set of users. Data can also be all accessible to the schema the application is installed in or you can create a whitelist to limit data access.

     

    I like it.

    The "Filter Report" type of report is similar to some other tools we use right now.

    It needs some work to "get there" before the end-users will "jump for joy" over its existence.

     

    Thanks for pointing it out.

     

    MK

Legend

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