As you probably know, Oracle 11g introduces new security enhancement “Access Control List" (ACL), which is a list of users and their privileges that are allowed or denied to access specific hosts.
As one of functionalities in our organization, a package need to access HTTPS site outside. After database upgrade, the following error occurred while we ran that .
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1029
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SCOTT.GTESTRS", line 313
ORA-06512: at "SCOTT.GTESTRS", line 201
ORA-06512: at line 1
Besides steps to implement it in prior-11g oracle databases, which is explained in my posting Configuring Wallet Manager to enable HTTPS connect with Oracle 10g Database, the following steps additionally need to be followed to enable HTTPS website access within oracle 11g database.
Step 1: create new Access Control List (ACL)
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => ‘http_access_acl.xml’,
description => ‘Permissions to access web resource’,
principal => ‘SCOTT’,
is_grant => TRUE,
privilege => ‘connect’);
COMMIT;
END;
/
Step 2: create role of “HTTP_ACCESS_ACL"
SQL> create role http_access_acl;
Step 3: grant role “HTTP_ACCESS_ACL" to user “SCOTT"
SQL> grant http_access_acl to scott;
Step4: add privilege “Connect" to role “HTTP_ACCESS_ACL"
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
acl => ‘http_access_acl.xml’,
principal => ‘HTTP_ACCESS_ACL’,
is_grant => TRUE,
privilege => ‘connect’,
position => null);
COMMIT;
END;
/Please note that name of role “HTTP_ACCESS_ACL" need to be capitalized here to get execution success.
Step 5: assign website/host to Access Control List “http_access_acl.xml"
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => ‘http_access_acl.xml’,
host => ‘https://www.oraclepoint.com/Web/XmlAuto/Upload.aspx’);
COMMIT;
END;
/
After that, we need to create new oracle wallet and then add trusted certificate of above https link to oracle wallet on 11g database. To do that, please follow posting Configuring Wallet Manager to enable HTTPS connect with Oracle 10g Database.
Here, I would like to try Oracle Wallet command line utility “orapki" to do that. For more information about “orapki", please refer to http://download.oracle.com/docs/cd/B28359_01/network.111/b28530/asoappf.htm#i634246
Step 1: Create new oracle wallet
db11g> /oracle11/db11g/bin/orapki wallet create – wallet /oracle11/db11g/owm -pwd password
Step 2: Add trusted certificate to oracle wallet
db11g>/oracle11/db11g/bin/orapki wallet add -wallet /oracle11/db11g/owm - trusted_cert -cert /tmp/https_site.cer
Once we finished that, we got success in testing https site access in SQL*PLUS as below.
SQL> connect scott@db11g
Enter password:
Connected.
SQL> select utl_http.request(‘https://www.oraclepoint.com/Web/XmlAuto/Upload.aspx’,null,’file:/oracle11/db11g/owm’,'password’) from dual;UTL_HTTP.REQUEST(‘https://www.oraclepoint.com/Web/XmlAuto/Upload.aspx’
——————————————————————————–
<?xml version="1.0" encoding="utf-8" ?>
<Aizan_XMLAutomation_Response>
<Error>
<Body>
Empty or wrong request XML
</Body>
</Error>
</Aizan_XMLAutomation_Response>
Done!!
Hello Great Job. I think you made some great points in your points and I am goign to do some follow up research topic related and learn more.
Greetings – I must say, I?m happy with your site. I had no problems navigating via all of the tabs and the information was very simple to access. I found what I required in no time at all. Pretty awesome.
How did you make this template? I got a website as well and my template looks kinda bad so people don’t stay on my website very long :/.
Hello, I found your blog in a new directory of blogs. I don’t know how your blog came up, must have been a typo. Your blog looks good, have a nice day.
Hello, I found your blog in a new directory of blogs. I don’t know how your blog came up, must have been a typo. Your blog looks good, have a nice day.
Right after looking into a few of the web site articles on your own web site now, and i also really like your approach regarding blogging and site-building. My spouse and i saved that to my take a note of web site itemizing and might always be looking at rear soon. Please try this site because efficiently as well as tell me how you feel.