adm_signing_api

The keys that sign PDF documents, and who may use them.

A signing key is the address of a private key and the public certificate that belongs to it. The provider of the key says where the private key is:

Provider	Where the private key is	Needs
TRANSIT	In the Transit secrets engine of OpenBao or HashiCorp Vault. The private key never reaches ADM or AOP.	An APEX web credential with the token
OCI_VAULT	In OCI Vault. The private key never reaches ADM or AOP. Not tested against a real vault.	DBMS_CLOUD and a credential for it
DATABASE	In the ADM database, encrypted. Not recommended.	Nothing

With TRANSIT and OCI_VAULT ADM sends a SHA-256 digest to the service, and the service returns the signature. ADM stores no private key and no token. The text of a certificate that holds a private key is refused.

Do not use the provider DATABASE for anything that matters. Anyone who can run code as the owner of the ADM schema can read the stored key and the key that decrypts it, and so sign in the name of the key. So can anyone who reads a copy of the schema together with its settings: a backup, an export, a clone. The encryption in the table keeps out someone who reads that table alone, and nobody else. Use it for tests, demonstrations and low-value internal stamps.

The package has two sets of procedures:

• An administrator registers, changes, disables and enables keys, and grants them to users and groups. Every other user who calls one of these procedures gets adm_error.e_admin_required. Every change writes an entry to the audit trail.
• A user asks which keys that user can use (list_usable_keys, user_may_use_key).

A user needs a grant to sign with a key, an administrator too. The grant is for a user, or for a group, and then every member of the group may sign. A key that is disabled, or that has a certificate that is not valid now, cannot sign. The signatures that it made stay valid. A session of an embed token cannot sign, whatever its grants are.

What the registration checks: the certificate is an RSA certificate with a key of at least 2048 bits and is valid now, the text holds no private key, and neither the name nor the certificate is registered. For TRANSIT it reads the public key of the Transit key and makes sure that it is the key of the certificate. For DATABASE it makes sure that the private key is the key of the certificate. ADM cannot check this for OCI_VAULT. A certificate of another key gives a signature that ADM refuses at the first use.

Example: register a key in Transit and grant it to a group

declare
  l_key_id adm_signing_keys.signing_key_id%type;
begin
  adm_context_api.system_user_login('ADMIN');

  l_key_id := adm_signing_api.register_key(
                p_key_name           => 'Contracts'
              , p_certificate        => l_certificate_pem
              , p_provider           => adm_signing_api.c_provider_transit
              , p_transit_url        => 'https://openbao.example.com:8200'
              , p_transit_key_name   => 'contracts'
              , p_transit_credential => 'ADM_TRANSIT'
              );
  adm_signing_api.grant_to_group(l_key_id, l_group_id);
end;

Types

t_key

One signing key, as list_keys returns it. The certificate itself is not in the record, and no private key and no token is in it: the fields for a provider that the key does not use are null.

Definition:

type t_key is record (
  signing_key_id adm_signing_keys.signing_key_id%type,
  key_name adm_signing_keys.key_name%type,
  description adm_signing_keys.description%type,
  provider adm_signing_keys.provider%type,
  vault_key_ocid adm_signing_keys.vault_key_ocid%type,
  crypto_endpoint adm_signing_keys.crypto_endpoint%type,
  credential_name adm_signing_keys.credential_name%type,
  transit_url adm_signing_keys.transit_url%type,
  transit_mount adm_signing_keys.transit_mount%type,
  transit_key_name adm_signing_keys.transit_key_name%type,
  transit_credential adm_signing_keys.transit_credential%type,
  algorithm adm_signing_keys.algorithm%type,
  cert_subject adm_signing_keys.cert_subject%type,
  cert_serial adm_signing_keys.cert_serial%type,
  cert_fingerprint adm_signing_keys.cert_fingerprint%type,
  valid_from adm_signing_keys.valid_from%type,
  valid_to adm_signing_keys.valid_to%type,
  status adm_signing_keys.status%type,
  created_date adm_signing_keys.created_date%type,
  created_by adm_signing_keys.created_by%type
);

Fields:

Name	Type	Description
signing_key_id	adm_signing_keys.signing_key_id%type	-
key_name	adm_signing_keys.key_name%type	-
description	adm_signing_keys.description%type	-
provider	adm_signing_keys.provider%type	-
vault_key_ocid	adm_signing_keys.vault_key_ocid%type	-
crypto_endpoint	adm_signing_keys.crypto_endpoint%type	-
credential_name	adm_signing_keys.credential_name%type	-
transit_url	adm_signing_keys.transit_url%type	-
transit_mount	adm_signing_keys.transit_mount%type	-
transit_key_name	adm_signing_keys.transit_key_name%type	-
transit_credential	adm_signing_keys.transit_credential%type	-
algorithm	adm_signing_keys.algorithm%type	-
cert_subject	adm_signing_keys.cert_subject%type	-
cert_serial	adm_signing_keys.cert_serial%type	-
cert_fingerprint	adm_signing_keys.cert_fingerprint%type	-
valid_from	adm_signing_keys.valid_from%type	-
valid_to	adm_signing_keys.valid_to%type	-
status	adm_signing_keys.status%type	-
created_date	adm_signing_keys.created_date%type	-
created_by	adm_signing_keys.created_by%type	-

---

t_effective_policy

What ADM does with the PDFs of a folder, see adm_signing_policy_api.

Member	Value
signing_mode	OFF, WATCH or SIGN
signing_key_id	The key of the setting AUTO_SIGN_KEY_ID. Null when the setting is empty. It is not checked here.
policy_id	The policy that decided. Null when no active policy covers the folder.
source	POLICY, or DEFAULT when the setting AUTO_SIGN_DEFAULT_MODE decided

Definition:

type t_effective_policy is record (
  signing_mode adm_signing_policies.signing_mode%type,
  signing_key_id adm_signing_keys.signing_key_id%type,
  policy_id adm_signing_policies.policy_id%type,
  source varchar2(10 char)
);

Fields:

Name	Type	Description
signing_mode	adm_signing_policies.signing_mode%type	-
signing_key_id	adm_signing_keys.signing_key_id%type	-
policy_id	adm_signing_policies.policy_id%type	-
source	varchar2(10 char)	-

---

Functions and Procedures

register_key

Registers a signing key. Only an administrator can call it (adm_error.e_admin_required).

The certificate is a PEM text, or the base64 text of the DER of the certificate. The first certificate is the certificate of the signer. A PEM text can hold the certificates of the chain behind it. The text must not hold a private key: the call refuses such a text with adm_error.e_invalid_param, and the message does not repeat the text.

What a provider needs:

• OCI_VAULT: p_vault_key_ocid, p_crypto_endpoint and p_credential_name.
• TRANSIT: p_transit_url, p_transit_key_name and p_transit_credential, and p_transit_mount when the engine is not mounted at transit. The address must start with https://. http:// is accepted for localhost, 127.0.0.1, host.docker.internal and a name without a dot (a container in a docker network), because the token goes to that address. With p_check_transit the call reads the key from Transit, which proves that the credential works, and compares its public key with the certificate.
• DATABASE: p_private_key_pem, the PEM text of an RSA private key without a password. Not recommended, see the description of the package. The key is stored encrypted in adm_signing_key_secrets. No audit entry, no log and no error message holds it.

A parameter of another provider must be null. The call raises adm_error.e_invalid_param for a value that is not usable: an endpoint that is not https://, a certificate that has no RSA key of at least 2048 bits or that is not valid now, a Transit key that is not the key of the certificate, a private key that is not the key of the certificate. It raises adm_error.e_signing_key_exists when a key with this name, or with this certificate, is registered. Transit that cannot be reached is adm_error.e_signing_failed.

The new key is active and nobody has a grant for it.

Signature:

function register_key (
  p_key_name in varchar2,
  p_certificate in clob default null,
  p_provider in varchar2 default c_provider_oci_vault,
  p_description in varchar2 default null,
  p_vault_key_ocid in varchar2 default null,
  p_crypto_endpoint in varchar2 default null,
  p_credential_name in varchar2 default null,
  p_transit_url in varchar2 default null,
  p_transit_mount in varchar2 default 'transit',
  p_transit_key_name in varchar2 default null,
  p_transit_credential in varchar2 default null,
  p_private_key_pem in clob default null,
  p_check_transit in boolean default true
) return adm_signing_keys.signing_key_id%type;

Parameters:

