Oracle

CREATE SCHEMA pgcrypto_2_text; SET search_path TO pgcrypto_2_text; /* ------------------------------------------------------------------------------------------------ * part 0: create some utils functions * ------------------------------------------------------------------------------------------------ */ CREATE FUNCTION strtoraw(string VARCHAR2) RETURN RAW IS BEGIN RETURN utl_raw.cast_to_raw(string); END; / CREATE FUNCTION rawtostr(rawdata RAW) RETURN VARCHAR2 IS BEGIN RETURN utl_raw.cast_to_varchar2(rawdata); END; / -- get data type name CREATE OR REPLACE FUNCTION oidgettype(typeoid OID) RETURNS TEXT AS $$ BEGIN RETURN (SELECT typname FROM pg_type WHERE oid = typeoid); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION oidgettype(typeoids OIDVECTOR) RETURNS TEXT[] AS $$ DECLARE result TEXT[] := '{}'; typoid OID; typename TEXT; BEGIN FOREACH typoid IN ARRAY typeoids LOOP SELECT typname INTO typename FROM pg_type WHERE oid = typoid; result := array_append(result, COALESCE(typename, 'Unknown OID')); END LOOP; RETURN result; END; $$ LANGUAGE plpgsql; -- find all functions from dbms_obfuscation_toolkit CREATE VIEW allfuncs AS SELECT proname name,pronargs args,oidgettype(prorettype) output,oidgettype(proargtypes) input FROM pg_proc WHERE pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'dbms_obfuscation_toolkit') ORDER BY 1,3,2; /* ------------------------------------------------------------------------------------------------ * part 1: install pgcrypt with different versions * ------------------------------------------------------------------------------------------------ */ -- available version SELECT * FROM pg_available_extension_versions WHERE name = 'pgcrypto'; -- 2.0 (default version) CREATE EXTENSION pgcrypto; SELECT * FROM pg_extension WHERE extname = 'pgcrypto'; SELECT * FROM allfuncs; DROP EXTENSION pgcrypto; -- 1.2 (old version) CREATE EXTENSION pgcrypto WITH VERSION '1.2'; SELECT * FROM pg_extension WHERE extname = 'pgcrypto'; SELECT * FROM allfuncs; DROP EXTENSION pgcrypto; -- 2.0 --> 1.2 CREATE EXTENSION pgcrypto; SELECT * FROM pg_extension WHERE extname = 'pgcrypto'; ALTER EXTENSION pgcrypto UPDATE TO '1.2'; SELECT * FROM pg_extension WHERE extname = 'pgcrypto'; SELECT * FROM allfuncs; DROP EXTENSION pgcrypto; -- 1.2 --> 2.0 CREATE EXTENSION pgcrypto WITH VERSION '1.2'; SELECT * FROM pg_extension WHERE extname = 'pgcrypto'; ALTER EXTENSION pgcrypto UPDATE TO '2.0'; SELECT * FROM pg_extension WHERE extname = 'pgcrypto'; SELECT * FROM allfuncs; DROP EXTENSION pgcrypto; /* ------------------------------------------------------------------------------------------------ * part 2: core functions * ------------------------------------------------------------------------------------------------ */ CREATE EXTENSION pgcrypto; -- 1 desencrypt SELECT dbms_obfuscation_toolkit.desencrypt(strtoraw('11111111'), strtoraw('11111111')) FROM dual; SELECT dbms_obfuscation_toolkit.desencrypt(strtoraw('1111111111111111'), strtoraw('11111111')) FROM dual; -- 2 desdecrypt SELECT rawtostr(dbms_obfuscation_toolkit.desdecrypt(hextoraw('655EA628CF62585F'), strtoraw('11111111'))) FROM dual; SELECT rawtostr(dbms_obfuscation_toolkit.desdecrypt(hextoraw('655EA628CF62585F20D3B8A6E657876B'), strtoraw('11111111'))) FROM dual; -- 3 des3encrypt SELECT dbms_obfuscation_toolkit.des3encrypt(strtoraw('11111111'), strtoraw('1111111122222222'), 0, strtoraw('11111111')) FROM dual; SELECT dbms_obfuscation_toolkit.des3encrypt(strtoraw('1111111111111111'), strtoraw('1111111122222222'), 0, strtoraw('11111111')) FROM dual; -- 4 des3decrypt SELECT rawtostr(dbms_obfuscation_toolkit.des3decrypt(hextoraw('0D28E17571051319'), strtoraw('1111111122222222'), 0, strtoraw('11111111'))) FROM dual; SELECT rawtostr(dbms_obfuscation_toolkit.des3decrypt(hextoraw('0D28E17571051319ACCE2D19D9AED5C8'), strtoraw('1111111122222222'), 0, strtoraw('11111111'))) FROM dual; -- 5 des3encrypt -- without iv SELECT dbms_obfuscation_toolkit.des3encrypt(strtoraw('11111111'), strtoraw('1111111122222222')) FROM dual; SELECT dbms_obfuscation_toolkit.des3encrypt(strtoraw('1111111111111111'), strtoraw('1111111122222222')) FROM dual; -- 6 des3decrypt -- without iv SELECT rawtostr(dbms_obfuscation_toolkit.des3decrypt(hextoraw('7934131269E334A2'), strtoraw('1111111122222222'))) FROM dual; SELECT rawtostr(dbms_obfuscation_toolkit.des3decrypt(hextoraw('7934131269E334A2BCC64878CC5A075A'), strtoraw('1111111122222222'))) FROM dual; /* ------------------------------------------------------------------------------------------------ * part 3: different args * ------------------------------------------------------------------------------------------------ */ -- 1 des CREATE TABLE tplain (id INT, plain TEXT, key TEXT); INSERT INTO tplain VALUES (1, '', ''), (2, NULL, NULL), -- different plain (11, NULL, '88888888'), (12, '', '88888888'), (13, '1', '88888888'), (14, '7777777', '88888888'), (15, '88888888', '88888888'), (16, '999999999', '88888888'), (17, '17171717171717171', '88888888'), (18, '242424242424242422424242','88888888'), -- different key (31, '88888888', NULL), (32, '88888888', ''), (33, '88888888', '1'), (34, '88888888', '7777777'), (37, '88888888', '88888888'), (38, '88888888', '999999999'), (37, '88888888', '17171717171717171'), (38, '88888888', '242424242424242422424242'); CREATE TABLE tcipher (id INT, key TEXT, cipher RAW); INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 1; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 2; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 11; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 12; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 13; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 14; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 15; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 16; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 17; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 18; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 31; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 32; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 33; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 34; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 35; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 36; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 37; INSERT INTO tcipher SELECT id, key, dbms_obfuscation_toolkit.desencrypt(strtoraw(plain), strtoraw(key)) FROM tplain WHERE id = 38; SELECT id,cipher,length(cipher) FROM tcipher ORDER BY id; SELECT id,rawtostr(dbms_obfuscation_toolkit.desdecrypt(cipher, strtoraw(key))) FROM tcipher ORDER BY id; DROP TABLE tplain,tcipher; -- 2 3des CREATE TABLE tplain (id INT, plain TEXT, keymod INT, key TEXT, iv TEXT); INSERT INTO tplain VALUES (1, '', 0, '', ''), (2, NULL, 0, NULL, NULL), -- different plain (11, NULL, 0, '1616161616161616', '88888888'), (12, '', 0, '1616161616161616', '88888888'), (13, '1', 0, '1616161616161616', '88888888'), (14, '7777777', 0, '1616161616161616', '88888888'), (15, '88888888', 0, '1616161616161616', '88888888'), (16, '999999999', 0, '1616161616161616', '88888888'), (17, '17171717171717171', 0, '1616161616161616', '88888888'), (18, '242424242424242422424242',0, '1616161616161616', '88888888'), -- different keymod (31, '1616161616161616', NULL, '1616161616161616', '88888888'), (32, '1616161616161616', -1, '1616161616161616', '88888888'), (33, '1616161616161616', -1.1, '1616161616161616', '88888888'), (34, '1616161616161616', 1.1, '1616161616161616', '88888888'), (35, '1616161616161616', 2, '1616161616161616', '88888888'), (36, '1616161616161616', '0', '1616161616161616', '88888888'), (37, '1616161616161616', 1, '242424242424242422424242', '88888888'), -- different key (51, '1616161616161616', 0, NULL, '88888888'), (52, '1616161616161616', 0, '', '88888888'), (53, '1616161616161616', 0, '1', '88888888'), (54, '1616161616161616', 0, '7777777', '88888888'), (55, '1616161616161616', 0, '88888888', '88888888'), (56, '1616161616161616', 0, '999999999', '88888888'), (57, '1616161616161616', 0, '17171717171717171', '88888888'), (58, '1616161616161616', 0, '242424242424242422424242', '88888888'), (59, '1616161616161616', 0, '272727272727272727272727272', '88888888'), (61, '1616161616161616', 1, NULL, '88888888'), (62, '1616161616161616', 1, '', '88888888'), (63, '1616161616161616', 1, '1', '88888888'), (64, '1616161616161616', 1, '7777777', '88888888'), (65, '1616161616161616', 1, '88888888', '88888888'), (66, '1616161616161616', 1, '999999999', '88888888'), (67, '1616161616161616', 1, '17171717171717171', '88888888'), (68, '1616161616161616', 1, '242424242424242422424242', '88888888'), (69, '1616161616161616', 1, '272727272727272727272727272', '88888888'), -- different iv (81, '1616161616161616', 1, '242424242424242422424242', NULL), (82, '1616161616161616', 1, '242424242424242422424242', ''), (83, '1616161616161616', 1, '242424242424242422424242', '1'), (84, '1616161616161616', 1, '242424242424242422424242', '7777777'), (85, '1616161616161616', 1, '242424242424242422424242', '88888888'), (86, '1616161616161616', 1, '242424242424242422424242', '999999999'), (87, '1616161616161616', 1, '242424242424242422424242', '17171717171717171'), (88, '1616161616161616', 1, '242424242424242422424242', '242424242424242422424242'), (89, '1616161616161616', 1, '242424242424242422424242', '272727272727272727272727272'); CREATE TABLE tcipher (id INT, CIPHER RAW, keymod INT, key TEXT, iv TEXT); INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 1; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 2; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 11; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 12; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 13; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 14; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 15; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 16; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 17; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 18; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 31; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 32; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 33; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 34; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 35; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 36; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 37; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 51; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 52; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 53; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 54; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 55; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 56; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 57; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 58; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 59; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 61; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 62; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 63; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 64; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 65; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 66; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 67; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 68; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 69; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 81; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 82; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 83; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 84; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 85; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 86; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 87; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 88; INSERT INTO tcipher SELECT id, dbms_obfuscation_toolkit.des3encrypt(strtoraw(plain), strtoraw(key), keymod, strtoraw(iv)), keymod, key, iv FROM tplain WHERE id = 89; SELECT * FROM tcipher; SELECT id, rawtostr(dbms_obfuscation_toolkit.des3decrypt(cipher, strtoraw(key), keymod, strtoraw(iv))) FROM tcipher ORDER BY id; DROP TABLE tplain, tcipher; /* ------------------------------------------------------------------------------------------------ * part 4: other functions * ------------------------------------------------------------------------------------------------ */ SELECT length(dbms_obfuscation_toolkit.desgetkey(strtoraw('11111111112222222222333333333344444444445555555555666666666677777777778888888888'))) FROM dual; SELECT length(dbms_obfuscation_toolkit.des3getkey(0, strtoraw('11111111112222222222333333333344444444445555555555666666666677777777778888888888'))) FROM dual; SELECT length(dbms_obfuscation_toolkit.des3getkey(1, strtoraw('11111111112222222222333333333344444444445555555555666666666677777777778888888888'))) FROM dual; SELECT dbms_obfuscation_toolkit.md5(strtoraw('1111111111111111')) FROM dual; CREATE TABLE deskey(id INT, key RAW); CREATE TABLE des3key0 (id INT, key RAW); CREATE TABLE des3key1 (id INT, key RAW); INSERT INTO deskey SELECT generate_series(1, 1000), dbms_obfuscation_toolkit.desgetkey(strtoraw('11111111112222222222333333333344444444445555555555666666666677777777778888888888')); INSERT INTO des3key0 SELECT generate_series(1, 1000), dbms_obfuscation_toolkit.des3getkey(0, strtoraw('11111111112222222222333333333344444444445555555555666666666677777777778888888888')); INSERT INTO des3key1 SELECT generate_series(1, 1000), dbms_obfuscation_toolkit.des3getkey(1, strtoraw('11111111112222222222333333333344444444445555555555666666666677777777778888888888')); CREATE TABLE allcnt (cnt INT); INSERT INTO allcnt SELECT count(*) FROM deskey GROUP BY key; INSERT INTO allcnt SELECT count(*) FROM des3key0 GROUP BY key; INSERT INTO allcnt SELECT count(*) FROM des3key1 GROUP BY key; SELECT cnt,count(*) FROM allcnt GROUP BY cnt; -- test (need to spend too much time) -- CREATE TABLE moretime(id INT, key RAW); -- INSERT INTO moretime SELECT generate_series(1, 100000), -- dbms_obfuscation_toolkit.desgetkey(strtoraw('11111111112222222222333333333344444444445555555555666666666677777777778888888888')); -- -- 预期 key列重复的字符不多 -- SELECT * FROM moretime ORDER BY key ASC LIMIT 10; -- SELECT * FROM moretime ORDER BY key DESC LIMIT 10; -- DROP TABLE moretime; DROP TABLE deskey, des3key0, des3key1, allcnt; DROP EXTENSION pgcrypto; RESET search_path; DROP SCHEMA pgcrypto_2_text CASCADE;

