K.19. lt_ope
Prev UpAppendix K. 额外提供的模块Home Next

K.19. lt_ope

K.19.1. What is lt_ope?
K.19.2. function usage

K.19.1. What is lt_ope?

Order-Preserving Encryption是一种加密方法，用于保留数据的顺序，可以直接对加密数据进行比较操作，而无需解密操作数。

lt_ope支持三种数据类型的保序加密：bigint、double precision和text。 text类型支持的最大明文长度为128字节，目前支持的字符类型包括ASCII中的0至127共128个字符。三种明文加密后得到的密文类型为ope_type。

密文类型ope_type支持以下操作：>、>=、<、<=、=和!=。 同时，由文本类型明文加密的ope_type类型密文支持like 'xxx%'操作。

密文类型ope_type还支持order by、between...and、IN、NOT IN和BTREE index。

K.19.2. function usage

K.19.2.1. bigint类型保序加密

bigint类型加密接口： ope_bigint_encrypt(plaintext bigint) return ope_type。 

SELECT ope_bigint_encrypt(12345678);
                    ope_bigint_encrypt                    
----------------------------------------------------------
 663667376838DD03DDFF2F57DE01DE028841DF02DF43DF63DF65DF68

K.19.2.2. double precision类型保序加密

double precision类型加密接口： ope_double_encrypt(plaintext double precision) return ope_type。 

SELECT ope_double_encrypt(1234.5678);
                                      ope_double_encrypt                                      
----------------------------------------------------------------------------------------------
 663667376838DC02DDFF5227DE01DE026961DF02DDF4DF43DF63DF65DF68DDFDDDFDDDFDDDFDDDFDDA8CDDFDDDFD

K.19.2.3. text类型保序加密

text类型加密接口： ope_text_encrypt(plaintext text) return ope_type。 

SELECT ope_text_encrypt('123456789@qq.com');
                                       ope_text_encrypt                                       
----------------------------------------------------------------------------------------------
 6535643463336232DDFF52CBDE01DE02FD63DF02DF43DF63DF65DF68DF6CDFE2FF82FF82DDF4FDDFDA8CFF01FDFC

K.19.2.4. OPE解密

OPE解密接口： ope_decrypt(ciphertext ope_type) return text。

K.19.2.5. OPE示例

以下是文本类型保序加密的示例： 

-- The info field in the test_ope table is plaintext of text type, and the opedata field is order-preserving encrypted ciphertext of type ope_type:
create table test_ope(id int, info text, opedata ope_type, crt_time timestamp not null);
CREATE TABLE

create index opedata_index on test_ope USING BTREE (opedata);
CREATE INDEX

select * from test_ope ;
    id |               info               |                                                                           opedata                                                                            |          crt_time
