Update with select in Oracle

In this article, I'll show you how you can update data from table to another with select statement. --1--create two table. CREATE TABLE LOCAT001 AS (SELECT LOCATION_ID, STREET_ADDRESS, STATE_PROVINCE FROM LOCATIONS ); alter table LOCAT001 add constraint PK_LOCATION_ID primary key("LOCATION_ID") ; CREATE TABLE LOCAT002 AS (SELECT LOCATION_ID, STREET_ADDRESS, STATE_PROVINCE FROM LOCATIONS ); alter table LOCAT002 add constraint PK_LOCATION02_ID primary key("LOCATION_ID") ; UPDATE LOCAT002 SET STATE_PROVINCE=NULL; UPDATE LOCAT002 SET STATE_PROVINCE=NULL / COMMIT / The source table. The target table. --2--Do the update now with the select. --3-- See the result .

Import export d'un CLOB

1- Export d'un CLOB

le présent article vous montre une méthode simple pour l'export du contenu d'un type CLOB sur un système de fichier.

Premiérement on doit crée un repértoire(directory) qui pointera sur le clob à exporter.

CREATE OR REPLACE DIRECTORY fichier AS 'C:\Images\'; SQL> Directory created.

On va lire le contenu du CLOB pour l'écrire(Enregistrer) dans le répertoire crée précédemment.

SQL> SET SERVEROUTPUT ON SQL>DECLARE 2 l_file UTL_FILE.FILE_TYPE; 3 l_clob CLOB; 4 l_buffer VARCHAR2(32767); 5 l_amount BINARY_INTEGER := 32767; 6 l_pos INTEGER := 1; 6 BEGIN 7 SELECT col1 8 INTO l_clob 9 FROM tab1 10 WHERE rownum = 1; 11 l_file := UTL_FILE.fopen('FICHIER', 'Test01.txt', 'w', 32767); 12 LOOP 13 DBMS_LOB.read (l_clob, l_amount, l_pos, l_buffer); 14 UTL_FILE.put(l_file, l_buffer); 15 l_pos := l_pos + l_amount; 16 END LOOP; 17 EXCEPTION 18 WHEN NO_DATA_FOUND THEN 19 UTL_FILE.fclose(l_file); 20 WHEN OTHERS THEN 21 UTL_FILE.fclose(l_file); 22 RAISE; 23 END; / PL/SQL procedure successfully completed.

Remarque: l’arrêt du processus est fait par le biais de l'exception NO_DATA_FOUND.D'autre exception pouvent causer l'arrêt

de l'écriture dans le répertoire, pour cela vous pouvez vous referez au package:UTL_FILE pour gérer d'autre exception

2- Import d'un CLOB

On va utiliser le répertoire crée précédent pour faire enregistrer le fichier dans le CLOB.

On va crèer la table qui va servir comme sauvgarde de notre fichier text.

SQL>CREATE TABLE tab1 ( id_file NUMBER, clob_data CLOB ); Table created

On va importer notre fichier text et l'insérer dans la table par le processu suivant:

SQL>DECLARE 2 l_bfile BFILE; 3 l_clob CLOB; 4 BEGIN 5 INSERT INTO tab1 (id_file, clob_date) 6 VALUES (1, empty_clob()) 7 RETURN clob_data INTO l_clob; 8 l_bfile := BFILENAME('FICHIER', 'Test01.txt'); 9 DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly); 10 DBMS_LOB.loadfromfile(l_clob, l_bfile, DBMS_LOB.getlength(l_bfile)); 11 DBMS_LOB.fileclose(l_bfile); 12 COMMIT; 13 END; / PL/SQL procedure successfully completed.

Cryptage de mot de passe (Storing Passwords in an Oracle Database)

Quand on parle de gestion de la sécurité des applications,il y'a souvent un besoin de stocker des mots de passe

dans une table de base de données. Ceci peut en soi mener aux questions de sécurité, puisque des utilisateurs

avec des privilèges appropriés peuvent lire le contenu des ces tables . Une approche de sécurité consiste en

le cryptage des mots de passe avant leurs stockages,mais un mécanisme de décryptage pourrait vous exposer a une

faille de sécurité. Une alternative plus sûr est de stocker le hash code du nom utilisateur et de son mot de passe

comme mot passe de sécurité.Dans cette article, je vous montre un processus utilisant le package DBMS_OBFUSCATION_TOOLKIT


qui est disponible sur Oracle9i ou bien en sha_1 avec Oracle 10

Premiérement on va crée une table pour sauvgarder le nom utilisateur et sont mot de passe.

Example

