Un buffer overflow (BOF) est littéralement un dépassement de la capacité d’un buffer de données. Rien de bien méchant, cela arrive fréquement lorsque l’on manipule des pointeurs et que l’on programme sans trop de précautions.
La conséquence est généralement un ‘crash’ du programme, qui tente un accès à des zones en dehors de son espace d’adressage (donne des segmentation fault ou protection fault).
La saturation ou le crash de serveur n’est généralement le but ultime des hackers. L’acquisition de privilèges ou l’ouverture de backdoor est plus souvent ciblée.

Dans le domaine de la sécurité cette technique du BOF est utilisée pour ‘dérouter’ la séquence normale des instructions d’un programme et le forcer à éxécuter une routine spécifique, permettant généralement l’obtention de droits ’super utilisateur’. Cette routine ou ce programme exploitant la faille est appelé un “exploit” (en Anglais dans le texte)

‘droits super utilisateur’ ne veut pas dire seulement obtention de l’UID ‘root’ ou ‘Administrateur’. Beaucoup d’applications utilisent des variables ou des tables pour gérér en interne des niveaux de privilèges indépendemment de l’OS. Des flags du type ‘useradmin=1′ ou ‘loginok=true’ ou ‘privileges=ALL’ sont monnaie courante dans les programmes et beaucoup plus facilement exploitables.
De l’importance des attaques par buffer oveflow

Ce problème est important de par sa notoriété et également de par sa persistance. Les premières mises en évidence de la possibilité de ce type d’attaque datent de 1995 et sont toujours d’actualité.
Statistiquement si l’on consulte les sites de référérence que sont le site du Cert et le site d’infosys security par exemple, on constate qu’une part importante des pbs traités concerne le BOF. Voir plus particulièrement le rapport du dernier trimestre 2003 du CERT.

Bien qu’abondamment documenté sur le web, ce type d’attaque nécessite une grande ténacité et de bonnes compétences en programmation de bas niveau (assembleur), en désassemblage (les sources sauf cas ideal de certains logiciels libres n’étant pas à la disposition des hackers) et en gestion de la mémoire et architecture système (structure et adressage de la pile, contenu des bibliothèques sytème notamment).
Des conditions d’occurence d’un buffer overflow

Un certain nombre de conditions sont indispensables à l’occurence d’un BOF :

• code de programmation vulnérable

o langage utilisé permissif (C),
o fonctions utilisées laxistes (strcpy(), strcat(), sprintf(), vsprintf(), gets(), scanf())
o appels à des fonctions privilégiées (system()),
o utilisation de variable donnant des privilèges,
o absence de test du code, etc.

• programme attaqué s’exécutant avec un niveau de privilège fort (uid root par exemple)

• lisibilité / accessibilité du code (accès au source ou désassemblage explicite, pas de free(), ni de user_check())