1 设置默认版本 contrib/pgcrypto/pgcrypto.control defaultl_version = '2.0' dbcompatibilities = 'A, B, PG' 生成同名的sql文件 touch contrib/pgcrypto/pgcrypto-2.0.sql 安装sql文件 # pgcrypto.control与pgcrypt.*.sql文件 ll $GAUSSHOME/share/postgresql/extension | grep pgcrypto 加载插件 # 执行：CREATE EXTENSION pgcrypto .. CreateExtension check_valild_extension_name # 检查插件名：要求不含 '-'、不含'..'等路径字符 get_extension_oid heap_open('pg_extension') systable_beginscan # 遍历系统表 pg_extension：确保插件存在 read_extension_control_file parse_extension_control_file get_extension_control_filename sprintf("{share}/extension/{extension_name}.control") ParreConfigFp # 获取 directory, default_version, module_pathname, dbcompatibilities 等参数 get_extension_script_filename get_extension_script_direcotry if name is 'pgcrypto': sprintf("{script_dir}/crypto-a.sql") read_extension_script_file InsertExtensionTuple # 插入系统表 pg_extension execute_extension_script ApplyExtensionUpdates

1 简介 1.1 目的 早期版本的oracle提供一个包含多个加密函数的包，用户可通过SQL语法调用加密函数，实现数据加密功能。本文主要介绍以下几点： oracle加密包的功能与使用方法 vastbase实现oracle加密包 vastbase加密包的功能与使用方法 本文档可指导开发者、测试者熟悉vastbase加密包特性的功能场景。 本文档的内容已被上传至tapd，tapd上，排版更好看，测试用例更方便复制。建议直接从tapd上查看本文档原文： https://www.tapd.cn/60475194/markdown_wikis/show/#1160475194001007194 1.2 适用范围 vastbase 3.0.8及之后版本。 1.3 术语定义、首字母缩写词和缩略语 DES：Data Encryption Standard，数据加密标准，一种早期通用的加密算法 3DES：Triple DES，基于DES演进的一种早期通用的加密算法 MD5：Message-Digest Algorithm，一种早期通用的数据摘要算法 1.3 参考资料 oracle dbms_obfuscation_toolkit 函数介绍：https://docs.oracle.com/cd/A97630_01/appdev.920/a96590/adgsec04.htm oracle dbms_obfuscation_toolkit 函数参数：https://docs.oracle.com/cd/B10500_01/appdev.920/a96612/d_obtoo2.htm tapd上，本需求文档： https://www.tapd.cn/60475194/markdown_wikis/show/#1160475194001007194 tapd上，本需求的测试用例： https://www.tapd.cn/60475194/markdown_wikis/show/#1160475194001007197 2 支持oracle加密插件dbms_obfuscation_toolkit 2.1 功能简述 早期版本oracle提供7个内置加密函数。早在4年或更早之前的旧版本vastbase中，也提供7个与oracle一致的加密函数，这7个函数，函数名、函数入参与oracle一致，但是，6个函数输出、15个函数参数约束与oracle不一致，本特性将重构这些加密函数，确保函数的输出、约束等与oracle一致。 一、基础知识 加密的原理 加密算法有很多种，不同场景中，适合的加密算法类型不同。对存储数据加密，通常采用对称密码算法，此处主要介绍对称密码算法： 加密原理：输入1个明文，输入1个密钥，对明文和密钥进行一些可逆的运算（比如异或，代换），可得到1个密文。密钥通常是随机生成的 加密算法：DES算法，发布于1977年，已确定为不安全加密算法。3DES算法，调用3次DES算法，生成3个密钥，安全性与DES差别不大，也属于不安全的加密算法。AES算法，发布于2001年，通用的国际标准，替代不安全的DES算法 数据库加密函数 几乎所有数据库，都提供内置加密函数，让用户通过SQL语句调用。最典型的场景如下： -- 假设，数据库提供函数encrypt(明文，密钥，加密算法)，输出为密文 -- 用户想表't1'中存储数据'shenkun'，则存储的语法如下：（用户自己保管密钥） INSERT INTO t1 VALUES (encrypt('shenkun', '一串随机的密钥', 'aes')); 二、Oracle加密包 早期版本的oracle中，包含1个名为dbms_obfuscation_toolkit的包，提供7个函数，主要实现加密、解密等功能： 版本信息：oracle 8i版本开始提供，oracle 10g版本开始不被推荐使用，由dbms_crypto代替，oracle 21c版本正式移除 函数列表： 生成随机数：desgetkey(seed) -> key 加密：desencrypt(input, key) -> encrypted_data 解密：descdecrypt(input, key) -> decrypted_data 生成随机数：des3getkey(which=>0, seed) -> key 加密：des3encrypt(input, key, which=>0, iv=>012345678abcdef) -> encrypted_data 解密：des3decrypt(input, key, which=>0, iv=>012345678abcdef) -> decrypted_data 哈希：md5(input) -> checksum 函数参数：函数参数有2种数据类型：varchar2, raw。与raw类型相比，如果是varchar2类型，参数名需加上_string后缀。比如，raw类型的入参名为input，则varcha2类型入参名为input_string 函数限制： desgetkey：seed长度最短为80字节 desencrypt：input长度是8字节的整数倍；key最短为8字节，超过8字节的部分被丢弃 descdecrypt：input长度是8字节的整数倍；key最短为8字节，超过8字节的部分被丢弃 des3getkey: seed长度最短为80字节 des3encrypt：input长度是8字节的整数倍；which为0时，key最短为16字节，超过16字节的部分被丢弃；which为1时，key最短为24字节，超过24字节的部分被丢弃 des3dencrypt：input长度是8字节的整数倍；which为0时，key最短为16字节，超过16字节的部分被丢弃；which为1时，key最短为24字节，超过24字节的部分被丢弃 md5：无 参考文档 函数介绍：函数背景、注意事项等：https://docs.oracle.com/cd/A97630_01/appdev.920/a96590/adgsec04.htm 函数参数：参数类型、调用示例等：https://docs.oracle.com/cd/B10500_01/appdev.920/a96612/d_obtoo2.htm 以下是上述函数的调用示例： ...

