dba:passwort_in_psql_schuetzen
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende ÜberarbeitungNächste Überarbeitung | Vorhergehende ÜberarbeitungLetzte ÜberarbeitungBeide Seiten der Revision | ||
dba:passwort_in_psql_schuetzen [2016/05/19 20:15] – [Vorbereitung] gpipperr | dba:passwort_in_psql_schuetzen [2016/05/20 15:11] – [Passwörter und ähnliche Schlüssel in PL/SQL Packages schützen] gpipperr | ||
---|---|---|---|
Zeile 27: | Zeile 27: | ||
Zusätzlicher Schutz: | Zusätzlicher Schutz: | ||
- | * Das zu schützende Package liegt in einem gesonderten Schema, => 12c Möglichkeit nützen das nur das Package explizit auf die eine Routine zu granten die das Package dann auch benötigt, nicht an den eigentlichen Applikationsuser. | + | * Das zu schützende Package liegt in einem gesonderten Schema |
+ | * => 12c Möglichkeit nützen das nur das Package explizit auf die eine Routine zu granten die das Package dann auch benötigt, nicht an den eigentlichen Applikationsuser | ||
+ | * => 12c Feature " | ||
Zeile 37: | Zeile 39: | ||
Bezgl. Scripting siehe auch diesen Ideen dazu [[dba: | Bezgl. Scripting siehe auch diesen Ideen dazu [[dba: | ||
- | |||
==== Eine PL/SQL Lösung=== | ==== Eine PL/SQL Lösung=== | ||
Zeile 53: | Zeile 54: | ||
Die Spezifikation: | Die Spezifikation: | ||
<code plsql encrypt_util_spec.sql> | <code plsql encrypt_util_spec.sql> | ||
- | |||
create or replace package encrypt_util | create or replace package encrypt_util | ||
as | as | ||
- | |||
| | ||
| | ||
Zeile 64: | Zeile 63: | ||
- | --+ ----------------------------------------------------------------------- | + | |
- | -- encrypt text | + | -- encrypt text |
- | --+ ----------------------------------------------------------------------- | + | --+ ----------------------------------------------------------------------- |
- | function encrypt (p_plaintext varchar2) | + | function encrypt (p_plaintext varchar2) |
- | return raw | + | return raw |
- | deterministic; | + | deterministic; |
+ | --+ ----------------------------------------------------------------------- | ||
+ | -- decrypt text | ||
+ | --+ ----------------------------------------------------------------------- | ||
+ | function decrypt (p_encryptedtext raw) | ||
+ | return varchar2 | ||
+ | deterministic; | ||
- | --+ ----------------------------------------------------------------------- | + | |
- | -- | + | -- store passwords in to a object |
- | --+ ----------------------------------------------------------------------- | + | --+ ----------------------------------------------------------------------- |
- | function decrypt | + | |
- | return | + | , p_slot number |
- | deterministic; | + | , p_store |
+ | , p_private_key varchar2 default ' | ||
+ | ); | ||
+ | --+ ----------------------------------------------------------------------- | ||
+ | -- get User password | ||
+ | --+ ----------------------------------------------------------------------- | ||
+ | function getuserpwd (p_slot number, | ||
+ | return varchar2; | ||
- | |||
- | --+ ----------------------------------------------------------------------- | ||
- | -- store passwords in to a object | ||
- | --+ ----------------------------------------------------------------------- | ||
- | |||
- | procedure storepwd (p_pwd varchar2 | ||
- | , p_slot number | ||
- | , p_store varchar2 default ' | ||
- | , p_private_key varchar2 default ' | ||
- | ); | ||
- | |||
- | --+ ----------------------------------------------------------------------- | ||
- | -- get User password | ||
- | --+ ----------------------------------------------------------------------- | ||
- | function getuserpwd (p_slot number, | ||
- | return varchar2; | ||
end encrypt_util; | end encrypt_util; | ||
/ | / | ||
Zeile 105: | Zeile 101: | ||
create or replace package body encrypt_util | create or replace package body encrypt_util | ||
as | as | ||
- | --+ ---------------------------------------------------------------------------- | + | |
- | -- thankt to: | + | -- thankt to: |
- | -- see http:// | + | -- see http:// |
- | --+ -------------------------------------------------------------------------- | + | --+ -------------------------------------------------------------------------- |
+ | g_crypt_clear_key | ||
+ | g_encryption_key | ||
- | g_crypt_clear_key varchar2 (256) := '' | + | --+ ---------------------------------------------------------------------------- |
+ | -- ENCRYPT_DES is the encryption algorithem. | ||
+ | -- Data Encryption Standard. Block cipher. | ||
+ | -- Uses key length of 56 bits. | ||
+ | -- | ||
+ | -- CHAIN_CBC Cipher Block Chaining. Plaintext is XORed with the previous ciphertext | ||
+ | -- block before it is encrypted. | ||
+ | -- | ||
+ | -- PAD_PKCS5 Provides padding which complies with the PKCS #5: Password-Based | ||
+ | -- Cryptography Standard | ||
+ | --+ ---------------------------------------------------------------------------- | ||
+ | g_encryption_type | ||
- | g_encryption_key raw (32) := '' | + | --+ ---------------------------------------------------------------------------- |
+ | -- encrypt a text | ||
+ | --+ ---------------------------------------------------------------------------- | ||
+ | function encrypt (p_plaintext varchar2) | ||
+ | return raw | ||
+ | deterministic | ||
+ | is | ||
+ | encrypted_raw | ||
+ | begin | ||
+ | encrypted_raw | ||
+ | dbms_crypto.encrypt (src => utl_raw.cast_to_raw (p_plaintext) | ||
+ | , typ => g_encryption_type | ||
+ | , key => g_encryption_key | ||
+ | ); | ||
+ | return encrypted_raw; | ||
+ | end encrypt; | ||
- | --+ ---------------------------------------------------------------------------- | + | |
- | -- ENCRYPT_DES is the encryption algorithem. | + | -- |
- | -- Data Encryption Standard. Block cipher. | + | --+ ---------------------------------------------------------------------------- |
- | -- Uses key length of 56 bits. | + | |
- | -- | + | return varchar2 |
- | -- CHAIN_CBC Cipher Block Chaining. Plaintext is XORed with the previous ciphertext | + | deterministic |
- | -- block before it is encrypted. | + | is |
- | -- | + | decrypted_raw |
- | -- PAD_PKCS5 Provides padding which complies with the PKCS #5: Password-Based | + | begin |
- | -- Cryptography Standard | + | decrypted_raw |
- | --+ ---------------------------------------------------------------------------- | + | |
- | g_encryption_type pls_integer | + | , typ => g_encryption_type |
+ | , key => g_encryption_key | ||
+ | ); | ||
+ | return (utl_raw.cast_to_varchar2 (decrypted_raw)); | ||
+ | end decrypt; | ||
- | --+ ---------------------------------------------------------------------------- | ||
- | -- encrypt a text | ||
- | --+ ---------------------------------------------------------------------------- | ||
- | function | + | --+ ---------------------------------------------------------------------------- |
- | return | + | -- clean string from whitespaces to avoid sql injection |
- | deterministic | + | --+ ---------------------------------------------------------------------------- |
- | is | + | |
- | encrypted_raw raw | + | return |
- | begin | + | is |
- | encrypted_raw | + | |
- | dbms_crypto.encrypt | + | begin |
- | | + | |
- | | + | |
- | | + | v_text := replace (v_text, ' ', '' |
- | return | + | |
- | end encrypt; | + | return |
+ | end cleantext; | ||
+ | --+ ---------------------------------------------------------------------------- | ||
+ | -- get a key from the local db enviroment | ||
+ | -- The encryption key for DES algorithem, should be 8 bytes or more. | ||
+ | --+ ---------------------------------------------------------------------------- | ||
+ | function getkeyfromlocal(p_private_key varchar2 default null) | ||
+ | return varchar2 | ||
+ | is | ||
+ | v_obj_id1 | ||
+ | v_obj_id2 | ||
+ | v_key | ||
+ | begin | ||
+ | -- get some ids from default objects, that not chaning so often .-) | ||
+ | select to_char (object_id) | ||
+ | into v_obj_id1 | ||
+ | from user_objects | ||
+ | where object_name = ' | ||
+ | and object_type = ' | ||
- | --+ ---------------------------------------------------------------------------- | + | select to_char (sum (object_id)) |
- | -- decrypt a text | + | into v_obj_id2 |
- | --+ ---------------------------------------------------------------------------- | + | from all_objects |
- | function decrypt | + | where object_name |
- | return varchar2 | + | and owner in (' |
- | deterministic | + | |
- | is | + | |
- | decrypted_raw raw (2000); | + | |
- | begin | + | |
- | decrypted_raw := | + | |
- | dbms_crypto.decrypt | + | |
- | | + | |
- | , key => g_encryption_key | + | |
- | | + | |
- | return (utl_raw.cast_to_varchar2 (decrypted_raw)); | + | |
- | end decrypt; | + | |
- | + | | |
- | --+ ---------------------------------------------------------------------------- | + | initcap (sys_context (' |
- | -- clean string from whitespaces to avoid sql injection | + | || '#' |
- | --+ ---------------------------------------------------------------------------- | + | || lower (sys_context (' |
- | function cleantext (p_text varchar2) | + | || ' |
- | return varchar2 | + | || nvl (v_obj_id1, ' |
- | is | + | || ' |
- | v_text varchar2 (32000); | + | || initcap (sys_context (' |
- | begin | + | || ' |
- | v_text := | + | || nvl (v_obj_id2, ' |
- | replace (p_text | + | |
- | , chr (10) | + | |
- | , '' | + | |
- | ); | + | |
- | v_text := | + | |
- | replace (v_text | + | |
- | , chr (13) | + | |
- | , '' | + | |
- | ); | + | |
- | v_text := | + | |
- | replace (v_text | + | |
- | , ' ' | + | |
- | , '' | + | |
- | ); | + | |
- | v_text := | + | |
- | regexp_replace (v_text | + | |
- | | + | |
- | | + | |
- | ); | + | |
- | + | ||
- | return v_text; | + | |
- | end cleantext; | + | |
- | + | ||
- | + | ||
- | --+ ---------------------------------------------------------------------------- | + | |
- | -- get a key from the local db enviroment | + | |
- | -- The encryption key for DES algorithem, should be 8 bytes or more. | + | |
- | --+ ---------------------------------------------------------------------------- | + | |
- | function getkeyfromlocal(p_private_key varchar2 default null) | + | |
- | return varchar2 | + | |
- | is | + | |
- | v_obj_id1 varchar2 (12) := ' | + | |
- | v_obj_id2 varchar2 (12) := ' | + | |
- | v_key varchar2 (2000) := ' | + | |
- | begin | + | |
- | -- get some ids from default objects, that not chaning so often .-) | + | |
- | select to_char (object_id) | + | |
- | into v_obj_id1 | + | |
- | from user_objects | + | |
- | where object_name = ' | + | |
- | and object_type = ' | + | |
- | + | ||
- | select to_char (sum (object_id)) | + | |
- | into v_obj_id2 | + | |
- | from all_objects | + | |
- | where object_name = ' | + | |
- | and owner in (' | + | |
- | + | ||
- | v_key := | + | |
- | initcap (sys_context (' | + | |
- | || '#' | + | |
- | || lower (sys_context (' | + | |
- | || ' | + | |
- | || nvl (v_obj_id1, ' | + | |
- | || ' | + | |
- | || initcap (sys_context (' | + | |
- | || ' | + | |
- | || nvl (v_obj_id2, ' | + | |
| | ||
- | if p_private_key is not null then | + | |
- | v_key := p_private_key||v_key; | + | v_key := p_private_key||v_key; |
- | | + | end if; |
- | return substr(v_key, | + | |
| | ||
- | end getkeyfromlocal; | + | |
- | --+ ---------------------------------------------------------------------------- | + | |
- | -- store passwords in to a object | + | -- store passwords in to a object |
- | -- need create function right | + | -- need create function right |
- | -- grant create procedure to < | + | -- grant create procedure to < |
- | -- grant create type to < | + | -- grant create type to < |
-- debug exec encrypt_util.storepwd(' | -- debug exec encrypt_util.storepwd(' | ||
- | --+ ---------------------------------------------------------------------------- | + | |
- | procedure storepwd (p_pwd varchar2 | + | |
- | , p_slot number | + | , p_slot number |
- | , p_store varchar2 default ' | + | , p_store varchar2 default ' |
, p_private_key varchar2 default ' | , p_private_key varchar2 default ' | ||
- | | + | ) |
- | is | + | is |
- | cursor c_read_type | + | cursor c_read_type |
- | is | + | is |
- | select text | + | select text |
- | | + | from user_source |
- | where name = ' | + | |
- | and type = 'TYPE BODY'; | + | |
- | v_f_template varchar2 (2000) | + | |
- | := ' | + | := ' |
- | v_o_template_s | + | |
- | := ' | + | := ' |
password varchar2(56) | password varchar2(56) | ||
,MEMBER FUNCTION getUserPWD1 RETURN raw | ,MEMBER FUNCTION getUserPWD1 RETURN raw | ||
Zeile 277: | Zeile 251: | ||
)'; | )'; | ||
- | v_o_template_b | + | |
- | := ' | + | := ' |
MEMBER | MEMBER | ||
FUNCTION getUserPWD1 RETURN raw | FUNCTION getUserPWD1 RETURN raw | ||
Zeile 312: | Zeile 286: | ||
'; | '; | ||
- | v_template varchar2 (32000) := ''; | + | |
- | v_key varchar2 (2000); | + | v_key |
- | v_slot varchar2 (2); | + | v_slot |
- | v_count pls_integer; | + | v_count |
- | begin | + | begin |
- | | + | -- set your private key to harden the safe |
+ | if p_private_key != ' | ||
+ | g_crypt_clear_key := getkeyfromlocal(p_private_key); | ||
+ | else | ||
+ | | ||
+ | end if; | ||
| | ||
- | if p_private_key != ' | + | |
- | | + | g_encryption_key := utl_raw.cast_to_raw (g_crypt_clear_key); |
- | else | + | |
- | g_crypt_clear_key := getkeyfromlocal; | + | |
- | end if; | + | |
- | + | ||
- | | + | |
- | + | ||
- | | + | |
| | ||
- | -- prevent for SQLInjection | + | |
- | v_key := cleantext (p_text => p_pwd); | + | v_key := cleantext (p_text => p_pwd); |
- | v_slot := cleantext (p_text => to_char (p_slot)); | + | v_slot := cleantext (p_text => to_char (p_slot)); |
+ | dbms_output.put_line (' | ||
- | dbms_output.put_line ('--Info :: store in Slot : ' | + | if p_store = 'FUNCTION' |
+ | then | ||
+ | v_template | ||
+ | v_template :=replace (v_template, | ||
+ | elsif p_store = ' | ||
+ | then | ||
+ | null; | ||
+ | -- implement here your setup table to store the pwd | ||
+ | -- | ||
+ | elsif p_store = ' | ||
+ | then | ||
+ | select count (*) | ||
+ | into v_count | ||
+ | from user_types | ||
+ | where type_name = ' | ||
- | if p_store = ' | + | |
- | then | + | then |
- | v_template := | + | -- create the object spec |
- | replace (v_f_template | + | |
- | , '## | + | |
- | , (encrypt (v_key)) | + | |
- | | + | |
- | v_template := | + | |
- | replace (v_template | + | else |
- | | + | -- get the code of the object |
- | | + | -- check if object exists |
- | | + | |
- | elsif p_store = ' | + | |
- | then | + | |
- | null; | + | |
- | -- implement here your setup table to store the pwd | + | |
- | -- | + | |
- | elsif p_store | + | |
- | then | + | |
- | select count (*) | + | |
- | into v_count | + | |
- | from user_types | + | |
- | where type_name = ' | + | |
- | if v_count < 1 | + | |
- | then | + | loop |
- | -- create the object spec | + | if instr (rec.text |
- | execute immediate v_o_template_s; | + | , ' |
- | + | ) > 1 | |
- | v_template := | + | then |
- | replace (v_o_template_b | + | -- correct line |
- | , '## | + | v_template := |
- | , (encrypt (v_key)) | + | v_template |
- | | + | || regexp_replace (rec.text |
- | else | + | , ' |
- | -- get the code of the object | + | , ' |
- | -- check if object exists | + | ); |
- | v_template := ' | + | else |
- | + | v_template := v_template || rec.text; | |
- | for rec in c_read_type | + | end if; |
- | loop | + | end loop; |
- | if instr (rec.text | + | end if; |
- | | + | end if; |
- | ) > 1 | + | |
- | then | + | |
- | -- correct line | + | |
- | v_template := | + | |
- | v_template | + | |
- | || regexp_replace (rec.text | + | |
- | | + | |
- | | + | |
- | ); | + | |
- | else | + | |
- | v_template := v_template || rec.text; | + | |
- | end if; | + | |
- | end loop; | + | |
- | end if; | + | |
- | end if; | + | |
- | --dbms_output.put_line(' | + | |
- | execute immediate v_template; | + | |
- | end storepwd; | + | end storepwd; |
- | --+ ----------------------------------------------------------------------- | + | |
- | -- get User password | + | -- get User password |
-- select encrypt_util.getuserpwd(2) from dual; | -- select encrypt_util.getuserpwd(2) from dual; | ||
- | --+ ----------------------------------------------------------------------- | + | |
- | function getuserpwd (p_slot number, p_private_key varchar2 default ' | + | |
- | return varchar2 | + | return varchar2 |
- | is | + | is |
- | v_count pls_integer; | + | v_count |
- | v_template_0 varchar2 (2000) := ' | + | |
v_pwd pwd_wallet; | v_pwd pwd_wallet; | ||
begin | begin | ||
Zeile 420: | Zeile 378: | ||
end; | end; | ||
'; | '; | ||
- | v_template_f varchar2 (2000) := ' begin | + | |
: | : | ||
end; | end; | ||
'; | '; | ||
- | v_pwd varchar2 (2000); | + | |
- | v_slot varchar2 (2); | + | v_slot |
- | v_template varchar2 (2000); | + | v_template |
- | begin | + | begin |
| | ||
-- set your private key to harden the safe | -- set your private key to harden the safe | ||
Zeile 441: | Zeile 399: | ||
g_encryption_key := utl_raw.cast_to_raw (g_crypt_clear_key); | g_encryption_key := utl_raw.cast_to_raw (g_crypt_clear_key); | ||
| | ||
- | v_slot := cleantext (p_text => to_char (p_slot)); | + | |
- | -- if object exitst use object | + | |
- | select count (*) | + | select count (*) |
- | | + | into v_count |
- | | + | from user_types |
- | where type_name = ' | + | |
- | if v_count > 0 | + | |
- | then | + | then |
- | v_template := | + | v_template :=replace (v_template_0 , '## |
- | replace (v_template_0 | + | else |
- | | + | -- must be a function or a table |
- | | + | select count (*) |
- | | + | into v_count |
- | else | + | from user_objects |
- | -- must be a function or a table | + | |
- | select count (*) | + | |
- | | + | |
- | | + | |
- | where object_name = upper (' | + | |
- | if v_count > 0 | + | |
- | then | + | then |
- | v_template := | + | v_template :=replace (v_template_f, |
- | replace (v_template_f | + | else |
- | | + | -- implment your table data store here |
- | | + | raise_application_error (-20001 |
- | | + | , 'No PWD Function found for the slot::' |
- | else | + | ); |
- | -- implment your table data store here | + | end if; |
- | raise_application_error (-20001 | + | end if; |
- | | + | |
- | ); | + | |
- | end if; | + | |
- | end if; | + | |
- | begin | + | |
- | execute immediate v_template using out v_pwd; | + | execute immediate v_template using out v_pwd; |
- | exception | + | exception |
- | when crypt_key_error then | + | when crypt_key_error then |
v_pwd := ' | v_pwd := ' | ||
| | ||
Zeile 489: | Zeile 439: | ||
dbms_output.put_line ('-- Error :: No PWD in Slot ' || v_slot || ' set! Error:' | dbms_output.put_line ('-- Error :: No PWD in Slot ' || v_slot || ' set! Error:' | ||
when others | when others | ||
- | then | + | |
- | raise_application_error (-20000, 'Error read pwd from pwd_wallet:: | + | raise_application_error (-20000, 'Error read pwd from pwd_wallet:: |
- | end; | + | end; |
- | return v_pwd; | + | |
- | end getuserpwd; | + | end getuserpwd; |
begin | begin | ||
| | ||
Zeile 538: | Zeile 488: | ||
- | <note important> | + | <note important> |
dba/passwort_in_psql_schuetzen.txt · Zuletzt geändert: 2016/05/20 15:20 von gpipperr