Nice, clear example found on AskTOM
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
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;
/
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;
/
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;
/
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;
Reference: 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.
I create the wallet
mkstore -wrl $ORACLE_HOME/network/admin -create <<CREATE w4ll3t-P455w0rd w4ll3t-P455w0rd CREATE
I create a tnsnames.ora entry that I’ll use to connect:
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
I add a credential for this entry — here SYSTEM user and its password:
mkstore -wrl $ORACLE_HOME/network/admin -createCredential \ CDB1A_SYSTEM SYSTEM <<CREATE user-P455w0rd user-P455w0rd w4ll3t-P455w0rd CREATE
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 <<CREATE user-P455w0rd user-P455w0rd w4ll3t-P455w0rd CREATE
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:
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