2 代码 新增试图 SELECT * FROM pg_user_settings; -- 新增视图 pg_user_settings SELECT rolname,profile FROM pg_authid WHERE rolcanlogin = true LEFT JOIN vb_profile ON rolname | setname | setting | settype | settype | setsource --------+-----------------------+----------+---------+-------------+---------------- u1 | password_effect_time | 90 | int | profile guc | pro1 | cluster guc | postgresql.conf 参数1: failed_login_attempts # opegauss使用1个内存中的hash表，存储密码失败次数 ClientAuthentication # 进行身份认证时，如果认证失败，则更新hash表中的失败次数值 UpdateFailCountToHashTable InitAccountLockHashTable hash_search # 如果失败次数超过GUC限制，则锁定账户 if failed_login_attempts： lockflag = True 参数2: password_lock_time 参数3: password_effect_time # 通过内置函数，主动查看密码剩余天数 gs_password_deadline GetCurrentUserId # 1 获取当前用户 GetUserCurrentPwdtime # 2 从系统表pg_auth_history中，获取密码创建时间passwordtime heap_open('pg_auth_history') systable_beginscan floor(u_sess->attr.attr_security.Password_effect_time) # 3 从guc参数password_effect_time中获取密码有效天数 # 计算步骤2和3中的时间，返回剩余过期天数 参数4: password_reuse_max AddAuthHistory systable_beginscan('pg_auth_history') # 1 从系统表pg_auth_history中，获取曾用过的密码rolpassword，获取生成密码的时间passwordtime systable_getnext_back # 倒着从系统表扫描，即先扫描不太古老的旧密码，再扫描更古老的旧密码 pg_sha256_encrypt # 使用旧密码的salt，加密新密码 if strncmp : # 比较新密码是否与旧密码相等 hasSame = True # 如果新旧密码相等，计算新旧密码间隔天数，与GUC参数password_reuse_time比较，判断是否满足密码重用时间限制 if now - pg_auth_history.passwordtime > 'password_reuse_time'： isTimeSafe = True # 计算这是倒数第几个密码 changeTimes++ if changeTimes > 'password_reuse_max': isMaxSafe # 满足 password_reuse_time 和 password_reuse_max 中任意一个条件，都可以修改密码 # - 要么用好几次之前的密码（满足 password_reuse_max 条件） # - 要么用很旧之间使用的密码（满足 password_reuse_time 条件） if hasSame and not isTimeSafe and not isMaxSafe: # 报错 参数5: password_reuse_time

