let say we have a Table of employees having the column EmployeeName, Department:

I want to have a new column in a view taking EmployeeName as data along with static private-key. Don't wanna change the table structure, but just to get UUID in select query For eg: select EmployeeName, Department, UUID({private-key},EmployeeName) as UUID from EmployeeTable

View result example:

is this achievable?

CodePudding user response：

It sounds like you want a cryptographic hash, not a UUID. A UUID is not a function of the input string, it's just a unique id.

You could use MD5() to create a 128-bit cryptographic hash of the EmployeeName, which is the same length as a UUID:

SELECT EmployeeName, Department,
  MD5(CONCAT(@privateKey, EmployeeName)) AS Not_Really_Uuid
FROM EmployeeTable.

CodePudding user response：

Use sha256, it is deterministic strong cryptographic one-way function:

SHA2(CONCAT(@salt, EmployeeName), 256)

Sha256 is collision tolerant, different input will produce different hashes, the same input will produce the same hash. So, you can use it for joins and grouping the same as original values

CodePudding user response：

The benefit of using hash algorithms is that you can be sure that the length of the hash string is constant.
So you can define a varchar of that length for it.

But if you really want an id that can be decrypted, then the length will depend on the encrypted name.

For example, here's a test that works in Postgresql.

It uses 2 functions, for encrypting and decrypting the names. The base64 encoding is just to make the encrypted string shorter.

create table test (name varchar(50), nameid varchar(80));

insert into test (name) values 
('Jane Doe'), 
('John James Archibald Doe Senior');

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE OR REPLACE FUNCTION fn_encrypt_name(txt text)
RETURNS text
AS $$
DECLARE salt text;
BEGIN
  salt := 'briny';
  RETURN 
    encode(
      encrypt(txt::bytea, salt::bytea, 'bf'::text)
    , 'base64');
END $$ LANGUAGE plpgsql STRICT IMMUTABLE SECURITY DEFINER;

CREATE OR REPLACE FUNCTION fn_decrypt_nameid(txt text)
RETURNS text
AS $$
DECLARE salt text;
BEGIN
    salt := 'briny';
    RETURN 
     convert_from(
      decrypt(decode(txt,'base64')::bytea, salt::bytea, 'bf'::text)
       , 'SQL_ASCII');
END $$ LANGUAGE PLPGSQL STRICT IMMUTABLE SECURITY DEFINER;

Normal users shouldn't be using those functions.

REVOKE ALL ON FUNCTION fn_encrypt_name(txt text) FROM PUBLIC;
REVOKE ALL ON FUNCTION fn_decrypt_nameid(txt text) FROM PUBLIC;
COMMIT;

update test
set nameid =fn_encrypt_name(name)
where nameid is null;

select *
, length(name) as len_name
, length(nameid) as len_nameid
, fn_decrypt_nameid(nameid) as decrypted_uuid
from test

name                            | nameid                                       | len_name | len_nameid | decrypted_uuid                 
:------------------------------ | :------------------------------------------- | -------: | ---------: | :------------------------------
Jane Doe                        | sy/4OkdCmm6maeuvjgpHIw==                     |        8 |         24 | Jane Doe                       
John James Archibald Doe Senior | f2IxooMK3FtPs1Fd2fdr4Slv6j5 23mMeul43vFtbDo= |       31 |         44 | John James Archibald Doe Senior

Demo on db<>fiddle here