PL/SQL et Sécurité de la base de données

1. PL/SQLetSécurité de la base de données Préparé par: Meryem MOUSSAMI WafaaHARKATI Salma GHAOUTA Encadré par: M.HANOUNE

2. 1 le langage PL/SQL Administration de base de données

3. Plan Introduction Langage PL/SQL Les objets PL/SQL Options de configuration PL/SQL conclusion

4. Définition : ProceduralLanguage / StructuredQueryLanguage Le langage PL/SQL est un langage de programmation de quatrième génération, propriétaire Oracle, qui fournit des extensions procédurales du langage SQL. Il offre un environnement de programmation commun pour les bases de données et applications Oracle, quel que soit le système d'exploitation ou la plate-forme matérielle.Il permet de : • définir un ensemble de commandes contenues dans ce que l'on appelle un "bloc" PL/SQL. Un bloc PL/SQL peut lui-même contenir des sous-blocs. Le PL/SQL peut être utilisé sous 3 formes : • un bloc de code, exécuté comme une commande SQL. • un fichier de commande PL/SQL • un programme stocké

5. Comment créer un bloc PL/SQL Syntaxe : DECLARE Variables, curseurs, exceptions définies BEGIN Instructions SQL & PL/SQL EXCEPTION Action à réaliser quand une exception est levée END; / Structure d’un bloc PL Partie DECLARE et EXCEPTION sont optionnel

6. Traitement d’un bloc PL/SQL

7. Administrer les objets PL/SQL Un DBA doit pouvoir: Identifier les Objets PL/SQL Recommander l’utilisation appropriée du langage PL/SQL Charger les objets PL/SQL dans la base de données Assister les développeurs PL/SQL dans la résolution des problèmes

8. Objets PL/SQL Il existe de nombreux types d’objet de base de données PL/SQL: Fonction Procédure Package Corps de Package Déclencheur

9. Les fonctions

10. Définition • Une fonction est un bloc PL/SQL nommé qui renvoie obligatoirementune valeur. • Une fonction peut être stockée dans la base de données, comme objet de base de données, en vue d'exécutions répétées. • Une fonction peut être appelée en tant que partie d'une expression

11. Comment créer une fonction Syntaxe : CREATE [OR REPLACE] FUNCTION function_name (parameter1 [mode1] datatype1, parameter2 [mode2] datatype2,. . .) RETURN datatype IS PL/SQL Bloc; Structure d’une fonction Le bloc PL/SQL doit y avoir au moins une instruction return

12. Exemple : CREATE OR REPLACE FUNCTIONget_sal(v_id IN emp.empno%TYPE) RETURN NUMBER IS v_salary emp.sal%TYPE :=0; BEGIN SELECT sal INTO v_salary FROM emp WHERE empno = v_id; RETURN (v_salary); ENDget_sal; /

13. A noter • Une fonction peut comporter un ou plusieurs paramètres IN, mais ne doit retourner qu'une seule valeur. • Le code d'une fonction PL/SQL doit contenir une instruction RETURN.

14. Définition Procédure • Une procédure est un bloc PL/SQL nommé qui effectue une action spécifique. • Elle accepte des paramètres en entrée (listes des arguments).

15. Comment est ce qu’on peut créer une procédure ? La Syntaxe : Syntaxe pour la création d’une procédure CREATE [OR REPLACE] PROCEDUREprocedure_name ( parameter1 [mode1] datatype1, parameter2 [mode2] datatype2, … ) IS | AS Block PL/SQL; Une procédure est appelée à l’aide de la commande CALL .

