Differences

This shows you the differences between two versions of the page.

--- wallet_acl_network_access [2018/12/08 12:49] – created 0.0.0.0
+++ wallet_acl_network_access [2020/10/25 14:00] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-====== Wallet_/_ACL_/_Network_Access ======
 Nice, clear example found on [[https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9526497800346930725|AskTOM]]
-=====Create a wallet=====
+  * Another one by Franck Pachot [[https://medium.com/@FranckPachot/passwordless-data-pump-19c-b21cd1e00c16|Passwordless Data Pump 19c|Passwordless Data Pump 19c]] - duplicated at the bottom of this page in case it disappears!
-<code>0@@</code>
-=====Create an ACL=====
-<code>1@@</code>
-=====Add privilege to ACL=====
-<code>2@@</code>
-=====Open the wallet and use UTL_HTTP to retrieve a web page=====
-<code>3@@</code>
-=====Configure Fine-Grained Access to External Network Services=====
+==== Create a wallet ====
-  * From [[http://docs.oracle.com/cd/B28359_01/server.111/b28300/afterup.htm#UPGRD12428|Oracle 11g Upgrade Manual]]
+<code>
-Oracle Database 11g Release 1 (11.1) includes fine-grained access control to the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, or UTL_INADDR packages using Oracle XML DB.<br />
+mkdir /home/oracle/wallet
-If you have applications that use one of these packages, you must install Oracle XML DB if it is not already installed.<br />
+orapki wallet create -wallet /home/oracle/wallet -pwd MyWallePassword999 -auto_login
-You must also configure network access control lists (ACLs) in the database before these packages can work as they did in prior releases.<br />
+orapki wallet add    -wallet /home/oracle/wallet -trusted_cert -cert /tmp/cert1.cer -pwd MyWallePassword999
-<br />
+orapki wallet add    -wallet /home/oracle/wallet -trusted_cert -cert /tmp/cert2.cer -pwd MyWallePassword999
-The following example first looks for any ACL currently assigned to host_name.<br />
+orapki wallet add    -wallet /home/oracle/wallet -trusted_cert -cert /tmp/cert3.cer -pwd MyWallePassword999
-If one is found, then the example grants user_name the CONNECT privilege in the ACL only if that user does not already have it.<br />
+</code>
+==== Create an ACL ====
+<code>
+begin
+    dbms_network_acl_admin.create_acl ( acl          => 'utl_http.xml'
+                                      , description  => 'my acl'
+                                      , principal    => 'MCDONAC'
+                                      , is_grant     => TRUE
+                                      , privilege    => 'connect'
+                                      , start_date   => null
+                                      , end_date     => null
+                                      );
+    commit;
+end;
+/
+</code>
+==== Add privilege to ACL ====
+<code>
+begin
+    dbms_network_acl_admin.add_privilege ( acl         => 'utl_http.xml'
+                                         , principal   => 'MCDONAC'
+                                         , is_grant    => false
+                                         , privilege   => 'connect'
+                                         , position    => null
+                                         , start_date  => null
+                                         , end_date    => null
+                                         );
+    commit;
+end;
+/
+</code>
+==== Open the wallet and use UTL_HTTP to retrieve a web page ====
+<code>
+set serverout on
+declare
+    l_url            varchar2(100) := 'https://www.litle.com/';
+    l_req            utl_http.req;
+    l_result         utl_http.resp;
+    l_data           varchar2(32767);
+begin
+    utl_http.set_wallet('file:/home/oracle/wallet', 'MyWallePassword999');
+    l_req    := utl_http.begin_request(l_url);
+    l_result := utl_http.get_response(l_req);
+    begin
+        loop
+            utl_http.read_text(l_result, l_data, 1000);
+            dbms_output.put_line (l_data);
+        end loop;
+    exception
+    when utl_http.end_of_body then
+        utl_http.end_response(l_result);
+    end;
+end;
+/
+</code>
+==== Configure Fine-Grained Access to External Network Services ====
+  *  From [[http://docs.oracle.com/cd/B28359_01/server.111/b28300/afterup.htm#UPGRD12428|Oracle 11g Upgrade Manual]]
+Oracle Database 11g Release 1 (11.1) includes fine-grained access control to the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, or UTL_INADDR packages using Oracle XML DB.\\
+If you have applications that use one of these packages, you must install Oracle XML DB if it is not already installed.\\
+You must also configure network access control lists (ACLs) in the database before these packages can work as they did in prior releases.\\
+The following example first looks for any ACL currently assigned to host_name.\\
+If one is found, then the example grants user_name the CONNECT privilege in the ACL only if that user does not already have it.\\
 If no ACL exists for host_name, then the example creates a new ACL called ACL_name, grants the CONNECT privilege to user_name, and assigns the ACL to host_name.
-<code>4@@</code>
+<code>
+DECLARE
+    acl_path  VARCHAR2(4000);
+BEGIN
+    SELECT acl INTO acl_path
+    FROM   dba_network_acls
+    WHERE  host       = 'host_name'
+    AND    lower_port IS NULL
+    AND    upper_port IS NULL;
+    IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl_path, 'user_name', 'connect') IS NULL THEN
+        DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path, 'user_name', TRUE, 'connect');
+    END IF;
+EXCEPTION
+WHEN no_data_found THEN
+    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('ACL_name.xml', 'ACL description', 'user_name', TRUE, 'connect');
+    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('ACL_name.xml','host_name');
+END;
+COMMIT;
+</code>
+==== Create a wallet for use with Data Pump in 19c ====
+Reference: [[https://medium.com/@FranckPachot/passwordless-data-pump-19c-b21cd1e00c16|Passwordless Data Pump 19c]]
+There’s a very light bug with a very simple workaround, but it may require a little change in scripts. If you use passwordless authentication (external password file or OS authentication) with Data Pump in 19c it will ask for the password. The solution is just to answer whatever you want because the external authentication will be used anyway.
+  * [[https://support.oracle.com/epmos/faces/DocContentDisplay?id=2556152.1|Data Pump Prompts For A Password Even Though Using External Authentication (Doc ID 2556152.1)]]
+=== Example ===
+I create the wallet
+<code>
+mkstore -wrl $ORACLE_HOME/network/admin -create <<CREATE
+w4ll3t-P455w0rd
+w4ll3t-P455w0rd
+CREATE
+</code>
+I create a tnsnames.ora entry that I’ll use to connect:
+<code>
+cat >> $ORACLE_HOME/network/admin/tnsnames.ora  <<CAT
+CDB1A_SYSTEM=(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=CDB1A))(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
+CAT
+</code>
+I add a credential for this entry — here SYSTEM user and its password:
+<code>
+mkstore -wrl $ORACLE_HOME/network/admin -createCredential \
+ CDB1A_SYSTEM SYSTEM <<CREATE
+user-P455w0rd
+user-P455w0rd
+w4ll3t-P455w0rd
+CREATE
+</code>
+I also add a credential for Eazy Connect Plus (see this previous post for the dummy parameter):
+<code>
+mkstore -wrl $ORACLE_HOME/network/admin -createCredential \
+ //localhost/PDB1?_user=system SYSTEM <<CREATE
+user-P455w0rd
+user-P455w0rd
+w4ll3t-P455w0rd
+CREATE
+</code>
+Finally, I set the sqlnet.ora for this:
+<code>
+cat >> $ORACLE_HOME/network/admin/sqlnet.ora <<'CAT'
+WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY="$ORACLE_HOME/network/admin")))
+SQLNET.WALLET_OVERRIDE=TRUE
+CAT
+</code>
+Then, I can connect passwordless
+<code>
+connect /@CDB1A_SYSTEM
+show user
+show con_name
+connect /@//localhost/PDB1?_user=system
+show user
+show con_name
+</code>
+Nothing new here. It is an old feature and very simple to setup. In 2019 we do not see any password in clear text in scripts or command line, right?
+Data Pump expdp/impdp
+But let’s try to use the same with Data Pump
+<code>
+expdp /@CDB1A_SYSTEM
+</code>
+I want to connect passwordless because the password is in the wallet, but Data Pump asks me for the password. Don’t worry: it asks but doesn’t care. The connection will use the wallet one. Then the solution is to send /dev/null as the stdin:
+=== “/ as sysdba” ===
+There’s the same when using OS authentication like running expdp or impdp connected SYSDBA. Note that this is not a recommendation: running Data Pump as SYSDBA is not a good idea. But if you don’t want to show the password and you are too lazy to setup a wallet, this was working until 19c. The only thing was to double-quote it so that Data Pump takes it as one parameter, and to single-quote around it so that the shell does not interpret your double quotes:
+''expdp '"/ as sysdba"'''
+Of course, another reason not to use this is that OS authentication connects you to the CDB$ROOT where you should not have a lot to export…
+Fixed in 20.1
+After writing this I realize that there’s now a MOS note about it, a patch and a solution (Upgrade to 20.1 when available): https://support.oracle.com/epmos/faces/DocContentDisplay?id=2556152.1