dbawiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
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 ====
<code>
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
</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>
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