You Asked

I need a example using DBMS_CRYPTO.SIGN.
I'm need to sign a hash using RSA-SHA1 and I need to know if can i use DBMS_CRYPTO.SIGN?

and Connor said...

Have you tried as per the docs?

DECLARE
  ip_str     VARCHAR2 (200) := 'Secret Message';
  -- Use OpenSSL to generate the private and public key (2048 bit RSA key)
  -- openssl genrsa -out private.pem 2048
  -- openssl rsa -in private.pem -outform PEM -pubout -out public.pem
  pubkey    VARCHAR (2000) := 'public_key';
  prvkey    VARCHAR (2000) := 'private_key'; 
  sign_raw   RAW (2000);
  returnval  BOOLEAN := false;
  sType      PLS_INTEGER := DBMS_CRYPTO.SIGN_SHA224_RSA;
  kType      PLS_INTEGER := DBMS_CRYPTO.KEY_TYPE_RSA;
BEGIN
  sign_raw := DBMS_CRYPTO.SIGN
  (
   src        => UTL_I18N.STRING_TO_RAW(ip_str,'AL32UTF8'),
   prv_key    => UTL_I18N.STRING_TO_RAW( prvkey, 'AL32UTF8'),
   pubkey_alg => kType,
   sign_alg   => sType
  );
  returnval := DBMS_CRYPTO.VERIFY
  (
   src        => UTL_I18N.STRING_TO_RAW( ip_str,'AL32UTF8'),
   sign       => sign_raw,
   pub_key    => UTL_I18N.STRING_TO_RAW( pubkey, 'AL32UTF8'),
   pubkey_alg => kType,
   sign_alg   => sType
  );
  DBMS_OUTPUT.PUT_LINE('-------------------------------------------------');
  IF returnval THEN
    DBMS_OUTPUT.PUT_LINE('True');
  ELSE
    DBMS_OUTPUT.PUT_LINE('False');
  END IF;
  DBMS_OUTPUT.PUT_LINE('-------------------------------------------------');
END;
/

Rating

(4 ratings)

Comments

DBMS_CRYPTO SIGN pubkey_alg and sign_alg parameter

Marisol Villatoro, June 27, 2024 - 5:45 pm UTC

/*I used these code, i need to sign a json generated from google account service
the json has private key and public key*/

