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”.

Note: If you have an OCI tenancy with IAM Domains instead of IDCS, use these instructions https://redthunder.blog/2023/03/20/oci-iam-domains-user-access-review/ instead of those below.

Note: From 22/05/2023 APEX is no longer required as the solution runs entirely inside the container. To run the new container for OCI with IDCS use the following command:

docker run -it --name peek --rm \
--mount type=bind,source=/Full/Path/To/.oci/,target=/root/.oci/,readonly \ -e OCI_PROFILE_NAME=<from your OCI config> \-e OCI_TENANCY_OCID=<from text file> \
-e OCI_IAM_URL=<from text file> \
-e IDCS_URL=<from text file> \
-e IDCS_CLIENT_ID=<from text file> \
-e IDCS_SECRET=<from text file> \-e TOOLTIP_LINE_PX=20 \
-p 4567:4567 \scottfletcher/oci-peek


After the docker container has started, you can access the web interface using the locally mapped port http://localhost:4567. You should see a progress window:

Once the mapping process is complete the visualisation will appear.

Depending on how long your policy statements are, you may wish to adjust TOOLTIP_LINE_PX to a number greater or smaller than 20. If your policy statements overflow the tooltip box then increase this value, or if the box is too big, then you can decrease this value.

If you haven’t run Peek before, please read on as I explain how to create the required credentials and where to obtain the values for the other environment variables. You can skip the APEX steps, as APEX will not be used.

Continue reading “OCI User Access Review Made Easy”

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.

Prerequisites:

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 –

8.png

 

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

01

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