• ordonnancement des données dans le programme (variable importante ‘écrasable’ , adresse de retour de subroutines ‘écrasable’

mais aussi

• exposition / intérêt du site

• ténacité du hacker

exemple de buffer overflow minimal …qui ne sert pas à grand chose !

void f() {
int a[10];
a[20] = 3;
}

sur la plipart des machines, le programme se plante et produit une erreur de segmentation de mémoire, du style : segmentation fault, core dumped…

Exemple simple de Buffer Overflow sur flag de sécurité

int main(int argc, char *argv[]) {
char passwd_ok = 0;
char passwd[8];
strcpy(passwd, argv[1]);
if (strcmp(passwd, “niklas”)==0)
passwd_ok = 1;

if (passwd_ok) {
…
}

La mémoire ressemble à ca :

on comprend bien qu’en envoyant le bon nombre de caractère le Hacker, va écrasé le flag PASSWD_OK et se dispenser de s’authentifier avec un mot de passe correct…

Un exemple idéal (un heap overflow)

D’après Ghost Rider “Introduction to Buffer Overflow”, www.governmentsecurity.org

Soit le petit programme C suivant

main(int argc, char **argv) {
char *somevar;
char *important;
somevar = (char *)malloc(sizeof(char)*4);
important = (char *)malloc(sizeof(char)*14);
strcpy(important, “command”); /*This one is the important variable*/
stcrpy(somevar, argv[1]);
….. Code here ….
}

Il présente qq caractéristiques remarquables :

• Il utilise des appels de commande système via la variable ‘important’.
• Il a (au moins) un paramètre d’entrée (argv[1]) stocké dans la variable ’somevar’.
• La variable ’someva’r est allouée en mémoire AVANT ‘important’, on peut en déduire que son adresse sera probablement inférieure a celle de ‘important’.
• Enfin, malgré cette précédence, ’somevar’ prendra sa valeur a l’execution APRES ‘important’

Modifions ce programme pour lui faire imprimer les adresses et les contenus, et executons le en lui passant le parametre ‘TOTO’ :

$mon_programme TOTO

0×8049700: T(0×616c62)
0×8049701: O(0×616c)
0×8049702: T(0×61)
0×8049703: O(0×0)
0×8049704: (0×0)
0×8049705: (0×0)
0×8049706: (0×0)
0×8049707: (0×0)
0×8049708: (0×0)
0×8049709: (0×19000000)
0×804970a: (0×190000)
0×804970b: (0×1900)
0×804970c: (0×19)
0×804970d: (0×63000000)
0×804970e: (0×6f630000)
0×804970f: (0×6d6f6300)
0×8049710: c (0×6d6d6f63)
0×8049711: o (0×616d6d6f)
0×8049712: m (0×6e616d6d)
0×8049713: m (0×646e616d)
0×8049714: a (0×646e61)
0×8049715: n (0×646e)
0×8049716: d (0×64)
0×8049717: (0×0)

On connait désormais le décalage d’adresse entre ’somevar’ et ‘important’.
Le C et strcpy() le permettant, on va se permettre un dépassement de buffer de la variable ’somevar’ pour substituer NOTRE commande à la ‘command’ d’origine.

0×8049700: T(0×646e6573)
0×8049701: O(0×2d646e65)
0×8049702: T(0×2d2d646e)
0×8049703: O(0×2d2d2d64)
0×8049704: - (0×2d2d2d2d)
0×8049705: - (0×2d2d2d2d)
0×8049706: - (0×2d2d2d2d)
0×8049707: - (0×2d2d2d2d)
0×8049708: - (0×2d2d2d2d)
0×8049709: - (0×2d2d2d2d)
0×804970a: - (0×2d2d2d2d)
0×804970b: - (0×2d2d2d2d)
0×804970c: - (0×2d2d2d2d)
0×804970d: - (0×6e2d2d2d)
0×804970e: - (0×656e2d2d)
0×804970f: - (0×77656e2d)
0×8049710: n (0×6377656e) <— c’est la !
0×8049711: e (0×6f637765)
0×8049712: w (0×6d6f6377)
0×8049713: c (0×6d6d6f63)
0×8049714: o (0×616d6d6f)
0×8049715: m (0×6e616d6d)
0×8049716: m (0×646e616d)
0×8049717: a (0×646e61)
0×8049718: n (0×646e)
0×8049719: d (0×64)
0×804971a: (0×0)

C’est la le miracle : on peut modifier le déroulement de l’exécution d’un programme sans en modifier le code (heureusement il faudrait le recompiler et reinstaller l’executable sur la cible). Le paramètre d’entrée est le seul point…d’entrée du programme et on peut faire une subsitution de code, si tant est que certains appels se fassent à travers des variables et qu’elles soient correctement ‘rangées’ en mémoire.

Un exemple un peu moins ideal (un stack overflow)

D’après airWalk, “Introduction to buffer overflows” - for interScape, may 1999

Soit le programme C suivant :

void someFunction(char *str) {
char buffer[16];
strcpy(buffer, str);
}
void main()
{
char bigString[256];
int i;
for( i = 0; i < 255; i++)
bigString[i] = ‘A’;
someFunction(bigString);
}

A l’exécution il provoque une erreur du type : ‘Segmentation violation’ .
Pourquoi ? Parce que c’est au sein, et plus précisément juste avant le ‘return’ de la fonction qu’a lieu un dépassement de buffer.
Les 240 ‘A’ supplémentaires vont écraser les zones mémoires suivant la fin de la variable ‘buffer’ , et en particulier l’adresse de retour de la fonction, en y subsituant une valeur (pleine de ‘A’) invalide…d’ou l’erreur sus nommée.
On perçoit que dans ce cas, si l’on si prend bien il est possible de modifier l’adresse de retour d’une fonction et donc modifier le déroulement de l’exécution d’un programme.

Reste maintenant un détail, écrire une exploit capable de menacer le système. A la différence de l’exemple précédent qui faisait des appels à des commandes système, que l’on remplaçait, celui ci n’en fait pas…c’est son coté moins idéal.

rappelons que “l’exploit” du programme est souvent conditionnée par le niveau de privilège d’exécution du programme hacké. Un uid root (ou son équivallent Windows administrateur) permet + facilement d’executer des commandes fatales.

Les “exploit”s de buffer overflow

Nous savons comment on peut derouter un programme en écrivant l’exacte quantité de mémoire et en écrasant l’adresse de retour de la routine par une nouvelle adresse. Rest e à faire exécuter un nouveau code.
Ou peut se trouver le nouveau code agressif ?
ll est impossible de modifier physiquemnt le programme compilé de la cible. Le nouveau code ne peut qu’être passé qu’AU SEIN des données modifiées responsables du buffer overflow !
La taille du code ne correspondant pas forcément exactement à l’offset entre début de buffer et adresse de retour à écraser, précédera le code par des NOPs.
Que contient le code agressif ?
L’hypothèse étant que le programme vulnérable a un niveau de privilège intéressant (root), une des exploits les + classiques sera de lancer un shell permettant d’executer des commandes ‘intéressantes’ : rm -R /*, cat /etc/passwd, mail, etc.
Le code sera d’abord écrit en C, compilé , linké (gcc) et désassemblé (avec gdb). Les codes hexadécimaux du programme seront ensuite passés directement dans le buffer.

Si certains octets du code sont à zero, certaines des fonctions vulnérables (comme strcpy()) risquent d’interpréter le 0×00 comme une fin de chaîne ;-((

Les moyens d’actions

•  Utiliser des langages ’sécurisés’ : Cyclone ou Java Vs C, strncpy() Vs strcpy()
• tester le code avec des outils spécialisés (Flawfinder, BFBtester, StackGuard)
• Patcher !

Pour plus de détails sur certains de ces aspects on pourra consulter la note du RSA : Countermeasures Againt BOF attacks ici : http://www.rsa.com/rsalabs/node.asp?id=2011

L’altération SQL
(souvent restreinte à l’injection SQL) est une technique permettant de récupérer des informations confidentielles de la base de données, ou simplement des méta données, en outrepassant les droits applicatifs ‘normaux’. Elle peut également être utilisée pour modifier ou détruire des données.
Le principe est de modifier indirectement les ordres SQL envoyés au serveur, en y incluant des chaînes de caractères spéciales en lieu et place des paramètres attendus par l’applicatif.

L’essentiel crédit de cette technique revient à ‘Rain Forest Puppy’ http://www.wiretrip.net/rfp/

Le passage de paramètres

L’altération SQL est facilement réalisable au sein d’une application web dynamique en enrichissant les données d’un champ de formulaire ou en étendant / modifiant artificiellement les paramètres de l’URL d’appel d’un programme serveur

<Form method=”GET” action=”traitement.php” >
<input type=”text” name=”nom”>
<input type=”submit” value=”Rechercher”>
</form>

remarque les paramètres sont visibles dans l’URL lorsque l’on utilise une méthode GET dans un formulaire) http://serveur/prog?par1=val1& par2=val2&par3…mais pas lorsqu’on utiliser une méthode ‘POST’

on aurait ici pour appeler directement le programme PHP sans passer par le formulaire :

http://serveur.domaine/traitement.php?nom=DELEGLISE

dans le progarmme PHP qui traite la ou les données du formulaire on aura généralement des choses du style :

<?
$sql = “SELECT * FROM users WHERE username LIKE ‘%$nom%’”
?>

L’ordre SQL est ici dynamique, il a une partie variable et est finalisé avec les données du formulaire pour pouvoir être exécuté.

Récupération directe d’informations

L’appel normal vu dans la barre d’adresse du navigateur est donc : http://serveur/traitement.php?nom=ddeleglise
Un appel falsifié serait : http://serveur/prog?nom=un_autre_user
Soit on a de la chance soit on connait un peu l’environnement et on peut essayer ‘admin’, ‘manager’, ’system,’ ‘root’…)
soit on a une erreur MAIS CELA PEUT AUSSI DONNER DES INDICATIONS !

invalid SQL statement ‘SELECT col1
FROM un_autre_user WHERE no= 246′ ,
‘un_autre_user’ table doesn’t exist.

On apprend le nom de user est un paramètre identifiant une table et qu’il y a une table par user, contenant un no qui sert de filtre. Cette chaine est simplement intercalée entre la clause FROM et la condition du SELECT.

‘SELECT col1 FROM’ . $parametre_user . ‘WHERE no = ‘. $no

Certaines des techniques suivantes nécessitent soit une connaissance spécifique de (la façon de coder) de l’applicatif, que notre ami ‘rain forest puppy’ a acquise en lisant les sources des versions de démo des logiciels concernés, soit de ‘parier’ sur une technique de codage ‘classique’ : traitement en boucle des paramètres fournis par un formulaire par exemple

Ajout de SQL supplémentaire en fin de champ

Le principe est de surcharger la valeur d’une variable saisie afin de passer des ordres SQL statiques EN PLUS de l’ordre SQL construit dynamiquement suite à la saisie.

Surcharge d’une variable numérique

Soit un champ de formulaire ‘no’ :1

à la saisie de 1 l’ordre SQL suivant est construit par le programme :

SELECT col FROM la_table WHERE no =1

mais si le controle de type de donnée saisi est permissif et que l’on saisit la valeur + un séparateur d’ordre SQL + un ordre SQL, par exemple :

no : 1; DELETE FROM EMP

l’ordre devient
SELECT col FROM la_table WHERE no =1; delete from EMP

Surcharge d’une variable chaîne (avec utilisation de commentaires)

nom : Deleglise

A la saisie de “Deleglise” (sans cote ni guillemet bien sur) , le programme serveur construit l’ordre SQL :

SELECT col FROM la_table WHERE nom = ‘Deleglise’

(l’ajout de cote par le programme est nécessaire pour que cet SQL soit syntaxiquement correct, une constante chaîne en SQL étant entre simple cote ). Il concatène le début du SELECT, une cote, la valeur, une cote.

Si l’on saisit : Deleglise’;delete from une_autre_table#

le programme serveur construit l’ordre SQL :

SELECT col FROM la_table WHERE nom = ‘Deleglise’;delete from une_autre_table ‘
ce qui est + intéressant mais syntaxiquement incorrect. Il suffira de masquer la dernière cote par un caractère adéquat.

exemple MYSQL

on utilise ‘#’ qui sert simplement à masquer les caractères qui suivent.
SELECT col FROM la_table WHERE nom = ‘Deleglise’;delete from une_autre_table# ‘

exemple Oracle

on utilise leS caractères ‘–’ qui servent à introduire un commentaire :
SELECT col FROM la_table WHERE nom = ‘Deleglise’;delete from une_autre_table–’

Variante : injection (proprement dite) de SQL

Prenons l’ordre SQL suivant, de construction classique.

‘SELECT col1 FROM’ . $parametre_user . ‘WHERE no = ‘. $no

Rien n’empêche de l’enrichir en insérant du SQL au milieu du SQL, tout en restant toujours syntaxiquement correct. Si $parametre_user prend la valeur : ‘  USERS; SELECT * FROM users’
on obtient :

‘SELECT col1 FROM’ . ‘ USERS; SELECT * FROM users ‘. ‘WHERE no = ‘. $no

ce qui enchaine 3 ordres SQL correct dont un qui recupere pas mal d’infos…

on aurait pu tenter pire, du genre ‘DELETE FROM users’ ou ‘DROP TABLE ddeleglise’ , mais il y a fort a parier que les droits auraient été insuffisants. Il est fréquent que les privilèges soient de consulter uniquement, soit de mettre à jour (UPDATE, INSERT, DELETE) les droits de CREATE, DROP et ALTER sont réservés (on espère) aux administrateurs !

Conditions toujours vraies

Soit une requête de connexion sur un serveur web du genre :
$sql = SELECT no FROM tab_users WHERE user=$nom AND password=$pwd
Lorsque l’on donnera un user et un mot de passe valide, et qui existent dans la table des users, on récuperera un no, qui donnera certains accès, dans la suite du programme. Tout cela est assez classique.

Si on arrive à ‘injecter’ une condition toujours vraie, on obtiendra un numéro, sans s’authentifier…
On sait que l’opérateur ‘OU’ est un peu laxiste : dès qu’un membre du ‘OU’ est vraie la condition complète est vraie…il suffit donc de rajouter un OU d’une expression toujours vraie.
Il en existe des tonnes en SQL : 1=1, 2>1, 1<>2,’A'=’A', NULL IS NULL, …
Alors on y va :

on saisit : ‘OR ‘A’='A pour $nom
comme $nom est encadré par des cotes par le programme, la première cote va se transformer en 2 cotes soit une chaine vide, et la deuxieme cote ajoutée fermera la cote de ‘A pour en faire une chaine !
$sql = SELECT no FROM tab_users WHERE user=$nom AND password=$pwd
devient
$sql = SELECT no FROM tab_users WHERE user=’ ‘OR ‘A’='A ‘ AND password=$pwd
– qui renverra TOUJOURS un no !

Utilisation de UNION

Cet opérateur ensembliste n’est disponible, que pour certaines versons de MySQL, mais existe pour toutes les versions d’Oracle.

On pourrait donc concaténer 2 SELECT par l’opérateur UNION. Le select d’origine

SELECT col FROM la_table WHERE nom = ‘Deleglise’

deviendrait par exemple :

SELECT col FROM la_table WHERE nom = ‘Deleglise’ UNION SELECT table_name FROM all_tables

et listerait en prime les noms des tables sur lesquelles j’ai des droits …

la seule contrainte (forte) de l’opérateur UNION est que les 2 SELECTs soient homogènes en terme de nombre de colonnes et de types, ce qui est le cas dans l’exemple (1 colonne de type caractere)

Mises à jour non prévue

L’hypothèse est ici que les champs de formulaire sont traités de manière générique, par une boucle itérative portant sur un tableau de variables de champs de formulaire. C’est la méthode la plus concise, et la plus universelle. PHP fournit par exemple des expressions qui implémentent ce genre de traitement ‘automatique’ en une seule ligne de code.

Prenons un formulaire simple à 2 champs : nom et prenom , et le code classique qui le gère :

while (champ_formulaire) {
$liste_maj = $nom_champ . ” = ‘ ” . $val_champ . “‘,” ;
}
$ordre_update = “UPDATE table_users SET ” . $liste_maj;
supprime_derniere_virgule($ordre_update);
sgbd.connexion.execute ($ordre_update);

La saisie de ‘Deleglise’ et ‘Didier’ donne :
update table_users set nom=’Deleglise’, prenom=’Didier’

La saisie de Deleglise pour nom et Didier \’ , niveau_privilege=\’admin pour prenom nous donne par contre :

update table_users set nom=’Deleglise’, prenom=’Didier’ , niveau_privilege=’admin’

;-( on met à jour 3 colonnes au lieu de 2, dont celle qui change le niveau de privilège de l’utilisateur !
4) Injection (inclusion) SQL proprement dite

Les parades à mettre en place

Précautions relevant de l’administration de base de données

• Maintenir la veille technologique (s’abonner aux alertes) et passer les patchs dès que disponibles. Cela pourra résoudre notamment les pbs de buffer overflow et autres failles système
• se baser sur la gestion des droits du SGBD plutôt que sur une gestion applicative
  on y gagnera de plus une meilleure traçabilité ce sui n’est pas négligeable en cas d’attaque
• mettre en place les privilèges minimums
  (droits de connexion , de consultation sur qq tables sont généralement suffisants)

Précautions relevant du développement

• Masquer au maximum les paramètres
• La methode GET encode (pauvrement) les paramètres et leurs valeurs dans l’URL envoyé au serveur. La méthode POST les encode au sein du corps du message de la requête HTTP et est donc moins visible.
  EN utilisant on tentera moins les utilisateurs, mais ils auront toujours la ressource de visualiser le source HTML ;-(
  Bien qu’en terme de norme de programmation ce ne soit pas conseillé, on peut utiliser des nom peu explicites pour les variables sensibles
• Mettre en place une gestion d’erreur non informative pour l’utilisateur final, en se réservant un niveau DEBUG très détaillé lors du développement
• sur quote
  En PHP il existe désormais l’option ‘magic_quote’ qui est à ON par défaut et évite l’injection simple
  Le moteur PHP substitue alors des \’ et \” , respectivement aux cotes et guillemets.
  Dans d’autres environnements, On peut sur quoter manuellement les paramètres d’entrées ‘éventuellement en utilisant une fonction personnalisées) et par la invalider le SQL supplémentaire
• tester que les numérique envoyés sont bien des numériques, et d’une manière générale l’authenticité du type attendu. Ceci est un corollaire du précédent !
• utiliser un niveau de privilège restreint, nécessaire et suffisant
• utiliser des procédures stockée

L’intérêt des ordres SQL paramétrés

Utiliser les ordres SQL paramétrés plutôt que les chaînes construites dynamiquement par concaténation. La structure de lordre SQL et le nombre de colonnes concernées (en consultation ou mise à jour) étant prédéfini on ne pourra pas ‘étendre’ le SQL.

exemple PHP / Oracle

{ …
// morceau de code PHP utilisant le SQL paramétré (bind variables)
$conn = OCILogon(”scott”,”tiger”);
$update = OCIParse($conn,”update emp set sal = :par_sql_sal );
OCIBindByName($update, “:par_sql_sal”, &$var_php_sal, 32);
OCIExecute($update);
…
}

mieux que :

{ …
// morceau de code PHP utilisant le SQL ‘concaténé’
$conn = OCILogon(”scott”,”tiger”);
$update = OCIParse($conn, “update emp set sal = “.$var_php_sal;);
OCIExecute($update);
…
}

en PHP / MYSQL

On utilisera en MYSQL, un tableau de variable d’entree
et le caractere ‘?’ comme marqueur de parametre dans l’ordre SQL.
Les principales fonctions MYSQLi pour gérer un ordre paramétré sont les suivantes :
mysqli_stmt_prepare(), mysqli_stmt_bind_param(),
mysqli_stmt_execute()

Voici un exemple extrait de la doc PHP (php.net)

<?
/* il y aura 4 parametres: 3 string et un double ! */
$stmt = mysqli_prepare($link, “INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)”);
mysqli_stmt_bind_param($stmt, ’sssd’, $code, $language, $official, $percent);

$code = ‘DEU’; $language = ‘Bavarian’;
$official = “F”; $percent = 11.2;

/* execute l’ordre prepare */
mysqi_stmt_execute($stmt);

printf(”%d Row inserees .\n”, mysqli_stmt_affected_rows($stmt));

/* libere les ressources */
mysqli_stmt_close($stmt);
mysqli_close($link);
?>

A noter :
Le SQL paramétré offre de plus de sérieux avantages en terme de performances puisqu’il évite de ré analyser (parsing) des ordres SQL qui font les mêmes opérations avec des variable d’entrée différentes (ce qui est très fréquent ! )

Pour terminer, la société FOUDSTONE fournit un certain nombre d’outils gratuits de vérification de sites et plus spécifiquement des outils de simulation d’injection SQL, voir HACME BOOK, HACME TRAVEL, ici : http://www.foundstone.com/us/resources-free-tools.asp

En Oracle 10g il existe un nouvel executable permettant de faire des export / import optimisés.

Quelques différences minimales à connaitre par rapport à l’export / import classique :

• les executables s’appellent respectivement expdp et impdp (toujours dans $ORACLE_HOME/bin)
• appelable aussi via un package standard : le package SYS.DBMS_DATAPUMP
• on peut filtrer des tables (option INCLUDE / EXCLUDE)
• on peut filtrer des lignes (option QUERY )
• le parametre OWNER est remplace par SCHEMA
• necessite un objet Oracle ‘ directory ‘ pour fonctionner
• peut être parallelisé

exemple d’import

SQL> create or replace directory dd_dir as ‘/work/pprun/oexp’;
SQL> grant read, write on directory dd_dir to public;
$> mpdp system schemas=SCOTT logfile=impSCOTT.log directory=dd_dir dumpfile=expSCOTT.dmp

Infos uttiles dans le dictionnaire :

vue DBA_DATAPUMP_JOB :  jobs actifs de DataPump et  statuts
vue DBA_DATAPUMP_SESSIONS :  les sessions d’utilisateurs ayant lancé des jobs  DataPump.

1 Introduction

2  A savoir (et à se rappeler…) avant de commencer

3  Les grandes étapes du “Projet” Sécurité

4  Les différents aspects de la sécurité

5  Les mécanismes mis en oeuvre pour la sécurité des BDs

6  Les principales attaques ciblant les SGBDs

7  Le cas de l’injection SQL

8  Autres menaces et faiblesses de comportement

9  Une application concrète : Les basiques de a sécurité d’Oracle

1) Introduction