----+----------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------
    1 | 27025cc9acd114a4f5192930ebc6fffb | 6535643463336232DE01067CDF65DDFD8BE9DE01DF43FDDFFDDFDF6CFDDAFDDFFDE2DDFFDDFFDF028584FDDADF02FDEBDF43DDFFDF6CDE01DF6CDE02DDFDFDE3FDDBFDDFDF63FDEBFDEBFDEBFDDB | 2022-06-16 11:10:37.170803
    2 | cc97ddaf161894af7d5fbbe4ae31d0a1 | 6535643463336232FDDFE67CFDDFDF6C8BE9DF65FDE2FDE2FDDAFDEBDDFFDF63DDFFDF68DF6CDF028584FDDAFDEBDF65FDE2DF43FDEBFDDBFDDBFDE3DF02FDDAFDE3DE02DDFFFDE2DDFDFDDADDFF | 2022-06-16 12:10:37.170955
    3 | 433dc866e329799e7d134992b13960fa | 6535643463336232DF021A7DDE02DE028BE9FDE2FDDFDF68DF63DF63FDE3DE02DE01DF6CDF65DF6C8584DF6CFDE3DF65FDE2DDFFDE02DF02DF6CDF6CDE01FDDBDDFFDE02DF6CDF63DDFDFDEBFDDA | 2022-06-16 13:10:37.170999
    4 | dff20cb13ce5d0365a6eb7625167e158 | 6535643463336232FDE2557DFDEBFDEB8BE9DE01DDFDFDDFFDDBDDFFDE02FDDFFDE3DF43FDE2DDFD8584DE02DF63DF43FDDADF63FDE3FDDBDF65DF63DE01DF43DDFFDF63DF65FDE3DDFFDF43DF68 | 2022-06-16 14:10:37.171109
    5 | c07b52a7943c3ee93aea8b05c04edbf3 | 6535643463336232FDDFC17DDDFDDF658BE9FDDBDF43DE01FDDADF65DF6CDF02DE02FDDFDE02FDE38584FDE3DF6CDE02FDDAFDE3FDDADF68FDDBDDFDDF43FDDFDDFDDF02FDE3FDE2FDDBFDEBDE02 | 2022-06-16 15:10:37.171169
    6 | cf87a3591009b652ba01dd3eb6a50d42 | 6535643463336232FDDF047EFDEBDF688BE9DF65FDDADE02DF43DF6CDDFFDDFDDDFDDF6CFDDBDF638584DF43DE01FDDBFDDADDFDDDFFFDE2FDE2DE02FDE3FDDBDF63FDDADF43DDFDFDE2DF02DE01 | 2022-06-16 16:10:37.171208
    7 | c2a1654f7e4887339e552d9d70666c1c | 6535643463336232FDDF157EDE01FDDA8BE9DDFFDF63DF43DF02FDEBDF65FDE3DF02DF68DF68DF658584DE02DE02DF6CFDE3DF43DF43DE01FDE2DF6CFDE2DF65DDFDDF63DF63DF63FDDFDDFFFDDF | 2022-06-16 17:10:37.171225
    8 | 127306338277eef17554ce089d28eb9d | 6535643463336232DDFF257EDE01DF658BE9DE02DDFDDF63DE02DE02DF68DE01DF65DF65FDE3FDE38584FDEBDDFFDF65DF43DF43DF02FDDFFDE3DDFDDF68DF6CFDE2DE01DF68FDE3FDDBDF6CFDE2 | 2022-06-16 18:10:37.171241
    9 | 4b7c57af3114003805bc0dd861ac7996 | 6535643463336232DF02387EFDDBDF658BE9FDDFDF43DF65FDDAFDEBDE02DDFFDDFFDF02DDFDDDFD8584DE02DF68DDFDDF43FDDBFDDFDDFDFDE2FDE2DF68DF63DDFFFDDAFDDFDF65DF6CDF6CDF63 | 2022-06-16 19:10:37.171261
    10 | 8ed90bd708f01094507636b80d9ef465 | 6535643463336232DF686F7EFDE3FDE28BE9DF6CDDFDFDDBFDE2DF65DDFDDF68FDEBDDFDDDFFDDFD8584DF6CDF02DF43DDFDDF65DF63DE02DF63FDDBDF68DDFDFDE2DF6CFDE3FDEBDF02DF63DF43 | 2022-06-16 20:10:37.171317

select * from test_ope order by opedata;
    id |               info               |                                                                           opedata                                                                            |          crt_time
