OdbVue

Appearance

Settings ​

Package provides methods for managing application settings

example
sql
SET SERVEROUTPUT ON;

DECLARE
    v_value app_settings.value%TYPE;
BEGIN
    pck_api_settings.write('DUMMY', 'Dummy setting', '1');
    COMMIT;

    pck_api_settings.read('DUMMY', v_value);
    DBMS_OUTPUT.PUT_LINE('Value of DUMMY setting: ' || v_value);
END;
/

DECLARE
    v_value VARCHAR2(200 CHAR) := 'This is a secret value';
BEGIN

    DBMS_OUTPUT.PUT_LINE(v_value);
    DBMS_OUTPUT.PUT_LINE('=>');
    v_value := pck_api_settings.enc(v_value);
    DBMS_OUTPUT.PUT_LINE(v_value);
    DBMS_OUTPUT.PUT_LINE('=>');
    v_value := pck_api_settings.dec(v_value);
    DBMS_OUTPUT.PUT_LINE(v_value);
    DBMS_OUTPUT.PUT_LINE('=>');
END;
/
specification
sql
create or replace 
PACKAGE ODBVUE.pck_api_settings AS -- Package provides methods for managing application settings     PROCEDURE write ( -- Procedure sets value of the setting with the specified id
        p_id      app_settings.id%TYPE, -- Id of the setting
        p_name    app_settings.name%TYPE, -- Name of the setting
        p_value   app_settings.value%TYPE, -- Value of the setting (variable character)
        p_options app_settings.options%TYPE DEFAULT NULL -- Additional options in JSON format
    );

    PROCEDURE read ( -- Procedure returns value of the setting with the specified id
        p_id    app_settings.id%TYPE, -- Id of the setting
        r_value OUT app_settings.value%TYPE -- Value of the setting (variable character)
    );

    FUNCTION read ( -- Function returns value of the setting with the specified id
        p_id app_settings.id%TYPE -- Id of the setting
    ) RETURN app_settings.value%TYPE; -- Value of the setting (variable character)

    PROCEDURE remove ( -- Procedure deletes setting with the specified id
        p_id app_settings.id%TYPE -- Id of the setting
    );

    FUNCTION enc ( -- Function encrypts the provided value
        p_value IN VARCHAR2 -- Value to be encrypted
    ) RETURN VARCHAR2; -- Encrypted value

    FUNCTION dec ( -- Function decrypts the provided value
        p_value IN VARCHAR2 -- Value to be decrypted
    ) RETURN VARCHAR2; -- Decrypted value
END;
/