| -| -| -| -| -| -| -| | 1 | FAILED_LOGIN_ATTEMPTS | [1, max] unlimited | failed_login_attempts | [1,1000] | 认证类 | 登录数据库时，如果密码错误次数超过<本参数>，则账号会被锁定，短期内无法再登录 | | 2 | PASSWORD_LOCK_TIME | [1, max] unlimited | password_lock_time | [1,525600]min | 认证类 | 如果因密码错误次数过多，导致账号被锁，将锁定<本参数>的时间 | | 3 | PASSWORD_LIFE_TIME | [1, max] unlimited | password_effect_time | [30,36500]day | 认证类 | 设置或更新用户密码时，超过<本参数>的时间，密码会过期（oracle与vastase表现不同） | | 4 | PASSWORD_GRACE_TIME | - | - | - | 认证类 | 如果密码过期，在<本参数>的时间内，密码仍可使用 | | 5 | PASSWORD_REUSE_TIME | [1, max] unlimited | password_reuse_time | [30, 90]day | 认证类 | 用户设置密码时，如果密码在过去的<本参数>的时间内别用过，则密码不能复用，opengauss [0,3650] | | 6 | PASSWORD_REUSE_MAX | - | password_reuse_max | [1,100] | 认证类 | 用户设置密码时，如果密码已被重复使用过<本参数>的次数，则密码不能复用 | | 7 | INACTIVE_ACCONT_TIME | - | - | - | 认证类 | 如果一个账号在<本参数>的时间内，未登录过，则锁定账号 | | 8 | PASSWORD_VERIFY_FUNCTION | - | - | - | 认证类 | 用户设置密码时，调用<本参数>指定的函数，检查密码是否符合要求 | | 9 | PASSWORD_ROLLOVER_TIME | - | - | - | 认证类 | 新旧密码滚动更新天数 | | 10 | SESSIONS_PER_USER | - | - | - | 资源类 | 1个用户可建立的最大连接数 | | 11 | CPU_PER_SESSION | - | - | - | 资源类 | 1个连接可使用CPU时间数 | | 12 | CPU_PER_CALL | - | - | - | 资源类 | 1个调用（比如1个SQL）可使用CPU时间数 | | 13 | CONNECT_TIME | - | - | - | 资源类 | 1个连接可持续的时间 | | 14 | IDLE_TIME | [1, max] unlimited | session_timeout | [0,86400] | 资源类 | 1个连接空闲时间限制 | | 15 | LOGICAL_READS_PER_CALL | - | - | - | 资源类 | 1次调用（比如1个SQL）允许读写的page数量 | | 16 | LOGICAL_READS_PER_SESSION | - | - | - | 资源类 | 1个连接允许读取的page数量 | | 17 | PRIVATE_SGA | - | - | - | 资源类 | 1个连接可使用的共享内存空间大小 | | 18 | CONPOSITE_LIMIT | - | - | - | 资源类 | 1个连接可可使用的总资源：按cpu时间、连接时间、page读写数等加权求和 | ...

