We're taking a break this week as Connor and Chris will both be at CloudWorld, the premier Oracle conference of 2024. If you're in Vegas, please come say Hi or pop into our sessions

Thanks for the question, GERSON.

Asked: May 21, 2024 - 6:35 pm UTC

Last updated: August 21, 2024 - 5:23 am UTC

Version: 12c

Viewed 1000+ times

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)

We're not taking comments currently, so please try again later if you want to add a comment.

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