-- sqlcl_snapshot {"hash":"775a114f755348ee150ff68706f066571cf5c2f8","type":"PACKAGE_SPEC","name":"PCK_API_SETTINGS","schemaName":"ODBVUE","sxml":""}
implementation
sql
CREATE OR REPLACE PACKAGE BODY odbvue.pck_api_settings AS

    g_master_key RAW(32);

    PROCEDURE write (
        p_id      app_settings.id%TYPE,
        p_name    app_settings.name%TYPE,
        p_value   app_settings.value%TYPE,
        p_options app_settings.options%TYPE DEFAULT NULL
    ) IS
    BEGIN
        MERGE INTO app_settings t
        USING (
            SELECT
                p_id id
            FROM
                dual
        ) s ON ( t.id = s.id )
        WHEN MATCHED THEN UPDATE
        SET t.value = p_value,
            t.name = coalesce(p_name, t.name),
            t.options = coalesce(p_options, t.options)
        WHEN NOT MATCHED THEN
        INSERT (
            id,
            name,
            value,
            options )
        VALUES
            ( p_id,
              p_name,
              p_value,
              p_options );

    END write;

    PROCEDURE read (
        p_id    app_settings.id%TYPE,
        r_value OUT app_settings.value%TYPE
    ) IS
    BEGIN
        SELECT
            CASE
                WHEN secret = 'Y' THEN
                    dec(value)
                ELSE
                    value
            END
        INTO r_value
        FROM
            app_settings
        WHERE
            id = p_id;

    EXCEPTION
        WHEN no_data_found THEN
            raise_application_error(-20001, 'Setting with ID "'
                                            || p_id
                                            || '" not found.');
    END read;

    FUNCTION read (
        p_id app_settings.id%TYPE
    ) RETURN app_settings.value%TYPE IS
        v_value app_settings.value%TYPE;
    BEGIN
        read(p_id, v_value);
        RETURN v_value;
    END read;

    PROCEDURE remove (
        p_id app_settings.id%TYPE
    ) IS
    BEGIN
        DELETE FROM app_settings
        WHERE
            id = p_id;

    END remove;

    FUNCTION enc (
        p_value IN VARCHAR2
    ) RETURN VARCHAR2 IS
    BEGIN
        RETURN utl_raw.cast_to_varchar2(utl_encode.base64_encode(dbms_crypto.encrypt(
            utl_raw.cast_to_raw(p_value),
            dbms_crypto.aes_cbc_pkcs5,
            g_master_key
        )));
    END enc;

    FUNCTION dec (
        p_value IN VARCHAR2
    ) RETURN VARCHAR2 IS
    BEGIN
        RETURN utl_raw.cast_to_varchar2(dbms_crypto.decrypt(
            utl_encode.base64_decode(utl_raw.cast_to_raw(p_value)),
            dbms_crypto.aes_cbc_pkcs5,
            g_master_key
        ));
    END dec;

    PROCEDURE initialize_master_key IS

        v_resp  dbms_cloud_types.resp;
        v_json  CLOB;
        v_b64   VARCHAR2(32767);
        v_raw   RAW(32767);
        v_local VARCHAR2(32767);
        v_uri   VARCHAR2(2000 CHAR) := read('APP_SETTINGS_MASTER_KEY_URI');
    BEGIN
        v_resp := dbms_cloud.send_request(
            credential_name => 'OCI$RESOURCE_PRINCIPAL',
            uri             => v_uri,
            method          => dbms_cloud.method_get
        );

        v_json := dbms_cloud.get_response_text(v_resp);
        v_b64 := JSON_VALUE(v_json, '$.secretBundleContent.content');
        g_master_key := utl_encode.base64_decode(utl_raw.cast_to_raw(v_b64));
    EXCEPTION
        WHEN OTHERS THEN
            BEGIN
                pck_api_audit.warn('Master Key');
                v_local := read('APP_SETTINGS_MASTER_KEY_LOCAL');
                g_master_key := utl_raw.cast_to_raw(v_local);
            EXCEPTION
                WHEN OTHERS THEN
                    pck_api_audit.fatal('Master Key');
                    raise_application_error(-20001, 'Failed to initialize master key from local setting: ' || sqlerrm);
            END;
    END initialize_master_key;

BEGIN
    initialize_master_key;
END;
/


-- sqlcl_snapshot {"hash":"1d7f4792bba75414714e9b5642ff2fe72f92338a","type":"PACKAGE_BODY","name":"PCK_API_SETTINGS","schemaName":"ODBVUE","sxml":""}

Dependencies:

Referenced typeReferenced name
TABLEAPP_SETTINGS

DEC ​

Function decrypts the provided value

Argument nameIn OutData typeDefault valueDescription
OUTVARCHAR2Value of the setting (variable character)
P_VALUEINVARCHAR2Value to be encrypted

ENC ​

Function encrypts the provided value

Argument nameIn OutData typeDefault valueDescription
OUTVARCHAR2Value of the setting (variable character)
P_VALUEINVARCHAR2Value to be encrypted

READ ​

Procedure returns value of the setting with the specified id

Argument nameIn OutData typeDefault valueDescription
P_IDINVARCHAR2Id of the setting
R_VALUEOUTVARCHAR2Value of the setting (variable character)

READ ​

Procedure returns value of the setting with the specified id

Argument nameIn OutData typeDefault valueDescription
OUTVARCHAR2Value of the setting (variable character)
P_IDINVARCHAR2Id of the setting

REMOVE ​

Procedure deletes setting with the specified id

Argument nameIn OutData typeDefault valueDescription
P_IDINVARCHAR2Id of the setting

WRITE ​

Procedure sets value of the setting with the specified id

Argument nameIn OutData typeDefault valueDescription
P_IDINVARCHAR2Id of the setting
P_NAMEINVARCHAR2Name of the setting
P_VALUEINVARCHAR2Value of the setting (variable character)
P_OPTIONSINCLOBNULLAdditional options in JSON format