Oracle Guide

System requirements

Oracle Database

Tested on 19c and 26ai. Published files require Oracle Linux 8 or later (glibc 2.28 or later). Single instance with a dedicated extproc agent.

Database settings

Character set AL32UTF8, MAX_STRING_SIZE=STANDARD

Database server OS

Linux x86_64 with glibc 2.28 or later (for example, Oracle Linux 8 or later). The published files do not load on Oracle Linux 7, which ships glibc 2.17.

Build tools

gcc, make, curl, tar, sha256sum (coreutils 8.25 or later), and oci.h under ORACLE_HOME

Privileges

The oracle OS account with sudo on the database server, and SYS (AS SYSDBA) in the database

RAC, the multithreaded extproc agent (agtctl), and MAX_STRING_SIZE=EXTENDED have not been tested.

Where to run each step

Oracle loads the C wrapper liboracle_strcrypt.so as an external procedure (extproc), and the wrapper calls libstrcrypt.so. extproc runs as oracle on the database server, so the libraries, key file, and settings all live on the database server. Nothing needs to be installed on the application server.

1. Pre-checks

echo "$ORACLE_HOME"
ldd --version | head -1
ls "$ORACLE_HOME/rdbms/public/oci.h"
command -v gcc make curl sha256sum tar sudo
lsnrctl status | grep -i '^Service'

Run this from a shell opened with sudo su - oracle. The last command lists the services; note the PDB service name (for example, orclpdb1) for step 5. command -v prints only the tools that are installed.

Common errors

2. Build and install the libraries

Only if the build tools are missing

sudo dnf install -y gcc make

Download, verify, build, and install

mkdir -p ~/strcrypt-oracle && cd ~/strcrypt-oracle
curl -fLO https://www.cubiware.co.kr/api/download/strcrypt/0.3.1/libstrcrypt.so
curl -fLO https://www.cubiware.co.kr/api/download/strcrypt/0.3.1/libstrcrypt.so.hmac
curl -fLO https://www.cubiware.co.kr/api/download/strcrypt/0.3.1/strcrypt-oracle-0.3.1.tar.gz
curl -fLO https://www.cubiware.co.kr/api/download/strcrypt/0.3.1/SHA256SUMS
sha256sum -c --ignore-missing SHA256SUMS
tar xzf strcrypt-oracle-0.3.1.tar.gz && cd strcrypt-oracle-0.3.1
make ORACLE_HOME="$ORACLE_HOME" STRCRYPT_LIB_DIR="$HOME/strcrypt-oracle" STRCRYPT_INCLUDE_DIR=.
sudo install -m 755 ../libstrcrypt.so liboracle_strcrypt.so /usr/lib/
sudo install -m 644 ../libstrcrypt.so.hmac /usr/lib/
ldd /usr/lib/liboracle_strcrypt.so | grep strcrypt

The wrapper has to be built against the Oracle headers (oci.h) on your database server, so it is provided as source. The build happens under ~/strcrypt-oracle, and only the two libraries are copied to /usr/lib. The install succeeded if the last line prints libstrcrypt.so => /usr/lib/libstrcrypt.so.

Common errors

3. Generate the key file

sudo install -d -o oracle -m 700 /etc/strcrypt
head -c 24 /dev/urandom | base64 | head -c 32 | sudo -u oracle tee /etc/strcrypt/strcrypt.key > /dev/null
sudo chmod 600 /etc/strcrypt/strcrypt.key
sudo -u oracle wc -c /etc/strcrypt/strcrypt.key

This creates a 32-byte key in a file only the oracle account can read. The last line should print 32 /etc/strcrypt/strcrypt.key. The key is never passed through SQL arguments or tables. If you lose the key, the ciphertext cannot be recovered, so keep a separate backup.

Common errors

4. extproc.ora settings

