[teiid-issues] [JBoss JIRA] (TEIID-4532) Provide one-way or cryptographic hash functions

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-4532?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins resolved TEIID-4532.
-----------------------------------
    Resolution: Done


Marking as initially resolved.  There are four functions added.  Each can take a string or a varbinary - md5, sha1, sha2_256, and sha2_512.  These were added as separate functions to keep the pushdown logic straight-forward - so that we aren't introducing special constants for the algorithm or bit length.  Pushdown support was added for both Teiid and SQL Server.  Pushdown support is possible for mysql (although we have to convert from the hex string to bytes), oracle (relying on the crypto package), postgres (relying on pgcrypto), and others but require more effort or that we need to detect that the extension is available.  I propose that we address that on an add needed basis.

The md5 function also overlaps with the osDQ hash function, so we'll likely end up removing the hash function.

> Provide one-way or cryptographic hash functions
> -----------------------------------------------
>
>                 Key: TEIID-4532
>                 URL: https://issues.jboss.org/browse/TEIID-4532
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: Query Engine
>    Affects Versions: 9.2
>            Reporter: Van Halbert
>            Assignee: Steven Hawkins
>             Fix For: 9.2
>
>
> Provide one-way or [cryptographic hash functions|https://en.wikipedia.org/wiki/SHA-3#Comparison_of_SHA_functions], such as any of the MD5, SHA-1, SHA-2, or SHA-3 functions, so that views can define columns that are hashes of other columns.
> The goal is to allow views to hide some columns (e.g., personally identifying information), but to expose a new "primary key" that is a hash of other existing columns. So, given this source table:
> {code:sql}
> CREATE TABLE person (
>   id INT PRIMARY KEY,
>   name VARCHAR(256) NOT NULL,
>   age INT,
>   height INT,
>   weight DOUBLE
> );
> {code}
> a view could be created to hide the personally identifying information:
> {code:sql}
> CREATE VIEW anonymousPerson ()
>   id VARCHAR(64) PRIMARY KEY,
>   age INT,
>   height INT,
>   weight DOUBLE
> ) AS
>    SELECT sha256(p.id, p.name) AS id, 
>           p.age AS age, 
>           p.height AS height, 
>           p.weight AS weight 
>           FROM person AS p;
> {code}



--
This message was sent by Atlassian JIRA
(v7.2.3#72005)