create or replace PACKAGE BODY jwt_util_pkg AS

    -- Función para convertir la clave privada de PEM a RAW
    FUNCTION pem_to_raw(pem_key CLOB) RETURN RAW IS
        l_raw_key RAW(32767);
        l_b64_key VARCHAR2(32767);
    BEGIN
        -- Remover las cabeceras y pies de la clave PEM
        l_b64_key := REPLACE(REPLACE(REPLACE(pem_key, '-----BEGIN PRIVATE KEY-----', ''), '-----END PRIVATE KEY-----', ''), CHR(10), '');

        -- Convertir de Base64 a RAW
        l_raw_key := UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(l_b64_key));

        RETURN l_raw_key;
    END pem_to_raw;

    FUNCTION sign_jwt(p_input VARCHAR2) RETURN CLOB IS
        l_private_key CLOB;
        l_signature RAW(256);
        l_raw_key RAW(32767);
        kType      PLS_INTEGER := DBMS_CRYPTO.KEY_TYPE_RSA;
        --kType      PLS_INTEGER := DBMS_CRYPTO.PKENCRYPT_RSA_PKCS1_OAEP;
        --sType      PLS_INTEGER := DBMS_CRYPTO.SIGN_SHA224_RSA;
        sType      PLS_INTEGER := DBMS_CRYPTO.SIGN_SHA256_RSA;
        --sType      PLS_INTEGER := DBMS_CRYPTO.HASH_SH256;

    BEGIN
        PRINT_CLOB_TO_OUTPUT('kType: ' || kType); 
        PRINT_CLOB_TO_OUTPUT('sType: ' || sType); 
        -- Recuperar la clave privada de la tabla
        SELECT private_key INTO l_private_key FROM secure_keys WHERE id = 1;

        -- Convertir la clave privada de PEM a RAW
        l_raw_key := pem_to_raw(l_private_key);

        -- Firmar el mensaje
        l_signature := DBMS_CRYPTO.SIGN(
            src       => UTL_I18N.STRING_TO_RAW(p_input, 'AL32UTF8'),
            prv_key   => UTL_I18N.STRING_TO_RAW(l_raw_key, 'AL32UTF8'),
            pubkey_alg => kType, 
            sign_alg  => sType 
        );

        -- Convertir la firma a Base64
        RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(l_signature));
        --RETURN l_private_key;
    END sign_jwt;

    FUNCTION create_jwt(
        p_service_account_email VARCHAR2,
        p_token_endpoint VARCHAR2,
        p_scope VARCHAR2
    ) RETURN CLOB IS
        l_header VARCHAR2(32767) := '{"alg":"RS256","typ":"JWT"}';
        l_payload VARCHAR2(32767);        
        l_unsigned_jwt VARCHAR2(32767);
        l_signed_jwt CLOB;
        l_iat NUMBER := TRUNC((SYSDATE - TO_DATE('1970-01-01','YYYY-MM-DD')) * 86400); -- Unix epoch time
        l_exp NUMBER := l_iat + 3600; -- Token expires in 1 hora

        u_header VARCHAR2(32767);
        u_payload VARCHAR2(32767);
    BEGIN
        -- Crear el payload del JWT
        l_payload := '{"iss":"' || p_service_account_email || '",'
                 || '"sub":"' || p_service_account_email || '",'
                 || '"aud":"' || p_token_endpoint || '",'
                 || '"scope":"' || p_scope || '",'
                 || '"iat":' || l_iat || ','
                 || '"exp":' || l_exp || '}';
        
        -- Crear el JWT sin firmar
        l_unsigned_jwt := UTL_RAW.CAST_TO_VARCHAR2(
            UTL_ENCODE.BASE64_ENCODE(UTL_I18N.STRING_TO_RAW(l_header, 'AL32UTF8'))
        ) || '.' || UTL_RAW.CAST_TO_VARCHAR2(
            UTL_ENCODE.BASE64_ENCODE(UTL_I18N.STRING_TO_RAW(l_payload, 'AL32UTF8'))
        );
        
        -- Firmar el JWT
        l_signed_jwt := jwt_util_pkg.sign_jwt(l_unsigned_jwt);

        -- Devolver el JWT firmado
        RETURN l_unsigned_jwt || '.' || l_signed_jwt;
    END create_jwt;

    PROCEDURE CONSULTA_CREDENTIAL IS
        l_service_account_email VARCHAR2(32767) := '***@apis-***.iam.gserviceaccount.com';
        l_token_endpoint VARCHAR2(32767) := 'https://oauth2.googleapis.com/token';
        l_scope VARCHAR2(32767) := 'https://www.googleapis.com/auth/admin.directory.user';
        l_signed_jwt CLOB;
    BEGIN
        -- Crear y firmar el JWT
        l_signed_jwt := jwt_util_pkg.create_jwt(l_service_account_email, l_token_endpoint, l_scope);

        --DBMS_OUTPUT.PUT_LINE('Signed JWT: ' || l_signed_jwt);
    END;

END jwt_util_pkg;

but when i run 
begin
    JWT_UTIL_PKG.CONSULTA_CREDENTIAL;
end;

/*i got these message, 
ORA-28817: PL/SQL function returned an error.
ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 304
ORA-06512: at "SYS.DBMS_CRYPTO", line 163
ORA-06512: at "UNIS_INTERFACES.JWT_UTIL_PKG", line 36
ORA-06512: at "UNIS_INTERFACES.JWT_UTIL_PKG", line 79
ORA-06512: at "UNIS_INTERFACES.JWT_UTIL_PKG", line 92
ORA-06512: at line 2
ORA-06512: at "SYS.DBMS_SQL", line 1721

ORA-28817: PL/SQL function returned an error.
ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 304
ORA-06512: at "SYS.DBMS_CRYPTO", line 163
ORA-06512: at line 2
ORA-06512: at "SYS.DBMS_SQL", line 1721

i don´t know where i could find what value put in the parameters 
pubkey_alg => kType, 
sign_alg  => sType
I tried the comments but is the same*/