Evolution des architectures vers plus de complexité

On trouvera ci’-après un résumé succint des principales étapes de l’évolution des architectures matérielles et
logicielles qui se sont profondément modifiées lors des deux dernières décennies.

la  plupart de ces architectures peuvent sembler désuettes, voire anachroniques,  mais il suffit de se pencher sur l’écran d’un ordinateur dans une grande surface ou un aéroport pour constater que l’émulation de terminal  (même enjolivée) est toujours très utilisée .
Quoi qu’aie pu en penser SUN il y a quelques années :” the Network  is NOT YET the computer”

On constate cependant à l’évidence une tendance à  la complexité :

• multiplication des matériels (plusieurs clients fixes ou mobiles,
  plusieurs serveurs, réseau hétérogènes)
• multiplication des couches logicielles (OS client, application client, client
  reseau, OS serveur, serveur reseau, serveur applicatif, SGBD)
• multiplication (voire ubiquité) des réseaux ( intranet, internet
  et surtout en ce qui concerne la sécurité des données
  EXTRAnet)
• généralisation du partage de données : entre particuliers,
  mais aussi employés, entreprises, clients, fournisseurs, partenaires

qui fait de la sécurité des données un enjeu majeur

La sécurité : un nouvel enjeu

Plusieurs raisons font que la sécurité devient un enjeu important  :