https://www.tapd.cn/60475194/markdown_wikis/show/#1160475194001006330 3 异常参数 3.1 DES encrypt 封装函数 CREATE OR REPLACE FUNCTION desencrypt(hex_plain VARCHAR2, hex_key VARCHAR2) RETURN VARCHAR2 IS cipher RAW(100); BEGIN dbms_obfuscation_toolkit.desencrypt(hextoraw(hex_plain), hextoraw(hex_key), encrypted_data=>cipher); RETURN rawtohex(cipher); END; / 函数格式 desencrypt(plain, key, cipher); 约束： 构造用例 明文长度 密钥长度 输出：密文长度 示例 结果 一、正常用例 8 8 8 desencrypt() abc 16 8 16 desencrypt() abc 一、明文长度异常 0 8 16 desencrypt() 明文不是8的倍数 1 8 - desencrypt() 明文不是8的倍数 7 8 - desencrypt() 明文不是8的倍数 15 8 - desencrypt() 明文不是8的倍数 二、密钥长度异常 8 0 8 desencrypt() 密钥长度不是8 8 1 8 desencrypt() 密钥长度不是8 8 15 8 desencrypt() 密钥长度不是8 8 16 8 desencrypt() 密钥长度不是8 3.2 DES decrypt 3.3 3DES encrypt 封装函数 ...

