Introduction au langage PLSQL d’Oracle

PLSQL 3 Commentaires »

Cet article a pour but de présenter ce qu’est PLSQL, pour les tous débutants et sera suivi de quelques autres, qui rentrent dans le vif du sujet et apprennent à coder / programmer avec le PLSQL d’Oracle.

PL SQL ?
L’acronyme veut dire Procedural Langage SQL, donc en Francais Langage procédural  SQL.

Si SQL est un langage ensembliste et non procédural soumis à une norme (à peu prés portable), il n’est pas un langage de programmation.
PL/SQL EST un vrai langage de programmation, procédural, propre à Oracle et donc NON PORTABLE
PL/SQL comme SQL s’exécutent sur le moteur de la base de données et non sur le client
SQL interprète les commandes une à une, Le moteur PL/SQL interprète des blocs de commandes (et est donc à priori plus performant)
PL/SQL peut être stocké dans la base ou utilisé de manière interactive

Environnements de travail

PL:SQL peut s’utiliser dans les environnement suivants :
Interpréteurs SQL : SQL*Plus, i*SQLPlus
Précompilateurs (Pro*C, Pro*Cobol.) (ca commence à dater !)
Outils de développement : SQLDeveloper, Designer, Forms, et même APEX
Autre langages de développement : C,C++,Java,PHP

Le + simple pour démarrer avec PLSQL est d’utiliser SQL*developper l’outil de développement gratuit d’Oracle, téléchargeable sur http://otn.oracle.com

Avantages / inconvénients

PLSQL est complémentaire du SQL (qui lui n’est pas un langage de programmation)
est en général stocké dans la base, et permet donc un emeilleure intégration/cohérence du code avec les données
permet le traitement par bloc de SQL et donc optimise le trafic réseau
utilisation de variable de stockage et de type simple et structuré dynamique (%TYPE, %ROWTYPE, etc)
traitements plus complexes, notamment pour la gestion des cas particuliers et des erreurs (traitement des exceptions)
l’utilisation de librairies standards prédéfinies (supplied PLSQL packages, comme les RDBMS_xxx)
paramétrage et création d’ordres SQL dynamiques.

Fonctionnalités issues de SQL

PL/SQL intègre des ordres SQL de gestion de la base :
interrogation de données : SELECT
manipulation de données : INSERT, UPDATE, DELETE
gestion transactionnelle : COMMIT, ROLLBACK
fonctions : TO_CHAR, TO_DATE, UPPER, ROUND…
manipulation de structures : CREATE, ALTER , DROP, RENAME, …

rem : Avec les versions antérieures à la 9i, Les ordres du LDD ne sont pas supportés par défaut il faut utiliser un package spécial : DBMS_SQL !

Spécificités PL/SQL

Définition de variables simple, et structurées (tableaux, record)
Définbitiion de nouveaux types
Traitements conditionnels (if, then , else)
Traitements répétitifs (boucles for, while, …)
Gestion des curseurs
Gestion des erreurs / exceptions

Déclinaisons et mécanismes implémentatnt la Sécurité des SGBDs

Sécurité bases de données pas de Commentaire »

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)

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)
Aspect sécurité mécanisme mis en oeuvre exemple d’implémentationau niveau SGBD exemple d’implémentationau niveau OS
confidentialité
  • authentification
  • indépendance logique / physique
  • référentiel user / password : DBA_USERS
  • tables de user applicatifs
  • identification externe :
    CREATE USER …IDENTIFIED EXTERNALLY
  • tables / tbs / fichiers
  • vues (1)
  • virtual private database
  • SSO LDAP
  • identification externe
  • architecture client serveur
  • droits et privilèges
  • droits d’accès aux données
    GRANT SELECT ON toto
  • droits du LDD ou Systeme
    GRANT CREATE TABLE TO…
    GRANT CREATE SESSION TO…
  • roles
  • user OS DBA ou root
traçabilité
  • logs et traces
  • tables d’audit
  • log Oracle Net
  • logs apache
  • logs OS
  • logs réseau
fiabilité, disponibilité, maintenabilité
  • tolérance de panne
  • stand by DB
  • cluster logiciels : architecture R.A.C
  • H.A.C.M.P
  • techno RAID
  • machine redondantes
  • sauvegarde et restauration
  • physique : sauvegarde + journalisation + restauration
  • logique : export / import
  • génération de SQL
  • copie physique totale
intégrité
  • transaction atomique
  • contraintes d’intégrité
  • Two Phase Commit (2PC)
  • contraintes ‘reference’
  • read consistancy

(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.)

Principes de base sécurité Oracle

Divers, Sécurité Oracle pas de Commentaire »

