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
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. When the database instance is
configured for PKCS #11 keystore, the KEYSTORE_HOST column contains a semi-colon separated list of information about the PKCS #11 keystore. The format is:
|
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.
This query returns the following output:SELECT OBJECT_NAME, OBJECT_TYPE, ALGORITHM, ALGORITHM_MODE, KEY_LENGTH, MASTER_KEY_LABEL FROM TABLE(SYSPROC.ADMIN_GET_ENCRYPTION_INFO())
Query output (continued):OBJECT_NAME OBJECT_TYPE ALGORITHM ... ------------------------------ -------------------- ---------------- ... SAMPLE DATABASE AES ...
... 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.
This query returns the following output:SELECT KEYSTORE_NAME, KEYSTORE_TYPE, KEYSTORE_HOST, KEYSTORE_IP, KEYSTORE_IP_TYPE FROM TABLE(SYSPROC.ADMIN_GET_ENCRYPTION_INFO())
Query output (continued):KEYSTORE_NAME KEYSTORE_TYPE ... --------------------------------------------- -------------------- ... /home/hotel70/geoffrey/sqllib/keystore.p12 PKCS12 ...
... 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.
This query returns the following output:SELECT PREVIOUS_MASTER_KEY_LABEL, AUTH_ID, APPL_ID, ROTATION_TIME FROM TABLE(SYSPROC.ADMIN_GET_ENCRYPTION_INFO())
Query output (continued):PREVIOUS_MASTER_KEY_LABEL AUTH_ID ... ----------------------------------------------- ------------ ... DB2_SYSGEN_geoffrey_SAMPLE_2013-12-10-02.12.34 GEOFFREY ...
... APPL_ID ROTATION_TIME ... ----------------------------- ----------------------------- ... *LOCAL.geoffrey.140104183255 2013-12-11-09.23.56.000000
- Retrieve information about
the PKCS #11 keystore:
This query returns the following output:SELECT KEYSTORE_HOST FROM TABLE(SYSPROC.ADMIN_GET_ENCRYPTION_INFO())
DB2partition ;Safenet, Inc. ;LunaSA ;1146376383692 ;