1 Oracle 集群管理 1.2 登录数据库 sqlplus / as sysdba 2 Oracle 语法 2.1 数据类型 仅列举常见数据类型： 大类 类型 解释 数值 INT or INTGER 整数 - NUMBESR(p, s) p: 精度，整数和小数总位数，s: 小数位数 - SMALLINT 等于 NUMBESR(38) - FLOAT(p) 浮点数，p: 二进制精度，最大126 字符 CHAR(n) 固定长度字符，n: 长度，不足n用空格填充 - VARCHAR2(n) 可变长度字符，n: 最大长度，最大4000 时间 DATE 日期 - TIMESTAMP 日期和时间 二进制 CLOB 长文本数据 2.2 常用SQL 常见语法 -- 1 创建表 CREATE TABLE t1 (c1 INT, c2 VARCHAR2(10)); -- 2 插入数据 INSERT INTO t1 VALUES (1, 'abcc'); INSERT INTO t1 VALUES (2, 'ddee'); -- 3 查询数据 SELECT * FROM t1 WHERE c1 > 0; -- 4 更新数据 UPDATE t1 SET c2 = 'abbc' WHERE c1 = 1; -- 5 删除数据 DELETE FROM t1 WHERE c1 = 2; -- 6 删除表 DROP TABLE t1; 内置函数 ...