Les principaux sont expliqués ici :

Compatibilité Dataguard Logique en 10gR2

dataguard pas de Commentaire »

Avant de se lancer dans l’entreprise de mettre en place un dataguard, il est judicieux de vérifier si vos applications le supportent !

En effet Quelques fonctionnalités et types de données ne sont pas supportés par un Dataguard Logique (LOGICAL STANDBY DATABASE).

Principalement :

  • un certain nombre de types de données un peu ‘exotiques’
  • les segments compressés
  • certains packages standards (supplied packages)

En détail ca nous donne :

DATATYPES non supportés
BFILE
Collections : VARRAYS , NESTED TABLE
Encrypted columns
spatial : SDO_%
media : ORD%
still images : SI_%
ROWID, UROWID
User-defined types : TYPE
XMLType

SUPPLIED PACKAGES non supportés
DBMS_JAVA, DBMS_REGISTRY,DBMS_ALERT, DBMS_SPACE_ADMIN, DBMS_REFRESH,
DBMS_REDEFINITION, DBMS_SCHEDULER, et DBMS_AQ.

Voici de petits scripts qui permettent de générer un rapport sur la compatibilité de votre base de données avec une STandby database logique. Ils recherchent les schémas et/ou objets qui peuvent poser problème :

Prompt Schemas internes non supportés
SELECT OWNER FROM DBA_LOGSTDBY_SKIP
WHERE STATEMENT_OPT = ‘INTERNAL SCHEMA’
/

PROMPT « Nested Tables »
SELECT owner, table_name
FROM DBA_NESTED_TABLES
WHERE OWNER NOT LIKE ‘%SYS%’
ORDER by 1,2
/

PROMPT « Types non supportés »
SELECT OWNER,TABLE_NAME,COLUMN_NAME, DATA_TYPE
FROM DBA_TAB_COLUMNS
WHERE DATA_TYPE NOT IN
(
‘BINARY_DOUBLE’,
‘BINARY_FLOAT’,
‘BLOB’,
‘CHAR’,
‘CLOB’,
‘NCLOB’,
‘DATE’,
‘INTERVAL YEAR TO MONTH’,
‘INTERVAL DAY TO SECOND’,
‘LONG’,
‘LONG RAW’,
‘NCHAR’,
‘NUMBER’,
‘FLOAT’,
‘INTEGER’,
‘NVARCHAR2′,
‘RAW’,
‘VARCHAR2′,
‘VARCHAR’,
‘UNDEFINED’
)
AND DATA_TYPE NOT LIKE ‘TIMESTAMP%’
AND OWNER NOT LIKE ‘%SYS%’
AND OWNER <> ‘XDB’
ORDER BY 1,2,3
/

PROMPT « Segments compressés »
SELECT OWNER, TABLE_NAME
FROM DBA_TABLES
WHERE COMPRESSION=’ENABLED’
/

PROMPT Table sans identifiant unique
SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE
WHERE (OWNER, TABLE_NAME) NOT IN
(SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED)
AND BAD_COLUMN = ‘Y’
/

Rem : on pourrait utiliser une vue du dictionnaire apparemment faite pour ca : DBA_LOGSTDBY_UNSUPPORTED mais cette vue mélange « segment compression » et data type non supportés, et donne d’après ce que j’ai pu tester des résultats incomplets…
PROMPT « Rapport générique

SQL non supporté

Un certain nombre d’ordres SQL ne son pas répliqués sur la base STANDBY (Skipped SQL) :

ALTER DATABASE
ALTER MATERIALIZED VIEW
ALTER MATERIALIZED VIEW LOG
ALTER SESSION
ALTER SYSTEM
CREATE CONTROL FILE
CREATE DATABASE
CREATE DATABASE LINK
CREATE PFILE FROM SPFILE
CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW LOG
CREATE SCHEMA AUTHORIZATION
CREATE SPFILE FROM PFILE
DROP DATABASE LINK
DROP MATERIALIZED VIEW
DROP MATERIALIZED VIEW LOG
EXPLAIN
LOCK TABLE
SET CONSTRAINTS
SET ROLE
SET TRANSACTION

La plupart n’ont rien à faire dans une application standard et ne devraient donc pas poser de problème.

Attention !

on portera une attention particulière à l’instruction ‘ ALTER SESSION’ souvent utilisée soit pour changer l’identité d’un user applicatif, ou pour positionner des paramètres NLS et ainsi paramétrer les dates, langues et autres monnaies (NLS_DATE, NLS_LANGUAGE, NLS_CURRENCY, …).

Vérifiez bien que vos applicatifs n’utilisent pas cette instruction pour changer de contexte au démarrage…