Oracle HTTP Requests: Set up the ACL

In order to use utl_http requests from Oracle 11g onward (or any network services for that matter), you need to set up an ACL. Two generic permissions to grant are connect and resolve, where * can be used as a wild card.

A good template I commonly use, just replacing the file name and schema name. This should be executed as a user with dba privileges.



DECLARE

 l_filename varchar2(200) := 'file_test_http.xml';
 l_schema varchar2(200) := 'FILE_TEST';

BEGIN

        BEGIN
            DBMS_NETWORK_ACL_ADMIN.DROP_ACL(
                acl => l_filename
            );

  EXCEPTION WHEN OTHERS THEN
      NULL; -- ACL does not exist yet
 END;

        DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
              acl           => l_filename
            , description   => 'All requests to test utl_http'
            , principal     => l_schema -- schema name
            , is_grant      => TRUE
            , privilege     => 'connect'
        );

        DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
              acl       => l_filename
            , principal => l_schema -- schema name
            , is_grant  => TRUE
            , privilege => 'resolve'
        );

        DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
              acl   =>l_filename
            , host  => '*'
        );
END;
/
commit;

Popular posts from this blog

APEX 5 Give me Interactive Reports

Image
One of the statements of direction for APEX 5 is multiple interactive reports on a single page. Multiple Interactive Reports – Allow any number of Interactive Reports to be defined on a single page. So, time to explore that functionality. One pattern I've used in the past is loading particular saved reports (rather than the default primary report). You may have some pre-defined filters, a row highlight, specific columns. So for demonstrations, I've built an IR on the emp table. Then I go and apply some filters, and save an alternate report: Then in the page designer assign an alias to that report. As you can see, that allows me to enter in the request portion of the URL: IR_SAL_TOTAL. However, if we add a second IR to the page and try to access that saved report, we would receive an error "Report does not exist". This is because it tries to find that that report name in both IR's. To get around this, you MUST  set a static ID property fo...
Read more

OCI: Enabling X11 Forwarding on an Oracle Linux instance

Image
I was connecting to one of my works servers the other day hoping to copy the contents of a file into my clipboard, which would require the use of an X11 forwarded session, but as I connected to the server with X11 forwarding enabled, I was sad to see the following message: X11 forwarding request failed on channel 0 This is on Oracle Linux. Note, if I connect to an Ubuntu instance, I get the message: /usr/bin/xauth:  file /home/ubuntu/.Xauthority does not exist But this file is created, and X forwarding works from the get-go. OK, back to Oracle Linux - how do we fix this? Actually, the fix is a simple one. SSH is already configured to enable X11Forwarding. The other change you need make is to turn off the setting X11UseLocalhost. So part of your configuration would likely look like this: X11Forwarding yes #X11DisplayOffset 10 X11UseLocalhost no After that, you will want to reload the SSH daemon. Do this by running the command: sudo systemctl reload sshd Fina...
Read more

Installing Oracle Instant Client on Ubuntu

Now that Oracle has enabled us to download instant client without any click through for accepting the license , I wanted to revisit a seamless install of the instant client on a new set up. Ubuntu has the documentation about installing the instant client here: https://help.ubuntu.com/community/Oracle%20Instant%20Client. First - because Oracle provides their releases in RPM archive format (or a tarball), in order to have an installer you need to create a DEB archive. There is a package in the archives, alien, which aids this process. This gives the start of the script: #!/bin/bash # Install dependencies sudo apt install alien The 3 packages the Ubuntu documentation tells us to retrieve are: - devel - basic (I opt for basiclite instead) - sqlplus So, over at the downloads page: https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html we can grab the link. # Download files. Example specific to 19.3 # Some links were not correct on the do...
Read more