steampipe plugin install oci

Table: oci_certificates_management_certificate_authority - Query OCI Certificate Management Certificate Authorities using SQL

The Oracle Cloud Infrastructure (OCI) Certificate Management service is a scalable and secure solution for managing digital certificates. It offers a centralized way to create, deploy, and renew certificates, helping to ensure the security of web services and applications. A Certificate Authority (CA) in OCI Certificate Management is a trusted entity that issues digital certificates, which are data files used to cryptographically link an entity with a public key.

Table Usage Guide

The oci_certificates_management_certificate_authority table provides insights into Certificate Authorities within the OCI Certificate Management service. As a security engineer, you can use this table to explore details about each Certificate Authority, including its status, type, and associated endpoints. This can be particularly useful for maintaining an up-to-date inventory of Certificate Authorities, ensuring they are valid and appropriately configured, and identifying any potential security risks.

Examples

Basic info

Explore the details of your organization's digital certificates to understand their current status and configuration. This can be useful for maintaining security standards and ensuring proper certificate management.

select
id,
name,
config_type,
issuer_certificate_authority_id,
description,
time_of_deletion,
kms_key_id,
lifecycle_details,
certificate_authority_rules,
current_version,
certificate_revocation_list_details,
subject,
signing_algorithm,
lifecycle_state as state
from
oci_certificates_management_certificate_authority;
select
id,
name,
config_type,
issuer_certificate_authority_id,
description,
time_of_deletion,
kms_key_id,
lifecycle_details,
certificate_authority_rules,
current_version,
certificate_revocation_list_details,
subject,
signing_algorithm,
lifecycle_state as state
from
oci_certificates_management_certificate_authority;

List inactive certificate authorities

Explore which Certificate Authorities are currently inactive. This is useful to identify potential areas of your system that may lack the necessary security due to inactive authorities.

select
id,
name,
lifecycle_state,
issuer_certificate_authority_id,
current_version
from
oci_certificates_management_certificate_authority
where
lifecycle_state <> 'ACTIVE'
select
id,
name,
lifecycle_state,
issuer_certificate_authority_id,
current_version
from
oci_certificates_management_certificate_authority
where
lifecycle_state <> 'ACTIVE'

List certificates with SHA256_WITH_RSA signed algorithm

Explore which certificates are using the SHA256_WITH_RSA signing algorithm. This is particularly useful for determining the security level of your certificates and identifying any potential risks.

select
id,
name,
current_version,
signing_algorithm,
certificate_authority_rules
from
oci_certificates_management_certificate_authority
where
signing_algorithm = 'SHA256_WITH_RSA';
select
id,
name,
current_version,
signing_algorithm,
certificate_authority_rules
from
oci_certificates_management_certificate_authority
where
signing_algorithm = 'SHA256_WITH_RSA';

List certificates created in the last 30 days

Explore which certificates have been created in the past month. This can be useful in managing and tracking newly issued certificates to ensure they are properly configured and authorized.

select
name,
id,
time_created,
lifecycle_state,
config_type,
issuer_certificate_authority_id,
kms_key_id
from
oci_certificates_management_certificate_authority
where
time_created >= now() - interval '30' day;
select
name,
id,
time_created,
lifecycle_state,
config_type,
issuer_certificate_authority_id,
kms_key_id
from
oci_certificates_management_certificate_authority
where
time_created >= datetime('now', '-30 day');

Get KMS key details of each certificate

Analyze the settings to understand the relationship between each certificate and its corresponding key in Oracle Cloud Infrastructure's Key Management service. This can help in identifying the certificates that are linked to a particular key, thereby aiding in key management and security compliance.

select
a.id,
a.name,
a.kms_key_id,
k.name as key_name,
k.vault_id,
k.current_key_version
from
oci_certificates_management_certificate_authority as a,
oci_kms_key as k
where
k.id = a.kms_key_id;
select
a.id,
a.name,
a.kms_key_id,
k.name as key_name,
k.vault_id,
k.current_key_version
from
oci_certificates_management_certificate_authority as a,
oci_kms_key as k
where
k.id = a.kms_key_id;

Get certificate authority rule details

Explore the rules of your certificate authority to understand the maximum validity duration for both the certificate authority itself and the leaf certificates. This helps in managing the lifecycle of your certificates, ensuring they remain valid and secure.

select
name,
id,
r ->> 'certificateAuthorityMaxValidityDuration' as certificate_authority_max_validity_duration,
r ->> 'leafCertificateMaxValidityDuration' as leaf_certificate_max_validity_duration,
r ->> 'ruleType' as rule_type
from
oci_certificates_management_certificate_authority,
jsonb_array_elements(certificate_authority_rules) as r;
select
name,
id,
json_extract(
r.value,
'$.certificateAuthorityMaxValidityDuration'
) as certificate_authority_max_validity_duration,
json_extract(r.value, '$.leafCertificateMaxValidityDuration') as leaf_certificate_max_validity_duration,
json_extract(r.value, '$.ruleType') as rule_type
from
oci_certificates_management_certificate_authority,
json_each(certificate_authority_rules) as r;

List certificates that are valid upto a certain date

Identify certificates that will remain valid until a specific date. This is useful for planning renewals and managing certificate lifecycles.

select
name,
id,
validity ->> 'timeOfValidityNotAfter' as time_of_validity_not_after
from
oci_certificates_management_certificate_authority
where
time_of_validity_not_after <= '2033-06-04T00:00:00Z';
select
name,
id,
json_extract(validity, '$.timeOfValidityNotAfter') as time_of_validity_not_after
from
oci_certificates_management_certificate_authority
where
json_extract(validity, '$.timeOfValidityNotAfter') <= '2033-06-04T00:00:00Z';

Schema for oci_certificates_management_certificate_authority

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
certificate_authority_rulesjsonbAn optional list of rules that control how the CA is used and managed.
certificate_revocation_list_detailsjsonbDetails about the CA revocation list.
compartment_idtext=The OCID of the compartment in Tenant in which the resource is located.
config_typetextThe origin of the CA.
current_versionjsonbDetails about the current version of the CA.
defined_tagsjsonbUsage of predefined tag keys.
descriptiontextA brief description of the CA.
freeform_tagsjsonbSimple key-value pair that is applied without any predefined name.
idtext=The OCID of the CA.
issuer_certificate_authority_idtext=The OCID of the parent CA that issued this CA. If this is the root CA, then this value is null.
kms_key_idtextThe OCID of the Oracle Cloud Infrastructure Vault key used to encrypt the CA.
lifecycle_detailstextAdditional information about the current CA lifecycle state.
lifecycle_statetext=The current lifecycle state of the certificate authority.
nametext=A user-friendly name for the CA.
signing_algorithmtextThe algorithm used to sign public key certificates that the CA issues.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
subjectjsonbSubject information for the CA.
tagsjsonbA map of tags for the resource.
tenant_idtext=, !=, ~~, ~~*, !~~, !~~*The OCID of the Tenant in which the resource is located.
time_createdtimestamp with time zoneTime that the Certificate Authority was created.
time_of_deletiontimestamp with time zoneAn optional property indicating when to delete the CA version.
titletextTitle of the resource.

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

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

steampipe_export_oci --config '<your_config>' oci_certificates_management_certificate_authority