1 概述 来源：https://docs.oracle.com/en/database/oracle/oracle-database/23/dbseg 基础安全 身份认证 密码最小长度 连续失败锁定 账户锁定 新旧密码重复 密码有效期 访问控制 敏感数据发现 传输加密 安全审计 高级安全 透明加密 数据脱敏 访问控制 行级访问控制 数据库保险柜：细粒度访问控制

1 Oracle 集群管理 1.2 登录数据库 sqlplus / as sysdba 2 Oracle 语法 2.1 数据类型 仅列举常见数据类型： 大类 类型 解释 数值 INT or INTGER 整数 - NUMBESR(p, s) p: 精度，整数和小数总位数，s: 小数位数 - SMALLINT 等于 NUMBESR(38) - FLOAT(p) 浮点数，p: 二进制精度，最大126 字符 CHAR(n) 固定长度字符，n: 长度，不足n用空格填充 - VARCHAR2(n) 可变长度字符，n: 最大长度，最大4000 时间 DATE 日期 - TIMESTAMP 日期和时间 二进制 CLOB 长文本数据 2.2 常用SQL 常见语法 -- 1 创建表 CREATE TABLE t1 (c1 INT, c2 VARCHAR2(10)); -- 2 插入数据 INSERT INTO t1 VALUES (1, 'abcc'); INSERT INTO t1 VALUES (2, 'ddee'); -- 3 查询数据 SELECT * FROM t1 WHERE c1 > 0; -- 4 更新数据 UPDATE t1 SET c2 = 'abbc' WHERE c1 = 1; -- 5 删除数据 DELETE FROM t1 WHERE c1 = 2; -- 6 删除表 DROP TABLE t1; 内置函数 ...