Autonomous
Database (ADB-S) users often interact with other resources in Oracle cloud
infrastructure (OCI) to perform various common operations such as accessing the
contents to and from the Object storage locations. In the past we have discussed
about the Credential objects, using this an ADB-S instance can access the other
resources (like OCI Buckets, Vault etc) , today we will discuss about OCI
Resource principal, what it is and how to configure them.
OCI
resource principal is principal type in Oracle Identity and Access Management
(IAM) that eliminates the need to create and configure OCI user credential
objects in the database. In other words, a resource principle
uses a certificate that is frequently refreshed to sign the API calls to
certain OCI services (e.g. Object Storage, Vault) and the authorization is
established through dynamic groups and IAM policies.
In
the remainder of this blogpost, I’m going to demonstrate how to create OCI
resource principal and use that to access OCI object storage contents.
Create
Dynamic Group and policy.
First,
we need to create dynamic group and policy, to be able to use Resource
principal authentication, that is we will be able to tell Identity and Access
management (IAM) that a given ADB-S should be able to access OCI buckets.
1.
In the OCI console, go to Identity &
Security >> Domains >> Dynamic Groups >> Create Dynamic
Groups
2.
Since I want my ADB-S instance to this
Dynamic Group, I need to add OCID of my database instance in the following
rule.
Now that we have created a Dynamic group that
includes our ADB-S instance, we can go ahead and create policy to allow this
resource to access other resources that resides in each compartment / tenancy.
1)
In the OCI console, go to Identity
& Security >> Policy >> create policy
2)
Add your policy statement in the plain
text or using a policy builder
The
above policy allows read and write access to OCI buckets.
Resource
principal is not enabled by default, In order to be able to use resource
principal in our ADB-S instance, we need to enable it using DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL()
method.
admin@ATP19C> EXEC
DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL();
PL/SQL procedure successfully
completed.
The
above step enables resource principal for ADMIN user, if you like other
database users to call DBMS_CLOUD API using resource principal, then ADMIN user
can enable resource principal authentication for other database users as well.
admin@ATP19C> EXEC
DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL('DEMO_USER');
PL/SQL procedure successfully
completed.
admin@ATP19C> EXEC
DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL('DEMO');
PL/SQL procedure successfully
completed.
To
verify the Resource principal is enabled as follows, we can use the below query.
admin@ATP19C> select
grantee
2 from all_tab_privs
3 where table_name ='OCI$RESOURCE_PRINCIPAL'
4 and grantor ='ADMIN';
GRANTEE
---------------------
C##CLOUD$SERVICE
GRAPH$METADATA
DEMO_USER
RAJESH
DEMO
As
a final step of demonstration, we can use the resource principal to access the
object storage contents.
demo-user@ATP19C> variable
uri varchar2(100)
demo-user@ATP19C> exec
:uri := 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/idcglquusbz6/b/MY_DEMO_BUCKET/o/EXPORT_DEMO/';
PL/SQL procedure successfully
completed.
demo-user@ATP19C> select
count(*)
2 from
dbms_cloud.list_objects(
3
credential_name =>'OCI$RESOURCE_PRINCIPAL'
4
, location_uri => :uri);
COUNT(*)
----------
1
As
you might have noticed, OCI$RESOURCE_PRINCIPLE
is the credential_name we need to specify in DBMS_CLOUD APIs whenever we
want to use resource principal authentication.
To
summarize, resource principal is a really neat Oracle IAM capability that
enables your OCI resources to access various OCI services through dynamic
groups and policies. Creating dynamic groups and policies can potentially be a
one-time operation since you can define your dynamic group such that it
includes all existing and future ADB-S instances in a given compartment.
Whenever you provision a new ADB-S instance, all you have to do would be to
enable resource principle for that instance via the DBMS_CLOUD_ADMIN API if the
instance needs to access other OCI services or resources. Much simpler and
easier than creating credential objects via auth
tokens or OCI
native authentication!
In this blogpost we will see about the EZConnect plus syntax of how to connect to autonomous cloud
database. EZConnect is a
simple syntax used in Oracle tools and API to identify the machine and database
that we want to connect to. We might have used an easy connect string like localhost:1521/ORCL
for a local database, it identifies the host machine, port number and the
database service name that is running up there.
In the tnsnames.ora file extracted
from the wallet ZIP, there are several network service name entries. The first
entry will be like this
atpdemo_high = (description=
(retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=g26be7c92912cdb_atpdemo_high.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com,
OU=Oracle BMCS US, O=Oracle Corporation, L=Redwood City, ST=California,
C=US")))
the mapping from tnsnames.ora to
the EZConnect plus string follow the form:
protocol://host:port/service_name?wallet_location=/my/dir&retry_count=N&retry_delay=N
so for the above service, the equivalent
EZConnect string is:
tcps://adb.us-ashburn-1.oraclecloud.com:1522/g26be7c92912cdb_atpdemo_high.adb.oraclecloud.com?wallet_location=C:\oracle\TNSnames\ATP21C
the only non-obvious parameter is the wallet_location
which needs to be set to the directory containing the cwallet.sso file
from the wallet ZIP. The other wallet files, including tnsnames.ora are
not needed when we use EZConnect plus syntax.
For quick testing we can supply the
connecting string to Oracle client tools for connection establishment.
C:\Users\Rajeshwaran>sqlplus
/nolog
SQL*Plus: Release 21.0.0.0.0
- Production on Sat Aug 6 18:59:20 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021,
Oracle. All rights reserved.
idle> conn
demo@'tcps://adb.us-ashburn-1.oraclecloud.com:1522/g26be7c92912cdb_atpdemo_high.adb.oraclecloud.com?wallet_location=C:\oracle\TNSnames\ATP21C'
Enter password:
Connected.
demo@ATP21C> select
banner_full from v$version;
BANNER_FULL
------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 21c
Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
If we are behind a firewall, then HTTPS
option can be used
C:\Users\Rajeshwaran>sqlplus
/nolog
SQL*Plus: Release 21.0.0.0.0
- Production on Sat Aug 6 19:01:28 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021,
Oracle. All rights reserved.
idle> conn demo@'tcps://adb.us-ashburn-1.oraclecloud.com:1522/g26be7c92912cdb_atpdemo_high.adb.oraclecloud.com?wallet_location=C:\oracle\TNSnames\ATP21C&https_proxy=www-proxy-ash7.us.oracle.com&https_proxy_port=80'
Enter password:
Connected.
demo@ATP21C> select
banner_full from v$version;
BANNER_FULL
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 21c
Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
Note: connecting over HTTP is not
recommended for production use due to overheads.
In this blogpost we will see the option
of connecting to the Oracle cloud database using the variable in sqlnet.ora
files
Similar to the first method, this also requires having the Oracle
wallets downloaded and store/extract them under their own location.
Let’s remove the “MY_WALLET_DIRECTORY” from
the TNS entries – so that it will be like this
C:\Users\Rajeshwaran>tnsping
ATPDEMO21C
TNS Ping Utility for 64-bit
Windows: Version 21.0.0.0.0 - Production on 01-AUG-2022 22:45:46
Copyright (c) 1997, 2021,
Oracle. All rights reserved.
Used parameter files:
C:\Oracle\TNSnames\sqlnet.ora
Used TNSNAMES adapter to
resolve the alias
Attempting to contact
(description= (retry_count=20)(retry_delay=3)
(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))
(connect_data=(service_name=g26be7c92912cdb_atpdemo_high.adb.oraclecloud.com))
(security=(ssl_server_cert_dn=CN=adwc.uscom-east-1.oraclecloud.com, OU=Oracle
BMCS US, O=Oracle Corporation, L=Redwood City, ST=California, C=US)))
OK (2820 msec)
C:\Users\Rajeshwaran>tnsping
ATPDEMO19C
TNS Ping Utility for 64-bit
Windows: Version 21.0.0.0.0 - Production on 01-AUG-2022 22:45:53
Copyright (c) 1997, 2021,
Oracle. All rights reserved.
Used parameter files:
C:\Oracle\TNSnames\sqlnet.ora
Used TNSNAMES adapter to
resolve the alias
Attempting to contact
(description= (retry_count=20)(retry_delay=3)
(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))
(connect_data=(service_name=g26be7c92912cdb_atpdemo19c_high.adb.oraclecloud.com))
(security=(ssl_server_cert_dn=CN=adwc.uscom-east-1.oraclecloud.com, OU=Oracle
BMCS US, O=Oracle Corporation, L=Redwood City, ST=California, C=US)))
OK (2490 msec)
We don’t see any reference to MY_WALLET_DIRECTORY
in the above TNS entries. Now this second option requires an environment
variable within, lets name it as DB_NAME_DIR (it is the name of the directory,
where the wallet is stored)
c:\Oracle\TNSnames>type
sqlnet.ora
WALLET_LOCATION = (SOURCE =
(METHOD = file) (METHOD_DATA = (DIRECTORY="C:\oracle\TNSnames\%DB_NAME_DIR%")))
SSL_SERVER_DN_MATCH=yes
Now, before we can connect to the
database, we must explicitly set the DB_NAME_DIR environment variable to
distinguish between the directories where those wallets are stored.
C:\Users\Rajeshwaran>set
DB_NAME_DIR=ATP21C
C:\Users\Rajeshwaran>sqlplus
admin@atpdemo21c
SQL*Plus: Release 21.0.0.0.0
- Production on Mon Aug 1 22:53:14 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021,
Oracle. All rights reserved.
Enter password:
Last Successful login time:
Mon Aug 01 2022 22:33:00 +05:30
Connected to:
Oracle Database 21c
Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
admin@ATPDEMO21C> select
banner_full from v$version;
BANNER_FULL
---------------------------------------------------------------------------
Oracle Database 21c
Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
Now to connect to the other database, we
need to change the environment variables like this
C:\Users\Rajeshwaran>set
DB_NAME_DIR=ATP19C
C:\Users\Rajeshwaran>sqlplus
admin@atpdemo19c
SQL*Plus: Release 21.0.0.0.0
- Production on Mon Aug 1 22:55:07 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021,
Oracle. All rights reserved.
Enter password:
Last Successful login time:
Mon Aug 01 2022 22:33:45 +05:30
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.1.0
admin@ATPDEMO19C> select
banner_full from v$version;
BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.1.0
Now if we try to connect to the other
database without changing the environment variable DB_NAME_DIR, we will get the
error message like this
admin@ATPDEMO19C> $ echo
%DB_NAME_DIR%
ATP19C
admin@ATPDEMO19C> conn
admin@atpdemo21c
Enter password:
ERROR:
ORA-12529:
TNS:connect request rejected based on current filtering rules
Warning: You are no longer
connected to ORACLE.
Today we will see about how to setup
Oracle client, which could seamlessly connect to multiple databases in the
Oracle cloud, for which we need the Oracle wallet.
Lets say we got two databases one is
running on Oracle 21c and other one is a 19c Instance, for which we will
describe two methods of connecting to database.
· First option is to use
MY_WALLET_DIRECTORY (a dynamic parameter) option in tnsnames.ora file
· Second option is to use variable
in sqlnet.ora file
First step for both the options is to
download the Oracle wallet for each database we want to connect – very similar
to the steps listed in the previous
blog post.
For example, lets create two
subdirectories under TNS_ADMIN location one for each database instance
c:\Oracle\TNSnames>echo
%TNS_ADMIN%
C:\Oracle\TNSnames\
c:\Oracle\TNSnames>dir /ad
Volume in drive C is System
Volume Serial Number is C814-8C19
Directory of c:\Oracle\TNSnames
01-Aug-2022 06:22
<DIR> .
27-Jul-2022 09:15
<DIR> ..
01-Aug-2022 06:23
<DIR> ATP19C
01-Aug-2022 06:23
<DIR> ATP21C
0 File(s) 0 bytes
4 Dir(s) 402,418,094,080 bytes free
Download the wallet zip for the first
database – say ATP 21c instance – to the directory ATP21C and store it there,
then extract the contents into that subdirectory, the contents of the extract
should be like this
c:\Oracle\TNSnames\ATP21C>dir
Volume in drive C is System
Volume Serial Number is C814-8C19
Directory of c:\Oracle\TNSnames\ATP21C
01-Aug-2022 06:23
<DIR> .
01-Aug-2022 06:22
<DIR> ..
25-Jul-2022 08:12 6,701 cwallet.sso
25-Jul-2022 08:12 6,656 ewallet.p12
25-Jul-2022 08:12 7,475 ewallet.pem
25-Jul-2022 08:12 3,192 keystore.jks
25-Jul-2022 08:12 691 ojdbc.properties
25-Jul-2022 08:12 2,775 README
25-Jul-2022 08:12 114 sqlnet.ora
25-Jul-2022 08:12 1,840 tnsnames.ora
25-Jul-2022 08:12 3,378 truststore.jks
25-Jul-2022 01:42 26,411 Wallet_ATPDEMO.zip
10 File(s) 59,233 bytes
2 Dir(s) 402,401,660,928 bytes free
Do the same for the second database as
well. So that its content will be like this
c:\Oracle\TNSnames\ATP19C>dir
Volume in drive C is System
Volume Serial Number is C814-8C19
Directory of c:\Oracle\TNSnames\ATP19C
01-Aug-2022 06:23
<DIR> .
01-Aug-2022 06:22
<DIR> ..
25-Jul-2022 01:06 6,701 cwallet.sso
25-Jul-2022 01:06 6,656 ewallet.p12
25-Jul-2022 01:06 7,475 ewallet.pem
25-Jul-2022 01:06 3,190 keystore.jks
25-Jul-2022 01:06 691 ojdbc.properties
25-Jul-2022 01:06 2,792 README
25-Jul-2022 01:06
114 sqlnet.ora
25-Jul-2022 01:06 1,870 tnsnames.ora
25-Jul-2022 01:06 3,378 truststore.jks
25-Jul-2022 06:36 26,416 Wallet_ATPDEMO19C.zip
10 File(s) 59,283 bytes
2 Dir(s) 402,402,549,760 bytes free
While using the dynamic parameter we can
specify the wallet location explicitly in the tnsnames.ora file and we don’t
need to set the wallet_location in other files like sqlnet.ora
Open the sqlnet.ora from TNS_ADMIN
location and remove the line containing WALLET_LOCATION parameter (both entries
got commented out here)
c:\Oracle\TNSnames>type
sqlnet.ora
# WALLET_LOCATION = (SOURCE =
(METHOD = file) (METHOD_DATA = (DIRECTORY="C:\oracle\TNSnames")))
# SSL_SERVER_DN_MATCH=yes
For each connection string we use, we
need to add the following parameters
(MY_WALLET_DIRECTORY=C:\oracle\TNSnames\ATP21C)(SSL_VERSION=1.2)(SSL_SERVER_DN_MATCH=yes)
Here is the example of two network alias,
that we prepared for two different databases, each using a different wallet.
ATPDEMO21C = (description=
(retry_count=20)(retry_delay=3)
(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))
(connect_data=(service_name=g26be7c92912cdb_atpdemo_high.adb.oraclecloud.com))
(security=(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com,
OU=Oracle BMCS US, O=Oracle Corporation, L=Redwood City, ST=California,
C=US")
(MY_WALLET_DIRECTORY=C:\oracle\TNSnames\ATP21C)(SSL_VERSION=1.2)(SSL_SERVER_DN_MATCH=yes)
)
)
ATPDEMO19C = (description=
(retry_count=20)(retry_delay=3)
(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))
(connect_data=(service_name=g26be7c92912cdb_atpdemo19c_high.adb.oraclecloud.com))
(security=(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com,
OU=Oracle BMCS US, O=Oracle Corporation, L=Redwood City, ST=California,
C=US")
(MY_WALLET_DIRECTORY=C:\oracle\TNSnames\ATP19C)(SSL_VERSION=1.2)(SSL_SERVER_DN_MATCH=yes)
))
Now if we try to connect both the
databases
C:\Users\Rajeshwaran>sqlplus
/nolog
SQL*Plus: Release 21.0.0.0.0
- Production on Mon Aug 1 22:32:36 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021,
Oracle. All rights reserved.
idle> conn admin@atpdemo21c
Enter password:
Connected.
admin@ATPDEMO21C> select banner_full from v$version;
BANNER_FULL
-----------------------------------------------------------------------------
Oracle Database 21c
Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
admin@ATPDEMO21C> conn admin@atpdemo19c
Enter password:
Connected.
admin@ATPDEMO19C> select banner_full from v$version;
BANNER_FULL
-----------------------------------------------------------------------------
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.1.0
admin@ATPDEMO19C>
as you can see, there is no extra step on
the client side, each network alias uses different wallet.
To connect your applications to an Oracle
Autonomous cloud database – any of the services like Autonomous Datawarehouse
(ADW), Autonomous transaction processing (ATP) or Autonomous JSON database
(ADJ) – we need to download and use a “wallet” and that gives the mutual TLS
which provides enhanced security for authentication and encryption.
This post shows how to download a wallet
and connect to autonomous database from application tools like SQL*Plus.
This blog post assumes that you got an
access to a Cloud database. During creation ensure that “allow secure access
from everywhere” option is selected and that will give access to the
database from all your applications.
Then Download the Oracle database wallet
files – downloading the wallet is simple, navigate to the database console page
and select “DB Connection”
Then select “download wallet”, this will
prompt for a wallet password. (This password won’t be used at runtime)
C:\Users\Rajeshwaran>cd
c:\Oracle\TNSnames
c:\Oracle\TNSnames>dir
*.zip
Volume in drive C is System
Volume Serial Number is C814-8C19
Directory of c:\Oracle\TNSnames
25-Jul-2022 01:42 26,411 Wallet_ATPDEMO.zip
25-Jul-2022 06:36 26,416 Wallet_ATPDEMO19C.zip
2 File(s) 52,827 bytes
0 Dir(s) 402,420,989,952 bytes free
Once the wallet ZIP file has been
downloaded, extract the content to a different folder (say ATP21C), the
extracted contents will have files like cwallet.sso, sqlnet.ora and tnsnames.ora
files, unlike other Oracle wallet files these files does not container database
credentials, but we need to keep these wallets and its files into a secure
location.
c:\Oracle\TNSnames\ATP21C>dir
Volume in drive C is System
Volume Serial Number is C814-8C19
Directory of c:\Oracle\TNSnames\ATP21C
01-Aug-2022 06:23
<DIR> .
01-Aug-2022 06:22
<DIR> ..
25-Jul-2022 08:12 6,701 cwallet.sso
25-Jul-2022 08:12 6,656 ewallet.p12
25-Jul-2022 08:12
7,475 ewallet.pem
25-Jul-2022 08:12 3,192 keystore.jks
25-Jul-2022 08:12 691 ojdbc.properties
25-Jul-2022 08:12 2,775 README
25-Jul-2022 08:12 114 sqlnet.ora
25-Jul-2022 08:12 1,840 tnsnames.ora
25-Jul-2022 08:12 3,378 truststore.jks
25-Jul-2022 01:42 26,411 Wallet_ATPDEMO.zip
10 File(s) 59,233 bytes
2 Dir(s) 402,412,912,640 bytes free
Now here is the important step, edit the
sqlnet.ora and change the wallet location directory to the directory containing
the cwallet.sso file.
WALLET_LOCATION = (SOURCE =
(METHOD = file) (METHOD_DATA = (DIRECTORY="C:\oracle\TNSnames")))
SSL_SERVER_DN_MATCH=yes
View the tnsnames.ora file, it will
contain some service names – each of these service names will give the choice
of workload capabilities, these “network services” provide different levels of
performance and concurrency for a particular database - that we can use it in
our applications
atpdemo_high = (description=
(retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=g26be7c92912cdb_atpdemo_high.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.uscom-east-1.oraclecloud.com,
OU=Oracle BMCS US, O=Oracle Corporation, L=Redwood City, ST=California,
C=US")))
then we did a TNSPING it went like this
C:\Users\Rajeshwaran>tnsping
atpdemo_high
TNS Ping Utility for 64-bit
Windows: Version 21.0.0.0.0 - Production on 01-AUG-2022 21:37:36
Copyright (c) 1997, 2021,
Oracle. All rights reserved.
Used parameter files:
C:\Oracle\TNSnames\sqlnet.ora
Used TNSNAMES adapter to
resolve the alias
Attempting to contact (description=
(retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=g26be7c92912cdb_atpdemo_high.adb.oraclecloud.com))(security=(ssl_server_cert_dn=CN=adwc.uscom-east-1.oraclecloud.com,
OU=Oracle BMCS US, O=Oracle Corporation, L=Redwood City, ST=California, C=US)))
OK (1390 msec)
Now we can connect from sql*plus and
enter the ADMIN user password that we set when the database was initially
created.
C:\Users\Rajeshwaran>sqlplus
/nolog
SQL*Plus: Release 21.0.0.0.0
- Production on Mon Aug 1 21:40:33 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021,
Oracle. All rights reserved.
idle> conn
admin@atpdemo_high
Enter password:
Connected.
admin@ATPDEMO21C> select
banner_full from v$version;
BANNER_FULL
-------------------------------------------------------------------------
Oracle Database 21c
Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
