项目文档

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 以下是上述函数的调用示例： ...

摘要：本文主要分为3个章节： 介绍Vastbase基于密码的身份认证机制，介绍密码相关配置参数，指出当前控制参数的局限性，提出PROFILE功能支持细粒度设置密码相关配置参数。 介绍Vastbase的PROFILE功能如何使用，包括管理PROFILE功能、管理PROFILE参数、管理用户PROFILE 介绍Vastbase身份认证流程与原理，介绍PROFILE功能的实现原理 目录 1 PROFILE 功能背景 1.1 身份认证机制 1.2 身份认证配置 1.3 身份认证优化 2 PROFILE 功能使用 2.1 PROFILE 简单示例 2.2 管理 PROFILE 功能 2.3 管理 PROFILE 参数 2.4 管理 用户 PROFILE 3 PROFILE 功能原理 3.1 身份认证元数据 3.2 身份认证流程 1 PROFILE 功能背景 1.1 身份认证机制 Vastbase身份认证模块，主要采用基于密码的认证机制，常用的密码操作包括： 创建密码 创建新用户时，需指定密码。语法示例如下： CREATE USER u1 PASSWORD 'u1.password'; Vastbase接收密码后，计算密码的哈希值，并将密码哈希值、密码创建时间等存储至系统表中。 使用密码 用户连接Vastbase时，需提供密码，使用vsql连接的命令如下： vsql -h {host} -p {port} -U u1 -W 'u1.password' -d vastbase -r Vastbase会与vsql进行身份认证交互，基于存储的密码哈希值，校验密码是否正确。同时，Vastbase还会进行其他检查，比如：密码是否过期、密码错误次数是否过多等。 修改密码 用户修改密码时，语法示例如下： ...