July 30, 2024 - 5:21 am UTC

Have
you
tried
as
per
the
docs?

Error after following your example

Jakob, August 19, 2024 - 6:42 pm UTC

Hey Connor,

I have tried as per the docs and still get en error:

DECLARE
  ip_str     VARCHAR2 (200) := 'Secret Message';
  pubkey    VARCHAR (2000) := '-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAxlvChTuYkqUlv7ceqq29
3VYz3z+86jhzbDNPq+qdA1xuNKgwIfEkhabtQTHrheZmgQii9HN2doJkjFyczwy1
GV6wcT3ZlDQJeTZqZAwyxpgpJ07iUhQfu1fAI0TfB+qF5d/8KMWNFJwNyzUeMMAy
d0fInl4VlVw5J3rtJSZMQXWf2QPtSig3En9cg8GAJSdawmgz50kKMDdkh7pZzd18
tWu8HISskenJWI2Pa5Xct6y4FxODNi1aFR3IBIar7xUrJ9JVNRe+OjuqYPIvREzs
az/MW5zVNn2QkO4P271qos7DiEV3UrBV9fkUJla/WjuVbhybBuTxDUObv1xwlfbg
kQIDAQAB
-----END PUBLIC KEY-----
';
  prvkey    VARCHAR (2000) := '-----BEGIN PRIVATE KEY-----
MIIEuwIBADANBgkqhkiG9w0BAQEFAASCBKUwggShAgEAAoIBAQDGW8KFO5iSpSW/
tx6qrb3dVjPfP7zqOHNsM0+r6p0DXG40qDAh8SSFpu1BMeuF5maBCKL0c3Z2gmSM
XJzPDLUZXrBxPdmUNAl5NmpkDDLGmCknTuJSFB+7V8AjRN8H6oXl3/woxY0UnA3L
NR4wwDJ3R8ieXhWVXDkneu0lJkxBdZ/ZA+1KKDcSf1yDwYAlJ1rCaDPnSQowN2SH
ulnN3Xy1a7wchKyR6clYjY9rldy3rLgXE4M2LVoVHcgEhqvvFSsn0lU1F746O6pg
8i9ETOxrP8xbnNU2fZCQ7g/bvWqizsOIRXdSsFX1+RQmVr9aO5VuHJsG5PENQ5u/
XHCV9uCRAgMBAAECgf818n7U/YBhYckCeys8AETDDc7NyOf4S6IYq1vfxc8uOtU+
PP/aO1tTfjLIqGsF38PBFDg9g6mUUaJHCmkbZrlaTQZvxgKG7CAnd0U4MJkeqgOH
TU0ARbjbmuIGtZRy8rW371MFQ7U7BbY6rhen6p3PExbc+3mjRZHFywj0MZZLttcP
CJXed3kfbC0Wk+N/hMJVp66zzEMP1up6pgHhzmYz+8uwMrIcyaJnDoJ/7wPKs1/m
GiZEji+t8Ge0OT30HPQnUDlJyDCZQjSs34PJ6IQck9JsLHmA5ckWuxV1vFJtJNrh
EW3UXcFU1zZpYIRrkV6rdlUXQwRpGQNFPJCyf4ECgYEA8EDRMkeakRRLMhl0+Fxc
zeHwo+lA2A0DgsJyRkQMQCAQwKoPuZ0wKtCvGUkr6M7Myb4NFY6okQHZKlhhtkRO
YXiOh/NjNHl4LVWuWJOVO9EPwVGsqoEOwte5ETipuAa/etHAye2oNmOlRhONrEwC
JLC7boojkXP7jP1TezXRKtECgYEA01v+uYelqy3tNPJT+BvccsJy52HWsJW7Fl94
2CMXcPG1EcuktvwjzLYL0zoP0RHoDTwnzUVUPH3lKrRnV18Dy+verhbw07qEyDPh
Pmz6l0A4j2e2onk8GocM1X2X8lvXGNQ3P+zojv0QFNgWFwS4JPNPs2p8CQTdthRO
gOEQScECgYBe3Oj9FmLEiCPMy1pxOl72eBEBV3dgF3tcZRSkA/hmVYLn3/5HhvZe
4XlR3Z911DnuKpmV+nc6kmVCIBWs1zmvqN92dLamIJRAKkWl+dgB/2FeLrajuEOw
0WbkkuAIprlycGHUGX/CwgOfMNdOwdBS1mmbkvXKV+ZqXBhbXPuTEQKBgEUvzxFS
ewOu9AAp9xy3mhEi39QoLcv+J5DqTbeKvSimppwdqlyP6vQe/330eqr4jpqOf4Tt
Vea7W2IATffGzYpaOrmIHutgGgkjGm+B4wHR7vHahGlpACi63pOgGjYmpCGnjjJp
zbLI2hPIrGp6jADkeMmFaPbW1fC9cvkzM07BAoGBAOrFVv8E545CehTlF5FV8fQH
Ky9Ap95xSHVuDdMMlqW6LHsVbnyfmIUv9fElRqqi9Qcut7WmjGo14BoQSqMDcc2V
jMadIPrFDq6kHh50zm0RUOPlQP8ImS0OqQveRXWgFsym7ETJSKmo/AnCajwi9sdw
g/juGQVXiyxFDJO+REL/
-----END PRIVATE KEY-----';

  sign_raw   RAW (2000);
  returnval  BOOLEAN := false;
  sType      PLS_INTEGER := DBMS_CRYPTO.SIGN_SHA224_RSA;
  kType      PLS_INTEGER := DBMS_CRYPTO.KEY_TYPE_RSA;
