DBMS_CLOUD is one of the crucial packages in Oracle AI Autonomous Database (ADB from now on). It allows you to perform many operations, including the creation of credentials. These are almost universally required for developers and admins alike.
DBMS_CLOUD.CREATE_CREDENTIAL is an overloaded procedure, its documentation can be found here. I recently experimented with Select AI, and needed to authenticate to the OCI services. One way to do so is by creating the credentials like so:
DBMS_CLOUD.CREATE_CREDENTIAL (
credential_name IN VARCHAR2,
user_ocid IN VARCHAR2,
tenancy_ocid IN VARCHAR2,
private_key IN VARCHAR2,
fingerprint IN VARCHAR2,
comments IN VARCHAR2 DEFAULT NULL);
Completing the call isn’t too hard, all I needed was my ~/.oci/config file and the grant to execute DBMS_CLOUD. The OCI config file I use for Terraform and the command line interface contains everything I needed. If you don’t have a config file, you can get the necessary OCIDs from the OCI Console, which is extensively documented.
The private key you pass must be provided in PEM format. That’s the typical format you use when creating the API keys in the OCI console, something I previously did. The PEM format looks like this:
$ nl not-an-acutal-private-key.pem
1 -----BEGIN PRIVATE KEY-----
2 MIIEvgIBA
[ many more rows skipped ]
27 iEsK31pm.....
28 -----END PRIVATE KEY-----
29 OCI_API_KEY%
The private_key argument in create_credential() requires you to pass anything between -----BEGIN PRIVATE KEY----- and -----END PRIVATE KEY-----. The parameter requires the argument to be a single line. You could now fire up your editor and remove all the newlines, but that’s not a lot of fun. The following bash script does this for you:
#!/usr/bin/env bash
set -euo pipefail
# make sure the tools needed for this trick are available in the path
which sed > /dev/null 2>&1 || {
echo "sed is not found in your path, but it is required"
exit 1
}
which tr > /dev/null 2>&1 || {
echo "tr is not found in your path, but it is required"
exit 1
}
pem_file="${1:-idontexistforsure}"
# enable debug mode if the second parameter is set to "DEBUG"
if [[ "${2:-NO}" == "DEBUG" ]]; then
set -x
fi
# check if this is a private key file
# - must exist and be a file
# - must possess a .pem suffix
# - must contain the magic BEGIN PRIVATE KEY line
if [[ -f "${pem_file}" && "${pem_file##*.}" == "pem" ]] && grep -q PRIVATE "${pem_file}"
then
# seems legit, let's extract the relevant part from the file
key=$(sed -n '/-----BEGIN PRIVATE KEY-----/,/-----END PRIVATE KEY-----/ {
/-----BEGIN PRIVATE KEY-----/d
/-----END PRIVATE KEY-----.*$/d
p
}' "${pem_file}" | tr -d '\n')
cat <<EOF
begin
DBMS_CLOUD.CREATE_CREDENTIAL (
credential_name => 'replace_me',
user_ocid => 'replace_me',
tenancy_ocid => 'replace_me',
private_key => '${key}',
fingerprint => 'replace_me',
comments => 'replace_me'
);
end;
/
-- details:
-- https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/dbms-cloud-subprograms.html
EOF
else
# validation failed
echo
echo "usage: ${0} <private key in PEM format> [ DEBUG ]"
echo "either the file did not exist or wasn't a PEM file"
exit 1
fi
This little script checks for a few things before creating a stub call to DBMS_CLOUD.CREATE_CREDENTIAL with the correct elements of the private key’s file extracted and populated. Hopefully! You sure as hell have to verify the output for correctness, it should be valid, but that cannot be guaranteed. You have been warned 😀
Call it as follows, after storing it in text format somewhere convenient. Assuming you named the file key.sh:
$ bash key.sh my_private_key_file.pem
Alternatively, provide the DEBUG verb to enable debugging. Review the output for correctness: if everything is looking good, all you need to do is add the other values to the call and you’re good to go!
Martins Blog 