Friday, April 12, 2024

Using database links with ADB-S with wallet

Autonomous database serverless (ADB-S) users have an option to deploy their instance on either public or private endpoints, whether your connections are made over the pubic internet or through the Virtual client network (VCN), there is one thing in common, they are all secure and uses the Transport layer security (TLS1.2) protocol, so any connection between the client and database is encrypted and both the client and database can authenticate each other. When it comes to authenticating the client and server, there are couple of options.
 
  • Both client and server authenticate each other (mutual TLS)
  • Only the client authenticates the server (one-way TLS)
 
ADB-S uses the mutual TLS by default regardless of network configuration, so both the client and database can verify each other certificates. To complete server side authentication, any client connecting to an ADB-S instance must present their client credentials which can be downloaded as a zip file and contains SSO wallet, keystore, truststore and other network config files, this pretty much sums up how mTLS works and why you need to download a wallet to connect to autonomous database.
 
In this blogpost, we will see about how to create a database link from an ADB-S (source Autonomous Transaction processing 19c) to publicly accessible another ADB-S (Autonomous JSON database 21c)  with a wallet (mTLS)
 
Copy the Target database wallet, cwallet.sso containing the certificates for target database to an object storage bucket.
 
demo-user@ATP19C> variable uri varchar2(200)
demo-user@ATP19C> exec :uri := 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/idcglquusbz6/b/MY_DEMO_BUCKET/o/DBLINK_TEST/';
 
PL/SQL procedure successfully completed.
 
demo-user@ATP19C> select object_name
  2  from dbms_cloud.list_objects('my_demo_cred',:uri);
 
no rows selected

demo-user@ATP19C>
 
use dbms_cloud.get_object to upload the target database wallet into a directory created / available  on the source database.
 
demo-user@ATP19C> begin
  2     dbms_cloud.get_object(
  3             credential_name =>'my_demo_cred',
  4             object_uri => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/idcglquusbz6/b/MY_DEMO_BUCKET/o/DBLINK_TEST/cwallet.sso',
  5             directory_name => 'DATA_PUMP_DIR' );
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
demo-user@ATP19C> select object_name
  2  from table( dbms_cloud.list_files('DATA_PUMP_DIR') )
  3  order by created desc
  4  fetch first 1 row only;
 
OBJECT_NAME
------------------------------
cwallet.sso
 
 
on the ADB-S instance create credentials to access the target database, the username and the password for the dbms_cloud.create_credentials are the credentials to the target database.
 
demo-user@ATP19C> begin
 2    dbms_cloud.create_credential(
 3            credential_name => 'target_db_cred',
 4            username => 'demo_user',
 5            password => 'Good2go1!1234' );
 6 end;
 7 /
 
PL/SQL procedure successfully completed.
 
Then create the database link to the target database using DBMS_CLOUD_ADMIN package, like this
 
demo-user@ATP19C> begin
 2    dbms_cloud_admin.create_database_link(
 3            db_link_name=>'target_db_link',
 4            hostname => 'adb.us-ashburn-1.oraclecloud.com',
 5            port => 1522,
 6            service_name => 'g26be7c92912cdb_ajd21c_low.adb.oraclecloud.com',
 7            credential_name => 'target_db_cred',
 8            directory_name => 'DATA_PUMP_DIR' );
 9 end;
 10 /
 
PL/SQL procedure successfully completed.
 
Then when we try to access the data on target database using database link, it fails like this
 
demo-user@ATP19C> select * from dual@target_db_link;
select * from dual@target_db_link
                  *
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from TARGET_DB_LINK
 
But the real problem is not due to the Incorrect password, instead it was due to USERNAME listed in lowercase, instead it should be in upper case.
 
demo-user@ATP19C> begin
  2     dbms_cloud.create_credential(
  3             credential_name => 'target_db_cred',
  4             username => 'DEMO_USER',
  5             password => 'Good2go1!1234' );
  6  end;
  7  /
 
PL/SQL procedure successfully completed. 

Once that was fixed, the database link works perfect.
 
demo-user@ATP19C> select host from dba_db_links where db_link = 'TARGET_DB_LINK';
 
HOST
-----------------------------------------------------------------------------------------------
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST="adb.us-ashburn-1.oraclecloud.com")(PORT=1522))
(CONNECT_DATA=(SERVICE_NAME=g26be7c92912cdb_ajd21c_low.adb.oraclecloud.com))
(SECURITY=(MY_WALLET_DIRECTORY="/u03/dbfs/E47379BFF4313E4EE0539118000A6636/data/dpdump")
(SSL_SERVER_DN_MATCH=TRUE)))
 
demo-user@ATP19C>
demo-user@ATP19C> select banner_full from v$version;
 
BANNER_FULL
-------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.1.0
 
 
demo-user@ATP19C> select banner_full from v$version@target_db_link ;
 
BANNER_FULL
-------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
 

No comments:

Post a Comment