Technical Blog Post
Abstract
75 ways to demystify DB2 #77: Techtip : DB2V105 Native Encryption Missing Function and Routine
Body
When customer using native encryption, in DB2V105FP5 and later fix packs, they may find that the following function and routine are missing:
ADMIN_GET_ENCRYPTION_INFO and ADMIN_ROTATE_MASTER_KEY.
db2 "select substr(funcschema,1,12)as funcs, substr(funcname,1,30) as name from syscat.functions where funcname='ADMIN_GET_ENCRYPTION_INFO' order by 1,2"
FUNCS NAME
------------ ------------------------------
0 record(s) selected.
db2 "select substr(ROUTINESCHEMA,1,12)as funcs, substr(ROUTINENAME,1,30) as name from syscat.ROUTINES where ROUTINENAME='ADMIN_ROTATE_MASTER_KEY' order by 1,2"
FUNCS NAME
------------ ------------------------------
0 record(s) selected.
If customer try to use these function and routine, they will receive the following error:
db2 "SELECT OBJECT_NAME, OBJECT_TYPE, ALGORITHM, ALGORITHM_MODE, KEY_LENGTH, MASTER_KEY_LABEL FROM TABLE(SYSPROC.ADMIN_GET_ENCRYPTION_INFO())"
SQL0440N No authorized routine named ADMIN_GET_ENCRYPTION_INFO of type
"FUNCTION" having compatible arguments was found.
db2 "CALL SYSPROC.ADMIN_ROTATE_MASTER_KEY (NULL)"
SQL0440N No authorized routine named "ADMIN_ROTATE_MASTER_KEY" of type "PROCEDURE" having compatible arguments was found.
These function and routine are created with the database and cannot be remove after creation. According to IC page https://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.sec.doc/doc/c0061758.html, native encryption was introduced in DB2V105FP5, if customer already using DB2V105FP5 or later fix packs, why can't customer find and use these function and routine?
This is because the database that customer is trying to use was created in a DB2 instance before the native encryption was introduced. Normally after the upgrade of instance, it should be follow by upgrade of database too. However, in some cases, especially when the database was created within the same DB2 version (i.e. DB2V105) but earlier fix pack (i.e. FP0, FP1, FP2, FP3 and FP4), customer may not upgrade the database to the latest instance version. So in the case customer is using an instance level of DB2V105FP5 or later, but the database level is DB2V105FP4 or earlier, this issue will occur.
The solution is to run command "db2updv105 -d <dbname>" to upgrade the database to the same level of the instance.
After upgrade the database, check again, the correct outputs are:
db2 "SELECT OBJECT_NAME, OBJECT_TYPE, ALGORITHM, ALGORITHM_MODE, KEY_LENGTH, MASTER_KEY_LABEL FROM TABLE(SYSPROC.ADMIN_GET_ENCRYPTION_INFO())"
OBJECT_NAME OBJECT_TYPE ALGORITHM ALGORITHM_MODE KEY_LENGTH MASTER_KEY_LABEL
--------------------- ------------------- ---------------- ----------------------------
------------------ ----------------------------
TESTDB02 DATABASE AES CBC 256 DB2_SYSGEN_DB2_03_TESTDB02_2015-11-04-21.32.4 0
1 record(s) selected.
db2 "CALL SYSPROC.ADMIN_ROTATE_MASTER_KEY (NULL)"
Value of output parameters
--------------------------
Parameter Name : LABEL
Parameter Value : DB2_SYSGEN_DB2_03_TESTDB02_2015-11-04-21.45.36
Return Status = 0
db2 "select substr(funcschema,1,12)as funcs, substr(funcname,1,30) as name from syscat.functions where funcname='ADMIN_GET_ENCRYPTION_INFO' order by 1,2"
FUNCS NAME
------------ ------------------------------
SYSPROC ADMIN_GET_ENCRYPTION_INFO
1 record(s) selected.
db2 "select substr(ROUTINESCHEMA,1,12)as funcs, substr(ROUTINENAME,1,30) as name from syscat.ROUTINES where ROUTINENAME='ADMIN_ROTATE_MASTER_KEY' order by 1,2"
FUNCS NAME
------------ ------------------------------
SYSPROC ADMIN_ROTATE_MASTER_KEY
1 record(s) selected.
UID
ibm11140898