1 Reply Latest reply on Jan 28, 2015 3:41 PM by partlycloudy

    Execute on DBMS_LOB


      ORDS 2.0.10 on Tomcat 8.0.15

      Oracle Database 11.1


      We recently refreshed our database and tightened up security and to my surprise, APEX with OHS/Apache still works fine but APEX on ORDS gives an error about needing execute on DBMS_LOB.



      1. Is it sufficient to grant EXECUTE access to just APEX_PUBLIC_USER or is a PUBLIC grant required?

      2. More importantly, why does OHS *not* need the grant?!




      Request Path passes syntax validation
      Mapping request to database pool: PoolMap [_failed=false, _lastUpdate=-1, _pattern=null, _poolName=apex, _regex=null, _type=REGEX, _workspaceIdentifier=null, _serviceName=null]
      Applied database connection info
      Attempting to process with PL/SQL Gateway
      ==== Processing Request: ====
      GET /apex/
      ==== Headers in Request ====
      accept: text/html, application/xhtml+xml, */*
      accept-language: en-US
      user-agent: Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7.0; rv:11.0) like Gecko
      accept-encoding: gzip, deflate
      host: foo.bar.com:8080
      connection: Keep-Alive
      cookie: ORA_WWV_REMEMBER_UN=ADMIN:xxx; _ga=GA1.2.130838378.1414603134
      authorization: YIIKWwYGKwYBBQUCoIIK....
      ==== Cookies in Request ====
      _ga =  GA1.2.130838378.1414603134
      Using default page: apex
      SID: 997
      isValidRequest(), procedure name: <apex>
      Validating: apex
      *** Total number of arguments: 1
      Parse: 1 ms
      *** Total number of arguments: 1
      Exec: 13 ms
      get_page FAILED:ORA-06550: line 16, column 7:
      PLS-00201: identifier 'DBMS_LOB' must be declared
      ORA-06550: line 16, column 7:
        • 1. Re: Execute on DBMS_LOB

          Looking closer at the Java error stack, I see this


          at oracle.dbtools.apex.OWA.getPage(OWA.java:258)


          I realize that Oracle's out-of-the-box installation grants access to PUBLIC on all the Oracle-supplied SYS.DBMS_* packages but our environment has heightened security by revoking public access and granting access only to specific accounts that need the access, including APEX_xxxxxx.


          But since this "getPage" is a new component introduced as a side-effect of using ORDS  (as opposed to other deployment options like OHS or EPG), maybe the documentation should make it clear that the minimally privileged database user APEX_PUBLIC_USER needs EXECUTE privilege on SYS.DBMS_LOB when using ORDS.