DB2 10.5 for Linux, UNIX, and Windows

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

Read syntax diagramSkip visual syntax diagram
>>-ADMIN_GET_ENCRYPTION_INFO--(--)-----------------------------><

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 algorithm 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

  1. 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
  2. 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            
  3. 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