• complexité croissante des SI
• exigence croissante de qualité (certification ISO 9001, et plus spécifiquement  ISO 27001)
• exposition accrue des données et applications avec INTERNET + INTRANET ++ EXTRANET
• augmentation (et meilleure diffusion / communication) des attaques

Pour info, ci après les statistiques du CERT  sur les enregistrements d’incidents des dernières années :

Note : l’absence de statistiques depuis 2003 indique simplement que le CERT a arrété de compter !!!!!!!!!!!!!!!!! Notamment à cause du fait que de + en + d’automates font des attaques  massives, ce qui rend leur nombre de moins en moins significatif.

2) A savoir (et à  se rappeler…) avant de commencer

• La complexité des SI impose une approche globale,
  ’systémique’ du problème (attention de ne pas envisager QUE la sécurité des BDs)

• Un SI a le niveau de sécurité du plus faible de ses composant  (principe du “maillon faible” !)

• Elle nécessite l’implication ET SURTOUT L’ADHESION  de TOUS  (si certains employés n’adhèrent pas, ils généreront  du “maillon faible”)

• La sécurité est fonction d’objectifs et d’un enjeu  (la mise en place d’un plan de sécurité est un projet)

• Il n’existe pas de système totalement sûr( on visera à satisfaire au mieux les objectifs et répondre aux besoins)

3) Les grandes étapes du “Projet” Sécurité

elles peuvent être résumées dans le schéma suivant  :

La phase stratégique

note : la phase stratégique reboucle sur elle même : la décision stratégique , plutôt une VOLONT2 STRATEGIQUE au départ donne lieu à une étude d’opportunité / faisabilité (en fonction des risques, des enjeux, des obligations légales, etc) et donne (ou pas) la décison stratégique.

• La législation 

Pourquoi la prendre en compte :
- il existe des lois, nul n’est censé les ignorer
- les enfreindre implique une responsabilité civile ou pénale de l’entreprise
- la loi peut imposer de nouvelles activités à l’entreprise (pas seulement interne) : exemple : la dématérialisation des marchés publics et l’e administration

Quelle législation ?
- lois nationales, directives, Européennes, lois internationales
- règlements intérieurs, conventions collectives (de la métallurgie par exemple), délibération des collectivités

Les lois nationales concernent principalement :
- la loi informatique et libertés (CNIL) (respect de la vie privée, des données personnelles, obligation de déclaration)
- la propriété intellectuelle
- protection/la copie/le piratage d’oeuvres ou de logiciels
- le recyclage de déchets électroniques et informatiques

• L’analyse des risques

On sait qu’il y a des risques, encore faut il les analyser / évaluer.
Il existe différentes catégories de menaces :
- internes / externes (80% / 20 % !!)
- par intérêt économique ou par jeu / défi intellectuel

Il existe différents types d’acteurs :
- des individus (employés rancuniers ou cupides, hackers)
- des entreprises
- des états (espionnage industriel, terrorisme)

On évaluera par exemple leur probalité (est ce qu’une administration est susceptible d’être attaquées par des entreprises concurrentes ???)

• La classification

Pragmatisme !! : toutes les informations ou ressources associées ne demandent pas le même niveau de sécurité. Cela implique de les classifier / quantifier. Un effet de bord intéressant sera que l’on limitera l’énergie et l’argent dépensé !

On quantifie la SENSIBILITE des infos et ressources (en fonction de la loi, des enjuex , des missions de l’entreprise)
==> taxinomie :

- par niveau de sensiblité
- par domaine de sécurité (confidentialité, disponiblité,…)
Ceci implique des procédures de protection et de gestion des documents et des ressources.
ET aussi un niveau de diffsuion des docs (de libre à…secret défense!)

4) Les différents aspects de la sécurité

On envisage souvent la sécurité sous un angle fermé, essentiellement celui de la confidentialité. Mais bien d’autres concepts sous tendent la sécurité. Ils sont pratiquement tous applicables aux OS ET aux SGBDs, tant il est vrai que ces deux domaines sont extrèmement recouvrants.

• confidentialité
  Tout n’est pas accessible à tout le monde! Se connecter à  l’OS ou à la base de données, donne un certain nombre de droits et de ressources en fonction d’un profil défini et maintenu par un administrateur. La granularité d’accès peut  aller jusqu’à la vision unique d’un champ d’un enregistrement d’une table articulière.
• disponibilité
  Faculté de délivrer correctement un service en terme de délai et de qualité à l’utilisateur. Se mesure en pourcentage du temps de fonctionnement total.Une disponibilité de 100% est possible (temps
  de reprise nul) il suffit de s’en donner les moyens logiciels et matériels…
• fiabilité
  Des mécanismes de sauvegarde variés (physique, logique, off-line, on-line, totale, partielle, incrémentale), ainsi que des mécanismes de journalisation, et de reprise permettent de restaurer une information sans
  pratiquement aucune perte, dans tous les cas de problème matériel ou logiciel.
• intégrité
  Que les données soient réparties ou non –dans ce dernier cas les mécanismes mis en jeux seront plus complexes– elles doivent être cohérentes. Cela sous entend, d’une part que les accès concurrents d’utilisateurs, notamment lors de mises à jour, ne doivent pas compromettre la cohérence des données et d’autre part que ces dernières satisfassent aux contraintes d’intégrité  du modèle, et / ou aux règles de gestion de l’entreprise.
• tracabilité
  en cas de problème important ou d’attaque du système, on peut recourir à l’analyse de traces ou de logs. Le niveau de détail de ces traces est paramétrable, et concerne soit les aspects système, soit réseau, soit l’accès aux données élémentaires elles-mêmes.
