Ensuring PL/SQL Code Integrity with Checksums

The idea is to implement a checksum for PL/SQL procedures to ensure they haven’t been tampered with, you can follow this general approach:

1. Generate a Hash/Checksum: Use a hashing algorithm to compute a checksum for the PL/SQL code of the procedure. For this, you can use Oracle’s DBMS_CRYPTO package.
2. Store the Original Checksum: Once the checksum is generated for the original procedure, store it in a table or external source for later verification.
3. Compare Checksums: Periodically, or before execution, compare the current checksum of the procedure with the stored checksum. If they differ, the procedure may have been altered.

Implementation Steps:

• Create a table to store checksums:

CREATE TABLE procedure_checksums (
    procedure_name VARCHAR2(100),
    checksum       VARCHAR2(64),
    last_verified  TIMESTAMP
);

• Generate and store the checksum:
  Use the DBMS_METADATA.get_ddl function to retrieve the DDL of the procedure and hash it using DBMS_CRYPTO. Insert a row for each check. This can be automated at regular intervals.

DECLARE
    ddl          CLOB;
    checksum_raw RAW(64);
    checksum     VARCHAR2(64);
BEGIN
-- Fetch the DDL of the procedure
    ddl := dbms_metadata.get_ddl('PROCEDURE', 'ADD_EMPLOYEE', 'RADU');
-- Compute a hash (SHA-256 in this case)
    checksum_raw := dbms_crypto.hash(utl_raw.cast_to_raw(ddl), dbms_crypto.hash_sh256);
-- Convert raw hash to a readable hex value
    checksum := rawtohex(checksum_raw);
    INSERT 
    INTO procedure_checksums
    (
        procedure_name,
        checksum,
        last_verified )
    VALUES
        ( 'ADD_EMPLOYEE',
        checksum,
        systimestamp );

END;

• Periodic verification:
  You can periodically compare the checksum of the procedure against the stored value, by calculation the current checksum and using a block like:

DECLARE
    ddl              CLOB;
    checksum_raw     RAW(64);
    current_checksum VARCHAR2(64);
    stored_checksum  VARCHAR2(64);
    n                INTEGER;
BEGIN
    n := 0;
-- Fetch the DDL of the procedure
    ddl := dbms_metadata.get_ddl('PROCEDURE', 'ADD_EMPLOYEE', 'RADU');
-- Compute a hash
    checksum_raw := dbms_crypto.hash(utl_raw.cast_to_raw(ddl), dbms_crypto.hash_sh256);

    current_checksum := rawtohex(checksum_raw);
-- Fetch the stored checksum
    SELECT
        COUNT(*)
    INTO n
    FROM
        procedure_checksums
    WHERE
            procedure_name = 'ADD_EMPLOYEE'
        AND checksum != current_checksum;
-- Compare checksums
    IF n = 0 THEN
        dbms_output.put_line('Procedure is unaltered.');
    ELSE
        dbms_output.put_line('Procedure has been altered!');
    END IF;
END;

• After the check is done and required action taken, the table can be cleaned:

DELETE FROM procedure_checksums
WHERE
    procedure_name = 'ADD_EMPLOYEE';

Notes:

• Ensure that the DBMS_CRYPTO package is enabled in your Oracle instance.
• You might want to consider extending this approach for other database objects (triggers, packages, etc.).
• Periodic or trigger-based verification might be implemented to automatically check integrity after changes.

This method would help track tampering or unintentional changes to the PL/SQL procedures.

Tested on:

Infrastructure	Database
Oracle Autonomous	Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 – Production Version 23.6.0.24.07

Test Procedure Used

CREATE OR REPLACE PROCEDURE add_employee(
    p_emp_id    IN NUMBER,
    p_emp_name  IN VARCHAR2,
    p_salary    IN NUMBER,
    p_dept_id   IN NUMBER
) AS
BEGIN
    -- Insert the provided employee data into the employees table
    INSERT INTO employees (emp_id, emp_name, salary, dept_id)
    VALUES (p_emp_id, p_emp_name, p_salary, p_dept_id);
    
    -- Optionally, you can commit the transaction
    COMMIT;
    
    -- Output a message to confirm success
    DBMS_OUTPUT.PUT_LINE('Employee ' || p_emp_name || ' has been added successfully.');
    
EXCEPTION
    -- Handle any errors that may occur
    WHEN OTHERS THEN
        -- Output an error message
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
        -- Optionally, roll back the transaction if there's an error
        ROLLBACK;
END;