OCI User Access Review Made Easy

I’m sure we can all agree, adopting a cloud strategy is awesome. The opportunities and benefits it affords are many. However cloud governance is an ongoing problem that plagues security, compliance, and management teams, which cloud vendors like Oracle are continually trying to solve.

If you’re reading this, you’ve probably been asked, or heard at least once:

Who has access to what in our environment?

Any Security / Compliance Manager

The answer should be easy and simple. However the reality is likely lots of manual time & work, spreadsheets, and endless clicking in a cloud console. If you’re doing this manually then I agree, it’s time that you could be dedicating to more important tasks.

The challenge in trying to answer these questions:

  • What users exist and what groups do they belong to?
  • What does my OCI tenancy compartment structure look like?
  • What policies have users explicitly created?
  • What permissions do users have in my tenancy?
  • Are there any excessive / non-compliant policies & permissions in my tenancy?

is that these complex relationships can’t be easily represented and interpreted in a table-like format. In the OCI ecosystem:

  • users can be federated with an Identity Provider and can belong to one or many federated, or local IAM groups,
  • policies can be defined for “any-user” or for a group,
  • policies are inherited meaning they apply to all sub-compartments from which the policies are applied.

To make things easier I’ve created a solution using Oracle tools and services to simplify the auditing of OCI tenancies and user permissions called “Peek”.

Peek comprises of a Docker container with MySQL, the OCI-CLI & a Ruby script and uses an Oracle APEX Instance to view the data and visualise these complex relationships. Here’s a short video that demonstrates and explains Peek in action:

Running Peek yourself, is easy, and can be set up in as little as 20 minutes. However before we begin there are a few pre-requisites which you’ll need:

Installing Peek into Oracle APEX

First we’ll install and configure the Peek application into your APEX instance. If you don’t have an existing APEX instance, you can create one via the OCI Console.

  1. Create an APEX instance with default settings E.g. Database Version 19c, 1 OCPU, 1TB Storage, or settings that you wish to use.
  2. Log in to your APEX instance with the ADMIN user password you specified during creation.
  3. Click “Create Workspace” and enter
    1. Database User: PEEK
    2. Password: <a strong password>
    3. Workspace Name: PEEK
  4. Click “Sign in to PEEK”, which is shown in the green alert box at the top of the page.
  5. Enter your user credentials created during Step 3.
  6. Download the Peek application ZIP file from Github https://github.com/scotti-fletcher/peek-apex/blob/main/peek-apex-1.0.zip
  7. Select “Import” from the App Builder drop down menu.
  8. Select the downloaded file, ensure File Type “Database Application, Page or Component Export” is selected and click Next.
  9. The File Import Confirmation will display, click Next.
  10. The Install Database Application will be displayed, you can accept all default values. Click “Install Application”.
  11. The Install Application confirmation will display, click Next.
  12. Click Install.
  13. Click “Edit Application”.
  14. Select “SQL Scripts” from the SQL Workshop drop down menu.
  15. Download the Peek application database script from Github https://github.com/scotti-fletcher/peek-apex/blob/main/peek-apex-db.sql
  16. Edit the SQL file and update the values of p_email_address and p_web_password to those you’d like to use. It’s highly recommended you choose a new, unique, strong password.
  17. Click Upload, Select the file you downloaded in Step 15 and edited in Step 16, enter “Install” into Script Name, and click Upload.
  18. Click the Run button on the uploaded script.
  19. Click “Run Now”.
  20. Click “Manage Users and Groups” from the top right menu.
  21. Click the API user.
  22. Click “Group Assignments”
  23. Select all “oracle.dbtools.role.autorest.*” groups from the left list box and add them to the right list box.
  24. Click “Apply Changes”
  25. To confirm your APEX ORDS RESTful services are configured correctly you can run:
    curl -i -k --user API:<password from Step 16> https://<your apex hostname>/ords/peek/grant/
    and you should receive a HTTP 200 response with a JSON body.

If you have an Oracle Identity Domain Cloud Service

This part is optional. However if you are using IDCS, to map and visualise the IDCS / IAM Group relationships we must configure IDCS to allow our Docker container to retrieve the information from the IDCS API.