• maintenabilité  aptitude à la réparation, évolution, maintenance du système. Mesuré en temps de reprise après panne (Mean Time To Recover)

5) Les mécanismes mis en oeuvre pour la sécurité des BDs

Les sgbds (dignes de ce nom) se doivent de fournir un certain n ombre de mécanismes internes ou de fonctionnalités assurant un niveau satisfaisant de sécurité.

• L’authentification, est le processus qui permet de vérifier
  qu’un utilisateur réclamant un accès est bien celui qu’il prétend être, ou plus simplement le processus qui contrôle l’identité de l’utilisateur. Cette action (login) se fait en général via la fourniture du couple nom d’utilisateur / mot de passe.
  Dans certains cas l’authentification peut être implicite et héritée d’une authentification précédente, ou reconnue automatiquement (@IP du user sur le réseau par exemple), bien que simplifiant les accès ce choix peut évidemment s’avérer dangereux.

La multiplication des couches logicielles sus évoquée, et l’inflation d’applications sur les postes utilisateur fait que ce dernier est fréquemment amené à s’authentifier des dizaines de fois par jour. La signature unique (Single Sign On ou SSO) est un objectif très louable mais rarement atteint !

• Les droits et privilèges : une fois correctement identifié l’utilisateur doit pouvoir accéder aux informations
  et ressources auxquelles il a droit (et uniquement à celle là! ) Ce problème est résolu le + simplement avec la gestion de droits élémentaires accordé à un individu, ou plus efficacement avec des rôles et / ou profils affectés à des groupes d’invidus…ou à des rôles ou profils.
• Les LOGs ou traces : permet d’enregistrer tout ou partie des informations concernant les accès (réussis ou échoués). Cette trace pourra être plus ou moins verbeuse et son volume étroitement surveillée. De ce fait on l’utilisera de manière cibllée sur des périodes de temps spécifiques
• tolérance aux pannes : permet par du matériel ou du logiciel redondant (CPUs, disques, IOs) de supporter de manière partiellemnt ou complètement transparentes différents types de pannes, tant au niveau du client, que du réseau, que du serveur. Une tolérancec totale a bien sur un cout certain.
• sauvegarde et restauration
  sauvegarder les données sur des supports externes (disques,  bandes, etc.) et pouvoir les restituer, les plus à jour possible.  Le but est de ne pas perdre de données suite à un pb matériel (panne disque) , logiciel (bug) ou une fausse manipulation d’un utilisateur.
• mécanismes transactionnels
  l’atomicité des transactions, par définition assure la cohérence des données, même dans des environnements distribués. L’image avant modification, stockée de manière redondante dans ou hors de la BD, permet de faire d’éventuels retours arrière pour retrouver le dernier état cohérent, ou de s’assurer qu’il n’y aps pas eu d’opérations partielles ou incomplète (transaction bancaires par exemple)

(1) la vue est pratiquement le seul contrôle d’accès offrant un niveau de granularité ligne ou colonne ! et qui plus est de manière contextuelle, en les paramétrant (tranches horaires, @ IP, etc.)

6) Les principales attaques ciblant les SGBDs

Crack de password

programmes spécialisé aléatoires ou basés sur  dictionnaire, ou crack manuel

mesures à prendre :

• ‘politique’ de mots de passes (durée de vie , historique, complecité

•   imposée, etc.)
• chiffrement ou hash non réversible
• changement ou suppression des users connus : super user, administrator,
  system, guest, etc  (==> AUdit régulier des bases)
• SSO (évite les passwords utilisateurs faibles)

exemple de crack de password Oracle

La table DBA_TABLES est est une table du référentiel Oracle, qui recèle, outre le nom et différentes info sur les utilisateurs de la base, leurs mos de passe crypté. On peut l’utiliser pour cracker un mot de passe avec une méthode brute.

Les avantages de travailler sur ce mot de passe crypté sont multiples  :

• Efficacité : on sait en gros comment Oracle chiffre ses mots de passe : algorithme DES 64 bits, password crypté de 30 caractères maximum, utilisation du user + mot de passe pour le hashage, etc. On ne part
donc pas de zéro
• travail OFFLINE : Contournement d’une éventuelle politique  de mot de passe (password policy) : ce n’est pas le cas par défaut, mais le DBA peut limiter le nombre de tentatives de connexions infructueuses
et faire échouer une méthode brute. Voir l’utilisation des ‘PROFILES’ Oracle pour plus d’information. En travaillant sur une valeur cryptée, sans tentative effective de connexion

• Discrétion : Cf. le point précédent, on peut travailler tranquillement OFFLINE, sur un poste extérieur sans limite de temps ou de nombre d‘essais. Un programme de forçage de mot de passe peut être trouvé
sur le site ‘toolcrypt.org’ à l’adresse suivante :

http://www.toolcrypt.org/index.html?orabf

Sur un poste de Windows moyen, il m’a fallu une dizaine de minutes avec ce programme pour casser un mot de passe ‘SYSTEM’ de 6 caractères.
En mode commande il suffit d’entrer la commande suivante :

C:\> orabf 70F277D6E92A1D9B:SYSTEM -n 6
– 70F277D6E92A1D9B est la valeur chiffrée lue dans le dictionnaire
– SYSTEM est le nom de l’utilisateur correspondant
– 6 la longueur minimale du mot de passe cherché

Mais attention : le décryptage d’un mot de passe alphanumérique  > 8 caractères peut durer de quelques jours à plusieurs semaines  !

Un comparatif intéressant des programmes de forçage des mots de passe Oracle, gratuits ou payants a été fait par la société  ‘Red Database Security’ et peut être trouvé ici :
http://www.red-database-security.com/whitepaper/oracle_password_cracker.html

Contournement de l’applicatif par programme client SQL

Au lieu de se connecter via le programme apllicatif, on peut utiliser le mode commande ligne ou un interpréteur SQL standard (SQL+ d’Oracle) ou un outil d’admin (PHPMyAdmin, OEM). On utilise alors directement les droits
du compte propriétaire des données (en général tous les droits).

mesures à prendre :

pas de gestion de droits applicative !
connexion au compte propriétaire interdite (compte locké par exemple)
une gestion des droits fine (connexion + consultation et / ou mise à jour de tables (voire de vues) ciblées

Récupération de données OFFLINE ou Hors production

Il existe des sources de données partiellement ou parfois totalement redondantes de la base de données de production. Ces données peuvent être dans le même format que les données d’origine (tables d’une BD Oracle) ou dans des formats différents (texte, CSV, SQL, …).

Ces données redondantes sont en général moins (ou pas du tout) sécurisées, par rapport à la base de production,
et seront donc une cible plus facile pour les pirates fatigués.

Pour corser le tout, une mauvaise habitude assez répandue dans les entreprises consiste à recopier intégralement des données de production, dans les bases de test ou de développement, pouyr éviter d’extraire des jeux d’essai de données complexes, ou pour ne pas repartir de données vides lors d’un eopération de maintenance logicielle…

Comme données Offline, on citera par exemple :

• BDs de développement, BDs de test,
• export de données au format propriétaire (export Oracle par exemple)
• reverse engineering SQL
• export au format texte fixe ou CSV
• sauvegardes binaires des fichiers de la BD, sur bande, disque ou DVD
• fichier de trace, de LOG ou d’audit
• base répliquées
• données répliquées en synchrone ou asynchrone

Back doors

(”Portes dérobées”) : Programmes usurpateurs qui détournent des fonctionnalités systèmes dans le but d’ouvrir
des accès utiles aux pirates pour contrôler à distance les machines ciblées (modification des programmes de login avec user/passwd en dur, ouverture de ports particuliers, etc.) . Ces programmes sont la plupart
du temps installés par le biais d’un “cheval de Troie”. Parmi les plus (tristement) célèbres, on peut citer BackOrifice (BO) ou encore NetBus.

Les accès illicites via ces backdoors pouvant être facilement détectés par des commandes système standards (liste des process connectés, des ports ouverts) ils sont parfois utilisés conjointement avec des rootkits, ensemble de commandes standards modifiées pour masquer les intrusions.

certaines back doors peuvent être inclsues dans le code d’applications standards,
sans intention forcément malveillante mais pour réserver au développeur du programme, un accès ‘privé’ à toute
les machines hébergeant son code. L’accès au source d’un logiciel libre peut nous prémunir contre ce genre d’indélicatesse.

Refus de service (Deny of Service)

voir le papier de sogoodtobe sur http://www.securiteinfo.com

Buffer Overflow :

Voir le document joint

Recherche d’infos de configuration

( d’identification, d’authentification , méta données )

• au sein de l’applicatif (en clair dans le source interprété… ou désassemblé)
• dans l’environnement (fichiers de configuration accessibles sur le serveur ou pire sur le client : *.ini)
• dans la bases de données elle même
• sur le réseau (écoute / sniff des lignes)

mesures à prendre :

• chiffrer (solidement) les infos sensibles dans la BD, dans les fichiers de config,
• restreindre les accès aux répertoires et fichiers
• restreindre l’accès aux méta données
• s’appuyer sur des mécanismes existant identification / authentification par le SGBD, par l’OS
• réseau : utiliser des protocoles sécurisés : SSL (nécessite des certificats), IPSec, paramétrer finement le firewall, utiliser ports et user ‘originaux’
•  ’politique’ de mots de passe

Les menaces les plus connues du grand public, visent à paralyser, ou détruire tout ou partie du système d’information. Elles ne ciblent pas vraiment les SGBDs mais nous les citerons néanmoins parce qu’incontournables.
Jetons un oeil aux définitions données par le grand glossaire de la sécurité de ECHU.ORG

7) Le cas de l’injection SQL

