ADMIN_GET_ENCRYPTION_INFO table function - Get database encryption settings

The ADMIN_GET_ENCRYPTION_INFO table function returns the current encryption settings for a database.

Authorization

The following authorization is required:

EXECUTE privilege on the routine. EXECUTE privilege on this routine can be granted only by the security administrator (SECADM).

Default PUBLIC privilege

None

Syntax

The schema is SYSPROC.

Information returned

Table 1. Information returned by the ADMIN_GET_ENCRYPTION_INFO routine
Column name	Data type	Description
OBJECT_NAME	VARCHAR(128)	Name of the object being encrypted.
OBJECT_TYPE	VARCHAR(20)	Type of object being encrypted.
ALGORITHM	VARCHAR(16)	Encryption algorithm used.
ALGORITHM_MODE	VARCHAR(16)	Encryption mode used.
KEY_LENGTH	SMALLINT	Encryption key length.
MASTER_KEY_LABEL	VARCHAR(255)	Master key label associated with the master key used.
KEYSTORE_NAME	VARCHAR(255)	Absolute path of the keystore file location.
KEYSTORE_TYPE	VARCHAR(20)	Type of keystore.
KEYSTORE_HOST	VARCHAR(255)	Host name of the server where the keystore file is located.
KEYSTORE_IP	VARCHAR(40)	IP address of the server where the keystore file is located.
KEYSTORE_IP_TYPE	VARCHAR(16)	Type of the IP address of the keystore (IPV4 or IPV6).
PREVIOUS_MASTER_KEY_LABEL	VARCHAR(255)	Master key label before the last master key rotation took place. If a master key rotation has not occurred, this value is the master key label with which the database is created.
AUTH_ID	VARCHAR(128)	Authorization ID that was used during the last master key rotation.
APPL_ID	VARCHAR(128)	Application ID that was used during the last master key rotation.
ROTATION_TIME	TIMESTAMP	Timestamp when the last master key rotation took place.

Examples

Retrieve the current database encryption settings.

SELECT OBJECT_NAME, OBJECT_TYPE, ALGORITHM, ALGORITHM_MODE, KEY_LENGTH, MASTER_KEY_LABEL
   FROM TABLE(SYSPROC.ADMIN_GET_ENCRYPTION_INFO())

This query returns the following output:

OBJECT_NAME                    OBJECT_TYPE          ALGORITHM         ... 
------------------------------ -------------------- ----------------  ...
SAMPLE                         DATABASE             AES               ...

Query output (continued):

... ALGORITHM_MODE   KEY_LENGTH MASTER_KEY_LABEL                              
... ---------------- ---------- ----------------------------------------------
... CBC                     256 DB2_SYSGEN_geoffrey_SAMPLE_2013-12-11-09.23.56

Retrieve information about the instance keystore setting.

SELECT KEYSTORE_NAME, KEYSTORE_TYPE, KEYSTORE_HOST, KEYSTORE_IP, KEYSTORE_IP_TYPE
   FROM TABLE(SYSPROC.ADMIN_GET_ENCRYPTION_INFO())

This query returns the following output:

KEYSTORE_NAME                                 KEYSTORE_TYPE        ... 
--------------------------------------------- -------------------- ... 
/home/hotel70/geoffrey/sqllib/keystore.p12    PKCS12               ...

Query output (continued):

... KEYSTORE_HOST               KEYSTORE_IP       KEYSTORE_IP_TYPE
... --------------------------- ----------------- ----------------
... hotel70.torolab.ibm.com     9.26.121.28       IPV4

Retrieve information about the last master key rotation operation.

SELECT PREVIOUS_MASTER_KEY_LABEL, AUTH_ID, APPL_ID, ROTATION_TIME
   FROM TABLE(SYSPROC.ADMIN_GET_ENCRYPTION_INFO())

This query returns the following output:

PREVIOUS_MASTER_KEY_LABEL                       AUTH_ID      ...
----------------------------------------------- ------------ ...
DB2_SYSGEN_geoffrey_SAMPLE_2013-12-10-02.12.34  GEOFFREY     ...

Query output (continued):

... APPL_ID                       ROTATION_TIME                                                                                
... ----------------------------- -----------------------------
... *LOCAL.geoffrey.140104183255  2013-12-11-09.23.56.000000