Back to the basics: How to find users who didn't activate MFA on OCI

Today this came up, I needed to find all the users in a certain Group in my identity domain. Of course console provides this information, if I look at details of all users one by one! But I don't want to do this, it is time consuming, error prone and doesn't scale. Imagine having hundred users!

1 OCI CLI should also provide this information. But not probably with one API call, so let's look at the following script:

First I am trying to queryonly the OCID of the Group with name"Administrators"

oci iam group list --compartment-id $TENANCY_OCID --name Administrators \
 --query 'data[*]."id"' --raw-output

Then after couple of string operations to eliminate paranthesis and double quotes. I am passing the output of the first command which is the OCID of Administrators Group to a second command. This time to find the users with a specific group assignment, and only interested in id and name columns. And a table like output would be better compared to default JSON.

oci iam group list-users --compartment-id $TENANCY_OCID --group-id $GROUP_OCID \
 --query 'data[*].{OCID:id,Name:name}' --output table

As you can see, it is quite handy and reliable. You can check Christian Gohmann's post for neat and tidy samples, link in the references.

2 CLI is good yet not the easiest way though. Especially when combining multiple commands, it requires a certain level of expertise on scripting. So is there an easier solution? Sure there is: Steampipe Since my colleague Jean-Pierre showed this, I am loving it. It has an OCI Plugin , and here is the GitHub Page of the plugin code. Once you install and run it, you will see it makes your day easy as joining couple of tables with an SQL is.

It supports PostgreSQL syntax, and using plugins you can use it for AWS, Azure or OCI. Or you can write your own. Here is another query that I needed. List of users who didn't activated MFA with last login time:

Steampipe Plugin documentation has hundreds of queries provided for different use cases.

Note: From this point on I am sharing my notes on how to do things

Install OCI CLI: On my WSL2 Ubuntu system, I needed to re-install cli. Quickstart is quite handy, script is doing everything for you. Once you install it, you need to configure it by following the prompts:

oci setup config

Extending Steampipe OCI Plugin: I realized the Last Login Date data was not available on plugin table but available on oci cli. So I decided to extend the plugin code. For this purpose:

1.I cloned the git repository as described in GitHub Page .

git clone https://github.com/turbot/steampipe-plugin-oci.git
cd steampipe-plugin-oci

2. I added last_successful_login_time into table definition file table_oci_identity_user.go
3. Installed Go on my Ubuntu host, and added to my path

rm -rf /usr/local/go && tar -C /usr/local -xzf go1.21.1.linux-amd64.tar.gz
export PATH=$PATH:/usr/local/go/bin

4. Then build the plugin from source and configure it as described on plugin GitHub page

make
cp config/* ~/.steampipe/config
vi ~/.steampipe/config/oci.spc

References:
1. OCI CLI Search and Filtering: https://christian-gohmann.de/2021/03/10/forma...
2. Steampipe: https://hub.steampipe.io/plugins/turbot/oci
3. Steampipe OCI Plugin: https://github.com/turbot/steampipe-plugin-oci
4. OCI CLI Installation: https://docs.oracle.com/en-us/iaas/Content/API/SDKDocs/cliin...
5. Install Go: https://go.dev/doc/install
6. Steampipe OCI Plugin Sample Queries: https://hub.steampipe.io/mods/turbot/oc...