Table: okta_app_assigned_user - Query Okta Assigned Users using SQL

Okta is an identity and access management service that provides secure access to tools and data. It allows organizations to manage their users, groups, and applications across different systems. An Okta Assigned User is a user that has been assigned to an application within Okta, allowing them access to that application.

Table Usage Guide

The okta_app_assigned_user table provides insights into the users assigned to applications within Okta. As a security analyst or administrator, explore user-application associations through this table, including the user's ID, the application's ID, and the assignment's status. Utilize it to uncover information about user access rights, such as which users have access to specific applications, and the verification of user-application associations.

Examples

Basic info

Explore which users are assigned to specific applications in your Okta environment, with details including their ID, username, the time they were created, and their current status. This can help you manage user access and ensure appropriate permissions are maintained.

select
  id as user_id,
  app_id,
  user_name,
  created,
  status
from
  okta_app_assigned_user;

select
  id as user_id,
  app_id,
  user_name,
  created,
  status
from
  okta_app_assigned_user;

List users that are not assigned to any application

Explore which users are not linked to any application, useful for identifying potential unused or inactive accounts. This can aid in optimizing resource allocation and enhancing security measures.

select
  usr.id as id,
  usr.login as login,
  usr.created as created,
  usr.status as status
from
  okta_user usr
  full outer join okta_app_assigned_user au on usr.id = au.id
where
  usr.id is null
  or au.id is null;

select
  usr.id as id,
  usr.login as login,
  usr.created as created,
  usr.status as status
from
  okta_user usr
  left join okta_app_assigned_user au on usr.id = au.id
where
  usr.id is null
  or au.id is null
union all
select
  usr.id as id,
  usr.login as login,
  usr.created as created,
  usr.status as status
from
  okta_user usr
  right join okta_app_assigned_user au on usr.id = au.id
where
  usr.id is null
  or au.id is null;

List applications with assigned user details

This query helps you identify all applications that have users assigned to them, along with the users' details. It's useful for monitoring application usage and managing user access, ensuring security and efficiency in your system.

select
  app.name as app_name,
  app.id as app_id,
  app.label as app_label,
  app.created as app_created,
  app.status as app_status,
  au.id as user_id,
  usr.login as user_login,
  usr.created as user_created,
  usr.status as user_status
from
  okta_application app
  inner join okta_app_assigned_user au on app.id = au.app_id
  inner join okta_user usr on au.id = usr.id;

select
  app.name as app_name,
  app.id as app_id,
  app.label as app_label,
  app.created as app_created,
  app.status as app_status,
  au.id as user_id,
  usr.login as user_login,
  usr.created as user_created,
  usr.status as user_status
from
  okta_application app
  join okta_app_assigned_user au on app.id = au.app_id
  join okta_user usr on au.id = usr.id;

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- okta

You can pass the configuration to the command with the --config argument:

steampipe_export_okta --config '<your_config>' okta_app_assigned_user