Name	Direction	Type	Description
p_key_name	in	varchar2	The name, unique, up to 255 characters
p_certificate	in	clob default null	The public certificate of the key, PEM or base64. TRANSIT only: null reads the certificate that is stored with the key in Transit (set-certificate).
p_provider	in	varchar2 default c_provider_oci_vault	One of the c_provider_* constants. Default: OCI_VAULT.
p_description	in	varchar2 default null	What the key is for. Optional.
p_vault_key_ocid	in	varchar2 default null	OCI_VAULT: the OCID of the key, for example ocid1.key.oc1.eu-frankfurt-1...
p_crypto_endpoint	in	varchar2 default null	OCI_VAULT: the cryptographic endpoint of the vault that holds the key, https://...
p_credential_name	in	varchar2 default null	OCI_VAULT: the name of the DBMS_CLOUD credential that signs the requests to OCI Vault
p_transit_url	in	varchar2 default null	TRANSIT: the address of the server, for example https://openbao.example.com:8200
p_transit_mount	in	varchar2 default 'transit'	TRANSIT: the mount of the Transit engine. Default: transit.
p_transit_key_name	in	varchar2 default null	TRANSIT: the name of the key in the engine
p_transit_credential	in	varchar2 default null	TRANSIT: the static ID of the APEX web credential of the type HTTP Header that holds the token (header X-Vault-Token)
p_private_key_pem	in	clob default null	DATABASE: the private key
p_check_transit	in	boolean default true	TRANSIT: false skips the call to the server. Only for a server that is not reachable from here.

Returns: adm_signing_keys.signing_key_id%type - The ID of the key

---

update_key

Changes the name, the description or the address of a key. A value that is null stays as it is. The key itself, the provider and the certificate cannot change: register a new key for them. A parameter of another provider than the one of the key must be null. Only an administrator can call it.

Signature:

procedure update_key (
  p_signing_key_id in adm_signing_keys.signing_key_id%type,
  p_key_name in varchar2 default null,
  p_description in varchar2 default null,
  p_crypto_endpoint in varchar2 default null,
  p_credential_name in varchar2 default null,
  p_transit_url in varchar2 default null,
  p_transit_credential in varchar2 default null
);

Parameters:

Name	Direction	Type	Description
p_signing_key_id	in	adm_signing_keys.signing_key_id%type	The key
p_key_name	in	varchar2 default null	The new name
p_description	in	varchar2 default null	The new description. It cannot be removed, only replaced.
p_crypto_endpoint	in	varchar2 default null	OCI_VAULT: the new cryptographic endpoint
p_credential_name	in	varchar2 default null	OCI_VAULT: the new DBMS_CLOUD credential
p_transit_url	in	varchar2 default null	TRANSIT: the new address of the server
p_transit_credential	in	varchar2 default null	TRANSIT: the new APEX web credential

---

disable_key

Disables a key. It cannot sign until it is enabled again. The signatures that it made stay valid. Only an administrator can call it.

Signature:

procedure disable_key (
  p_signing_key_id in adm_signing_keys.signing_key_id%type
);

Parameters:

Name	Direction	Type	Description
p_signing_key_id	in	adm_signing_keys.signing_key_id%type	The key

---

enable_key

Enables a key that was disabled. Only an administrator can call it.

Signature:

procedure enable_key (
  p_signing_key_id in adm_signing_keys.signing_key_id%type
);

Parameters:

Name	Direction	Type	Description
p_signing_key_id	in	adm_signing_keys.signing_key_id%type	The key

---

grant_to_user

Lets a user sign with a key. A grant that exists stays as it is. Only an administrator can call it.

Signature:

procedure grant_to_user (
  p_signing_key_id in adm_signing_keys.signing_key_id%type,
  p_user_id in adm_users.user_id%type
);

Parameters:

Name	Direction	Type	Description
p_signing_key_id	in	adm_signing_keys.signing_key_id%type	The key
p_user_id	in	adm_users.user_id%type	The user (adm_users.user_id)

---

grant_to_group

Lets every member of a group sign with a key. A grant that exists stays as it is. Only an administrator can call it.

Signature:

procedure grant_to_group (
  p_signing_key_id in adm_signing_keys.signing_key_id%type,
  p_group_id in adm_groups.group_id%type
);

Parameters:

