Table: servicenow_sys_user_has_role - Query ServiceNow User Roles using SQL

ServiceNow User Roles is a feature within ServiceNow that allows administrators to manage access levels and permissions for individual users. It provides a structured way to assign and monitor roles for various users, including administrators, developers, and IT support staff. ServiceNow User Roles helps in maintaining security and efficient workflow by ensuring that users have appropriate access to perform their tasks.

Table Usage Guide

The servicenow_sys_user_has_role table provides insights into User Roles within ServiceNow. As a system administrator or IT manager, explore user-specific role details through this table, including assigned roles and related metadata. Utilize it to uncover information about user access levels, such as those with administrative permissions, the relationships between users and their roles, and the verification of user permissions.

Examples

Which users have been granted a specific role through inheritance?

This query allows you to identify which users have been assigned a specific administrative role through inheritance. It's useful in managing user permissions and understanding the distribution of administrative roles in your system.

select
  uhr.user ->> 'value' as user_sys_id,
  u.name
from
  servicenow_sys_user_has_role uhr
  join servicenow_sys_user_role r on uhr.role ->> 'value' = r.sys_id
  join servicenow_sys_user u on uhr.user ->> 'value' = u.sys_id
where
  r.name = 'user_admin'
  and uhr.inherited = true;

select
  json_extract(uhr.user, '$.value') as user_sys_id,
  u.name
from
  servicenow_sys_user_has_role uhr
  join servicenow_sys_user_role r on json_extract(uhr.role, '$.value') = r.sys_id
  join servicenow_sys_user u on json_extract(uhr.user, '$.value') = u.sys_id
where
  r.name = 'user_admin'
  and uhr.inherited = 1;

What is the total number of roles granted?

Analyze the settings to understand the total count of unique roles granted to users in your ServiceNow instance. This can help in assessing the distribution of responsibilities and privileges within your team.

select
  count(distinct role ->> 'value') as total_roles_granted
from
  servicenow_sys_user_has_role;

select
  count(distinct json_extract(role.value, '$.value')) as total_roles_granted
from
  servicenow_sys_user_has_role,
  json_each(role);

Which roles have been granted to a specific user?

Determine the areas in which a particular user has been assigned roles. This helps in understanding the user's permissions and access levels within the system.

select
  r.name as role_name
from
  servicenow_sys_user_role r
  join servicenow_sys_user_has_role uhr on uhr.role ->> 'value' = r.sys_id
where
  uhr.user ->> 'value' = 'd8f57f140b20220050192f15d6673a98';

select
  r.name as role_name
from
  servicenow_sys_user_role r,
  servicenow_sys_user_has_role uhr
where
  json_extract(uhr.role, '$.value') = r.sys_id
  and json_extract(uhr.user, '$.value') = 'd8f57f140b20220050192f15d6673a98';

How many users have been granted each role?

Determine the distribution of user roles within your system to understand the level of access granted to different users. This can aid in security audits by identifying potential over-privileged users.

select
  r.name as role_name,
  count(distinct uhr.user ->> 'value') as user_count
from
  servicenow_sys_user_role r
  join servicenow_sys_user_has_role uhr on uhr.role ->> 'value' = r.sys_id
group by
  r.name;

select
  r.name as role_name,
  count(distinct json_extract(uhr.user, '$.value')) as user_count
from
  servicenow_sys_user_role r
  join servicenow_sys_user_has_role uhr on json_extract(uhr.role, '$.value') = r.sys_id
group by
  r.name;

Which users have been granted a role with elevated privileges?

Identify instances where users have been given roles with increased access rights. This can be useful for auditing purposes, ensuring only authorized personnel have such privileges.

select
  distinct uhr.user ->> 'value' as user_sys_id
from
  servicenow_sys_user_has_role uhr
  join servicenow_sys_user_role r on uhr.role ->> 'value' = r.sys_id
where
  r.elevated_privilege = true;

select
  distinct json_extract(uhr.user, '$.value') as user_sys_id
from
  servicenow_sys_user_has_role uhr
  join servicenow_sys_user_role r on json_extract(uhr.role, '$.value') = r.sys_id
where
  r.elevated_privilege = 1;

Schema for servicenow_sys_user_has_role

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
granted_by	jsonb	User or role that granted this role to the user.
included_in_role	jsonb	Role in which this role is included.
included_in_role_instance	jsonb	Role instance in which this role is included.
inh_count	bigint	Count of inherited roles.
inh_map	text	Mapping of inherited roles.
inherited	boolean	Indicates if the role is inherited.
role	jsonb	Role assigned to the user.
state	text	State of the role assignment.
sys_created_by	text	User who created the record.
sys_created_on	timestamp with time zone	Date and time when the record was created.
sys_id	text	Unique system identifier for the record.
sys_mod_count	bigint	Number of times the record was modified.
sys_updated_by	text	User who last updated the record.
sys_updated_on	timestamp with time zone	Date and time when the record was last updated.
user	jsonb	User to whom the role is assigned.

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)" -- servicenow

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

steampipe_export_servicenow --config '<your_config>' servicenow_sys_user_has_role