Security Functions

Security functions allow you to interact with the CData Virtuality Server security system.

HASROLE

This function checks if the user on whose behalf the inner query of a view or procedure is executed has the specified Data Virtuality Server data role:

HASROLE([roleType,] roleName)

The two-argument form is provided for backward compatibility.

• roleType is a string and must be of type data;
• roleName must be a string

The return type is boolean.

Role names are case-sensitive and only match the CData Virtuality server Data Roles. JAAS role/group names are not valid for this function unless there is a corresponding data role with the same name.

HASROLE function is nondeterministic.

HASROLE function changed from user-deterministic to nondeterministic since v4.1

HASCALLERROLE

This function checks if the current caller has the specified CData Virtuality server data role:

HASCALLERROLE([roleType,] roleName)

The two-argument form is provided for backward compatibility.

• roleType is a string and must be of type data;
• roleName must be a string, and the return type is boolean.

Role names are case-sensitive and only match the CData Virtuality Server Data Roles. JAAS role/group names are not valid for this function unless there is a corresponding data role with the same name.

The HASCALLERROLE function is user-deterministic.

HASCALLERROLE function available since v4.6

Cryptographic Hash Functions

These functions compute the hash of the given value.

To convert the output varbinary value to hex representation, you can use the TO_CHARS(VALUE, 'HEX') function.

Pushdown

Currently, pushdown is not supported.

HASHCODE()

The HASHCODE() function calculates the MD5-produced hex value of a string and converts it to a Numeric(65, 0) representation:

biginteger HASHCODE(string)

Here are some usage examples for this function:

SELECT HASHCODE('test1');;
 
SELECT HASHCODE(col1)
FROM myschema.mytable;;

If the underlying data source supports MD5() functions or an equivalent rewriting, the HASHCODE function can be pushed down and processed directly on the DBMS. The HASHCODE function is pushed down to the following data sources:

• PostgreSQL
• MySQL
• Microsoft SQL Server
• Oracle
• Redshift
• Vertica
• Teradata

For the databases not listed here, the HASHCODE() function is processed locally in the CData Virtuality Server. 

For a correct pushdown of the HASHCODE() function, a correct pushdown of the MD5() function is mandatory. 

MD5()

The MD5() function calculates the MD5 hash of a string. Here are some examples:

SELECT MD5('test1');;

SELECT MD5(col1)
FROM myschema.mytable;;

If the underlying data source supports MD5() functions or an equivalent rewriting, the function can be pushed down and processed directly on the DBMS. The MD5() function is pushed down to the following data sources:

• PostgreSQL;
• MySQL;
• Microsoft SQL Server;
• Oracle;
• Redshift;
• Vertica;
• Greenplum;
• Exasol;
• Teradata.

For those databases not listed here, the MD5() function is processed locally in the Data Virtuality Server. 

Please note that the result of an MD5() function can be returned in uppercase or lowercase depending on the DBMS. If the function is not pushed down but processed locally in the CData Virtuality Server, the result is always returned in uppercase. As for the different DBMS, please refer to the table below:

In most cases, the MD5() function is supported and available in a standard installation of the platforms above (PostgreSQL, MySQL, Redshift, etc.), but in other cases, some configuration steps are needed.

However, if these libraries are not available or local processing of the MD5() function is preferred instead of pushing down the function, it is possible to set the value of the translator property supportsMd5Function to FALSE (by default, it is set to TRUE). For example:

CALL SYSADMIN.createConnection(name => 'pg', jbossCLITemplateName => 'postgresql', connectionOrResourceAdapterProperties => 'db=<db_name>,user-name=<username>,password=<pass>,host=<hostname>,port=<port_number>');;
 
CALL SYSADMIN.createDataSource(name => 'pg', translator => 'postgresql', modelProperties => 'importer.useFullSchemaName=false,importer.TableTypes="TABLE,VIEW",importer.importIndexes=false,importer.schemaPattern=public', translatorProperties => 'supportsMd5Function=false');;

In the example above, the MD5() function will be processed locally instead of being pushed down to PostgreSQL. This translator property can be defined on any translator, but it does not affect translators which do not support pushdown of the MD5() function. 

Below we will describe how to configure Oracle and Teradata. For more details, please refer to the related documentation.

Oracle

In Oracle, the MD5() function is rewritten using the HASH function available in the DBMS_CRYPTO package. By default, non-admin users may have no grants for the DBMS_CRYPTO package. If this is the case, please connect as a system user (usually SYSDBA) and execute the following command:

GRANT EXECUTE ON DBMS_CRYPTO TO <username>

If giving permission to use DBMS_CRYPTO package is not possible, you can set the value of the translator property supportsMd5Function to false so that the function will be calculated locally in the CData Virtuality Server.

Teradata

In a standard installation of Teradata, no hash functions are provided, but they can be installed as user-defined functions (UDF) from external libraries. You can download the MD5() function here.

Typically, the DBC user (the default user created for Teradata) has not enough permissions to create functions. To correctly install the external library, you can create another user and assign rights as follows:

CREATE USER tera FROM DBC
AS PERM = <perm_size>
PASSWORD = "tera"
DEFAULT DATABASE = TERA;
 
GRANT EXECUTE, SELECT, INSERT, UPDATE, DELETE, STATISTICS, DUMP, RESTORE, CHECKPOINT, SHOW, EXECUTE PROCEDURE, ALTER PROCEDURE, EXECUTE FUNCTION, ALTER FUNCTION, ALTER EXTERNAL PROCEDURE, CREATE OWNER PROCEDURE, CREATE TABLE, CREATE VIEW, CREATE MACRO, CREATE DATABASE, CREATE TRIGGER, CREATE PROCEDURE, CREATE FUNCTION, CREATE EXTERNAL PROCEDURE, CREATE AUTHORIZATION, DROP TABLE, DROP VIEW, DROP MACRO, DROP DATABASE, DROP TRIGGER, DROP PROCEDURE, DROP FUNCTION, DROP AUTHORIZATION ON TERA TO TERA WITH GRANT OPTION;
 
GRANT EXECUTE, SELECT, STATISTICS, SHOW ON DBC TO TERA WITH GRANT OPTION;

ENCRYPT

The function encodes a given string using an AES algorithm. It can be used to encode passwords for the cases of storing passwords in configuration files.

SELECT encrypt('test');;

AES_ENCRYPT

This function allows to encrypt data using the official AES (Advanced Encryption Standard) algorithm, 16 bytes(128 bit) key length, and AES/CBC/PKCS5Padding cipher algorithm with an explicit initialization vector.

SELECT aes_encrypt(string data, string key);;

SQL

SELECT aes_decrypt(string data, string key);;

The AES_DECRYPT() function will return a string decrypted data. The argument data is the string data to decrypt, and the argument key is a string used in decryption.

The aes_encrypt() and aes_decrypt() functions are available since v4.4