mkdir -p $ORACLE_HOME/hs/admin
grep -sn '^SET EXTPROC_DLLS' $ORACLE_HOME/hs/admin/extproc.ora
cat >> $ORACLE_HOME/hs/admin/extproc.ora <<'EOF'
SET EXTPROC_DLLS=ONLY:/usr/lib/liboracle_strcrypt.so
SET LD_LIBRARY_PATH=/usr/lib
SET STRCRYPT_KEY_FILE=/etc/strcrypt/strcrypt.key
EOF

This appends three lines to $ORACLE_HOME/hs/admin/extproc.ora. The grep on the second line checks for an existing SET EXTPROC_DLLS line and prints nothing if there is none. The extproc started by the database reads this file, so you do not need to edit listener.ora or restart anything. The settings apply to new sessions. To use algorithms other than the Korean standards, add a SET STRCRYPT_MODE=non-approved line.

Common errors

5. Install the SQL objects

cd ~/strcrypt-oracle/strcrypt-oracle-0.3.1
sqlplus sys@//localhost:1521/ORCLPDB1 as sysdba @install.sql
sqlplus sys@//localhost:1521/ORCLPDB1 as sysdba @install_bulk.sql

Replace ORCLPDB1 with the PDB service name from step 1. sqlplus asks for the SYS password. install.sql creates six SQL functions, and install_bulk.sql creates four collection types and the STRCRYPT_BULK package, and each script runs an encryption and decryption test at the end. Two [OK] lines from each script mean success. Both scripts can be run again.

Common errors

6. Restrict execute privileges

