6 Replies Latest reply on Jul 23, 2013 3:48 PM by Joe Upshaw

    Not Your Usual ACL Issue

    Joe Upshaw

      Here are the schemas that are involved (that I know of):

       

      1. APEX040200 - The owner of the APEX installation
      2. APEX_PRS_MAPS - The parsing schema of the application
      3. APEX_MAPS - The owner of the PL/SQL function to be called from APEX
      4. APEX_PUBLIC_USER - Included as a desperate attempt to get this to work.

       

      I have a small PL/SQL function wrapper (owned by APEX_MAPS) that calls the APEX_WEB_SERVICE.MAKE_REST_REQUEST method.

       

      CREATE OR REPLACE FUNCTION APEX_MAPS.NOTIFY_CLIENTS( as_MessageType IN VARCHAR2,

                                                           as_MessageTitle IN VARCHAR2,

                                                           as_Message IN VARCHAR2 )

      RETURN CLOB

      AUTHID DEFINER

      IS

          lclb_WebResponse CLOB;

       

          CURSOR lcsr_GetWebSocketPort IS

              SELECT PARAMETER_VALUE AS WEB_SOCKET_PORT

              FROM MAPS_PARAMETER

              WHERE PARAMETER_NAME = 'WEB_SOCKET_PORT';

       

          ls_WebSocketPort    MAPS_PARAMETER.PARAMETER_VALUE%TYPE;

       

          WEB_SOCKET_HOST CONSTANT VARCHAR2(64) := SYS_CONTEXT( 'USERENV', 'SERVER_HOST' ) ;

       

      BEGIN

       

       

          OPEN lcsr_GetWebSocketPort;

          FETCH lcsr_GetWebSocketPort INTO ls_WebSocketPort;

          CLOSE lcsr_GetWebSocketPort;

       

       

          lclb_WebResponse := APEX_WEB_SERVICE.MAKE_REST_REQUEST

                              ( p_url         => WEB_SOCKET_HOST || ':' || ls_WebSocketPort,

                                p_http_method => 'POST',

                                p_body        => '{"message":"'|| as_MessageType ||

                                                 '|' || as_MessageTitle ||

                                                 '|' || as_Message || '"}' );

                            

          RETURN lclb_WebResponse;

                               

      END;

      /

       

      I can successfully execute this as all of the above users from within TOAD,SQL*Plus, etc.

       

      However, *only* when I try to call this same function from APEX, I get the following error stack every time:

       

      sqlerrm:ORA-29273: HTTP request failed

      ORA-06512: at "SYS.UTL_HTTP", line 1130

      ORA-24247: network access denied by access control list (ACL)

       

      Here are some sanity check query results:

       

      SELECT * FROM DBA_NETWORK_ACLS;

                                                                                                                

      HOSTLOWER_PORTUPPER_PORTACL
      mailhost
      NULL
      NULL
      /sys/acls/mailserver_acl.xml
      pdk-hosty-host
      1337
      1337
      /sys/acls/nodejs_access.xml
      mailhost
      25
      25
      /sys/acls/email_smtp.xml
      *
      NULL
      NULL
      /sys/acls/Resolve_Access.xml
      10.4.299.299
      1337
      1337
      /sys/acls/nodejs_access.xml
      10.3.199.99
      1337
      1337
      /sys/acls/nodejs_access.xml

       

      SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES;

                                                                                                                                                                                                                              

      ACLPRINCIPALPRIVILEGEIS_GRANTINVERTSTART_DATEEND_DATE
      /sys/acls/email_smtp.xmlRISKDM2connecttruefalse07/17/2012 2:02:04.035107000 PM -04:00
      /sys/acls/email_smtp.xmlICEENTconnecttruefalse
      /sys/acls/Resolve_Access.xmlICEENTresolvetruefalse
      /sys/acls/mailserver_acl.xmlAPEX_040200connecttruefalse
      /sys/acls/nodejs_access.xmlAPEX_040200connecttruefalse
      /sys/acls/nodejs_access.xmlAPEX_MAPS_PRSconnecttruefalse
      /sys/acls/nodejs_access.xmlAPEX_MAPSconnecttruefalse
      /sys/acls/nodejs_access.xmlAPEX_PUBLIC_USERconnecttruefalse

       

      What am I missing?

       

      -Joe

        • 1. Re: Not Your Usual ACL Issue
          Tom Petrus

          It looks ok on first sight, can't spot any obvious errors. Public user shouldn't get the rights. The function is in apex_maps, so in apex it will go through apex_maps_prs. I don't think any other user than apex_040200 and apex_maps_prs need rights.

          Did you run the function from the sql workshop too? Result should be the same as if you are running from a sql sheet for user apex_maps_prs.

          Is the WEB_SOCKET_HOST an ip or a hostname? Could you try giving apex_maps/apex_maps_prs/apex_040200 resolve rights aswell?

          • 2. Re: Not Your Usual ACL Issue
            Joe Upshaw

            Thanks, for replying, Tom.

             

            Agreed that it shouldn't be necessary to grant to the public user...note my tongue and cheek response "Included as a desperate attempt to get this to work" ;-)


            I actually created ACLs for both the host name and IPs (pdk-hosty-host and 10.4.299.299). I will give it a try form Workshop.


            I don;t think resolve will help. The documentation says: "...the resolve privilege has no effect to those with a port range" Also, remember, as all of these users, it works outside of APEX. It seems that APEX is sending something unexpected (???).


            I also opened a tar but, of course, am just getting the canned response."As of 11g, Oracle has implemented..." frustrating!


            In the absence of someone just saying, "Oh, I see what you missed, do this" if someone could just say, these are the steps to figure out what ACL is missing, I'd be happy.


            Thanks,


            -Joe

            • 3. Re: Not Your Usual ACL Issue
              riedelme

              Review the info on setting up and using ACL in the docs. You may have missed a step.

              • 4. Re: Not Your Usual ACL Issue
                Joe Upshaw

                You can't possibly have thought that would be helpful so; I am very perplexed why you even bothered to respond at all with an answer like that, Riedelme.


                Why don't we just tell every poster on the forum to go read the manual because they might have missed something ?

                 

                I think I provided enough information in the original post to have demonstrated that the question was more complicated that a RTFM response would have warranted.

                • 5. Re: Not Your Usual ACL Issue
                  Mike Kutz

                  It looks like you have everything for the ACL to work.

                  It sounds like you have tested it from APEX_* accounts... and it does work.

                  As such, you already did the RTFM step.

                   

                  I've have discovered that the HOST name in the ACL must match exactly to what you use.

                  IP address (10.4.299.299), shortname (mailhost), and FQDN (mailhost.mycompany.com) are three different HOSTs as far as 11g is concerned.

                   

                  At first glance, I'm going to guess that the VPD-esq code SYS_CONTEXT() may not be returning the value you are expecting.

                   

                  You will need to add some debuging code in your procedure to find out what server/port it is trying to use.

                  APEX_DEBUG_MESSAGE may be useful in this situation.  I haven't used it, yet.

                  • 6. Re: Not Your Usual ACL Issue
                    Joe Upshaw

                    Oh, how stuff likes this drives me crazy. I changed nothing. *NOTHING*

                     

                    and now it is working.

                     

                    The only thing that I can figure is that, perhaps, the connections from APEX to the DB had to be recycled to pick up the ACL changes.

                     

                    Thanks, to everyone who answered (even you Riedelme).

                     

                    -Joe