Oracle 사용법

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'

sudo dnf install -y gcc make

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

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

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

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

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;
/

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

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;

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;

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)));

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;
/

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;