----+----------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------
    8 | 127306338277eef17554ce089d28eb9d | 6535643463336232DDFF257EDE01DF658BE9DE02DDFDDF63DE02DE02DF68DE01DF65DF65FDE3FDE38584FDEBDDFFDF65DF43DF43DF02FDDFFDE3DDFDDF68DF6CFDE2DE01DF68FDE3FDDBDF6CFDE2 | 2022-06-16 18:10:37.171241
    1 | 27025cc9acd114a4f5192930ebc6fffb | 6535643463336232DE01067CDF65DDFD8BE9DE01DF43FDDFFDDFDF6CFDDAFDDFFDE2DDFFDDFFDF028584FDDADF02FDEBDF43DDFFDF6CDE01DF6CDE02DDFDFDE3FDDBFDDFDF63FDEBFDEBFDEBFDDB | 2022-06-16 11:10:37.170803
    3 | 433dc866e329799e7d134992b13960fa | 6535643463336232DF021A7DDE02DE028BE9FDE2FDDFDF68DF63DF63FDE3DE02DE01DF6CDF65DF6C8584DF6CFDE3DF65FDE2DDFFDE02DF02DF6CDF6CDE01FDDBDDFFDE02DF6CDF63DDFDFDEBFDDA | 2022-06-16 13:10:37.170999
    9 | 4b7c57af3114003805bc0dd861ac7996 | 6535643463336232DF02387EFDDBDF658BE9FDDFDF43DF65FDDAFDEBDE02DDFFDDFFDF02DDFDDDFD8584DE02DF68DDFDDF43FDDBFDDFDDFDFDE2FDE2DF68DF63DDFFFDDAFDDFDF65DF6CDF6CDF63 | 2022-06-16 19:10:37.171261
    10 | 8ed90bd708f01094507636b80d9ef465 | 6535643463336232DF686F7EFDE3FDE28BE9DF6CDDFDFDDBFDE2DF65DDFDDF68FDEBDDFDDDFFDDFD8584DF6CDF02DF43DDFDDF65DF63DE02DF63FDDBDF68DDFDFDE2DF6CFDE3FDEBDF02DF63DF43 | 2022-06-16 20:10:37.171317
    5 | c07b52a7943c3ee93aea8b05c04edbf3 | 6535643463336232FDDFC17DDDFDDF658BE9FDDBDF43DE01FDDADF65DF6CDF02DE02FDDFDE02FDE38584FDE3DF6CDE02FDDAFDE3FDDADF68FDDBDDFDDF43FDDFDDFDDF02FDE3FDE2FDDBFDEBDE02 | 2022-06-16 15:10:37.171169
    7 | c2a1654f7e4887339e552d9d70666c1c | 6535643463336232FDDF157EDE01FDDA8BE9DDFFDF63DF43DF02FDEBDF65FDE3DF02DF68DF68DF658584DE02DE02DF6CFDE3DF43DF43DE01FDE2DF6CFDE2DF65DDFDDF63DF63DF63FDDFDDFFFDDF | 2022-06-16 17:10:37.171225
    2 | cc97ddaf161894af7d5fbbe4ae31d0a1 | 6535643463336232FDDFE67CFDDFDF6C8BE9DF65FDE2FDE2FDDAFDEBDDFFDF63DDFFDF68DF6CDF028584FDDAFDEBDF65FDE2DF43FDEBFDDBFDDBFDE3DF02FDDAFDE3DE02DDFFFDE2DDFDFDDADDFF | 2022-06-16 12:10:37.170955
    6 | cf87a3591009b652ba01dd3eb6a50d42 | 6535643463336232FDDF047EFDEBDF688BE9DF65FDDADE02DF43DF6CDDFFDDFDDDFDDF6CFDDBDF638584DF43DE01FDDBFDDADDFDDDFFFDE2FDE2DE02FDE3FDDBDF63FDDADF43DDFDFDE2DF02DE01 | 2022-06-16 16:10:37.171208
    4 | dff20cb13ce5d0365a6eb7625167e158 | 6535643463336232FDE2557DFDEBFDEB8BE9DE01DDFDFDDFFDDBDDFFDE02FDDFFDE3DF43FDE2DDFD8584DE02DF63DF43FDDADF63FDE3FDDBDF65DF63DE01DF43DDFFDF63DF65FDE3DDFFDF43DF68 | 2022-06-16 14:10:37.171109

select * from test_ope where opedata > ope_text_encrypt('c') and opedata <= ope_text_encrypt('d');
    id |               info               |                                                                           opedata                                                                            |          crt_time
----+----------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------
    2 | cc97ddaf161894af7d5fbbe4ae31d0a1 | 6535643463336232FDDFE67CFDDFDF6C8BE9DF65FDE2FDE2FDDAFDEBDDFFDF63DDFFDF68DF6CDF028584FDDAFDEBDF65FDE2DF43FDEBFDDBFDDBFDE3DF02FDDAFDE3DE02DDFFFDE2DDFDFDDADDFF | 2022-06-16 12:10:37.170955
    5 | c07b52a7943c3ee93aea8b05c04edbf3 | 6535643463336232FDDFC17DDDFDDF658BE9FDDBDF43DE01FDDADF65DF6CDF02DE02FDDFDE02FDE38584FDE3DF6CDE02FDDAFDE3FDDADF68FDDBDDFDDF43FDDFDDFDDF02FDE3FDE2FDDBFDEBDE02 | 2022-06-16 15:10:37.171169
    6 | cf87a3591009b652ba01dd3eb6a50d42 | 6535643463336232FDDF047EFDEBDF688BE9DF65FDDADE02DF43DF6CDDFFDDFDDDFDDF6CFDDBDF638584DF43DE01FDDBFDDADDFDDDFFFDE2FDE2DE02FDE3FDDBDF63FDDADF43DDFDFDE2DF02DE01 | 2022-06-16 16:10:37.171208
    7 | c2a1654f7e4887339e552d9d70666c1c | 6535643463336232FDDF157EDE01FDDA8BE9DDFFDF63DF43DF02FDEBDF65FDE3DF02DF68DF68DF658584DE02DE02DF6CFDE3DF43DF43DE01FDE2DF6CFDE2DF65DDFDDF63DF63DF63FDDFDDFFFDDF | 2022-06-16 17:10:37.171225