> SQLPLUS scott/tiger Oracle Database 10g Release 10.2.0.1.0 - Production SQL> SET LINESIZE 130 SQL> CREATE TABLE demo_users ( id_user NUMBER(12) NOT NULL, username VARCHAR2(128) NOT NULL, password VARCHAR2(128) NOT NULL ); SQL> Table created. SQL>ALTER TABLE demo_users ADD ( CONSTRAINT id_users_pk PRIMARY KEY (id_user) ); SQL> Table altered. SQL>ALTER TABLE demo_users ADD ( CONSTRAINT users_name_uk UNIQUE (username) ); SQL> Table altered. SQL>CREATE SEQUENCE demo_users_seq; SQL> sequence created. --Création du package pour la sécurisation des informations des utilisateur SQL> CREATE OR REPLACE PACKAGE demo_user_security AS FUNCTION GET_HASH (p_username IN VARCHAR2, p_password IN VARCHAR2) RETURN VARCHAR2; PROCEDURE add_user (p_username IN VARCHAR2, p_password IN VARCHAR2); PROCEDURE change_password (p_username IN VARCHAR2, p_old_password IN VARCHAR2, p_new_password IN VARCHAR2); PROCEDURE valid_user (p_username IN VARCHAR2, p_password IN VARCHAR2); FUNCTION valid_user (p_username IN VARCHAR2, p_password IN VARCHAR2) RETURN BOOLEAN; END; SQL> package created. SQL>CREATE OR REPLACE PACKAGE BODY demo_user_security AS FUNCTION GET_HASH (p_username IN VARCHAR2, p_password IN VARCHAR2) RETURN VARCHAR2 AS v_secur VARCHAR2(30) := 'Test'; BEGIN -- Pre Oracle 10g RETURN DBMS_OBFUSCATION_TOOLKIT.MD5( input_string => UPPER(p_username) || v_secur || UPPER(p_password)); -- Oracle 10g+ : Require EXECUTE on DBMS_CRYPTO --RETURN DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW(UPPER(p_username) --|| v_secur || UPPER(p_password)),DBMS_CRYPTO.HASH_SH1); END; PROCEDURE add_user (p_username IN VARCHAR2, p_password IN VARCHAR2) AS BEGIN INSERT INTO demo_users ( id_user, username, password ) VALUES ( demo_users_seq.NEXTVAL, UPPER(p_username), GET_HASH(p_username, p_password) ); COMMIT; END; PROCEDURE change_password (p_username IN VARCHAR2, p_old_password IN VARCHAR2, p_new_password IN VARCHAR2) AS v_rowid ROWID; BEGIN SELECT rowid INTO v_rowid FROM demo_users WHERE username = UPPER(p_username) AND password = get_hash(p_username, p_old_password) FOR UPDATE; UPDATE demo_users SET password = get_hash(p_username, p_new_password) WHERE rowid = v_rowid; COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20010, 'nom utilisateur/mot de passe incorrect.'); END; PROCEDURE valid_user (p_username IN VARCHAR2, p_password IN VARCHAR2) AS v_numy VARCHAR2(1); BEGIN SELECT '1' INTO v_numy FROM demo_users WHERE username = UPPER(p_username) AND password = get_hash(p_username, p_password); EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20000, 'nom utilisateur/mot de passe incorrect.'); END; FUNCTION valid_user (p_username IN VARCHAR2, p_password IN VARCHAR2) RETURN BOOLEAN AS BEGIN valid_user(p_username, p_password); RETURN TRUE; EXCEPTION WHEN OTHERS THEN RETURN FALSE; END; END; / SQL> package body created.

 

La surcharge de VALID_USER permet un contrôle de sécurité de façon différente.La fonction de GET_HASH est

utilisée pour hacher la combinaison du nom d'utilisateur et du mot de passe. Il rend toujours un VARCHAR2

indépendamment de la longueur des paramètres de saisie.DBMS_OBFUSCATION_TOOLKIT.MD5 permet de vous générer un

hash code en MD5.

 

--Exemple: --Création des utilisateurs SQL> exec demo_user_security.add_user('Smith','secret'); PL/SQL procedure successfully completed. SQL> exec demo_user_security.add_user('William','terces121'); PL/SQL procedure successfully completed. SQL> select * from demo_users; ID USERNAME PASSWORD ----------- ----------------------------------------------- 1 Smith ãXFõˆC„®W3–E 2 William b¾õmûMÀ*愶}ˆ --Ensuite on va vérifier la procédure VALID_USER SQL> EXEC demo_user_security.valid_user('Smith','secret'); PL/SQL procedure successfully completed. SQL> EXEC demo_user_security.valid_user('William','bblsld'); BEGIN app_user_security.valid_user('william','bblsld'); END; * ERROR at line 1: ORA-20000: nom utilisateur/mot de passe incorrect. ORA-06512: at "W2K1.DEMO_USER_SECURITY", line 66 ORA-06512: at line 1 --Ensuite on vérifiéra la fonction VALID_USER. SQL> SET SERVEROUTPUT ON SQL> BEGIN 2 IF demo_user_security.valid_user('Smith','secret') THEN 3 DBMS_OUTPUT.PUT_LINE('TRUE'); 4 ELSE 5 DBMS_OUTPUT.PUT_LINE('FALSE'); 6 END IF; 7 END; 8 / TRUE PL/SQL procedure successfully completed. SQL> BEGIN 2 IF demo_user_security.valid_user('William','bblsld') THEN 3 DBMS_OUTPUT.PUT_LINE('TRUE'); 4 ELSE 5 DBMS_OUTPUT.PUT_LINE('FALSE'); 6 END IF; 7 END; 8 / FALSE PL/SQL procedure successfully completed. SQL> --Au final on vérifiéra la procédure CHANGE_PASSWORD. SQL> exec demo_user_security.change_password('Smith','secret','tresect'); PL/SQL procedure successfully completed. SQL> exec demo_user_security.change_password('William','vfrdtg','vcftg12'); BEGIN app_user_security.change_password('William','vfrdtg','vfrd14g'); END; * ERROR at line 1: ORA-20000: nom utilisateur/mot de passe incorrect. ORA-06512: at "W2K1.DEMO_USER_SECURITY", line 52 ORA-06512: at line 1