1 CREATE/ALTER/DROP PROFILE 2 CREATE/ALTER USER .. PROFILE 3 验证profile生效 验证 failed_login_attempts 与 password_lock_time 验证 password_life_time 与 password_grace_time 验证 password_reuse_time 和 password_reuse_max 验证 idle_time 3 view CREATE PROFILE pro1 limit password_reuse_time 0.00002 password_reuse_max 10; -- 1.72s CREATE USER u1 PASSWORD 'u1.pass1' PROFILE pro1; ALTER USER u1 PASSWORD 'u1.pass2'; ALTER USER u1 PASSWORD 'u1.pass1'; -- error, interval < password_reuse_time \! sleep 2 ALTER USER u1 PASSWORD 'u1.pass1'; -- succeed, interval > password_reuse_time ALTER PROFILE pro1 LIMIT password_reuse_time 0.00005; -- 4.32s ALTER USER u1 PASSWORD 'u1.pass3'; ALTER USER u1 PASSWORD 'u1.pass4'; ALTER USER u1 PASSWORD 'u1.pass3'; -- error, interval < password_reuse_time \! sleep 5 ALTER USER u1 PASSWORD 'u1.pass3'; -- succeed, interval > password_reuse_time 1 CREATE/ALTER/DROP PROFILE -- check profile feature SHOW resource_limit; -- ================================ part 1 ================================ -- create profile CREATE PROFILE pro1; -- succeed: can set setting CREATE PROFILE pro2 LIMIT failed_login_attempts 10; -- succeed: can set more settings CREATE PROFILE pro3 LIMIT failed_login_attempts 10 password_lock_time 20 password_life_time 30 password_grace_time 40 password_reuse_time 50 password_reuse_max 60 idle_time 80; -- succeed: repeated settings are allowed CREATE PROFILE pro4 LIMIT password_life_time 10 password_life_time 20.2; -- succeed: setting value could be 'unlimited' CREATE PROFILE pro5 LIMIT failed_login_attempts unlimited password_lock_time 10 password_life_time unlimited; -- succeed: name max len is 64, name will be truncated CREATE PROFILE aaaaa111111111122222222223333333333444444444455555555556666666666; -- too long, max 64, wi -- truncated name: aaaaa1111111111222222222233333333334444444444555555555566666666 SELECT profile,count(*) FROM vb_profiles GROUP BY profile ORDER BY profile; SELECT * FROM vb_profiles WHERE profile = 'pro5' ORDER BY setname; -- error: profile name is invalid CREATE PROFILE 1; -- int CREATE PROFILE 1.1; -- numeric -- error: profile already exists CREATE PROFILE default LIMIT failed_login_attempts 10; CREATE PROFILE pro1 LIMIT failed_login_attempts 10; /* error: setting is invalid, supported settings: unit type min max ------------------------+---------+----------+---------+------------+---- failed_login_attempts | times | int | 1 | 2147483646 | 10 password_lock_time | days | double | 0.00001 | 24855 | 1 password_life_time | days | double | 0.00001 | 24855 | 180 password_grace_time | days | double | 0.00001 | 24855 | 7 password_reuse_time | days | double | 0.00001 | 24855 | 1 password_reuse_max | times | int | 1 | 2147483646 | 1 idle_time | minutes | double | 0.00001 | 2147483646 | 10 */ CREATE PROFILE epro2 LIMIT data_directory anyvalue; CREATE PROFILE epro3 LIMIT password_effect_time 10; CREATE PROFILE epro4 LIMIT 10 failed_login_attempts; -- error: setting lose value CREATE PROFILE epro5 LIMIT failed_login_attempts; CREATE PROFILE epro6 LIMIT failed_login_attempts 10 password_lock_time; -- error: setting value type is invalud CREATE PROFILE epro7 LIMIT failed_login_attempts 'iamstring'; CREATE PROFILE epro8 LIMIT failed_login_attempts 1.1; CREATE PROFILE epro9 LIMIT failed_login_attempts noint; CREATE PROFILE epro10 LIMIT failed_login_attempts default; -- error: setting value is out of limit CREATE PROFILE epro11 LIMIT failed_login_attempts 0; CREATE PROFILE epro12 LIMIT failed_login_attempts 1 password_lock_time 0; CREATE PROFILE epro13 LIMIT password_lock_time 24856; CREATE PROFILE epro14 LIMIT password_lock_time 0.000001; SELECT profile,count(*) FROM vb_profiles GROUP BY profile ORDER BY profile; DROP PROFILE pro1, pro2, pro3, pro4, pro5; DROP PROFILE aaaaa111111111122222222223333333333444444444455555555556666666666; -- ================================ part 2 ================================ -- alter profile CREATE PROFILE pro1; CREATE PROFILE pro2 LIMIT failed_login_attempts 100; CREATE PROFILE pro3 LIMIT password_lock_time 20; -- succeed: can set setting ALTER PROFILE pro1 LIMIT failed_login_attempts 1000; -- succeed: can set more settings ALTER PROFILE pro2 LIMIT failed_login_attempts 10 password_lock_time 20 password_life_time 30 password_grace_time 40 password_reuse_time 50 password_reuse_max 60 idle_time 80; -- succeed: setting value could be 'unlimited' ALTER PROFILE pro3 LIMIT failed_login_attempts unlimited password_lock_time 10 password_life_time unlimited; -- succeed: can alter default profile ALTER PROFILE default LIMIT failed_login_attempts 80 password_reuse_time 70.3; SELECT * FROM vb_profiles ORDER BY profile,setname; -- error: profile name is invalid ALTER PROFILE 1 LIMIT failed_login_attempts 10; -- int ALTER PROFILE 1.1 LIMIT failed_login_attempts 10; -- numeric ALTER PROFILE aaaaa111111111122222222223333333333444444444455555555556666666666 LIMIT failed_login_attempts 10; -- too long, max 64 -- error: repeated settings are not allowed ALTER PROFILE pro3 LIMIT password_reuse_time 10 password_reuse_time 20.2; -- error: profile doesn't exist ALTER PROFILE epro1 LIMIT failed_login_attempts 10; ALTER PROFILE epro2 LIMIT failed_login_attempts 10; /* error: setting is invalid */ ALTER PROFILE pro1 LIMIT data_directory anyvalue; ALTER PROFILE pro1 LIMIT password_effect_time 10; ALTER PROFILE pro1 LIMIT 10 failed_login_attempts; -- error: setting lose value ALTER PROFILE pro1 LIMIT failed_login_attempts; ALTER PROFILE pro1 LIMIT failed_login_attempts 10 password_lock_time; -- error: setting value type is invalud ALTER PROFILE pro1 LIMIT failed_login_attempts 'iamstring'; ALTER PROFILE pro1 LIMIT failed_login_attempts 1.1; ALTER PROFILE pro1 LIMIT failed_login_attempts noint; ALTER PROFILE pro1 LIMIT failed_login_attempts default; -- error: setting value is out of limit ALTER PROFILE pro1 LIMIT failed_login_attempts 0; ALTER PROFILE pro1 LIMIT failed_login_attempts 1 password_lock_time 0; ALTER PROFILE pro1 LIMIT password_lock_time 24856; ALTER PROFILE pro1 LIMIT password_lock_time 0.000001; DROP PROFILE pro1, pro2, pro3; -- ================================ part 3 ================================ -- drop profile CREATE PROFILE pro1; CREATE PROFILE pro2 LIMIT failed_login_attempts 100; CREATE PROFILE pro3 LIMIT password_lock_time 20; CREATE PROFILE pro4; CREATE PROFILE pro5; -- succeed: drop profile DROP PROFILE pro1; DROP PROFILE IF EXISTS pro1; DROP PROFILE IF EXISTS pro1, pro2, pro3; SELECT profile,count(*) FROM vb_profiles GROUP BY profile; -- error: profile doesn't exist DROP PROFILE pro0; DROP PROFILE pro1, pro4, pro5; -- error: default profile can't be dropped DROP PROFILE default; DROP PROFILE pro4, default; DROP PROFILE IF EXISTS pro4, pro5; 2 CREATE/ALTER USER .. PROFILE -- ================================ part 4 ================================ -- set profile for user SELECT * FROM vb_user_profiles; CREATE PROFILE pro1; CREATE PROFILE pro2; -- succeed: set profile for user CREATE USER u1 PASSWORD 'u1.pass1' PROFILE pro1; CREATE USER u2 PASSWORD 'u2.pass1' PROFILE pro1; CREATE USER u3 PASSWORD 'u3.pass1' PROFILE default; CREATE USER u4 PASSWORD 'u4.pass1'; -- error: profile doesn't exist CREATE USER eu1 PASSWORD 'u4.pass1' PROFILE pro1000; -- error: profile name invalid CREATE USER eu2 PASSWORD 'u4.pass1' PROFILE 1; CREATE USER eu3 PASSWORD 'u4.pass1' PROFILE; SELECT * FROM vb_user_profiles; DROP USER IF EXISTS u1, u2, u3, u4; -- catalog also changed SELECT * FROM vb_user_profiles; DROP PROFILE IF EXISTS pro1, pro2; -- ================================ part 5 ================================ -- alter user profile CREATE PROFILE pro1; CREATE PROFILE pro2; CREATE USER u1 PASSWORD 'u1.pass1'; CREATE USER u2 PASSWORD 'u2.pass1' PROFILE pro2; CREATE USER u3 PASSWORD 'u3.pass1' PROFILE default; -- succeed: alter user set profile ALTER USER u1 PROFILE pro1; ALTER USER u2 PROFILE default; ALTER USER u3 PROFILE pro1; -- error: profile doesn't exist ALTER USER u1 PROFILE pro1000; -- error: profile name invalid ALTER USER u1 PROFILE 1; ALTER USER u1 PROFILE; SELECT * FROM vb_user_profiles ORDER BY roloid; DROP USER IF EXISTS u1, u2, u3; DROP PROFILE IF EXISTS pro1, pro2; -- ================================ part 6 ================================ -- drop user and profile CREATE PROFILE pro1; CREATE PROFILE pro2; CREATE PROFILE pro3; CREATE USER u1 PASSWORD 'u1.pass1' PROFILE pro1; CREATE USER u2 PASSWORD 'u2.pass1' PROFILE pro2; -- succeed: drop user SELECT * FROM vb_user_profiles WHERE rolname = 'u1'; DROP USER u1; SELECT * FROM vb_user_profiles WHERE rolname = 'u1'; -- error: can't drop profile if any user are using it DROP PROFILE pro2; -- error: default profile can't be dropped DROP PROFILE default; DROP PROFILE pro3, default; DROP PROFILE default, pro3; SELECT * FROM vb_user_profiles ORDER BY roloid; DROP USER IF EXISTS u1, u2; DROP PROFILE IF EXISTS pro1, pro2, pro3; 3 验证profile生效 验证 failed_login_attempts 与 password_lock_time CREATE PROFILE pro1 LIMIT failed_login_attempts 3 password_lock_time 0.0001; CREATE USER u1 PASSWORD 'u1.pass1' PROFILE pro1; -- 运行登录失败3次，失败后锁定8秒 CREATE USER u2 PASSWORD 'u2.pass1'; -- 允许登录失败无数次 vsql -p 5432 -d postgres -U u1 -W "u1.error1" -c "SELECT 1;" vsql -p 5432 -d postgres -U u1 -W "u1.error1" -c "SELECT 1;" vsql -p 5432 -d postgres -U u1 -W "u1.error1" -c "SELECT 1;" vsql -p 5432 -d postgres -U u1 -W "u1.pass1" -c "SELECT 1;" -- sleep 8s vsql -p 5432 -d postgres -U u1 -W "u1.pass1" -c "SELECT 1;" -- u2 vsql -p 5432 -d postgres -U u2 -W "u2.error1" -c "SELECT 1;" vsql -p 5432 -d postgres -U u2 -W "u2.error1" -c "SELECT 1;" vsql -p 5432 -d postgres -U u2 -W "u2.error1" -c "SELECT 1;" vsql -p 5432 -d postgres -U u2 -W "u2.pass1" -c "SELECT 1;" -- sleep 8s vsql -p 5432 -d postgres -U u2 -W "u2.pass1" -c "SELECT 1;" vsql -p 5432 -d postgres -c "DROP USER u1,u2" 验证 password_life_time 与 password_grace_time CREATE PROFILE pro2 LIMIT password_life_time 0.0001 -- 8s password_grace_time 0.0001; CREATE USER u1 PASSWORD 'u1.pass1' PROFILE pro2; -- 密码有效期为8s，密码宽容期也为8s CREATE USER u2 PASSWORD 'u2.pass1'; -- 密码有效期无限制 \q vsql -p 5432 -d postgres -U u1 -W "u1.pass1" -c "SELECT 1;" -- sleep 8s vsql -p 5432 -d postgres -U u1 -W "u1.pass1" -c "SELECT 1;" -- sleep 8s vsql -p 5432 -d postgres -U u1 -W "u1.pass1" -c "SELECT 1;" vsql -p 5432 -d postgres -U u2 -W "u2.pass1" -c "SELECT 1;" -- sleep 8s vsql -p 5432 -d postgres -U u2 -W "u2.pass1" -c "SELECT 1;" -- sleep 8s vsql -p 5432 -d postgres -U u2 -W "u2.pass1" -c "SELECT 1;" 验证 password_reuse_time 和 password_reuse_max CREATE PROFILE pro3 LIMIT password_reuse_time 0.0001 -- 不能用过去8秒使用过的密码 password_reuse_max 100; -- 不能用过去100次使用过的 DROP USER IF EXISTS u1,u2; CREATE USER u1 PASSWORD 'u1.pass1' PROFILE pro3; ALTER USER u1 PASSWORD 'u1.pass2'; ALTER USER u1 PASSWORD 'u1.pass1'; SELECT pg_sleep(3); ALTER USER u1 PASSWORD 'u1.pass1'; SELECT pg_sleep(3); ALTER USER u1 PASSWORD 'u1.pass1'; SELECT pg_sleep(3); ALTER USER u1 PASSWORD 'u1.pass1'; CREATE USER u2 PASSWORD 'u2.pass1'; ALTER USER u2 PASSWORD 'u2.pass2'; ALTER USER u2 PASSWORD 'u2.pass1'; ALTER USER u2 PASSWORD 'u2.pass2'; ALTER USER u2 PASSWORD 'u2.pass1'; -- ----------------------------- CREATE PROFILE pro4 LIMIT password_reuse_max 2 -- 不能用过去2次使用过的 password_reuse_time 20; DROP USER IF EXISTS u1,u2; CREATE USER u1 PASSWORD 'u1.pass1' PROFILE pro4; ALTER USER u1 PASSWORD 'u1.pass2'; ALTER USER u1 PASSWORD 'u1.pass3'; ALTER USER u1 PASSWORD 'u1.pass4'; ALTER USER u1 PASSWORD 'u1.pass3'; -- error ALTER USER u1 PASSWORD 'u1.pass2'; -- ok 验证 idle_time CREATE PROFILE pro5 LIMIT idle_time 0.1; -- 6s DROP USER IF EXISTS u1,u2; CREATE USER u1 PASSWORD 'u1.pass1' PROFILE pro5; CREATE USER u2 PASSWORD 'u2.pass1'; \q vsql -p 5432 -d postgres -U u1 -W "u1.pass1" -r SELECT 1; -- 不用pg_sleep \! sleep 7 SELECT 1; \q vsql -p 5432 -d postgres -U u2 -W "u2.pass1" -r SELECT 1; -- 不用pg_sleep \! sleep 7 SELECT 1; 3 view CREATE OR REPLACE VIEW SYS.DBA_PROFILES AS SELECT profile "PROFILE", setname "RESOURCE_NAME", setkind "RESOURCE_TYPE", setval "LIMIT" FROM vb_profiles; 升级 审计 oid knl_u_misc_init AuthenticatedUserId = InvalidOid ResetStreamStatus AuthenticatedUserId = InvalidOid InitializeSessionUserId AuthenticatedUserId = SearchSysCache(rolname / useroid) InitializeSessionUserIdStandalone AuthenticatedUserId = BOOTSTRAP_SUPERUSERID SessionUserId CurrentUserId create_session_context # get_setting_from_guc_or_profile enable_session_sig_alarm # get_setting_from_guc_or_profile ReadCommand # get_setting_from_guc_or_profile enable_session_sig_alarm # get_setting_from_guc_or_profile disable_session_sig_alarm TpoolSchedulerMain CheckSessionTimeout # get_setting_from_guc_or_profile handle_sig_alarm CheckSessionTimeout # get_setting_from_guc_or_profile WaitMission # get_setting_from_guc_or_profile enable_session_sig_alarm