voir le document joint

8 Autres menaces et faiblesses de comportement

• Virus : Au sens large du terme, on qualifie généralement de virus tout programme capable de se reproduire (techniquement, se recopier) lui-même et d’endommager des données informatiques. On les classe
  en plusieurs catégories, principalement: parasite, compagnon, amorce, multiformes, résident mémoire ou non, furtifs, polymorphes, réseau et flibustier.
• Ver (ou Worm) : programme qui peut s’auto-reproduire et se déplacer à travers un réseau en utilisant les mécanismes réseau, sans avoir réellement besoin d’un support physique ou logique (disque dur, programme hôte, fichier …) pour se propager; un ver est donc un virus réseau.
• Cheval de Troie : (en anglais trojan horse) un programme informatique effectuant des opérations malicieuses à l’insu de l’utilisateur : vol des mots de passe, copie de données sensibles, exécution d’action nuisible …Une intro très accessible de ces notions est dispo sur :
  http://www.commentcamarche.net/ et d’autres infos encore sur : http://assiste.com

Quelques faiblesses de comportement

• ‘portes’ ouvertes : (pas seulement les ‘back doors’ !)porte de la salle machine ouverte, poste ou serveur sans mot de passe ou mot de passe faible, poste sans veille, post it (!)
  fermez les portes !! mettez en oeuvre la gestion de mots de passe !!
• Installation par défaut :- les valeurs de paramètres sont connues (port 80 / 1525, les administrateurs sont connus (SA SQLServer, SYS et SYSTEM Oracle, ROOT MySQL)
  - des services superflus sont accessibles (srv ftp, srv samba, snmp, serveur d’admin, etc )- les communications ne sont pas chiffrées (ftp, telnet, pop)
  lisez la doc !! auditez vos serveurs !!
• mauvaise politique de gestion des droits (top, au lieu de bottom-up) :- installation confortable : tous les logiciels sont installés sous root, tous les utilisateurs de l’applicatif sont DBAs
  - allow all implicite…deny N / deny all.. allow n
  - utilisation abusive de l’héritage
  - mots de passe faibles
  maitrisez la gestions des droits de vos OS / logiciels serveurs / bases de données
• absence de mise à jour
  faites de la veille technologique, patchez régulièrement, surveillez les alertes
• mauvais codage (parametres en clair dans les URLs, connexion non chiffrées, code ‘injectable’, etc.) ;-) documentez vous (best practices, faites tester vos programmes)
• controle d’accès au niveau applicatif, qui peut facilement être court circuité
  (re) centralisez (ET FACTORISEZ!) les controles au niveau données : contraintes d’intégrité

Comme chacun sait (depuis certaines émissions de télé) c’est le maillon faible de la chaîne qui cassera imanquablement. On peut mettre en place le plus beau (et le + cher) des firewalls, il sera bien
inutile si un adminitrateur est resté ‘loggé’ root sur son poste (Statistiquement la majorité des attaques provient de l’intérieur des entreprises !)

Cependant, on n’oubliera pas de rester pragmatique : tous les PMEs ne sont pas le pentagone et n’intéressent pas tous les hackers de la planète.
Les besoins et les objectifs doivent être clairement définis au départ et l’adéquation de la solution vérifiée.
Un certains nombres d’utilitaires libres sont disponibles sur internet pour vérifier la fiabilité de votre système d’information. Voir parmi la liste des liens utiles.

Il faudra de + trouver un équilibre entre niveau de sécurité satisfaisant et confort (voire simple possibilité) de travai ldes autres acteurs.

Quelques liens utiles sur la sécurité en général

Annexe : Systèmes haute sécurité (Trusted Systems)

voir le document (sécurisé) joint sur lessystèmes haute sécurité

Les principaux sont expliqués ici :

• la notion d’utilisateur, d’authentification et de profil
• privilèges DBA et exploitant au niveau OS
• les roles
• les droits sur les objets
• les droits systeme
• la sécurité niveau ligne

Privilèges système
Les privilèges systèmes d’accès aux objets s’intéressent plutôt au contenant qu’au contenu. Ils concernent principalement des ordres de création, de modification de structure et de suppression d’objets (SQL DDL plutot que DML)

Ce sont donc des privilèges d’assez haut niveau, que l’on réservera par exemple aux développeurs mais qui seront utilisés avec beaucoup de parcimonie en phase de production…

Liste de quelques  privilèges  système d’Oracle

Ca veut bien dire ce que ca veut dire !
Une fois les policies créées,  les packages et fonctions testées, l’utilisation implicite des polices (par un SELECT sur la table sécurisée par exemple) peut renvoyer cette erreur.

Une xeception est arrivée lors de l’execution qui n’est pas ‘attrapée’ par le programme et remonte donc au noyau. Reste à savoir quelle exception et pourquoi…

Pour avoir plus d’infos, il faut voir le fichier trace *ora*.trc généré dans USER_DUMP_DEST!!!!!!
Voici un exemple de contenu de trace intéressant (chercher éventuellement la chaine ORA-28112 si le fichier est trop gros)

srv-test$ > more pprun_ora_237820.trc
Dump file /work/oracle/log/prod_ora_237820.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /oracle/10GDB
System name: AIX
Node name: sr-pprod-1
Release: 3
Version: 5
Machine: 005F0ADA4C00
Instance name: prod
Redo thread mounted by this instance: 1
Oracle process number: 23
———————————————————-
Policy function execution error:
Logon user : SYSTEM
Table/View : SC1.CONTROLE
Policy name : SC1_APP_RESTRICT
Policy function: SYSTEM.PK_SET_VPD.FN_RESTRICT
ORA-04063: package body “SYSTEM.PK_SET_VPD” has errors
ORA-06508: PL/SQL: could not find program unit being called: “SYSTEM.PK_SET_VPD”
ORA-06512: at line 1
*** 2008-09-11 11:39:48.898
———————————————————-
Policy function execution error:
Logon user : SYSTEM
Table/View : SC1.CONTROLE_RMI
Policy name : SC1_APP_RESTRICT
Policy function: SYSTEM.PK_SET_VPD.FN_RESTRICT
ORA-06508: PL/SQL: could not find program unit being called: “SYSTEM.PK_SET_VPD”
ORA-06512: at line 1

On voit qu’il ne trouve pas le package de la policy dans SYSTEM…parce qu’elle a été créé dans un autre schéma (SC1 sur cet exemple)