BEGIN
  sign_raw := DBMS_CRYPTO.SIGN
  (
   src        => UTL_I18N.STRING_TO_RAW(ip_str,'AL32UTF8'),
   prv_key    => UTL_I18N.STRING_TO_RAW( prvkey, 'AL32UTF8'),
   pubkey_alg => kType,
   sign_alg   => sType
  );
  returnval := DBMS_CRYPTO.VERIFY
  (
   src        => UTL_I18N.STRING_TO_RAW( ip_str,'AL32UTF8'),
   sign       => sign_raw,
   pub_key    => UTL_I18N.STRING_TO_RAW( pubkey, 'AL32UTF8'),
   pubkey_alg => kType,
   sign_alg   => sType
  );
  DBMS_OUTPUT.PUT_LINE('-------------------------------------------------');
  IF returnval THEN
    DBMS_OUTPUT.PUT_LINE('True');
  ELSE
    DBMS_OUTPUT.PUT_LINE('False');
  END IF;
  DBMS_OUTPUT.PUT_LINE('-------------------------------------------------');
END;
/

Gives me:

[99999][28817]
ORA-28817: PL/SQL-Functin threw an exception
ORA-06512: in "SYS.DBMS_CRYPTO_FFI", line 883
ORA-06512: in "SYS.DBMS_CRYPTO", line 422
ORA-06512: in line 51

Do you have an idea, what the error might be? I get the same error when I do the sign with freshly generated ECDSA keys (generated by dbms_crypto).

August 21, 2024 - 5:23 am UTC

You're close :-) Those carriage returns matter