16. Exemple CREATEORREPLACEPROCEDURE Augmentation (v_NumempIN numéro de l'employé EMP.empno%Type, v_PourcentINNUMBER) pourcentage d’augmentation Is BEGIN-- augmentation de l'employé Update EMP Setsal = sal+ sal * v_PourcentWhereempno = v_Numemp ; END; /

17. A noter • D'une façon générale, les procédures ne devraient pas exécuter des instructions de fin de transaction (COMMIT, ROLLBACK, Ordre DDL) . • La décision d'enregistrer ou annuler la transaction en cours relève du programme appelant.

18. Définition Package • Un paquetage/package est un ensemble de procédures et fonctions regroupées dans un objet nommé. Par exemple : • Le paquetage Oracle DBMS_LOB. • Le paquetage UTL_FILE.

19. Définition • Un paquetage est organisé en deux parties distinctes : • Une partie spécificationqui permet de spécifier à la fois les fonctions et procédures publiques ainsi que les déclarations des types, variables, constantes, exceptions et curseurs utilisés dans le paquetage et visibles par le programme appelant. • Une partie corpsqui contient les blocs et les spécifications de tous les objets publics listés dans la partie spécification.Cette partie peut inclure des objets qui ne sont pas listés dans la partie spécification, et sont donc privés.Cette partie peut également contenir du code qui sera exécuté à chaque invocation du paquetage par l'utilisateur.

20. Package

21. Comment est ce qu’on peut créer un package ? La Syntaxe pour la spécification: CREATE [OR REPLACE] PACKAGE nom_package As Définition des fonctions , procédure … End nom_package ; / Exemple : Syntaxe pour la création d’un package CREATE PACKAGE clients ASPROCEDURE insere_client (no INTEGER, nom VARCHAR2, ...);PROCEDURE supprime_client (no INTEGER);...END;

22. Package

23. Comment est ce qu’on peut créer un package ? La Syntaxe pour le corps: CREATE [OR REPLACE] PACKAGE BODY nom_package As Définition des fonctions BEGIN … END; Définition procédure BEGIN … END; ….. End nom_package ; / Syntaxe pour la création d’un package(suite)

24. Corps du package

25. Les avantages de pl/sql package Une meilleur performance Information en cachette Une conception facile de l’application La modularité

26. Packages intégrés Les packages PL/SQL intégrés fournis avec Oracle Database 10g permettent l'accès à des fonctionnalités de base de données . Ils comprennent également de nombreux utilitaires d'administration et de maintenance.

27. Les packages utilisés par un administrateur dépendent du type d'application que la base de données exécute. • Voici quelques-uns des packages d'administration et de maintenance les plus courants

28. Le package PL/SQL intégrés • DBMS_STATSpermet de collecter, modifier, consulter, exporter et supprimer les statistiques relatives à vos tables. • DBMS_TTS : validation des tablespaces transportables. • DBMS_SESSION : accès PL/SQL aux instructions ALTER SESSION et SET ROLE

29. Le package DBMS_OUTPUT Ce package permet de stocker de l’information dans un tampon avec les procédures PUT ou PUT_LINE. Il est possible de récupérer l’information grâce aux procédures GET et GET_LINE • Get_line(ligne out varchar2, statut out integer) : extrait une ligne du tampon de sortie. • Get_lines(lignes out varchar2, n in out integer) : extrait à partir du tampon de sortie un tableau de n lignes. • New_line : place un marqueur de fin de ligne dans le tampon de sortie. • Put (variable|conatante in {varchar2|number|date} : combinaison de put et new_line. • Enable (taille tampon in integer default 2000) : permet de mettre en route le mode trace dans une procédure ou une fonction. • Disable : permet de désactiver le mode trace dans une procédure ou une fonction.

30. Les déclencheurs • Un déclencheur est un bloc PL/SQL associé à une vue ou une table, qui s'exécutera lorsqu'une instruction du langage de manipulation de données (DML) sera exécutée • Il est utilisé pour implémenter des règles de gestion complexes et pour étendre les règles d’intégrité référentielle associée à table lors de leur création de cette dernière

31. Caractéristiques d'un Trigger • Le traitement est exprimé en PL/SQL. Il peut lui-même faire appel à des procédures et des fonctions écrites en PL ou Java. • Son code est stocké dans la base de données. • Un déclencheur peut être actif ou non • Si un déclencheur aboutit, la transaction qui l'a appelé peut se poursuivre • Le déclenchement peut se propager en cascade, tout en respectant le principe d'atomicité d'une transaction OPEN_CURSORS • Un déclencheur s'exécute dans le cadre d'une transaction. Il ne peut donc pas contenir d'instruction COMMIT ou ROLLBACK ou toute instruction générant une fin de transaction implicite (ordre DDL) 

32. Comment est ce qu’on peut créer un trigger ? Syntaxe pour la création d’un trigger • Create or replace trigger <trigger_name> • before/after • insert or update of <colonne1>, <colonne2>, ... • or delete • on <tab_name> • [for each row [when (<trigger_condition>)]] • [declare • types, constantes ou variables ´eventuelles] • begin • texte du trigger • end;

33. Comment est ce qu’on peut créer un trigger ? Exemple pour la création d’un trigger

34. Résolution multi-événements • Un Trigger peut répondre à plusieurs événements. • Dans ce cas, il est possible d'utiliser les prédicats intégrés INSERTING, UPDATING ou DELETING pour exécuter une séquence particulière du traitement en fonction du type d'événement

35. Comment est ce qu’on peut créer un trigger ? Exemple pour la création d’un trigger

36. Maintenance des déclencheurs • Activation/désactivation d'un déclencheur: ALTER TRIGGER nom_déclencheur DISABLE ALTER TRIGGER nom_déclencheur ENABLEALTER TABLE nom_table DISABLE ALL TRIGGERS ALTER TABLE nom_table ENABLE ALL TRIGGERS

37. Maintenance des déclencheurs(Suite) • Les informations sur les déclencheurs sont visibles à travers les vues du dictionnaire de données • USER_TRIGGERS pour les déclencheurs appartenant au schéma. • ALL_TRIGGERS pour les déclencheurs appartenant aux schémas accessibles • DBA_TRIGGERS pour les déclencheurs appartenant à tous les schémas . La colonne BASE_OBJECT_TYPE permet de savoir si le déclencheur est basé sur une table, une vue, un schéma ou la totalité de la base La colonne TRIGGER_TYPE permet de savoir s'il s'agit d'un déclencheur BEFORE, AFTER ou INSTEAD OF,sison mode est FOR EACH ROW ou nons'il s'agit d'un déclencheur évènementiel ou non  La colonne TRIGGERING_EVENT permet de connaître l'événement concerné par le déclencheur La colonne TRIGGER_BODY contient le code du bloc PL/SQL

38. Options de configuration PL/sql • Certaines règles devront être mise en place et/ou avancées pour donner aux développeurs des opportunités de production, et de ce fait des meilleures performances. • Pour cela, la base de données devra prendre en compte certaines procédure PL/SQL: • - PLSQL_WARNING • - PLSQL_DEBUG • - PLSQL_OPTIMIZE_MODE • - PLSQL_CODE_TYPE

39. Options de configuration PL/sql •PL/SQL gestion des erreurs en Oracle 10g permet d’opter pour un code propre sans bug et sans erreur. En général on rend DISABLE cette gestion en production. ALTER SYSTEM SET plsql_warnings= ‘DISABLE :ALL’ SCOPE=BOTH ; • PLSQL debuginterprète en incluant les données de débug. En général on rend DISABLE cette gestion en production. ALTER SYSTEM SET plsql_debug=FALSE SCOPE=BOTH ; PLSQL_OPTIMIZE_MODEintroduit en 10g, pour l’activer : ALTER SYSTEM SET plsql_optimize_mode=2 SCOPE=BOTH; Le paramètre PLSQL_CODE_TYPE définit si le compilateur PL/SQL interprète le code où utilise le code natif machine

40. FIN

41. Plan Introduction Langage PL/SQL Les objets PL/SQL Options de configuration PL/SQL conclusion

42. 2 Sécurité de la base de donnée Administration de base de données

43. Plan Introduction Appliquer le principe du moindre privilège Désactiver l’authentification à distance par le système Implémenter les fonctionnalités standards de sécurité du mot de passe Vérification des mots de passe Les outils et options d’audit Affichage des résultats de l’audit Audit détaillé(FGA) Stratégie d’audit détaillé Mise à jour de sécurité Conclusion

44. Introduction

45. Sécurisation d’une BD : objectifs Assurer la sécurité d’une BD c’est maintenir : • la confidentialité • l’intégrité • et la disponibilité des données.

46. Appliquer le Principe De moindre privilège Objectif: • Protéger le dictionnaire de données • Révoquer les privilèges non nécessaires de PUBLIC • Limiter les répertoires accessibles par les utilisateurs • Limiter les utilisateurs dotés de privilèges d’administration • Limiter l’authentification à distance auprès de la base de données

47. Protéger le dictionnaire de donnée Protégez le dictionnaire de données en prenant soin d’affecter la valeur FALSE au paramètre d’initialisation suivant : 07_DICTIONARY_ACCESSIBILITY=FALSE Cette configuration empêche les utilisateurs dotés du privilège système ANY TABLE d’accéder aux tables de base du dictionnaire de données. La valeur FALSE empêche également l’utilisateur SYS de se connecter sous un autre compte que SYSDBA.

48. Révoquer les privilèges non nécessaires de PUBLIC • Révoquer tous les privilèges et rôles non nécessaire du groupe d’utilisateurs PUBLIC du serveur de base de données • De nombreux packages intégrés accordent le privilège EXECUTE à PUBLIC • Le privilège d’ exécution sur les packages suivants doit toujours être révoqué de PUBLIC: •  UTL_TCP • UTL_SMTP  • UTL_HTTP • UTL_FILE  • DBMS_OBFUSCATION et DBMS_CRYPTO 

49. Exemple • Les commandes sont les suivantes :-REVOKE EXECUTE ONutl_tcp FROM PUBLIC; -REVOKE EXECUTE ONutl_smtp FROM PUBLIC;-REVOKE EXECUTE ONutl_http FROM PUBLIC;-REVOKE EXECUTE ONutl_file FROM PUBLIC;-REVOKE EXECUTE ONdbms_obfuscation FROM PUBLIC;-REVOKE EXECUTE ONdbms_crypto FROM PUBLIC;

50. Limiter les répertoires du système d’exploitation accessibles par l’utilisateur UTL_FILE_DIR est le paramètre de configuration qui: Désigne les répertoires disponibles pour les E/S de fichiers PL/SQL Permet aux utilisateurs de la base de lire ou d’écrire dans ces répertoires, sur le serveur de base de données