💡
Notice: Since we’re about to modify the contents of the database container, it is important to understand that these changes will not persist if we were to destroy this container and create a new one.
## Podman Terminal
Since we’re running the database in a container, and we can’t SSH to that container, we will need to use Podman’s terminal. This terminal give us a basic shell where we can run standard commands from. It’s a little feature-starved, but it will work for what we need.
To access Prodman’s terminal:
1. Open up **Podman**.
2. Select the **Containers** icon (2nd from the top).
3. Click on the three dots and select **Open Terminal**.
![](https://cdn.hashnode.com/res/hashnode/image/upload/v1733953553996/c995c9f9-d8a7-42b3-9d65-ca50716a8646.png align="center")
From here, we’ll kick off the database installation.
## Database Installation
First, let’s create a new directory.
1. In the **Terminal**, enter and run the following command:
```plaintext
mkdir -p /home/oracle/dbc/
```
Next, we will create a script that when run, will install `DBMS_CLOUD`.
2. To create the script, run the following command:
```plaintext
vi /home/oracle/dbc/dbms_cloud_install.sql
```
If you’re not familiar with the `vi` editor, shame on you. Seriously. If that is the case, there’s tons of other resources that will familiarize you with basic commands. I’ll outline precisely what you need to do here, just in case.
3. Once the previous command is run, hit the `I` key to place vi into INSERT mode. You should see `—- INSERT -—` along the bottom of the window.
4. Copy the following script:
```sql
@$ORACLE_HOME/rdbms/admin/sqlsessstart.sql
set verify off
-- you must not change the owner of the functionality to avoid future issues
define username='C##CLOUD$SERVICE'
create user &username no authentication account lock;
REM Grant Common User Privileges
grant INHERIT PRIVILEGES on user &username to sys;
grant INHERIT PRIVILEGES on user sys to &username;
grant RESOURCE, UNLIMITED TABLESPACE, SELECT_CATALOG_ROLE to &username;
grant CREATE ANY TABLE, DROP ANY TABLE, INSERT ANY TABLE, SELECT ANY TABLE,
CREATE ANY CREDENTIAL, CREATE PUBLIC SYNONYM, CREATE PROCEDURE, ALTER SESSION, CREATE JOB to &username;
grant CREATE SESSION, SET CONTAINER to &username;
grant SELECT on SYS.V_$MYSTAT to &username;
grant SELECT on SYS.SERVICE$ to &username;
grant SELECT on SYS.V_$ENCRYPTION_WALLET to &username;
grant read, write on directory DATA_PUMP_DIR to &username;
grant EXECUTE on SYS.DBMS_PRIV_CAPTURE to &username;
grant EXECUTE on SYS.DBMS_PDB_LIB to &username;
grant EXECUTE on SYS.DBMS_CRYPTO to &username;
grant EXECUTE on SYS.DBMS_SYS_ERROR to &username;
grant EXECUTE ON SYS.DBMS_ISCHED to &username;
grant EXECUTE ON SYS.DBMS_PDB_LIB to &username;
grant EXECUTE on SYS.DBMS_PDB to &username;
grant EXECUTE on SYS.DBMS_SERVICE to &username;
grant EXECUTE on SYS.DBMS_PDB to &username;
grant EXECUTE on SYS.CONFIGURE_DV to &username;
grant EXECUTE on SYS.DBMS_SYS_ERROR to &username;
grant EXECUTE on SYS.DBMS_CREDENTIAL to &username;
grant EXECUTE on SYS.DBMS_RANDOM to &username;
grant EXECUTE on SYS.DBMS_SYS_SQL to &username;
grant EXECUTE on SYS.DBMS_LOCK to &username;
grant EXECUTE on SYS.DBMS_AQADM to &username;
grant EXECUTE on SYS.DBMS_AQ to &username;
grant EXECUTE on SYS.DBMS_SYSTEM to &username;
grant EXECUTE on SYS.SCHED$_LOG_ON_ERRORS_CLASS to &username;
grant SELECT on SYS.DBA_DATA_FILES to &username;
grant SELECT on SYS.DBA_EXTENTS to &username;
grant SELECT on SYS.DBA_CREDENTIALS to &username;
grant SELECT on SYS.AUDIT_UNIFIED_ENABLED_POLICIES to &username;
grant SELECT on SYS.DBA_ROLES to &username;
grant SELECT on SYS.V_$ENCRYPTION_KEYS to &username;
grant SELECT on SYS.DBA_DIRECTORIES to &username;
grant SELECT on SYS.DBA_USERS to &username;
grant SELECT on SYS.DBA_OBJECTS to &username;
grant SELECT on SYS.V_$PDBS to &username;
grant SELECT on SYS.V_$SESSION to &username;
grant SELECT on SYS.GV_$SESSION to &username;
grant SELECT on SYS.DBA_REGISTRY to &username;
grant SELECT on SYS.DBA_DV_STATUS to &username;
alter session set current_schema=&username;
REM Create the Catalog objects
@$ORACLE_HOME/rdbms/admin/dbms_cloud_task_catalog.sql
@$ORACLE_HOME/rdbms/admin/dbms_cloud_task_views.sql
@$ORACLE_HOME/rdbms/admin/dbms_cloud_catalog.sql
@$ORACLE_HOME/rdbms/admin/dbms_cloud_types.sql
REM Create the Package Spec
@$ORACLE_HOME/rdbms/admin/prvt_cloud_core.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_task.plb
@$ORACLE_HOME/rdbms/admin/dbms_cloud_capability.sql
@$ORACLE_HOME/rdbms/admin/prvt_cloud_request.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_internal.plb
@$ORACLE_HOME/rdbms/admin/dbms_cloud.sql
@$ORACLE_HOME/rdbms/admin/prvt_cloud_admin_int.plb
REM Create the Package Body
@$ORACLE_HOME/rdbms/admin/prvt_cloud_core_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_task_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_capability_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_request_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_internal_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_admin_int_body.plb
-- Create the metadata
@$ORACLE_HOME/rdbms/admin/dbms_cloud_metadata.sql
alter session set current_schema=sys;
@$ORACLE_HOME/rdbms/admin/sqlsessend.sql
```
5. Select the Podman Terminal window.
6. Paste in the script with `CTRL-V` (PC) or `Command-V` (Mac).
7. Hit the `Esc` key.
8. Type the following: `:wq` and hit return.
Congrats, you’re a `vi` expert now!
Next, we’re going to run the script via `catcon.pl`. This perl script will install `DBMS_CLOUD` into all PDBs as well as the CDB. We’ll pass our freshly minted script as a parameter and run it as `SYS`.
9. Copy the following code, changing `[SYS Password]` to the actual SYS password in the CDB before running.
```plaintext
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl \
-u sys/[SYS Password] \
--force_pdb_mode 'READ WRITE' \
-b dbms_cloud_install \
-d /home/oracle/dbc \
-l /home/oracle/dbc \
dbms_cloud_install.sql
```
This script will run for a few seconds. If successful, you should see something similar to the following:
![](https://cdn.hashnode.com/res/hashnode/image/upload/v1733970516490/0effe862-7e45-46a7-9a49-dd75ce8908ba.png align="center")
Take a moment to browse the log file to see if there’s anything amiss.
Even better - simply connect to the CDB and check to see if `DBMS_CLOUD` is there:
10. From the Podman terminal, type the following: `sqlplus / as sysdba`
11. Once connected, enter and run the following: `desc dbms_cloud`
You should see all of the procedures and functions associated with `DBMS_CLOUD`.
Let’s quickly check our PDB:
12. Enter and run the following command: `alter session set container = freepdb1;`
13. Enter and run the following: `desc dbms_cloud`
Once again, you should see all of the procedures and functions associated with `DBMS_CLOUD`.
# Wallet Installation & Configuration
In Oracle Database 23ai, there’s no need to install and configure a wallet, since all of the root CA certificates from the operating system can be shared. See [this post](https://connor-mcdonald.com/2024/05/04/23ai-leave-your-wallet-at-home/) by Connor McDonald for details.
# Adding ACL Entries
Now that we have `DBMS_CLOUD` installed, we need to create another entry in the database’s ACL so that it can call outbound web services - such as object storage. I’ve trimmed the steps required to do this to assume that we’re running on the Oracle 23ai free container. The steps found on Tim’s blog and the Oracle Support Note are much more comprehensive, and I recommend that you take a look there if your needs fall outside the scope of the container.
Adding ACL entries needs to be done in the CDB this time, not our PDB, so we need to run the following commands from the Podman Terminal again.
1. Navigate to the Podman **Terminal**.
2. Start **SQL\*Plus** and connect as `SYS` by entering the following: `sqlplus / as sysdba`
3. Enter and run the following:
```sql
begin
dbms_network_acl_admin.append_host_ace
(
host =>'*',
lower_port => 443,
upper_port => 443,
ace => xs$ace_type
(
privilege_list => xs$name_list('http', 'http_proxy'),
principal_name => upper('C##CLOUD$SERVICE'),
principal_type => xs_acl.ptype_db)
);
commit;
end;
/
```
We can verify that this worked with the following formatting & query. Since we’re stuck with SQL\*Plus, we’ll have to throw some good ol’ `COL` commands to make the data fit.
4. Enter and run the following:
```sql
col host for a10
col lower_port for a5
col upper_port for a5
col principal for a20
col principal_type for a20
col privilege for a20
set lin 200
SELECT
host
,lower_port as lower
,upper_port as upper
,principal
,principal_type
,privilege
FROM
dba_host_aces
ORDER BY
host
,ace_order
/
```
The output should look similar to this:
```sql
HOST LOWER UPPER PRINCIPAL PRINCIPAL_TYPE PRIVILEGE
---------- ---------- ---------- -------------------- -------------------- --------------------
* GSMADMIN_INTERNAL DATABASE RESOLVE
* 443 443 C##CLOUD$SERVICE DATABASE HTTP_PROXY
* 443 443 C##CLOUD$SERVICE DATABASE HTTP
```
# Creating OCI Components
Remember - what we’ve just spent time creating is the plumbing. It does not give us any specific access into OCI, but rather facilitates the calls when we make them. We still need to present the correct credentials, which need to be mapped to a user, which needs to be mapped to a group, which needs to be mapped to a policy that grants access to the object storage buckets in a specific compartment.
## Create a User, Auth Token & Group
1. Login to the OCI console at [https://cloud.oracle.com](https://cloud.oracle.com) with an administrator account.
2. Using the main menu, navigate to **Identity & Security** > **Domains**.
3. Click **Default** (*current domain*).
4. Click on the **Users** tab. You should see a list of users in your domain.
![](https://cdn.hashnode.com/res/hashnode/image/upload/v1734100456377/20d1a238-9aec-4ccc-9b98-cab5e4ccea75.png align="center")
5. Click **Create User**.
6. Fill out the form, entering a **First Name**, **Last Name** and **Username/Email**. Ensure that **Use the email address as the username** is checked and click **Create**.
![](https://cdn.hashnode.com/res/hashnode/image/upload/v1734100728660/7ed3b1de-c9b8-4aed-922a-f159d990c528.png align="center")
We can dial down this user’s capabilities a bit so that all they are allowed to do is use Auth Tokens.
7. Click the **Edit user capabilities** button.
8. Uncheck all options except **Auth Token**.
![](https://cdn.hashnode.com/res/hashnode/image/upload/v1734100821941/b75c8fa2-0710-43b1-9c83-2d07e32d2fea.png align="center")
9. Click **Save changes**.
Next, let’s create the **Auth Token**.
10. Under the **Resources** section, click **Auth tokens**.
11. Click the **Generate token** button.
12. Enter `Object Storage` for the **Description** and click **Generate Token**.
![](https://cdn.hashnode.com/res/hashnode/image/upload/v1734104131504/32b7c36d-0204-49d0-ba68-b2b506bcc9dd.png align="center")
10. On the next screen, be sure to **copy** the generated token. This will be the only opportunity to do so.
![](https://cdn.hashnode.com/res/hashnode/image/upload/v1734104123035/db304f8c-191d-448d-84d9-b225192de6f8.png align="center")
11. Click **Close** to dismiss the modal.
While we’re here, let’s create a group. This group will he mapped to a policy that we’ll create in the next section that will allow our user & auth token to access object storage buckets.
12. In the breadcrumb, click **Default Domain**.
13. Click the tab for **Groups**.
14. Click **Create Group**.
15. Enter `Read Object Storage` as the Name and be sure to select the user that you created in the previous steps.
![](https://cdn.hashnode.com/res/hashnode/image/upload/v1734123258373/a6623763-0db9-4035-bf45-8906df1338dd.png align="center")
16. Click **Create**.
## Create a Policy
Now that we have a user and auth token, we will need to create a policy that permits access to the object storage buckets, map that policy to a group, and then add our user to that group. That will allow us to use `dbms_credential` to make calls to Object Storage using the auth token associated with our user.
The steps here are similar for what was done to get OCI Email Services integrated with our portable development environment.
1. From the OCI Console, navigate to **Identity & Security** > **Policies**.
2. Click **Create Policy**.
3. Enter `ReadObjectStorage` for the **Name** and `Read Object Storage Policy` for the **Description**.
4. Since we used the Default Domain, set the compartment to the **Root** compartment. That’s the first one on the list and should end with `(root)`.
5. In the **Policy Builder**, set **Policy Use Cases** to **Storage Management**.
6. Set **Common policy templates** to **Let users download objects from Object Storage buckets**.
7. In the middle select list labeled **Select a group**, set it to **Read Object Storage**.
8. In **Location** select list, set it to any compartment and make note of that. We will need to create an Object Storage bucket in that compartment in the next section.
![](https://cdn.hashnode.com/res/hashnode/image/upload/v1734184174341/5bf79742-7d4f-4b06-af76-297b0faf78d1.png align="center")
9. Click **Create**.
At this point, we have configured our OCI security model - the policy includes a specific set of resources and a group and the group includes the user. Thus, the user that we created can call `DBMS_CLOUD` and present their auth token to read any bucket in the compartment we specified.
## Create a Bucket
Before we can test `DBMS_CLOUD`, let’s make sure that we have at least one bucket in the compartment we referenced in the policy.
1. From the OCI Console, navigate to **Storage** > **Buckets**.
2. Set the **Compartment** to the one that you selected in the previous steps.
3. Click **Create Bucket**.
4. Enter `bucket_test` for the **Name**.
![](https://cdn.hashnode.com/res/hashnode/image/upload/v1734184509998/b9c2d791-ef96-457a-85ee-54ce6f42763b.png align="center")
5. Click **Create**.
6. Next, click the name of the bucket that was just created - `bucket_test` - to edit the bucket.
Let’s upload a couple images or files to the bucket so that we can properly test it out. Anything will work.
5. In the **Objects** region, click **Upload**.
6. Drag the file you wish to upload to the region on the page and click **Upload**.
7. Once the file uploads, click the **Close** button.
Repeat steps 5 through 7 a couple of times so that you have a few files to test with.
Before we leave the Object Storage page, we need one more thing - the URL that we will use to access the button. The easiest way to get this it to edit one of the file and copy it from there.
8. In the **Objects** region, click the three dots next to any of your files and select **View Object Details**.
![](https://cdn.hashnode.com/res/hashnode/image/upload/v1734185156335/d72055e0-b642-4fd5-9e8a-626791153764.png align="center")
9. Copy the value of the **URL Path (URI)**, as we will need that in the next section.
10. Click **Cancel** to dismiss the modal window.
## Create a DBMS\_CREDENTIAL
We’re almost there! The last thing we need to do before testing is create a `DBMS_CREDENTIAL`. This `DBMS_CREDENTIAL` will allow us to store our username and auth token in a secure place in the database. When we do call `DBMS_CLOUD`, we’ll pass in the `DBMS_CREDENTIAL` that we create here, so that `DBMS_CLOUD` can properly authenticate when making its calls.
The `DBMS_CREDENTIAL` should be created in the same schema that your APEX application will parse as. Thus, in our case, we’ll stick with the `DEMO` schema that we created a while back.
1. Open a new terminal window.
2. Connect to the database as the `DEMO` user, using the following command, replacing `[demo password]` with the password of the `DEMO` schema:
```plaintext
sql demo/[demo password]@localhost:1521/freepdb1
```
3. Once, connected, run the following command, replacing `[email address]` with the **Username** that you created previously and `[auth token]` with the value of the **Auth Token** that you copied earlier.
```sql
begin
dbms_credential.create_credential
(
credential_name => 'obj_store_cred'
,username => '[email address]'
,password => '[auth token]'
);
end;
/
```