Nice, clear example found on [[https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9526497800346930725|AskTOM]]
* 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!
==== Create a wallet ====
mkdir /home/oracle/wallet
orapki wallet create -wallet /home/oracle/wallet -pwd MyWallePassword999 -auto_login
orapki wallet add -wallet /home/oracle/wallet -trusted_cert -cert /tmp/cert1.cer -pwd MyWallePassword999
orapki wallet add -wallet /home/oracle/wallet -trusted_cert -cert /tmp/cert2.cer -pwd MyWallePassword999
orapki wallet add -wallet /home/oracle/wallet -trusted_cert -cert /tmp/cert3.cer -pwd MyWallePassword999
==== Create an ACL ====
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;
/
==== Add privilege to ACL ====
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;
/
==== Open the wallet and use UTL_HTTP to retrieve a web page ====
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;
/
==== 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.
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;
==== 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
mkstore -wrl $ORACLE_HOME/network/admin -create <
I create a tnsnames.ora entry that I’ll use to connect:
cat >> $ORACLE_HOME/network/admin/tnsnames.ora <
I add a credential for this entry — here SYSTEM user and its password:
mkstore -wrl $ORACLE_HOME/network/admin -createCredential \
CDB1A_SYSTEM SYSTEM <
I also add a credential for Eazy Connect Plus (see this previous post for the dummy parameter):
mkstore -wrl $ORACLE_HOME/network/admin -createCredential \
//localhost/PDB1?_user=system SYSTEM <
Finally, I set the sqlnet.ora for this:
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
Then, I can connect passwordless
connect /@CDB1A_SYSTEM
show user
show con_name
connect /@//localhost/PDB1?_user=system
show user
show con_name
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
expdp /@CDB1A_SYSTEM
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