Name	Direction	Type	Description
p_signing_key_id	in	adm_signing_keys.signing_key_id%type	The key
p_group_id	in	adm_groups.group_id%type	The group (adm_groups.group_id)

---

revoke_from_user

Takes the grant of a user back. It does nothing when there is no grant. A user who is a member of a group with a grant can still sign. Only an administrator can call it.

Signature:

procedure revoke_from_user (
  p_signing_key_id in adm_signing_keys.signing_key_id%type,
  p_user_id in adm_users.user_id%type
);

Parameters:

Name	Direction	Type	Description
p_signing_key_id	in	adm_signing_keys.signing_key_id%type	The key
p_user_id	in	adm_users.user_id%type	The user

---

revoke_from_group

Takes the grant of a group back. It does nothing when there is no grant. Only an administrator can call it.

Signature:

procedure revoke_from_group (
  p_signing_key_id in adm_signing_keys.signing_key_id%type,
  p_group_id in adm_groups.group_id%type
);

Parameters:

Name	Direction	Type	Description
p_signing_key_id	in	adm_signing_keys.signing_key_id%type	The key
p_group_id	in	adm_groups.group_id%type	The group

---

list_keys

All keys, active and disabled, ordered by name. Only an administrator can call it.

Signature:

function list_keys return t_keys;

Returns: t_keys - The keys

---

list_usable_keys

The keys that the current user can sign with now: active, with a certificate that is valid, and granted to the user or to a group of the user. A session of an embed token gets an empty list. Any user can call it.

Signature:

function list_usable_keys return t_keys;

Returns: t_keys - The keys, ordered by name

---

get_key

One key. An administrator can read every key. Another user can read a key that the user may use, and gets adm_error.e_signing_key_not_found for every other key.

Signature:

function get_key (
  p_signing_key_id in adm_signing_keys.signing_key_id%type
) return t_key;

Parameters:

Name	Direction	Type	Description
p_signing_key_id	in	adm_signing_keys.signing_key_id%type	The key

Returns: t_key - The key

---

get_certificate

The PUBLIC certificate of a key, as it was registered. An administrator can read it, and a user who may use the key.

Signature:

function get_certificate (
  p_signing_key_id in adm_signing_keys.signing_key_id%type
) return clob;

Parameters:

Name	Direction	Type	Description
p_signing_key_id	in	adm_signing_keys.signing_key_id%type	The key

Returns: clob - The certificate as PEM text

---

user_may_use_key

True when the user holds a grant for the key, or is a member of a group that holds one. It does not look at the status of the key or at the session. An administrator needs a grant, as every other user.

Signature:

function user_may_use_key (
  p_signing_key_id in adm_signing_keys.signing_key_id%type,
  p_username in adm_users.username%type default sys_context(adm_context_api.c_ctx_namespace,
  adm_context_api.c_ctx_username
);

Parameters:

Name	Direction	Type	Description
p_signing_key_id	in	adm_signing_keys.signing_key_id%type	The key
p_username	in	adm_users.username%type default sys_context(adm_context_api.c_ctx_namespace	The user. Default: the user of the ADM context.

Returns: boolean - True or false, never null

---

get_effective_policy

The mode and the key that apply to a folder. The longest active policy that covers the path of the folder decides. Without a policy the setting AUTO_SIGN_DEFAULT_MODE decides, and WATCH is the mode when the setting is empty. Any user can call it, because every upload and every PDF operation asks. The path of the folder is read at every call, so a folder that was moved has the policy of its new place. The call does not cache.

The call does not check the key. For the mode SIGN use assert_auto_sign_key_usable.

Signature:

function get_effective_policy (
  p_folder_id in adm_folders.folder_id%type
) return t_effective_policy;

Parameters:

Name	Direction	Type	Description
p_folder_id	in	adm_folders.folder_id%type	The folder

Returns: t_effective_policy - The mode, the key and where the mode comes from

---

assert_auto_sign_key_usable

Makes sure that the key of the setting AUTO_SIGN_KEY_ID can sign: the setting is not empty, the key exists, it is active, and its certificate is valid now. It does not look at grants and at the session, because ADM signs for the system and not for a user.

Only for the administration of the policies and for the code that signs. Any user can call it.

Signature:

procedure assert_auto_sign_key_usable;

---