select * from test_ope where opedata like 'dff20%';
    id |               info               |                                                                           opedata                                                                            |          crt_time
----+----------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------
    4 | dff20cb13ce5d0365a6eb7625167e158 | 6535643463336232FDE2557DFDEBFDEB8BE9DE01DDFDFDDFFDDBDDFFDE02FDDFFDE3DF43FDE2DDFD8584DE02DF63DF43FDDADF63FDE3FDDBDF65DF63DE01DF43DDFFDF63DF65FDE3DDFFDF43DF68 | 2022-06-16 14:10:37.171109

select * from test_ope where opedata between ope_text_encrypt('c') and  ope_text_encrypt('d');
    id |               info               |                                                                           opedata                                                                            |          crt_time
----+----------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------
    2 | cc97ddaf161894af7d5fbbe4ae31d0a1 | 6535643463336232FDDFE67CFDDFDF6C8BE9DF65FDE2FDE2FDDAFDEBDDFFDF63DDFFDF68DF6CDF028584FDDAFDEBDF65FDE2DF43FDEBFDDBFDDBFDE3DF02FDDAFDE3DE02DDFFFDE2DDFDFDDADDFF | 2022-06-16 12:10:37.170955
    5 | c07b52a7943c3ee93aea8b05c04edbf3 | 6535643463336232FDDFC17DDDFDDF658BE9FDDBDF43DE01FDDADF65DF6CDF02DE02FDDFDE02FDE38584FDE3DF6CDE02FDDAFDE3FDDADF68FDDBDDFDDF43FDDFDDFDDF02FDE3FDE2FDDBFDEBDE02 | 2022-06-16 15:10:37.171169
    6 | cf87a3591009b652ba01dd3eb6a50d42 | 6535643463336232FDDF047EFDEBDF688BE9DF65FDDADE02DF43DF6CDDFFDDFDDDFDDF6CFDDBDF638584DF43DE01FDDBFDDADDFDDDFFFDE2FDE2DE02FDE3FDDBDF63FDDADF43DDFDFDE2DF02DE01 | 2022-06-16 16:10:37.171208
    7 | c2a1654f7e4887339e552d9d70666c1c | 6535643463336232FDDF157EDE01FDDA8BE9DDFFDF63DF43DF02FDEBDF65FDE3DF02DF68DF68DF658584DE02DE02DF6CFDE3DF43DF43DE01FDE2DF6CFDE2DF65DDFDDF63DF63DF63FDDFDDFFFDDF | 2022-06-16 17:10:37.171225

select * from test_ope where opedata = ope_text_encrypt('127306338277eef17554ce089d28eb9d');
    id |               info               |                                                                           opedata                                                                            |          crt_time
----+----------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------
    8 | 127306338277eef17554ce089d28eb9d | 6535643463336232DDFF257EDE01DF658BE9DE02DDFDDF63DE02DE02DF68DE01DF65DF65FDE3FDE38584FDEBDDFFDF65DF43DF43DF02FDDFFDE3DDFDDF68DF6CFDE2DE01DF68FDE3FDDBDF6CFDE2 | 2022-06-16 18:10:37.171241

select ope_text_encrypt('cc97ddaf161894af7d5fbbe4ae31d0a1') IN (select opedata from test_ope);
    ?column?
----------
    t

select ope_text_encrypt('cc97ddaf161894af7d5fbbe4ae31d0a1') NOT IN (select opedata from test_ope);
    ?column?
----------
    f

select ope_decrypt(test_ope.opedata) from test_ope where id = 2;
            ope_decrypt
----------------------------------
    cc97ddaf161894af7d5fbbe4ae31d0a1
Prev Up Next
K.18. lt_log_long_xact Home K.20. lt_patch