AES暗号化

(8.0.29)
https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0174

https://qiita.com/mhagita/items/899483f08347fddd9567

 

SET block_encryption_mode = 'aes-256-cbc';

select @@block_encryption_mode;

暗号化

select HEX(AES_ENCRYPT('あいうえお12345ABCDE','key12345678901234567890123456789','init_vector12345') );

復号

select convert( AES_DECRYPT(UNHEX('F45A08688C52009F187FE2EC1722C8D886EE3AB9C5415A3EDD037BCDE7711671'), 'key12345678901234567890123456789','init_vector12345') USING utf8 );

 

 

(19c)

https://blogs.oracle.com/sec/post/055_dbms_crypto


select grantee, privilege from dba_tab_privs where table_name='DBMS_CRYPTO';

DBMS_CRYPTOパッケージに対するオブジェクト権限の付与が必要

grant execute on dbms_crypto to test;


暗号化アルゴリズムパッケージ定数の調査

set serveroutput on
declare
n number;
begin
  n := DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5;
  dbms_output.put_line(DBMS_CRYPTO.ENCRYPT_AES256);
  dbms_output.put_line(DBMS_CRYPTO.CHAIN_CBC);
  dbms_output.put_line(DBMS_CRYPTO.PAD_PKCS5);
  dbms_output.put_line(n);
end;
/


暗号化

select dbms_crypto.encrypt(src => UTL_I18N.STRING_TO_RAW ('あいうえお12345ABCDE', 'AL32UTF8'), 
                           typ => 4360 ,
                           key => UTL_I18N.STRING_TO_RAW('key12345678901234567890123456789', 'AL32UTF8') ) from dual;

復号

select UTL_I18N.RAW_TO_CHAR(dbms_crypto.decrypt(src => '045CC0A9135CE9BA301D8432CF2341E79DBB0B8EEC325F9020DA140B14797109',
                                                typ => 4360,
                                                key => UTL_I18N.STRING_TO_RAW('key12345678901234567890123456789', 'AL32UTF8') ),
                            'AL32UTF8') from dual;

 

 

(14)
https://qiita.com/niharu/items/f812ca3ba924ed94eefd
https://www.postgresql.jp/document/13/html/pgcrypto.html

 


\dx
CREATE EXTENSION pgcrypto;


暗号化
select pgp_sym_encrypt('あいうえお12345ABCDE', 'key12345678901234567890123456789','cipher-algo=aes256');

復号
select pgp_sym_decrypt('\xc30d04090302562a4e2b1ae7d0897ad24a014e20df974641d7813f76c7ca5e31a8cfb58959383cc8be323c7dff89b001d643c6c277363a4f574570fac4cea9a3e74d125637063ffb4b0e566b6477176ad7be9d92f2685187096a3f', 'key12345678901234567890123456789','cipher-algo=aes256');

 

 

 

(2019)

https://matutak.hatenablog.com/entry/20091020/1256176938

 

-- 1. 対称キーの作成 
CREATE SYMMETRIC KEY key01
WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = 'Key!1234567890123456789012345678';
go

select * from sys.symmetric_keys;
go


-- 2. 対称キーのオープン
OPEN SYMMETRIC KEY key01
DECRYPTION BY PASSWORD = 'Key!1234567890123456789012345678';
go

-- 3. 暗号化

declare @kguid uniqueidentifier
SET @kguid = Key_GUID('key01')

select EncryptByKey(@kguid, N'あいうえお12345ABCDE');

-- 4. 復号


select CONVERT( nvarchar, DecryptByKey(0x00F6F5BB1FA04544815A846097C58E1B02000000127289273FEB81C1D7F71EF9D01E533123DE78F3C9872A299823F51E06C96A8ADAB247AB0B44D549C07FB8CA39C84A29EC6B2C80F7A1ED728F2895783787FE2F) );
go

-- 5. 対称キーのクローズ
CLOSE SYMMETRIC KEY key01
go

-- 6. 対称キーの削除
DROP SYMMETRIC KEY key01
go

select * from sys.symmetric_keys;
go