SQL> set serverout on
SQL> DECLARE
  2    ip_str     VARCHAR2 (200) := 'Secret Message';
  3    pubkey    VARCHAR (2000) := 'MIIBIjANBgkqhkiG9w0BAQEFAAOCA....(the rest on ONE LINE)';
  4    prvkey    VARCHAR (2000) := 'MIIEuwIBADANBgkqhkiG9w0BAQEF....(the rest on ONE LINE)';
  5
  6    sign_raw   RAW (2000);
  7    returnval  BOOLEAN := false;
  8    sType      PLS_INTEGER := DBMS_CRYPTO.SIGN_SHA224_RSA;
  9    kType      PLS_INTEGER := DBMS_CRYPTO.KEY_TYPE_RSA;
 10  BEGIN
 11    sign_raw := DBMS_CRYPTO.SIGN
 12    (
 13     src        => UTL_I18N.STRING_TO_RAW(ip_str,'AL32UTF8'),
 14     prv_key    => UTL_I18N.STRING_TO_RAW( prvkey, 'AL32UTF8'),
 15     pubkey_alg => kType,
 16     sign_alg   => sType
 17    );
 18    returnval := DBMS_CRYPTO.VERIFY
 19    (
 20     src        => UTL_I18N.STRING_TO_RAW( ip_str,'AL32UTF8'),
 21     sign       => sign_raw,
 22     pub_key    => UTL_I18N.STRING_TO_RAW( pubkey, 'AL32UTF8'),
 23     pubkey_alg => kType,
 24     sign_alg   => sType
 25    );
 26    DBMS_OUTPUT.PUT_LINE('-------------------------------------------------');
 27    IF returnval THEN
 28      DBMS_OUTPUT.PUT_LINE('True');
 29    ELSE
 30      DBMS_OUTPUT.PUT_LINE('False');
 31    END IF;
 32    DBMS_OUTPUT.PUT_LINE('-------------------------------------------------');
 33  END;
 34  /
-------------------------------------------------
True
-------------------------------------------------

PL/SQL procedure successfully completed.

Nahum, August 20, 2024 - 7:11 am UTC

Hey Gerson, I might have the solution for your needs, I'm have just started signing.
Say you have this:

v_private_key raw(32767);
v_base64_key VARCHAR2(32767) := 'MIIEvQIBADAN...'; --This is the actual encoded body

This is the way you have to assign the raw value:

v_private_key := UTL_I18N.string_to_raw(v_base64_key, 'AL32UTF8');

And then:

v_raw_signature := DBMS_CRYPTO.SIGN(
src => UTL_RAW.CAST_TO_RAW(v_input),
prv_key => v_private_key,
pubkey_alg => dbms_crypto.KEY_TYPE_RSA,
sign_alg => dbms_crypto.SIGN_SHA1_RSA
);

Error still there for ECDSA

Jakob, August 20, 2024 - 7:32 am UTC

Ah, I failed deleting the "BEGIN PRIVATE KEY"... my bad, I'm sorry.

But I still don't get. why the error occurs with this snippet as well:

declare
  l_text VARCHAR2 (200) := 'Secret Message';
  l_sign raw(32000);
  l_pub_key raw(32000);
  l_priv_key raw(32000);
  l_is_valid boolean;
begin
  DBMS_CRYPTO.ECDH_GENKEYPAIR (
   curveid => dbms_crypto.SECP_256_R1,
   pubkey => l_pub_key,
   privkey => l_priv_key);

  l_sign := DBMS_CRYPTO.SIGN(
   src => UTL_I18N.STRING_TO_RAW(l_text,'AL32UTF8'),
   prv_key => l_priv_key,
   pubkey_alg => dbms_crypto.KEY_TYPE_ECDSA,
   sign_alg => dbms_crypto.SIGN_SHA256_ECDSA);

  l_is_valid := dbms_crypto.verify(
    src => UTL_I18N.STRING_TO_RAW(l_text,'AL32UTF8')
    ,sign => l_sign
    ,pub_key => l_pub_key
    ,pubkey_alg => dbms_crypto.KEY_TYPE_ECDSA
    ,sign_alg => dbms_crypto.SIGN_SHA256_ECDSA
  );

  if l_is_valid then
    dbms_output.put_line('Valid!');
  else
    dbms_output.put_line('Invalid :(');
  end if;
end;

Shouldn't it work out-of-the-box when generating the credentials in the DB directly? Or am I missing something, again?

Breadcrumb

Question and Answer