BEGIN
  FOR o IN (SELECT object_name FROM dba_objects
             WHERE owner = 'SYS'
               AND (object_name LIKE 'STRCRYPT\_%' ESCAPE '\' OR object_name = 'ORACLE_STRCRYPT_LIB')
               AND object_type IN ('FUNCTION', 'PACKAGE', 'TYPE', 'LIBRARY')) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'REVOKE EXECUTE ON SYS.' || o.object_name || ' FROM PUBLIC';
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE != -1927 THEN
          RAISE;
        END IF;
    END;
    IF o.object_name <> 'ORACLE_STRCRYPT_LIB' THEN
      EXECUTE IMMEDIATE 'GRANT EXECUTE ON SYS.' || o.object_name || ' TO APP_USER';
    END IF;
  END LOOP;
END;
/

The install scripts grant execute to everyone (PUBLIC). Left as is, anyone who can connect to the database can decrypt, so connect as SYS the same way as in step 5 and run this block. Replace APP_USER with the user that will encrypt and decrypt, and repeat that line for more users. You can run this block again.

Common errors

7. Verify the installation

Character set and a basic round trip

SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';
SELECT STRCRYPT_DECRYPT(STRCRYPT_ENCRYPT('test')) FROM DUAL;

Batch round-trip test (1,000 rows; the test table is dropped at the end)

CREATE TABLE strcrypt_selftest AS
SELECT level AS id, 'row-' || level || '-홍길동' AS val FROM dual CONNECT BY level <= 1000;

SELECT COUNT(*) AS checked, SUM(CASE WHEN d.v = t.val THEN 0 ELSE 1 END) AS mismatches
FROM TABLE(STRCRYPT_BULK.decrypt_rows(CURSOR(
       SELECT e.k, e.v, CAST(NULL AS RAW(2000))
         FROM TABLE(STRCRYPT_BULK.encrypt_rows(CURSOR(
                SELECT TO_CHAR(id), val, CAST(NULL AS RAW(2000)) FROM strcrypt_selftest), 'aria256gcm')) e))) d
JOIN strcrypt_selftest t ON t.id = TO_NUMBER(d.k);

DROP TABLE strcrypt_selftest PURGE;

Connect to the same PDB as the application user. The first query should return AL32UTF8, the second test, and the round-trip test CHECKED 1000 and MISMATCHES 0. The round-trip test needs the CREATE TABLE privilege.

Common errors

SQL functions

CREATE TABLE users_encrypted AS
SELECT user_id, STRCRYPT_ENCRYPT(email) AS email_encrypted
FROM users;

SELECT user_id, STRCRYPT_DECRYPT(email_encrypted) AS email
FROM users_encrypted;

SELECT STRCRYPT_ENCRYPT_ALGO('alice@example.com', 'seed128gcm') FROM DUAL;

Use them like any other function in SELECT, INSERT, or UPDATE. Each row makes its own extproc call, so switch to batch processing below when you handle many rows. A NULL input returns NULL.

Ciphertext differs every time, even for the same value, so it cannot be indexed. For exact-match search, store STRCRYPT_SEARCH_INDEX(value, 'table.column') in an indexed search column and compare it with the index of the search term. Do not use an unkeyed SHA-256 hash: guessable values such as national ID numbers can be recovered by hashing every candidate. A search column reveals which rows share a value, so add one only where search is needed, and grant execute on the search index function only to users who need it.

Batch processing

Decrypt while selecting many rows

SELECT TO_NUMBER(k) AS id, v AS ssn
FROM TABLE(STRCRYPT_BULK.decrypt_rows(
       CURSOR(SELECT TO_CHAR(id), ssn_enc, CAST(NULL AS RAW(2000))
                FROM customers)));

decrypt_rows and encrypt_rows read the cursor in chunks (100 rows by default) and make one extproc call per chunk. The cursor must return three columns in this order: key, value, and AAD (RAW). Pass NULL when there is no AAD.

Encrypt an existing column in bulk

DECLARE
  TYPE t_id IS TABLE OF customers.id%TYPE;
  v_id   t_id;
  v_in   STRCRYPT_VC_TAB;
  v_out  STRCRYPT_VC_TAB;
  v_last customers.id%TYPE := -1;
BEGIN
  LOOP
    SELECT id, ssn BULK COLLECT INTO v_id, v_in
      FROM customers WHERE id > v_last ORDER BY id FETCH FIRST 100 ROWS ONLY;
    EXIT WHEN v_id.COUNT = 0;
    STRCRYPT_BULK.encrypt_tab(v_in, 'aria256gcm', v_out);
    FORALL i IN 1 .. v_id.COUNT
      UPDATE customers SET ssn_enc = v_out(i) WHERE id = v_id(i);
    v_last := v_id(v_id.COUNT);
    COMMIT;
  END LOOP;
END;
/

Collect a chunk in primary key order, call encrypt_tab once, apply the results by primary key, and commit after each chunk. Committing with a cursor still open can raise ORA-01555, so each chunk is queried again. If a run fails partway, the committed chunks stay, so start again from the last primary key.

Load decrypted results into another table

INSERT /*+ APPEND */ INTO customers_plain (id, ssn)
SELECT TO_NUMBER(k), v
FROM TABLE(STRCRYPT_BULK.decrypt_rows(
       CURSOR(SELECT /*+ PARALLEL(4) */ TO_CHAR(id), ssn_enc, CAST(NULL AS RAW(2000))
                FROM customers)));
COMMIT;

Inside the pipelined functions the library uses a single thread, and Oracle parallel execution handles parallelism. For encrypt_tab and decrypt_tab, keep p_threads at its default of 1. With 1 KB records, values above 1 made bulk UPDATEs slower.

Limitations

• Results must fit in 4,000 bytes of VARCHAR2; longer results raise ORA-20503. With ARIA-256-GCM, plaintext can be up to 2,971 bytes.
• If any record in a batch call fails, the whole call fails (ORA-20502). Failed records are not silently filled with NULL.
• Values containing a NUL (CHR(0)) are rejected by STRCRYPT_ENCRYPT and STRCRYPT_ENCRYPT_ALGO with ORA-20502.
• The same value encrypts to a different ciphertext each time, so you cannot search or index the ciphertext column. Keep a separate search index (STRCRYPT_SEARCH_INDEX) column if you need lookups. Do not use an unkeyed hash column.
• Plaintext passes through the memory of the extproc process on the database server.

API reference

Test environments

In both environments, steps 1 to 7, the SQL function and batch examples (a 250-row round trip), and rejection of calls from users without a grant were confirmed (2026-09-17).