SET ENCRYPTION PASSWORD statement

The SET ENCRYPTION PASSWORD statement sets the password to be used by the ENCRYPT, DECRYPT_BIN and DECRYPT_CHAR functions. The password is not tied to database authentication, and is used for data encryption and decryption only.

This statement is not under transaction control.

Important: The SET ENCRYPTION PASSWORD statement is deprecated and might not appear in future releases.

Invocation

The statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

None required.

Syntax

Read syntax diagramSkip visual syntax diagram SET ENCRYPTION PASSWORD = host-variablestring-constant

Description

The encryption password can be used by the ENCRYPT, DECRYPT_BIN, and DECRYPT_CHAR built-in functions for password-based encryption. The length of the password must be between 6 and 127 bytes and all characters must be specified in the exact case intended, because there is no automatic conversion to uppercase characters. To maintain the best level of security on your system, it is recommended that you use a host variable or dynamic parameter markers to specify the password, rather than using a literal string in your SET ENCRYPTION PASSWORD statement.

host-variable
A variable of type CHAR or VARCHAR. The length of the host-variable must be between 6 and 127 bytes (SQLSTATE 428FC). It cannot be set to null. All characters are specified in the exact case intended, as there is no conversion to uppercase characters.
string-constant
A character string constant. The length must be between 6 and 127 bytes (SQLSTATE 428FC).

Notes

  • The initial value of the ENCRYPTION PASSWORD is the empty string.
  • The host-variable or string-constant is transmitted to the database server using normal database mechanisms.

Example

The following example shows how you can set the ENCRYPTION PASSWORD special register in an embedded SQL application using parameter markers. It is strongly recommended that this special register is always set up using parameter markers in your applications. 
EXEC SQL BEGIN DECLARE SECTION; 
      char hostVarSetEncPassStmt[200]; 
      char hostVarPassword[128]; 
EXEC SQL END DECLARE SECTION; 

/* prepare the statement with a parameter marker */ 
strcpy(hostVarSetEncPassStmt, "SET ENCRYPTION PASSWORD = ?"); 
EXEC SQL PREPARE hostVarSetEncPassStmt FROM :hostVarSetEncPassStmt;

/* execute the statement for hostVarPassword = 'Gre89Ea' */ 
strcpy(hostVarPassword, "Gre89Ea"); 
EXEC SQL EXECUTE hostVarSetEncPassStmt USING :hostVarPassword;