La 10 et la 11g offrent un nouveau package pour gérer les jobs : DBMS_SCHEDULER en remplacement de DBMS_JOB (voir en fin d’article une migration, ou un résumé des changement de l’un à l’autre extrait de la doc officielle.
Plusieurs avantages :
- une gestion des droits plus fine,
- des jobs nommés (finis les job_id peu explicites),
- une gestion des intervalles de temps simplifiée (finis les calculs savants avec SYSDATE mais une frequence horaire, hebdomadaire, mensuelle,…)

Prerequis

Qu’il existe au moins une JOB QUEUE et le process associé

SQL> SHOW PARAMETER JOB

ou

SQL> select name
from v$bgprocess
where name like ‘CJQ%’

Le user créateur du JOB doit aussi avoir le privilège system ‘CREATE JOB’ …ou etre DBA

Infos minimales pour créer / planifier un JOB

Job Name: le nom du job (unique / aux noms d’objets)
Job Type: 3 types possibles : PLSQL_BLOCK, STORED_PROCEDURE, EXECUTABLE. .
Job Action: un bloc, un nom de proc, ou un chemin complet d’executable
Start Date: date de debut (la 1ere fois ou le job s’execute , format : TIMESTAMP WITH TIME ZONE
Enabled: TRUE ou FALSE. Attention !!! par default le job est créé DISABLED
Repeat Interval: intervalle de répétition. On peut utiliser l’aritmetique de date ‘old style’ comme avec DBMS_JOB, ou préférablement la notion de fréquence :
exemple :FREQ=WEEKLY;BYDAY=MON,THU;BYHOUR=18;BYMINUTE=0
les valeurs possibles sont BYMONTH, BYMONTHDAY, BYYEARDAY, BYDAY, BYHOUR, BYMINUTE, BYWEEKNO

On peut facilement tester l’execution en lancant le Job sans attendre…

exec DBMS_SCHEDULER.run_job (job_name => ‘job_dd’);

Création d’un Job

On utilise le package DBMS_SCHEDULER et la procédure CREATE_JOB

BEGIN
DBMS_SCHEDULER.create_job (
job_name => ‘job_dd’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘BEGIN NULL; /* a fe ren */ END;’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘FREQ=DAILY’,
enabled => TRUE,
comments => ‘c est pas obligatoire mais un bon commentaire…’);
END;
/

– SUPPRESSION

exec DBMS_SCHEDULER.drop_job (job_name => ‘job_dd’ );

remarque : si l’on ne veut pas supprimer un JOB définitivement mais simplement empechere son activation pendant un certain temps, on peut mettre son statut à DISABLED :

dbms_scheduler.disable

Des infos dans le dictionnaire ?

A minima

SQL> select job_name, enabled
from user_scheduler_jobs;

ou avec un peu plus de détails sur la planification

SQL> select JOB_NAME , JOB_CREATOR , JOB_TYPE , JOB_ACTION , STATE ,
START_DATE , REPEAT_INTERVAL , LAST_START_DATE , NEXT_RUN_DATE
FROM USER_SCHEDULER_JOBS

JOB_NAME JOB_CREATOR JOB_TYPE JOB_ACTION STATE START_DATE REPEAT_INTERVAL NEXT_RUN_DATE
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
JOB_DD SYSTEM PLSQL_BLOCK BEGIN NULL;END; SCHEDULED 08-SEP-08 11.00.03 FREQ=DAILY 09-SEP-08 11.00.03

mais aussi les vues
DBA_SCHEDULER_SCHEDULES - informations sur les jobs planifiés courants
DBA_SCHEDULER_PROGRAMS , DBA_SCHEDULER_PROGRAM_ARGS - informations sur les programmes

De DBMS_JOB à DBMS_SCHEDULER

créer un Job

VARIABLE jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(:jobno, ‘INSERT INTO employees VALUES (7935, ”SALLY”,
”DOGAN”, ‘’sally.dogan@xyzcorp.com”, NULL, SYSDATE, ”AD_PRES”, NULL,
NULL, NULL, NULL);’, SYSDATE, ‘SYSDATE+1′);
COMMIT;
END;
/

equivalent statement using DBMS_SCHEDULER is the following:

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => ‘job1′,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘INSERT INTO employees VALUES (7935, ”SALLY”,
”DOGAN”, ‘’sally.dogan@xyzcorp.com”, NULL, SYSDATE,”AD_PRES”, NULL,
NULL, NULL, NULL);’);
start_date => SYSDATE,
repeat_interval => ‘FREQ = DAILY; INTERVAL = 1′);
END;
Modifier un Job

BEGIN
DBMS_JOB.WHAT(31, ‘INSERT INTO employees VALUES (7935, ”TOM”, ”DOGAN”,
”tom.dogan@xyzcorp.com”, NULL, SYSDATE,”AD_PRES”, NULL,
NULL, NULL, NULL);’);
COMMIT;
END;
/

equivalent à

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => ‘JOB1′,
attribute => ‘job_action’,
value => ‘INSERT INTO employees VALUES (7935, ”TOM”, ”DOGAN”,
”tom.dogan@xyzcorp.com”, NULL, SYSDATE, ”AD_PRES”, NULL,
NULL, NULL, NULL);’);
END;
/

supprimer un Job de la Job Queue

BEGIN
DBMS_JOB.REMOVE(14144);
COMMIT;
END;
/

équivallent à

BEGIN
DBMS_SCHEDULER.DROP_JOB(’myjob1′);
END;
/

Un exemple de script pour gérer et utiliser des tables externes Oracle (external tables)

On va charger emp a partir d’un fichier externe CSV, en utilisant SQL et les tables externes Oracle ( Oracle 9i, Oracle 10g et + si affinités )

D’abord, creer un repertoire oracle de travail (Oracle directory) associé au repertoire ou se trouve le fichier d’entree.
Pour ce faire, SCOTT doit avoir le privilège Systeme ‘CREATE ANY DIRECTORY’
…et le CREATE TABLE bien sur !

sous SQL>

CONNECT SYSTEM/xx@db
GRANT CREATE ANY DIRECTORY TO scott;
CONNECT scott/tiger@db
create or replace directory dossier_temp as ‘/tmp’;
drop table table_externe_emp;

rem : si le user qui lit/ecrit dans la directory n’est pas celui qui l’a créé
(ici c’est SCOTT qui fait tout), ce uyser aura aussi besoin des droitsd R/W sur la directory :

SQL> grant read,write on directory dossier_temp to scott;

Créer une ‘table’ externe attachee au fichier d’entree

create table table_externe_emp
(EMPNO NUMBER(4) ,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
)
ORGANIZATION EXTERNAL
( type oracle_loader
default directory dossier_temp
access parameters
( records delimited by newline
badfile ‘charge_emp.bad’
logfile ‘charge_emp.log’
fields terminated by ‘,’
(empno, ename, job, mgr, hiredate date, sal, comm, deptno)
)
location (’emp.csv’)
)
– attention c’est important sinon ca marche moins bien !
reject limit unlimited
/

Lire la table externe

select * from table_externe_emp;

!! Attention en cas de problème regardez les fichiers LOG et BAD sur le serveur

– lecture table externe et INSERTION dans une autre cible
– on insère dans la table destination si elle existe

insert into mon_emp
select * from table_externe_emp;
commit;

– lecture table externe et CREATION/INSERTION dans une autre cible
– on peut aussi pour aller + vite recopier la table externe ET son contenu !

create table mon_emp
as select * from table_externe_emp;

De l’utilité des tablespaces

Un tablespace ou espace disque logique, est une partition logique de la base contenant un ou plusieurs fichiers.
Un fichier appartient à 1 et 1 seul tablespace.
Par défaut un tablespace à la création est ON LINE et donc accessible, il peut être mis OFFLINE (et les fichiers qu’il contient par conséquent) pour en interdire l’accès ou pour certaines opérations de maintenance

Il existe toujours deux tablespace baptisés SYSTEM et SYSAUX .
- SYSTEM : contient le dictionnaire de données et segment d’annulation SYSTEM
- SYSAUX : contient les informations nécessaires aux composants et outils supplémentaires
et traditionnellement on créera également
- ‘TEMP’ : pour les données ’swappées’ sur disque lors d’opération de tri ou de fusion trop volumineuses en mémoire
- ‘UNDO’ : pour les segment d’annulation, qui stockent les images avant, lors des ROLLBACKS