Note: Oracle Identity Cloud Service is in the process of becoming OCI IAM Identity Domains. I will update this guide to include Identity Domain specific instructions in the near future.

  1. Log in to your Oracle Identity Cloud Service console.
  2. Select Applications from the left-hand menu.
  3. Click “Add”, and select “Confidential Application” to create a new Application.
  4. Name the Application “Peek”, optionally provide a description and click “Next”.
  5. Select “Configure this application as a client now” and check the “Client Credentials” checkbox. Note the checkboxes are on the left side of the word.
  6. Scroll down to “Grant the client access to Identity Cloud Service Admin APIs” and click Add.
  7. Select “Audit Administrator” and click Add.
  8. Scroll up and click Next.
  9. On the “Expose API’s to Other Applications” page leave “Skip for later” selected and click Next.
  10. On the “Web Tier Policy” page leave “Skip for later” selected and click Next.
  11. Click Finish, Your Client ID and Client Secret will be displayed. Note these down in a text file for later and click Close.
  12. Click “Activate”. This is important otherwise your API calls will fail.

Gathering Required Input Values

Before we run the Docker container we first need to gather a few additional details which are required by the Ruby script running in the docker container, specifically:

  • Your OCI Tenancy OCID.
  • The OCI IAM URL.
  • The URL for your APEX Instance.
  • The credentials you created for the APEX API user.
  • The full path to your local directory where your OCI config and .pem key exists.

And, if you have an IDCS Instance:

  • The previously created IDCS Client ID.
  • The previously created IDCS Secret.
  • The URL of your IDCS instance.
  • The OCID of your IDP integration.

Below are instructions on how to obtain these values. I suggest noting them values down in a text file as we will be passing these values as environment variables to our Docker container.

To find your OCI Tenancy OCID

In the OCI Console, navigate to “Identity & Security” and click Compartments. Your Tenancy OCID should be the first line, hover over the OCID and click Copy

In your text editor add a line:

OCI_TENANCY_OCID = <value from clipboard>

To find your IAM URL

Your IAM URL will be https://identity.<oci-region>.oraclecloud.com. You will need to replace “<oci-region>” with one of the regions you are subscribed. As an example my URL looks like https://identity.ap-sydney-1.oraclecloud.com

After making the substitution, add the line to your text editor:

OCI_IAM_URL = https://identity.<oci-region>.oraclecloud.com

To find your APEX URL

Click “Launch Apex” from the OCI console, and copy the Hostname from your web browser address bar. As an example the URL looks like https://yj6g7sq14zvoj-peek.adb.ap-sydney-1.oraclecloudapps.com. Note that there should be no trailing forward slash at the end.

Add this line to your text file:


To find your OCI config Directory

The OCI CLI config typically lives in ~/.oci/. The config file must contain a DEFAULT profile which will be used by the Docker container. The ~/.oci/ directory must also contain the .pem key. If you have multiple profiles in your ~/.oci/config file you may wish to create a separate directory with just the config and .pem key just for this activity. As an example my ~/.oci/ directory looks like:

scott@scott-mac ~ % ls -alh /Users/scott/.oci 
drwxr-xr-x   6 scott  staff   192B 15 Jun 14:59 .
drwxr-x---+ 58 scott  staff   1.8K 20 Jun 11:55 ..
-rw-------@  1 scott  staff   1.7K  7 Apr 15:12 mykey.pem
-rw-------@  1 scott  staff   610B 15 Jun 14:59 config

Note that I’m using the full path to the Directory, and I’ll use this full path when mounting this directory to the Docker container.

If you don’t have federation configured you can skip ahead to Running Peek Docker container

To find your IDCS URL

In the OCI Console, navigate to “Identity & Security” and click Federation, click your Federation Provider, as an example mine is called OracleIdentityCloudService.

Copy the Hostname (without the path) from the “Oracle Identity Cloud Service Console” URL displayed. As an example it looks like https://idcs-5dfhhd61c419aad5d31c8.identity.oraclecloud.com

Add this line to your text editor:


To find your IDP ID

To obtain your IDP ID run the OCI-CLI command:

oci iam identity-provider list --protocol SAML2 --compartment-id <your tenancy OCID>

which will return a JSON response, in it is an “id” field. As an example it looks like ocid1.saml2idp.oc1..aaaaaaaaosb62razrasjddsjdsjjsdsbto655kdfvow5ipfb4i7yina

Add this line to your text file:

IDP_ID = <your IDP OCID>

Lastly add these values to your text file:

IDCS_CLIENT_ID = <Client ID from Step 11>
IDCS_SECRET = <Secret from Step 11>

Running Peek Docker container

Before we run the Docker container, you’ll need to ensure you have all the correct values in your text file. It should contain the following values:

APEX_PASSWORD=<Password from Step 16>
APEX_URL=<Your APEX Hostname>

#If you're using IDCS
IDCS_CLIENT_ID=<Client ID from Step 11>
IDCS_SECRET=<Secret from Step 11>
IDCS_URL=<Your IDCS Hostname>

Pull the Docker container by running the command

 docker pull scottfletcher/oci-peek:latest

Launch the Peek Docker container from the image with the command:

docker run -it --name peek \
--mount type=bind,source=/Full/Path/To/.oci/,target=/root/.oci/,readonly \
-e OCI_TENANCY_OCID=<from text file> \
-e OCI_IAM_URL=<from text file> \
-e APEX_URL=<from text file> \
-e APEX_PASSWORD=<from text file> \
-e IDCS_URL=<from text file> \
-e IDP_ID=<from text file> \
-e IDCS_CLIENT_ID=<from text file> \
-e IDCS_SECRET=<from text file> \

Note: You’ll need to replace the /Full/Path/To/.oci/ value to your local directory as explained earlier. Also ensure there are no spaces between your key name, the equals sign, and the value as shown.

The Docker container will start and the information will populate to your APEX Instance. Populating Users & Groups takes approximately 5-10 minutes, after which time you’ll be able to see those results in your APEX application:

Viewing users in Peek APEX Application

Depending on the number of users, groups, compartments and policies in your tenancy the entire process may take an hour or so to complete.

After mapping the users and groups, compartments and policies the visualisation tree will begin populating. As each user’s permission tree is created, it is pushed to APEX via API and you can start your User Access Review.

When the process has completed, the Ruby script in the container will terminate and you’ll be able to see that in the Docker output in your terminal. You can then stop the Docker container.

To re-run the process, simply start another docker container. All previous results stored in the APEX database will be removed and new results populated.

For more information on how to view and interpret the data in Peek, I recommend watching the video at the beginning of this post.

Some final comments

  • Use strong, randomly-generated passwords as described in this post. Also be conscious of where credentials are stored, and who has access to them.
  • As a security practitioner, I would always recommend reviewing code before running it. The code for the script lives in /peek in the Docker container
  • If you have any issues, would like some help interpreting the results please feel free to contact me at scott.fletcher@oracle.com.
  • Lastly, I’ll be updating the solution to work with IAM Identity Domains in the next release. If you’d like to use Peek before then, please let me know in the comments.

I hope you’ve found this post helpful and Peek a useful tool to help with your OCI User Access Review.

AWR Data Warehouse Repository using Autonomous Database

In Oracle Enterprise Manager (OEM) there is the ability to host an AWR Data Warehouse which enables you consolidate all your detailed performance data of all your database and store in a central location.

This enables you to do long-term analysis trend across your AWR data to determine, performance, capacity impact on the databases in your IT estate.

In OEM 13.5, Oracle now supports the AWR Warehouse repository for Autonomous Data Warehouse.

If you don’t have the infrastructure or capacity to store AWR data on-premise, you can now send your data to the Autonomous Data Warehouse (ADW) in Oracle Cloud (OCI).

There are enormous benefits to using Autonomous Data Warehouse (ADW). One of many benefits is that you can scale up/down cpu and storage whilst the database remains online.

Continue reading “AWR Data Warehouse Repository using Autonomous Database”

Discover Autonomous Database in Enterprise Manager (EM)

In Enterprise Manager (EM) release 13.4 and 13.5, the Autonomous Database can be discovered as a target along with your other target databases on-premise deployments.

In this post I will share with you on how easily you can discover your Autonomous Database.

Continue reading “Discover Autonomous Database in Enterprise Manager (EM)”

#WorldInnovationDay Tech Showcase

April 16-19 saw people from across the world come together to focus on three Sustainable Development Goals (SDGs) at the #WorldInnovationDay Hackathon. This blog highlights the technologies used to help accelerate the team’s execution. If you want to read more about the event itself – check out this previous blog (here).

Over the course of the weekend, Oracle Cloud tenancies with $500 USD credits were provided to each participant to use. It was not mandated that they use Oracle Cloud nor was it given to participants automatically. It was encouraging to see people open to explore and learn with Oracle Cloud. I want to thank the Oracle mentors supported the participants ensuring that whilst the teams explored, they could confidently execute and deliver. Here are the common requests from teams and the cloud services that they used over the weekend.

Continue reading “#WorldInnovationDay Tech Showcase”

#WorldInnovationDay Hack Information Pack

We are providing to each participant access to Oracle Cloud Infrastructure (OCI) for the hackathon. The following resources are to make you effective with OCI.

Oracle Cloud Infrastructure

From past hackathons, we noticed a trend of what technologies and services are being used by the winning teams. Here are the top resources with some additional references.

  • For a single platform to store JSON, Graph & Spatial, perform ML or deliver Low-Code Apps, use Autonomous Database (here)
  • For establishing infrastructure with your OS of choice, use OCI Compute (here)
  • For Jupyter notebooks and ML packages, use Data Science Cloud (here)
  • For quick self-service and data visualisation, use Analytics Cloud (here)
  • For making sure everything is secured, use Oracle Security (here)
  • For most things you need for #AppDev, use Oracle AppDev (here)