1 Oracle兼容性-支持profile功能 1.1 功能简述 本特性实现与oracle profile相同的功能，支持为不同用户设置7个不同的配置参数： failed_login_attempts password_lock_time password_life_time password_grace_time password_reuse_time password_reuse_max idle_time 简单使用示例如下： CREATE PROFILE pro1 LIMIT failed_login_attempts 10; CREATE USER u1 PASSWORD 'u1.pass1'; ALTER USER u1 PROFILE u1; 1.2 实现方案 一、元数据 本特性的关键实现为：对用户提供DDL接口，在内核中存储配置数据。因此，在介绍代码流程前，先介绍接口与元数据。 新增GUC参数resource_limit (与oracle参数名一致) 控制是否启用oracle profile功能。 如果该参数为on，以下5个guc参数则不再生效：[failed_login_attempts, password_lock_time, password_effect_time, password_reuse_time, password_reuse_max]。另外，对于已经过身份认证的连接，session_timeout也不再生效，相关功能均由profile中的用户级参数控制。 对于新安装的集群、旧版本升级的集群，该参数的值存在差异： 新安装：新安装的数据库，即3.0.8版本及之后，该guc参数值默认为on（初始值为off，由initdb自动设置为on） 升级：从3.0.8之前版本，升级到3.0.8版本时，在升级的过程中，该参数的值仍然是off，即升级上来默认不可用profile功能 SELECT * FROM pg_settings WHERE name = 'resource_limit'; -[ RECORD 1 ]------------------------------------------- name | resource_limit setting | on -- 默认为on （由initdb设置为on，从旧版本升级到新版本时，升级脚本会将该参数设置为off） unit | category | Ungrouped short_desc | Used to enable profile feature extra_desc | default on context | postmaster -- 需重启生效 vartype | bool source | default min_val | max_val | enumvals | boot_val | off reset_val | off sourcefile | sourceline | 修改6个GUC参数的生效条件 当GUC参数resource_limit取值为on时，以下6个GUC参数不再生效：[failed_login_attempts, password_lock_time, password_effect_time, password_reuse_time, password_reuse_max, session_timeout] ...

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 简介 1.1 目的 vastbase与oracle都支持设置运行参数： vastbase：支持设置集群级guc参数，例如，guc参数failed_login_attempts用于设置’用户密码错误多少次后锁定账户’，该参数对集群中所有用户（非初始用户）都有效。 oracle：也支持failed_login_attempts参数，但是oracle支持为不同用户设置不同的failed_login_attempts值。我们且将此类参数称为用户级参数。 与oracle相比，vastbase的guc参数功能存在2个差异： 作用范围：oracle支持设置用户级参数，vastbase大部分参数都是集群级guc参数 参数个数：23版本的oracle支持18个用户级参数，其中6个可在vastbase找到对应的guc参数 参数取值：vastbase中，6个guc参数的取值范围与oracle profile不同 本文主要介绍以下几点： oracle用户级参数的个数、设置方法 vastbase中对应集群级guc参数的个数、设置方法 vastbase新需求：尽量实现与oracle一致的用户级guc参数 本文档可指导开发者、测试者熟悉vastbase用户级guc配置参数特性 本文档的内容已被上传至tapd，tapd上，排版更好看，测试用例更方便复制。建议直接从tapd上查看本文档原文: https://www.tapd.cn/60475194/markdown_wikis/show/#1160475194001007196 1.2 适用范围 vastbase 3.0.8及之后版本，vastbase用户级参数profile特性接口设计、使用指导等。 1.3 术语定义、首字母缩写词和缩略语 GUC：Grand Unifed Configuralion，postgresql和vastbase的配置参数 1.3 参考资料 原始需求： https://www.tapd.cn/60475194/prong/stories/view/1160475194001074627 tapd上本需求文档链接： https://www.tapd.cn/60475194/markdown_wikis/show/#1160475194001007196 tapd上6个与密码相关的guc参数的使用参考： https://www.tapd.cn/60475194/markdown_wikis/show/#1160475194001007200 oracle 23 profile语法以及相关参数： https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/CREATE-PROFILE.html mysql 8.0 create user语法以及相关参数： https://mysql.net.cn/doc/refman/8.0/en/create-user.html mysql 配置参数列表：https://mysql.net.cn/doc/refman/8.0/en/server-system-variable-reference.html vastbase g100 create user语法以及相关参数： https://docs.vastdata.com.cn/zh_CN/VastbaseG100/V3.0.8/1/6a4766151e29496b9f88cd2d9dd3db2e 2 支持oracle用户级配置profile 2.1 功能简述 【需求来源】桂林银行 需求链接：https://www.tapd.cn/60475194/prong/stories/view/1160475194001074627 需求描述：桂林银行客户指出，目前对于数据库用户的管理方式不够便捷，无法像oracle profile方式为用户赋予一组安全策略。一些属性如密码有效期、密码过期天数、密码尝试次数等也只能全局设定，无法指定用户配置。- 需求范围：桂林银行使用的数据库兼容模式包括oracle和mysql两种兼容模式。 【需求背景】oracle 23的profile功能 通俗来讲，oracle的profle可理解为细粒度的配置参数。1个profile包含1组配置参数，不同用户可使用不同的profile。按上述例子，要单独控制某个用户的密码有效期，操作步骤如下： 创建用户 CREATE USER u1 IDENTIFIED BY "u1.1234"; 创建profile，同时设置用户级参数 此处，以设置密码有效期参数为例： CREATE PROFILE pro1 LIMIT FAILED_LOGIN_ATTEMPTS 10; 设置用户u1使用profile pro1 ...

