steampipe plugin install okta

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;

Schema for okta_app_assigned_user

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
app_idtext=Unique key for the application.
createdtimestamp with time zoneTimestamp when application user was last updated.
domaintext=, !=, ~~, ~~*, !~~, !~~*The okta domain name.
emailtext=The email of the application user.
external_idtextThe external ID of the application user.
first_nametext=The first name of the application user.
idtext=Unique key for the application user.
last_nametextThe last name of the application user.
last_synctimestamp with time zoneTimestamp when application user was last synced.
last_updatedtimestamp with time zoneTimestamp when application user was last updated.
linksjsonbThe link details of the application user.
password_changedtimestamp with time zoneTimestamp when application user's password was last changed.
profilejsonbThe profile details of the application user.
scopetextThe scope of the application user.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
statustextThe status of the application user.
status_changedtimestamp with time zoneTimestamp when application user's status was last changed.
sync_statetextThe sync state of the application user.
titletextThe title of the resource.
user_nametext=The username of the application user.

Export

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