Oracle数据屏蔽 [英] Oracle data masking

问题描述

我们有一个要求，要使用一个提供永久屏蔽输出字符串的Oracle函数来屏蔽特定的表列.

We have one requirement to mask a particular table column using a Oracle function which gives persistent masked output string.

• 我们尝试了Oracle哈希函数，但是它没有给出String类型的返回值.
• 我们尝试了Oracle随机函数(dbms_random.string)，但是它没有提供持久输出字符串.

我在互联网上读到，这被称为确定性屏蔽.但是我们不想使用Oracle企业管理器.但是，我们需要直接使用Oracle函数.

I read on internet that this is called deterministic masking. But we do not want to use Oracle Enterprise Manager; however we require a direct Oracle function.

请提出建议.

推荐答案

在12c中，使用功能

This problem is easily solved in 12c with the function STANDARD_HASH.

以前版本中的解决方案只是稍微复杂一点.围绕DBMS_CRYPTO构建一个简单的包装程序，其行为类似于STANDARD_HASH:

The solution in previous versions is only slightly more complicated. Build a simple wrapper around DBMS_CRYPTO that acts just like STANDARD_HASH:

--Imitation of the 12c function with the same name.
--Remember to drop this function when you upgrade!
create or replace function standard_hash(
    p_string varchar2,
    p_method varchar2 default 'SHA1'
) return varchar2 is
    v_method number;
    v_invalid_identifier exception;
    pragma exception_init(v_invalid_identifier, -904);
begin
    --Intentionally case-sensitive, just like the 12c version.
    if p_method = 'SHA1' then
        v_method := dbms_crypto.hash_sh1;
    --These algorithms are only available in 12c and above.
    $IF NOT DBMS_DB_VERSION.VER_LE_11 $THEN
        elsif p_method = 'SHA256' then
            v_method := dbms_crypto.hash_sh256;
        elsif p_method = 'SHA384' then
            v_method := dbms_crypto.hash_sh384;
        elsif p_method = 'SHA512' then
            v_method := dbms_crypto.hash_sh512;
    $END
    elsif p_method = 'MD5' then
        v_method := dbms_crypto.hash_md5;
    else
        raise v_invalid_identifier;
    end if;

    return rawToHex(dbms_crypto.hash(utl_raw.cast_to_raw(p_string), v_method));
end;
/

您可能需要登录SYS并授予用户访问DBMS_CRYPTO的权限，以使该功能正常工作:

You may need to logon with SYS and grant your user access to DBMS_CRYPTO to make the function work:

grant execute on sys.dbms_crypto to <your_schema>;

创建一个公共同义词，将其授予所有人，其工作方式完全相同.

Create a public synonym, grant it to everyone, and it works exactly the same way.

create public synonym standard_hash for <schema with function>.standard_hash;
grant execute on standard_hash to public;

select standard_hash('Some text', 'MD5') from dual;
    9DB5682A4D778CA2CB79580BDB67083F

select standard_hash('Some text', 'md5') from dual;
    ORA-00904: : invalid identifier

以下是使用函数的简单示例:

Here is a simple example of using the function:

update some_table
set column1 = standard_hash(column1),
    column2 = standard_hash(column2);

但是更新大量数据可能会很慢.创建新表，删除旧表，重命名新表等可能更快，并且哈希值可能大于列大小，可能需要alter table some_table modify column1 varchar2(40 byte);

But updating large amounts of data can be slow. It may be faster to create a new table, drop the old one, rename the new one, etc. And the hash value may be larger than the column size, it may be necessary to alter table some_table modify column1 varchar2(40 byte);

让我惊讶的是，有这么多产品和工具可以完成如此​​简单的事情.

It amazes me how many products and tools there are to do such a simple thing.

这篇关于Oracle数据屏蔽的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！