Outre ces tablespaces ’système’ qui servent en quelque sorte à la cuisine interne d’Oracle, il faudra bien tout de même stocker quelques données (et indexs)
Ici plusieurs stratégies sont possibles :
- séparation des indexs et des datas,
- séparation des différents domaines fonctionnels

note : Il serait possible également de stocker les datas, les index dans ces SYSTEM ou SYSAUX.
Ceci est vivement déconseillé, car on aurait ainsi une base minimale peu structurée.

Les tablespaces sont donc utiles pour répartir les données, les index, mais aussi les segments d’annulations et les espaces temporaires sur plusieurs espaces logiques et disques.
Ils permettent :
- performance (répartitions des accès disques),
- souplesse (séparation fonctionnelle ou métier, meilleure granularité des sauvegardes),
- sécurité (séparation des infos systèmes des données utilisateurs)

SQL de base pour la gestion des TBS

SQL> CREATE TABLESPACE …
SQL> DROP TABLESPACE…
SQL> ALTER TABLESPACE…

exemples

SQL> CREATE TABLESPACE COMPTA DATAFILE
‘E:orantdatabaseTESTcompta1TEST.ora’ SIZE 100M;
SQL> ALTER TABLESPACE COMPTA OFFLINE;
SQL> ALTER TABLESPACE COMPTA ADD DATAFILE
‘E:orantdatabaseTESTcompta2TEST.ora’ SIZE 100M;
SQL> DROP TABLESPACE COMPTA INCLUDING CONTENTS AND DATAFILE;
SQL> ALTER TABLESPACE CHARGEMENT_BATCH NOLOGGING;
SQL> ALTER TABLESPACE INFOCENTRE READ ONLY;

Description des tablespaces et fichiers de la base courante dans les vues
DBA_TABLESPACES , DBA_DATA_FILES, DBA_FREE_SPACE du dictionnaire.

SQL> SELECT TABLESPACE_NAME “Nom TBS”, CONTENTS “Type de contenu”, STATUS “EN ligne?”, LOGGING “Journalise?”, BIGFILE FROM DBA_TABLESPACES;

Nom TBS Type de
contenu EN ligne? Journalise?
——– —— — ———–
SYSTEM PERMANENT ONLINE LOGGING NO
UNDOTBS1 UNDO ONLINE LOGGING NO
SYSAUX PERMANENT ONLINE LOGGING NO
TEMP TEMPORARY ONLINE NOLOGGING NO
USERS PERMANENT ONLINE LOGGING NO
EXAMPLE PERMANENT ONLINE LOGGING NO
6 rows selected.

Tablespaces et fichiers

Un tablespace contient AU MOINS un fichier. Celui-ci est créé lors de la création du tablespace, de manière automatique par
Oracle, en fonction des paramètres donnés par la commande CREATE ou ALTER tablespace (emplacement du fichier, nom, taille, et mode d’extension).

note : Lors de la suppression du tablespace (DROP TABLESPACE…) les fichiers correspondant ne sont PAS SUPPRIMES par Oracle par défaut. Utilisez la clause ‘AND DATAFILE’…

exemples

SQL>
Nom_Tbs   Nom_Fic.                                       MO  AUTOEXTENSILE
USERS     C:ORACLEPRODUCT10.1.0ORADATAORCLUSERS01.DBF    5   YES
SYSAUX    C:ORACLEPRODUCT10.1.0ORADATAORCLSYSAUX01.DBF   230 YES
UNDOTBS1  C:ORACLEPRODUCT10.1.0ORADATAORCLUNDOTBS01.DBF  30  YES
SYSTEM    C:ORACLEPRODUCT10.1.0ORADATAORCLSYSTEM01.DBF   440 YES
EXAMPLE   C:ORACLEPRODUCT10.1.0ORADATAORCLEXAMPLE01.DBF  150 NO
EXAMPLE   C:ORACLEPRODUCT10.1.0ORADATAORCLEXAMPLE012.DBF 10  NO

Extension et gestion d’espace des tablespaces et des fichiers

La taille d’un tablespace est la taille de son (ses) fichier(s) d’origine.
Pour augmenter la taille d’un tablespace, il y a 2 solutions :
* Ajouter un fichier au tablespace, qui sera chainé au premier (ALTER TABLESPACE toto ADD DATAFILE…)
* mettre le fichier du tablespace en AUTO extension (ALTER DATABASE DATAFILE toto.dbf AUTOEXTEND ON)
Une table (et tout segment en général) , peut “s’étaler” sur plusieurs fichiers. Ainsi le fait qu’une table sature un tablespace n’est pas bloquant il suffit d’augmenter la taille du tablespace.

autoextension des fichiers

ATTENTION : la clause AUTOEXTEND spécifie la taille d’extension du fichier d’un tablespace. La clause STORAGE INITIAL, NEXT, MINEXTENTS … spécifie la taille d’extension d’UN SEGMENT du tablespace par exemple une table. Ces 2 paramètres sont totalement indépendants. La preuve en est qu’une table (un segment de données) est forcément en allocation dynamique alors qu’un fichier peut avoir une taille fixe (AUTOEXTEND OFF)

note : Le changement de mode AUTOEXTEND se fait avec la commande ‘ALTER DATABASE’ pour les ‘SMALLFILE’ et ‘ALTER TABLESPACE’ pour les ‘BIGFILE’

exemples

SQL> - passage en AUTO extension d’un fichier de tablespace existant
SQL> ALTER DATABASE DATAFILE ‘E:orantdatabaseTESTUsr1TEST.ora’ AUTOEXTEND ON;
SQL> ALTER DATABASE DATAFILE ‘C:ORACLEPRODUCT10.1.0ORADATAORCLEXAMPLE01.DBF’
AUTOEXTEND OFF
SQL> - ajout d’un ficheir auto extensible jusqu’a 100 MO
SQL> ALTER TABLESPACE toto ADD DATAFILE ‘E:orantdatabaseTESTTEST.ora’ SIZE 10M AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

extension des segments

* clause LOCAL : Tablespaces gérés localement (Locally managed tablespaces)
Anciennement les tablespaces étaient gérés au niveau du dictionnaire de données, la gestion de l’espace physique (allocation / libération de blocs) se fait désormais dans l’entête du fichier(s) du tablespace. Une table binaire d’allocation (bitmap) y
est maintenue. C’est le fonctionnement par défaut (sauf pour le tablespace SYSTEM)
Avantages :
* pas de contention en mise à jour au niveau du dictionnaire
* et conséquemment pas d’utilisation de Rollback segment pour ces transactions
* pas de soucis de gestion de l’espace (calcul d’un storage adéquat)
* “coalesce” automatique (fusion des espaces libres contigus pour optimiser l’espace libre)
Evidemment la clause “DEFAULT STORAGE” est invalide pour les tablespaces gérés localement.

* Clause AUTOALLOCATE
C’est Oracle qui gère !

* Clause UNIFORM
Les extents ont tous la meme taille, par défaut 1MO, sinon elle est précisée par le paramètre ‘SIZE’

* clause STORAGE
Les règles et les statistiques d’allocations sont gérées au niveau du dictionnaire.
Pour plus d’informations voir le chapitre sur les ’segments et extents’
changement des paramètres d’un tablespace existant
ALTER TABLESPACE SYSTEM
DEFAULT STORAGE ( INITIAL 100K NEXT 100K MINEXTENTS 1 MAXEXTENTS 300 PCTINCREASE 1);

Quelques exemples de syntaxe

SQL> CREATE TABLESPACE COMPTA DATAFILE ‘E:orantdatabaseTESTcompta1TEST.ora’
SIZE 100M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
SQL> CREATE TABLESPACE COMPTA DATAFILE ‘E:orantdatabaseTESTcompta1TEST.ora’ SIZE 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 500K;
SQL> CREATE TABLESPACE COMPTA DATAFILE ‘E:orantdatabaseTESTcompta1TEST.ora’ SIZE 100M EXTENT MANAGEMENT DICTIONARY;