2 Replies Latest reply on Jan 29, 2013 11:38 AM by user5148377

    Attempts to use example ApEx 4.2 RESTful services results in ORA-28000


      My environment:

      Oracle 11g XE R2 on Windows
      Apex 4.2.1
      GlassFish Server Open Source
      Apex Listener 2 deployed as an application within the above GlassFish server

      My problem:

      When I attempt to test any of the example RESTful services via Apex 4.2, as accessed via SQL Workshop > RESTful Services > oracle.example.hr and test (by clicking the "Test" button) any of the example resource handlers e.g. empinfo/ I see a "500 - Internal Server Error - SQL Error Message: ORA-28000: the account is locked".

      Initially I thought this error may relate to the example "hr" user being locked, however unlocking it still provokes the same error message.

      I have not found any documentation to describe exactly how RESTful services are working from a Db connection perspective, however my guess was that this connection would be facilitated via the APEX_REST_PUBLIC_USER or APEX_LISTENER accounts, which were configured in line with the documented script, @apex_rest_config.sql, as defined in section "3.3.4 Configure RESTful Services" of the ApEx 4.2 installation guide (http://docs.oracle.com/cd/E37097_01/doc/install.42/e35123/otn_install.htm#BABFIBEJ), hence these accounts are not locked either.

      I've also tried creating a new RESTful service from scratch against a new schema, but the same error message as above persists.

      I'm also aware of section "3.3.8 Enable Network Services in Oracle Database 11g" of the Apex 4.2 installation guide, however running the script identified in section " Granting Connect Privileges" has no impact on this problem either.

      Any suggestions as which account is locked, or any other information to assist in resolving this issue would be greatly appreciated.


        • 1. Re: Attempts to use example ApEx 4.2 RESTful services results in ORA-28000
          Hilary Farrell-Oracle
          Hi Jody,

          It sounds like you've followed the installation steps pretty closely, but can I just ask whether you carried out step *3.3.6 Configure APEX_PUBLIC_USER Account* - http://docs.oracle.com/cd/E37097_01/doc/install.42/e35123/otn_install.htm#BABDEEIH in the installation guide? As part of that step, you're required to unlock the APEX_PUBLIC_USER account, and change the password for the account. This step should be carried out before configuring the Listener, as you will be prompted to enter the password for that account during the configuration. So maybe there's a chance the account hasn't been locked? It might just be worth double-checking that all installation steps have been carried out for configuration against the APEX Listener: http://docs.oracle.com/cd/E37097_01/doc/install.42/e35123/otn_install.htm#BABJJAGF. Please let me know whether this resolves your issue.

          • 2. Re: Attempts to use example ApEx 4.2 RESTful services results in ORA-28000
            Hi Hilary,

            Thank you for your reply.

            I had another look at the problem this morning and noticed that the parsing schema for the example ApEx application was set to a Db account that was locked. Unlocking this Db account resolved the problem.

            I find this interesting behaviour given that typically the ApEx connection to the Db is via the APEX_PUBLIC_USER account and the parsing schema is used to establish privileges.
            The error ORA-28000 error message I've experienced suggests that when a request is made to a RESTful service a Db connection is established using the parsing schema. From a security perspective you may wish to use a separate Db account with the minimum privileges necessary to facilitate the RESTful request.

            As I said in my original message, I've not been able to find any documentation that describes how Apex RESTful services are functioning from a Db connection perspective. Is this something that you could explain, or could you possibly post a link to some documentation that describes the Apex RESTful architecture from a connection, authentication and security perspective?

            Kind regards,