1 需求背景 客户使用vb什么版本？ 2215 客户的要求： 2 需求分析 2.1 适配点 在使用vastbase的多个阶段，都可能涉及pg_hba.conf 编译安装 初始化 升级 配置 gs_guc修改pg_hba 手动编辑pg_hba 启动 运维 pg_backup pg_rewind vb_ctl restart 2.2 代码流程 编译 初始化 main # initdb.cpp canonicalize_path # PGDATA find_other_exec # 在gs_initdb bin目录下，查找gaussdb bin # 在$GAUSSHOME/share/postgrsql目录下，检查以下文件是否存在 check_input('postgres.bki, postgres.description, pg_hba.conf.sample, postgresql.conf.sample, ..., system_views.sql, ...') check_locale_encoding init_log gs_getenv_r('GAUSSLOG') canonicalize_path('$PGDATA/PG_VERSION') fprintf(PG_MAJORVERSION = '9.2') setup_config readfile("postgresql.conf.sample") readfile("pg_hba.sample") writefile("pg_hba.conf") bootstrap_template1 write_version_file setup_auth get_set_pwd setup_depend load_plpgsql setup_sysviews 升级 配置 启动 pg_ctl 运行 PostmasterMain # postmaster 线程 InitializeGUCOptions getopt_r load_hba # 加载hba ServerLoop ConnCreate StreamConnection BackendStartup PostgresMain # postgres 线程 InitBackendWorker InitSession CheckAuthentication PerformAuthentication port->protocol_config->fn_authenticate ClientAuthentication hba_getauthmethod check_hba g_instance.libpq_cxt_comm_parsed_hba_lines # 读取缓存的hba if init_user: get_default_auth_method else: check_hostname sendAuthRequest recv_and_check_password_packet # 接收客户端的password crypt_verify for (;;) ReadCommand exec_simple_query('SQL') SIGHUB_handler load_hba 运维 ...

| -| -| -| -| -| -| -| | 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读写数等加权求和 | ...