Table: oci_certificates_management_certificate - Query OCI Certificates Management Certificates using SQL
Oracle Cloud Infrastructure (OCI) Certificates Management is a service that helps in managing and controlling SSL/TLS certificates. It allows users to create, import, and manage SSL/TLS certificates for their cloud-based applications and resources. This service ensures that the certificates are valid, trusted, and can be used for secure connections.
Table Usage Guide
The oci_certificates_management_certificate
table provides insights into the SSL/TLS certificates managed by OCI Certificates Management. As a security analyst, you can delve into certificate-specific details through this table, including certificate type, validity, and associated metadata. Utilize it to uncover information about certificates, such as their expiration dates, the resources they're associated with, and their overall status within your OCI environment.
Examples
Basic info
Discover the details of your certificates, including their lifecycle state and associated metadata. This is useful to understand the status and configuration of your certificates for better management and security compliance.
select id, name, config_type, issuer_certificate_authority_id, description, certificate_rules, time_of_deletion, lifecycle_details, current_version, subject, certificate_revocation_list_details, key_algorithm, signature_algorithm, certificate_profile_type, lifecycle_state as statefrom oci_certificates_management_certificate;
select id, name, config_type, issuer_certificate_authority_id, description, certificate_rules, time_of_deletion, lifecycle_details, current_version, subject, certificate_revocation_list_details, key_algorithm, signature_algorithm, certificate_profile_type, lifecycle_state as statefrom oci_certificates_management_certificate;
List imported certificates
Explore imported certificates to understand their lifecycle state, rules, and signature algorithm. This can be helpful in managing and understanding the security and validity of certificates in your environment.
select name, id, certificate_rules, lifecycle_state, config_type, signature_algorithmfrom oci_certificates_management_certificatewhere config_type = 'IMPORTED';
select name, id, certificate_rules, lifecycle_state, config_type, signature_algorithmfrom oci_certificates_management_certificatewhere config_type = 'IMPORTED';
List failed certificates
Discover the segments that have failed certificates to gain insights into potential security risks or issues in your system. This is useful for quickly identifying and addressing problematic areas, enhancing the overall security of your system.
select name, id, current_version, subject, certificate_revocation_list_details, key_algorithmfrom oci_certificates_management_certificatewhere lifecycle_state = 'FAILED';
select name, id, current_version, subject, certificate_revocation_list_details, key_algorithmfrom oci_certificates_management_certificatewhere lifecycle_state = 'FAILED';
List certificates created in the last 30 days
Discover the segments that have been certified within the last month. This is useful for tracking recent changes and ensuring system security.
select name, id, current_version, key_algorithm, time_created, time_of_deletionfrom oci_certificates_management_certificatewhere time_created >= now() - interval '30' day;
select name, id, current_version, key_algorithm, time_created, time_of_deletionfrom oci_certificates_management_certificatewhere time_created >= datetime('now', '-30 day');
Get the current version details of each certificate
Explore which certificates are currently in use and their respective details. This is useful for tracking certificate versions and their revocation status, which aids in maintaining secure connections.
select name, id, current_version ->> 'certificateId' as certificate_id, current_version ->> 'issuerCaVersionNumber' as issuer_ca_version_number, current_version ->> 'revocationStatus' as revocation_status, current_version ->> 'serialNumber' as serial_number, current_version ->> 'stages' as stagesfrom oci_certificates_management_certificate;
select name, id, json_extract(current_version, '$.certificateId') as certificate_id, json_extract(current_version, '$.issuerCaVersionNumber') as issuer_ca_version_number, json_extract(current_version, '$.revocationStatus') as revocation_status, json_extract(current_version, '$.serialNumber') as serial_number, json_extract(current_version, '$.stages') as stagesfrom oci_certificates_management_certificate;
Schema for oci_certificates_management_certificate
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form. | |
certificate_profile_type | text | The name of the profile used to create the certificate. | |
certificate_revocation_list_details | jsonb | Certificate revocation details. | |
certificate_rules | jsonb | A list of rules that control how the certificate is used and managed. | |
compartment_id | text | = | The OCID of the compartment in Tenant in which the resource is located. |
config_type | text | The origin of the certificate. | |
current_version | jsonb | Details about the current version of the certificate. | |
defined_tags | jsonb | Defined tags for this resource. | |
description | text | A brief description of the certificate. | |
freeform_tags | jsonb | Free-form tags for this resource. | |
id | text | = | The OCID of the certificate. |
issuer_certificate_authority_id | text | = | The OCID of the certificate authority (CA) that issued the certificate. |
key_algorithm | text | The algorithm used to create key pairs. | |
lifecycle_details | text | Additional information about the current lifecycle state of the certificate. | |
lifecycle_state | text | = | The current lifecycle state of the certificate. |
name | text | = | A user-friendly name for the certificate. |
signature_algorithm | text | The algorithm used to sign the public key certificate. | |
sp_connection_name | text | =, !=, ~~, ~~*, !~~, !~~* | Steampipe connection name. |
sp_ctx | jsonb | Steampipe context in JSON form. | |
subject | jsonb | Certificate subject informnation. | |
tags | jsonb | A map of tags for the resource. | |
tenant_id | text | =, !=, ~~, ~~*, !~~, !~~* | The OCID of the Tenant in which the resource is located. |
time_created | timestamp with time zone | Time that the Certificate was created. | |
time_of_deletion | timestamp with time zone | An optional property indicating when to delete the certificate version. | |
title | text | Title 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