Live Labs

You will need to learn quickly. These hands-on workshops will help you learn more about the services available.

  • For those who are interested in a single platform to store data (here)
  • For those who want to #lowcode (here)
  • For adding Blockchain to your datasets (here)
  • For those who are interested in data science (here)
  • For ML on datasets in the database (here)
  • For data storytelling with data visualisation (here)
  • For building microservices applications (here)
Next Steps

There will be a series of workshops where we will be present on different OCI topics in the week leading up to the hackathon. Make sure you have these in your calendar so you don’t miss out.

And reach out on the #WorldInnovationDay Hack 2021 Slack workspace if you need anything from the team.

Adding OAuth to ORDS

Adding security over the APIs across multiple layers was something that we considered when putting this project together. This perspective was reinforced at the #DigitalDefence hackathon in Nov 2020. Check out what happened (here).

We focus on the score and event APIs exposed by Autonomous Data Warehouse.

Here we will focus on the different REST APIs exposing the data hosted by Autonomous Data Warehouse. We started off with HTTP Basic Authentication but quickly turned to using OAuth. Here we’ll explore more about the OAuth side and how to get that started.

Continue reading “Adding OAuth to ORDS”

Kiron – A #VisFSG Project

Over the past couple of weeks, there’s been another Viz for Social Good project that was running. For this project, the supporter was Kiron Open Higher Education (https://kiron.ngo/en/) – an organisation that is providing a learning platform for refugees and underserved communities in the Middle East.

The project was to put a spotlight on refugees and immigrants and was linked to the virtual refugee conference called Amplify Now (https://virtualrefugeeconference.com/). Submitted projects went into the running to be featured at the conference itself.

So what’s my take on this?

Continue reading “Kiron – A #VisFSG Project”

The tech behind the social good

Over the past couple of weeks, there’s been some work by a few of us to #GiveFirst. I’ll share a little context but will get to the tech very quicker.

From March 3rd (2020), an event called Viz for Social Good with Sunny Street (click “here” for the eventbrite for some of the details) was being run. This was a virtual datathon “a data visualisation hackathon” being hosted by Frederic Fery for a company called Sunny Street. Also with what’s happening, the pitch event had gone virtual. I’ll add comments to this post as well get closer or to reflect on the outcomes. The virtual event is live on March 19th on zoom (https://zoom.us/j/9065708856) at 5pm AEST (+10 GMT).

Continue reading “The tech behind the social good”

Connecting Jupyter Notebook to Oracle Autonomous Database

Jupyter Notebook is an open source web application for Machine Learning and Data Exploration.

In this post I will show you how to connect a Jupyter Notebook to Oracle Autonomous Database and explore the data using Python.

The assumption is we already have a Jupyter notebook sandbox running on Oracle Cloud compute instance.


Continue reading “Connecting Jupyter Notebook to Oracle Autonomous Database”

Oracle Integration Cloud Autonomous Transaction Processing Adapter Configuration !!!

Oracle’s two major ground breaking innovation last year were Autonomous Data warehouse (ADW) and Autonomous Database Transaction processing (ATP) both are database offering suitable for different workload and are self-driving, self-securing, and self-repairing in nature. If you want to read more about these services then please go through above links.

ADW/ATP both can be quickly provisioned on Oracle Cloud Infrastructure, it’s take less than 5 minute to spin ADW/ATP instance and database is ready to connect.

User can use Oracle SQL Developer to connect to ADW/ATP database as long as they are supported version. These DBaaS services also offers out-of-box browser based SQL Developer tool which can be used to run any kind of SQL statements.

Here is sample snap of browser based SQL Developer capabilities –



Once user has Database ready, obviously there could be requirement to access data residing inside ADW/ATP instances.

Fortunately, Oracle Integration Cloud provide Adapter for connecting ADW/ATP instance, click here to know more about ATP Adapter capabilities –

In this blog I will be covering simple steps how you can connect to ADW/ATP instances using OIC Autonomous Transaction Processing Adapter (ATP) Adapter.

I made assumption that ADW/ATP instance already exists. if you not sure how to create ADW/ATP instance then refer this blog which was written by my colleague who already explained how to create ADW/ATP database instance and connect from SQL developer.

So, let move forward. Login to your Oracle Integration Cloud (OIC) home page >> Integration >> Connection >> Create >> search for “Oracle ATP” >> select the same


Continue reading “Oracle Integration Cloud Autonomous Transaction Processing Adapter Configuration !!!”

%d bloggers like this: