Security Functions

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

HASROLE

This function checks if the current caller has the specified Data Virtuality Server data role:

SQL

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, and the return type is boolean.

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

Cryptographic Hash Functions

These functions compute the hash of the given value.

Function	Definition	Data Type Constraint
`MD5_BINARY(x)`	Returns the MD5 hash of the value	x in { string, varbinary }, returns varbinary
`SHA1(x)`	Returns the SHA-1 hash of the value	x in { string, varbinary }, returns varbinary
`SHA2_256(x)`	Returns the SHA-2 256-bit hash of the value	x in { string, varbinary }, returns varbinary
`SHA2_512(x)`	Returns the SHA-2 512-bit hash of the value	x in {string, varbinary}, returns varbinary

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:

SQL

biginteger HASHCODE(string)

Here are some usage examples for this function:

SQL

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 Data 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:

SQL

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 Data Virtuality Server, the result is always returned in uppercase. As for the different DBMS, please refer to the table below:

DBMS	Uppercase/Lowercase
PostgreSQL	Lowercase
MySQL	Lowercase
Microsoft SQL Server	Lowercase
Oracle	Uppercase
Redshift	Lowercase
Vertica	Lowercase
Greenplum	Lowercase
Exasol	Lowercase
Teradata	Uppercase

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:

SQL

EXEC SYSADMIN.createConnection(name => 'pg', jbossCLITemplateName => 'postgresql', connectionOrResourceAdapterProperties => 'db=<db_name>,user-name=<username>,password=<pass>,host=<hostname>,port=<port_number>');;
 
EXEC 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, it is possible that non-admin users 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:

SQL

GRANT EXECUTE ON DBMS_CRYPTO TO <username>

If giving permissions 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 Data 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:

SQL

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 a DES algorithm. It can be used to encode passwords for the cases of storing passwords in configuration files.

SQL

SELECT ENCRYPT('test');;