Table: consul_acl_role - Query OCI Consul ACL Roles using SQL

Consul ACL Roles are a feature in Oracle Cloud Infrastructure's Consul service. They are used to manage permissions and access control. ACL Roles can be assigned to tokens to grant the token the permissions of the role.

Table Usage Guide

The consul_acl_role table provides insights into ACL Roles within OCI Consul. As a system administrator, explore role-specific details through this table, including role ID, name, and description. Utilize it to manage and monitor access control and permissions within your OCI environment.

Important Notes

You need to specify the token parameter in the consul.spc file to be able to query this table.

Examples

Basic info

Explore the roles within your Consul ACL system to gain insights into their creation and modification indices, as well as their associated namespaces and partitions. This is useful for understanding the structure and organization of your access control system.

select
  id,
  name,
  description,
  create_index,
  modify_index,
  namespace,
  partition
from
  consul_acl_role;

select
  id,
  name,
  description,
  create_index,
  modify_index,
  namespace,
  partition
from
  consul_acl_role;

List roles which are not attached to any service identities

Discover the roles that are not linked to any service identities. This can help in identifying unused roles and aid in system optimization by removing unnecessary elements.

select
  id,
  name,
  description,
  create_index,
  modify_index,
  namespace,
  partition
from
  consul_acl_role
where
  service_identities is null;

select
  id,
  name,
  description,
  create_index,
  modify_index,
  namespace,
  partition
from
  consul_acl_role
where
  service_identities is null;

Show ACL policies attached to a particular ACL role

Determine the access control list (ACL) policies linked to a specific ACL role. This can be helpful in managing and understanding the permissions associated with different roles within your system.

select
  id,
  name,
  rules,
  description,
  create_index,
  modify_index
from
  consul_acl_policy
where
  id in (
    select
      p ->> 'ID'
    from
      consul_acl_role,
      jsonb_array_elements(policies) as p
    where
      name = 'aclRole'
  );

select
  id,
  name,
  rules,
  description,
  create_index,
  modify_index
from
  consul_acl_policy
where
  id in (
    select
      json_extract(p.value, '$.ID')
    from
      consul_acl_role,
      json_each(policies) as p
    where
      name = 'aclRole'
  );

List roles which are attached to ACL tokens

Discover the segments that have roles attached to ACL tokens to understand the user permissions and security settings in your system. This can help in managing access control and identifying potential security risks.

select
  id,
  name,
  description,
  create_index,
  modify_index
from
  consul_acl_role
where
  id in (
    select
      r ->> 'ID'
    from
      consul_acl_token,
      jsonb_array_elements(roles) as r
  );

select
  id,
  name,
  description,
  create_index,
  modify_index
from
  consul_acl_role
where
  id in (
    select
      json_extract(r.value, '$.ID')
    from
      consul_acl_token,
      json_each(roles) as r
  );

Schema for consul_acl_role

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
address_url	text		The address URL.
create_index	bigint		The index when the ACL role was created.
description	text		A human-readable, operator set description that can provide additional context about the ACL role.
hash	jsonb		The hash of the acl role.
id	text	=	The ID of the ACL role.
modify_index	bigint		The index when the ACL role was last modified.
name	text		The name of the ACL role.
namespace	text	=	Namespace is the namespace the ACL role is associated with.
node_identities	jsonb		Node identities attached to the acl role.
partition	text		Partition is the partition the ACL role is associated with.
policies	jsonb		An array of ACL policy links.
service_identities	jsonb		Service identities attached to the acl role.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
title	text		The title of the acl role.

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

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

steampipe_export_consul --config '<your_config>' consul_acl_role