• III-1 Introduction à SQL *

• III-1-1 Requêtes de gestion de transaction *
• III-1-2 Principes de SQL *
• III-1-3 Les différents help sous SQL *
• III-1-4 Noms des objets *
• III-1-4-1 Conventions sur les noms des objets *
• III-1-4-2 Identificateurs délimités *
• III-1-5 Création de schéma *
• III-1-6 Synonymes *
• III-1-6-1 création d'un synonyme *
• III-1-6-2 destruction d'un synonyme *
• III-1-7 Types de données *
• III-1-8 Les constantes utilisées dans SQL *

• III-1-9 Principaux ordres SQL *
• III-1-9-1 ordres SQL de manipulation de données *
• III-1-9-2 ordres SQL d'administration de base *
• III-2 Select, premiers pas *
• III-2-1 L'ordre select *
• III-2-2 Opérateurs de comparaison *
• III-2-3 Opérateurs logiques *
• III-2-4 Enlever les doubles *
• III-2-5 Faire des recherches triées *
• III-2-6 Mettre un commentaire dans une requête *
• III-2-7 Recherches sur chaînes de caractères *
• III-2-7-1 comparaison d'une colonne textuelle à une constante *
• III-2-7-2 comparaison d'une colonne textuelle à un modèle, uniquement avec le comparateur like *
• III-2-7-3 séquences spéciales *
• III-3 Fonctions *
• III-3-1 Fonctions de conversion de type *
• III-3-2 Fonctions sur les dates *
• III-3-3 Fonctions numériques *
• III-3-4 Fonctions sur chaînes de caractères *
• III-3-5 La fonction ifnull *
• III-4 Conditions de recherche du where *
• III-4-1 Définition d'une expression *
• III-4-2 Définition d'une condition de recherche *
• III-4-3 Utilisation de "between" *
• III-4-4 Utilisation d'une "subquery" avec "in" : *
• III-4-5 Utilisation d'une "subquery" avec "exists" *
• III-4-6 Utilisation de "any et all" *
• III-4-7 Utilisation de "is null" *
• III-5 Jointure de tables *
• III-5-1 Jointure interne *
• III-5-2 Jointures externes *
• III-6 Agrégations *
• III-6-1 Les fonctions d'agrégation *
• III-6-2 Agrégats avec clauses where, group by et having *
• III-6-3 Ordre conceptuel dans lequel les opérations sont effectuées *
• III-7 Définitions à propos de select, subselect, et subquery *
• III-8 Création de table *
• III-8-1 Syntaxe de l'ordre create table *
• III-8-2 Valeurs nulles et par défaut des colonnes *
• III-8-3 Contraintes sur les colonnes *
• III-8-4 Contraintes sur les tables *
• III-8-5 Clause with du create table *
• III-8-6 Limitations *
• III-9 Création de table à partir d'une recherche *
• III-9-1 Syntaxe de create table *
• III-9-2 Options supplémentaires de la clause with *
• III-10 Création de tables temporaires *
• III-11 Création de vue *
• III-12 Destruction d'une table ou d'une vue *
• III-13 Mise à jour de table *
• III-13-1 Ajout de lignes dans une table *
• III-13-2 Destruction de lignes d'une table *
• III-13-3 Mise à jour de lignes d'une table *
• III-14 Commentaires sur les tables et les colonnes *
• III-15 Chargement et déchargement de tables (export/import) *
• III-16 Sécurité des données*
• III-16-1 Privilèges dans l'environnement OpenIngres *
• III-16-2 Privilèges objets *
• III-16-2-1 l'ordre grant *
• III-16-2-2 suppression de privilèges *
• III-16-2-3 privilèges dépendants *
• III-16-2-4 vues et privilèges *
• III-16-2-5 procédures de bases de données et privilèges *
• III-16-2-6 privilège de mise à jour multicolonne *
• III-16-3 Privilèges utilisateurs *
• III-16-3-1 présentation *
• III-16-3-2 opérations et syntaxe *
• III-16-3-3 gestion des locations *
• III-16-4 Contrôle d'accès par les vues *
• III-16-5 Privilèges sur une base et sur l'installation *
• III-16-5-1 généralités *
• III-16-5-2 privilèges sur une base et l'installation complète *
• III-16-5-3 exemples *
• III-17 Les contraintes d'intégrité *
• III-17-1 Créer une contrainte d'intégrité *
• III-17-2 Lister les contraintes d'intégrité *
• III-17-3 Détruire les intégrités en indiquant leurs numéros *
• III-18 Gestions d'événements, règles et procédures *
• III-18-1 Introduction *
• III-18-2 Gestion des procédures *
• III-18-3 Gestion des règles *
• III-18-4 Exemples de règles et procédures *
• III-19 Autres types de données : les objets spatiaux *
• III-19-1 Le type point "point" *
• III-19-2 Le type boîte "box" *
• III-19-3 Le type segment "lseg" *
• III-19-4 Le type ligne "line" *
• III-19-5 Le type ligne longue "long line" *
• III-19-6 Le type polygone "polygon" *
• III-19-7 Le type polygone long "long polygon" *
• III-19-8 Le type cercle "circle" *
• III-19-9 Tableau des formats des types spatiaux *
• III-19-10 Les opérateurs spatiaux disponibles *
• III-19-11 Les fonctions disponibles sur les attributs de types spatiaux *

chaque sortie de sql (isql) termine une transaction.

Mises à jour et insertions ne sont sauvegardées qu'en fin de transaction, même si les données paraissent modifiées en cours de transaction. 
 

roolback; commit;	valide les modifications apportées depuis le début de transaction, démarre une nouvelle transaction
set autocommit on | off;	placé à on, chaque requête de modification dans la base est automatiquement validée. Sur les machines du CIR, par défaut à on lorsqu'on appelle isql ou sql, à off si on appelle ingmenu

III-1-2 Principes de SQL

C?est un langage interactif (outil isql) ; il est également utilisé comme langage intégré ("embedded ") dans un langage de programmation (Fortran ou C) pour le développement d?applications.

Ce langage possède ses objets, sa syntaxe, ses commandes, ses mots-clés.

La commande help fournit des informations sur les objets de la base de données.

III-1-4 Noms des objets

• les noms ne peuvent contenir que des caractères alphanumériques et doivent commencer par une lettre ou (sauf les noms de bases) par un "blanc souligné";
• il n'y a pas de distinction minuscules/majuscules, par défaut (voir le réglage de db_name_case);
• les noms peuvent contenir les caractères suivants : chiffres de 0 à 9, #, @ et $; les noms utilisés comme identificateurs, entre guillemets peuvent contenir d'autres caractères spéciaux;
• les noms de tables ne peuvent pas commencer par "ii" (réservé aux tables systèmes);
• la longueur maximale d'un nom est de 32 caractères (24 dans l'ancienne version de Ingres)

blanc, étoile (*), tiret (-), etc., 

• les noms doivent être entre guillemets 
• les noms peuvent être utilisés pour les noms de : tables, vues, identificateurs de privilèges, database events, etc. 

Pour utiliser des guillemets dans un identificateur délimité, il faut les répéter, par exemple : """nom""identificateur""" est interprété comme :

"nom"identificateur"

Les blancs de fin sont supprimés des noms utilisant les identificateurs délimités. Par exemple, la requête :

create table "essai blancs " (scolumn int);

crée une table appelée "essai blancs";

si la chaîne ne contient que des blancs, le nom consistera en un seul blanc.

III-1-5 Création de schéma

Le nom du schéma est celui de l'utilisateur qui l'a créé. Chaque utilisateur ne peut créer qu'un seul schéma par base. Il n'y aura donc pas création de schéma s'il en existe déjà un pour l'utilisateur.

La création d'un schéma se fait soit par une instruction spécifique, soit, si un utilisateur n'a pas de schéma, automatiquement lors de la création de la première table par l'utilisateur

Syntaxe : .

Exemple :

Exemple

III-1-8 Les constantes utilisées dans SQL

Ces constantes peuvent être utilisées dans les requêtes et expressions, par exemple :

III-2 Select, premiers pas

Syntaxe : 

Conditions des comparaisons avec un type decimal :
 

• le tri peut s'effectuer sur plusieurs colonnes 
• les colonnes triées doivent être présentes dans la clause select 
• le tri est ascendant par défaut (asc)

la constante s'écrit entre cotes ex : nom = 'Dupont' 

% n'importe quelle chaîne de caractères, y compris la chaîne vide 

_ n'importe quel caractère 

ex : '%ton' reconnaîtra 'Tonton', 'mironton', 'ton' 

ex : '___ton' reconnaîtra 'Tonton', 'chaton' 

\[1-x\] reconnaîtra un caractère pris entre 1 et x dans l'ordre des caractères ASCII (de 1 à x dans notre cas) 

Attention : pour ces séquences spéciales entourées de crochets, il faut obligatoirement indiquer quel est le caractère d'échappement. Dans l?exemple qui suit c?est le contre-slash. Sinon, SQL comparerait à la chaîne ?\[A-D\]?.

ex : 

Les fonctions s'appliquent aux constantes, colonnes, expressions; elles peuvent s'imbriquer en respectant le type de leurs arguments.

III-3 Fonctions

Valeurs de p et s pour les conversions par la fonction decimal, selon le type converti :
 

Les unités mentionnées ci-dessus doivent être obligatoirement indiquées entre cotes.

exemple :

'today'

exemples de dates : 

attention aux priorités, mettre des parenthèses 

atan (x)

cos (x)

exp (x)

log (x)

mod (i, j) i, j et le résultat de mod sont des entiers

sin (x)

sqrt (x) 

remarques sur le type "decimal" :

Les opérateurs et fonctions arithmétiques peuvent être utilisés avec des types "decimal" :

• les opérations arithmétiques peuvent se faire sur des valeurs de précision et échelle différentes; 
• les fonctions ensemblistes (max, min, avg, sum) prennent des arguments de type decimal; 
• la fonction avg rend un résultat plus précis qu'avec des types float; 
• la fonction mod tronque les arguments à leur partie entière et rend un entier; 
• toutes les autres fonctions numériques convertissent les valeurs en float et retournent un float.

Remarque :

Le type de donnée n?est pas changé par ces fonctions : ainsi, si adresse est un char(150), la fonction left (adresse,3) rendra un char(150).

ifnull (v1, v2) a pour valeur v1 si v1 est non "null" et pour valeur v2 si v1 est "null" 

Par exemple, les fonctions d'agrégations sum, avg, max et min rendent une valeur nulle si l'ensemble sur lequel elles portent est vide. Pour recevoir une valeur non nulle, vous pouvez utiliser la fonction ifnull comme ceci :

La définition de la jointure apparaîtra dans la clause where du select.

Exemple : donner l'identificateur, l'année de naissance, l'année du panel, et la puissance fiscale de tous les véhicules de tous les foyers : 

Toutes les tables intervenantes doivent être présentes dans le from.

On doit spécifier, à l'aide des synonymes, la tabled'appartenance pour toute colonne commune à deux tables.

La jointure interne donne comme table résultats une table contenant les lignes communes aux 2 tables d'origine.

Par exemple, si on fait la jointure entre une table de foyers et une table de véhicules, suivant la condition "tel foyer possède un véhicule", la jointure interne ne donnera que les foyers ayant un ou plusieurs véhicules dans la table véhicules.

Exemple de schéma conceptuel :
 

exemple : sélection des identificateurs, département, nombre de personnes des foyers ainsi que l'année d'enquête et la marque-modèle des véhicules associés

Dans tous les cas, une table (ou vue) résultant d'une jointure sera dite interne si seules les lignes vérifiant la condition de jointure sont produites, et externe si toutes les lignes sont produites, qu'elles vérifient ou pas la condition de jointure. Les colonnes provenant de la table interne ne vérifiant pas la condition de jointure auront la valeur "null".

Syntaxe de la jointure externe :

L'option inner join désigne la jointure interne, les autres les 3 types de jointures externes.

Dans chaque cas de jointure externe, on désigne la table (ou vue) qui sera externe, c'est-à-dire dont toutes les lignes seront affichées; ainsi, en cas de left join, c'est la table de gauche qui est externe; en cas de right join, c'est celle de droite, et les deux en cas de full join.

Les jointures peuvent être emboîtées dans une clause from. Elles sont alors évaluées de gauche à droite. On peut, en utilisant des parenthèses, forcer l'ordre d'évaluation.

Schémas des jointures externes correspondant aux tables foyer et véhicule précédentes:

exemple : sélection d'informations sur tous les foyers et tous les véhicules

exemple : sélection de tous les foyers et des informations sur leurs véhicules éventuels

exemple : sélection de tous les véhicules et des informations sur les foyers éventuels associés

A l'intérieur de sql, elles sont référencées comme des Set Functions

Ce sont : 

elles ne peuvent pas être dans une clause where; 

elles ne peuvent être que dans les clauses select et having;

avg, count, sum peuvent avoir l'argument distinct;

les valeurs nulles sont ignorées, sauf pour count(*) qui compte les lignes.

exemple : sélection de la moyenne du nombre de véhicules par foyer :

- sans clause where

Les lignes restantes sont groupées avant calcul de la moyenne.

Le regroupement peut se faire suivant plusieurs colonnes.

Le select ne peut contenir que des agrégats, ou des constantes et les colonnes du group by. 

exemple : même question que précédemment, mais en ne s'intéressant qu'aux CSP avec plus de 2 foyers dans la base :

La clause having doit produire une seule valeur par groupe

1. formation du produit cartésien des tables indiquées dans le from 
2. élimination des lignes ne satisfaisant pas le where 
3. regroupement des lignes suivant la clause group by 
4. élimination des groupes ne satisfaisant pas la clause having 
5. évaluation des expressions dans la clause select 
6. si select distinct, élimination des lignes en double dans la table résultat 

Tout ce qui commence par select est un "subselect" .

Un subquery n'apparaît que dans une condition de recherche, il est introduit par, et seulement par :

Il y a toujours deux façons d'écrire une même requête mettant en oeuvre plus d'une table :

- avec jointure

- avec subquery

III-8 Création de table

avec le type "logical key", on peut ajouter la clause "with system maintained"

with default spécifie que si aucune valeur n'est fournie, on aura 0 pour les types numériques et money, une chaîne vide pour les types caractères et date;

[with default] default_spec spécifie la valeur que prend la colonne par défaut si elle n'est pas fournie;

exemple :

not null spécifie que la colonne ne peut pas prendre la valeur "null";

Lorsque l'on autorise la valeur logique 'null' pour une colonne, un octet est rajouté pour le stockage de cette colonne dans chaque ligne de la table.

exemple : 

Avec "with system maintained", la seule clause de défaut valide est "with default". Elle est prise par défaut si vous l'oubliez. La seule clause de nullité valide est "not null". Si vous ne spécifiez pas de contrainte de colonne ou de clause de nullité, la clause "not null" est prise par défaut.

• unique spécifie que l'on ne peut pas avoir deux lignes avec la même valeur de colonne;
• check (controle_spec) définit une condition que doit vérifier la colonne

• references [schema.]nom_tab[(nom_col)] définit une contrainte référentielle entre la colonne et une colonne d'une autre table;

• primary key spécifie que la colonne est une clé primaire (une seule possible par table) et qu'une contrainte référentielle dans une autre table pourra y faire référence;

table référencée :

• unique(nom_col {, nom_col) permet de spécifier que les données d'un groupe de colonnes sont uniques;

• check (controle_spec) permet de spécifier une condition à vérifier pour un groupe de colonnes;

• references [schema.]nom_tab[(nom_col {, nom_col})] permet de spécifier des contraintes référentielles au niveau de la table, en utilisant l'option "foreign key"

• location = (disc-logic-i {, disc-logic-i }) spécifie les localisations de la table; ceci permet de répartir les données sur différents disques, si l'administrateur Ingres l'a prévu;
• [no]journaling spécifie avec ou sans journalisation de la table

• [no]duplicates permet ou pas les lignes doubles

• label_granularity = table | row | system_default spécifie le niveau du label de sécurité;
• security_audit = (audit_opt {, audit_opt}) spécifie un niveau d'audit par ligne (audit_opt=row) ou par table (audit_opt=table);
• security_audit_key = (nom_col) cette option permet de spécifier un attribut qui sera écrit dans le fichier log de l'audit; par exemple on utilise le numéro d'employé comme clé d'audit

nom de colonne <= 32 caractères 

au plus 300 colonnes par table

au plus 2008 octets par ligne, sauf pour les colonnes de type "blob" (binary long object), long varchar et long byte, qui peuvent aller jusqu'à 2 gigaoctets.

III-9 Création de table à partir d'une recherche

  Le résultat d'une recherche est une table; on peut l'affecter à une table, temporaire ou permanente et les types des colonnes de la nouvelle table correspondent au type calculé dans le select, ce qui peut permettre des conversions de type dans certains cas. Il est également possible de renommer les colonnes de la nouvelle table; il n'est par contre pas possible de leur assigner un type.

• structure = hash | heap | isam | btree spécifie la structure de stockage de la table (voir le chapitre optimisations) 
• key = (nom_col {, nom_col}) spécifie les colonnes servant de clés 
• fillfactor = n spécifie le pourcentage de remplissage des pages de données primaires (voir modify) 
• minpages = n spécifie le nombre minimum de pages primaires pour une table en "hash"(voir modify) 
• maxpages = n spécifie le nombre maximum de pages primaires pour une table en "hash"(voir modify) 
• leaffill = n spécifie le pourcentage de remplissage des pages d'index "feuilles" en BTREE (voir modify) 
• nonleaffill = n spécifie le pourcentage de remplissage des pages d'index "non feuilles" en BTREE (voir modify) 
• compression[= ([[no]key] [,[no]data])] | nocompression spécifie si la clé ou les données sont compressées 
• allocation =n spécifie le nombre de pages initialement allouées à la table (4 par défaut) 
• extend =n spécifie le nombre de pages pour l'extension de la table (16 par défaut)

- existent pour la durée de la session, 

- sont automatiquement détruites à la fin de la session.

Le nom de la session fait partie du nom de la table, ce qui élimine les conflits de noms.

L'accès à ces tables est rapide, car elles sont, autant que possible, conservées en mémoire :

- pas de privilèges à vérifier, 

- pas d'entrées de catalogues à parcourir, 

- pas de logging ou de locking à gérer (ni sauvegarde, ni verrouillage).

Restrictions

- elles ne sont visibles que de la session qui les a créées, 

- elles disparaissent à la fin de la session, 

- on ne peut leur appliquer que les instructions modify et drop.

Syntaxe :

création d'une table temporaire originelle

location = (nom_loc {, nom_loc})

[no]duplicates

allocation = n

extend =n

Pour les tables temporaires créées avec un subselect, les options suivantes peuvent être utilisées dans with_clause:

III-11 Création de vue
  Une vue est une définition logique, sans stockage de données. Cela permet des calculs (et/ou jointures) sur des données qui sont refaits à chaque appel, prenant en compte toutes les modifications intervenues dans les données. Seule la définition est stockée (accessible par help view nom_vue).

Avec cette clause, on peut insérer des lignes, mettre à jour des colonnes, par l'intermédiaire d'une vue, si les conditions du where sont respectées

exemple : créer la vue des foyers de Paris

exemple : recherche dans la vue "sur_mot" 

drop nom_table | nom_vue | nom_index{, nom_table | nom_vue | nom_index} ;ou 

exemple : destruction de la table "commune" 

III-13 Mise à jour de table

Création : 

III-15 Chargement et déchargement de tables (export/import)

  La commande copy table permet de charger une table à partir d'un fichier ou de la décharger dans un fichier. 

Le descripteur de format de lecture - écriture est très simple. 

Le format est indépendant du type de colonne. 

Syntaxe :

Les formats peuvent s'écrire aussi en précisant un délimiteur de champ autre que la virgule (tabulation, retour chariot), il faut alors le préciser notamment après char(0) ou c0. 

ex : char(0)'?'

Certains caractères spéciaux sont représentés par un mot clé :

Il faut absolument faire figurer, dans le format de lecture, le délimiteur de ligne au bon endroit (char(0)nl dans l'exemple qui suit). Ces formats peuvent être utilisés également en écriture pour exporter des données, mais il existe des outils Ingres et CIR pour le faire plus aisément.

exemple de contenu du fichier à importer : 

La table suivante explicite les formats de données des fichiers par rapport aux types de données SQL.
 

Format	Stockage (Copy Into)	Lecture (Copy From)
byte(0)	donnée binaire de longueur fixe (complétée de zéros si nécessaire).	donnée binaire de longueur variable terminée par la première virgule, tabulation ou fin de ligne rencontrée.
byte(0)delim	donnée binaire de longueur fixe (complétée de zéros si nécessaire). Le délimiteur sur 1 caractère est inséré immédiatement après la valeur (zéro n'est pas un délimiteur autorisé).	donnée binaire de longueur variable terminée par le caractère spécifié.
byte(n) où 1< n < 2000	donnée binaire de longueur fixe	donnée binaire de longueur fixe
byte varying(0)	donnée binaire de longueur variable précédée d'une longueur sur 5 car.	donnée binaire de longueur variable précédée d'une longueur sur 5 car.
byte varying(n)  où 1< n < 2000	donnée binaire de longueur fixe précédée d'une longueur sur 5 car. Complétée par des zéros si nécessaire	donnée binaire de longueur fixe précédée d'une longueur sur 5 car.
char(0)	Chaîne de longueur fixe, complétée par des blancs si nécessaire. Pour les données character, la longueur de la chaîne écrite dans le fichier est la même que la longueur de la colonne	chaîne de longueur variable terminée par la première virgule, tabulation ou fin de ligne rencontrée.
char(0)delim	Complété à la longueur déclarée de la colonne. Le délimiteur sur 1 caractère est inséré immédiatement après la valeur (un espace n'est pas un délimiteur autorisé).	chaîne de longueur variable terminée par le caractère spécifié.
char(n) où 1< n < 2000	Chaîne de longueur fixe	Chaîne de longueur fixe
d0	(non applicable)	Champ bidon. Chaîne de longueur variable terminée par la première virgule, tabulation ou fin de ligne rencontrée. La donnée dans le champ est sautée

 
 
 
 

Format	Stockage (Copy Into)	Lecture (Copy From)
d0delim	Indique une colonne bidon délimitée. Au lieu de mettre une valeur dans le fichier, copy insère le délimiteur spécifié. (Contrairement au format dn, ce format n'insère pas le nom de colonne.)	champ bidon : lu comme une chaîne de longueur variable délimitée par le caractère spécifié. La donnée dans le champ est sautée.
date	donnée de format date.	Champ date.
decimal	donnée de format decimal	donnée de type decimal.
dn	Indique une colonne bidon délimitée. Au lieu de mettre une valeur dans le fichier, copy insère le nom de la colonne n fois. Par exemple, si vous spécifiez x=d1, le nom de colonne 'x' est inséré une fois, si vous spécifiez x=d2, le nom de colonne 'x' est inséré deux fois, etc. Vous pouvez spécifier un délimiteur comme nom de colonne, par exemple, nl=d1.	champ bidon : lu comme une chaîne de longueur variable de la longueur spécifiée. La donnée dans le champ est sautée.
float	flottant double précision	flottant double précision
float4	flottant simple précision	flottant simple précision
integer	entier sur 4 octets	entier sur 4 octets
integer1	entier sur 1 octet	entier sur 1 octet
long byte(0)	Donnée binaire stockée dans des segments. Chaque segment est composé d'un entier, donnant la longueur du segment, suivi par un espace, puis par le nombre de bytes spécifié. Si la colonne peut avoir la valeur null, il faut spécifier la clause "with null". Une colonne vide est stockée comme un 0 suivi par un espace.	Champ de type long byte.

 
 
 
 

Format	Stockage (Copy Into)	Lecture (Copy From)
long varchar(0)	Donnée stockée dans des segments. Chaque segment est composé d'un entier, donnant la longueur du segment, suivi par un espace, puis par le nombre de caractères spécifié. Exemple de 2 segments : le premier segment a 5 caractères et le second a 10 caractères : 5 abcde 10 abcdefghij... Si la colonne peut avoir la valeur null, il faut spécifier la clause "with null". Une colonne vide est stockée comme un 0 suivi par un espace.	Champ de type long varchar
money	donnée de format. money	champ de format. money
smallint	entier sur 2 octets	entier sur 2 octets
varchar(0)	chaîne de longueur variable précédée de 5-characteres donnant la longueur	chaîne de longueur variable précédée de 5-characteres donnant la longueur
varchar(n)  où 1<n< 2000	chaîne de longueur fixe précédée de 5-characteres donnant la longueur. Complétée si nécessaire par des caractères null	chaîne de longueur fixe précédée de 5-characteres donnant la longueur.

III-16 Sécurité des données
 

On distingue :

• des privilèges utilisateurs que l'usager peut exécuter dans l'installation OpenIngres. 
• des privilèges sur les objets d'une base. 
• des privilèges de base et d'installation, décrivant les opérations que l'usager peut exécuter ou non, et pouvant être définis pour les bases individuelles ou pour toutes les bases de l'installation OpenIngres.

Les contrôles d'accès par les vues sont étendus à d'autres fonctionnalités, permettant de gérer les accès sur les colonnes.

Enfin les privilèges sur les bases et l'installation OpenIngres permettent une gestion plus précise des privilèges.

Avec l'ancienne version de Ingres, les privilèges se rapportaient seulement aux objets que possédait l'administrateur de la base, les objets possédés par d'autres utilisateurs étaient toujours privés.

Avec OpenIngres tout utilisateur peut accorder des privilèges sur les objets qu'il possède, et ce à n'importe quel autre usager.

Les privilèges objets sont manipulés en utilisant les ordres SQL grant et revoke, qui sont les ordres de la norme ANSI pour la manipulation de privilèges.

create/drop/permit sont les ordres spécifiques à Ingres, et sont retirés progressivement

(revoke et drop permit se comportent différemment).

select

update

delete

insert

execute (uniquement pour les procédures, droit unique des procédures)

references donne le droit à créer des contraintes référentielles sur les tables et colonnes spécifiées

copy_into

copy_from

all [privileges] tous les droits (doit être seul présent) 

type_objet peut être :

table

procedure

database

dbevent

current installation

Par défaut, le type d'objet est "table". Le privilège possible dépend du type d'objet

nom_objet est le nom de l'objet désigné 

type_autoris est le type d'autorisation à qui vous donnez les privileges :

user (par défaut)

group

rôle

id_autoris est le nom de users, groups, ou roles à qui vous donnez les privileges, ou public (droit accordé à tous). 

exemple : on met l'accès en lecture à tous sur la table "commune"

Avec "with grant option", les usagers peuvent déléguer (en cascade) à d'autres usagers le droit de donner des accès aux objets, pour les mêmes privilèges qu'on leur a donnés (ceci s'applique donc aux objets possédés par d'autres usagers).

Exemple : passage de privilèges

Rémy : 

Rémy et Line peuvent insérer dans cette table.

Rémy, Michel et Line peuvent donner toutes les permissions aux autres usagers, Alain ne peut pas.

with grant option n'est pas valide pour les permissions données aux groupes et rôles, ni pour les permissions données sur les bases ou sur l'installation courante.

cascade enlève le privilège à supprimer, et aussi tous ceux qui en découlent.

restrict enlève le privilège spécifié seulement s'il n'y a pas d'autres privilèges dépendants : ceci évite d'avoir des privilèges "orphelins".

On ne peut supprimer que les privilèges qu'on a soi-même donnés.

On peut enlever le privilège "with grant option".

On peut ôter en cascade les privilèges dépendants.

Nécessite d'être le propriétaire de l'objet, mais pas nécessairement le donneur d'accès.

Echoue si un privilège ou objet quelconque est abandonné.

with grant option

Ainsi, de l'exemple précédent :

Le privilège select de Michel sur remy.dept dépend de Line ayant à la fois les droits select et grant option

Si les droits select ou grant option de Line sont supprimés, la permission select de Michel l'est également. On peut donc faire :

  La création de vues nécessite le privilège de sélectionner sur toutes les tables ou vues sur lesquelles la vue est basée.

Donner un droit sur une vue nécessite de pouvoir donner les permissions désirées sur toutes les tables ou vues de la base.

Le mieux est d'avoir le privilège grant option sur les autres tables ou vues.

La définition d'un privilège sur une vue est transmissible.

Elles sont parfois appelées procédures de base de données "actives".

La procédure de base de données devient "dormante" si les conditions changent après la création de la procédure de base de données (suppression de privilèges, par exemple ...).

Le propriétaire doit posséder le privilège grant option sur tous les objets référencés dans la procédure de base de données pour y donner les privilèges à d'autres usagers (elles sont appelées procédures autorisables).

soit la table : vendeur (vnom, vnum, rue, ville, dept, code) :

Le privilège revoke update est utilisable pour les colonnes :

revoke excluding ne laisse qu'un privilège intact :

Security Administrator (Administrateur de la Sécurité)

Equivalent au SuperUser des versions Ingres précédentes, donne tous les droits. Create Database (création de base)

Habilitation à créer des bases. Set Trace Flags (mettre des traces)

Possibilité de faire des "trace flags" ou des "trace points". Operator (Opérateur)

Possibilité d'exécuter checkpoint (point de reprise) et rollforward (recharge de la base à partir de checkpoint) tout comme l'administrateur de la base. Maintain locations

Possibilité de gérer les locations pour l'installation. Audit all activity (journalisation de l'ensemble de l'activité)

Tous les accès d'enregistrements effectués par les usagers ayant ce droit seront écrits dans le fichier d'audit.

On peut aussi les manipuler via les nouvelles commandes SQL :

create/alter/drop user : créer, modifier, détruire un usager

ceci nécessite d'avoir le privilège "Security Administrator" et doit être effectué à partir d'une session connectée à la base maître iidbdb.

Syntaxe des privilèges utilisateurs :

Les usagers ayant le privilège "maintain locations" peuvent gérer les locations :

par accessdb.

ou par les nouveaux ordres SQL :

alter location remplace le précédent usage d'une location et n'est pas cumulatif.

on peut préciser les colonnes qu'un usager peut modifier, mais on ne peut pas préciser les colonnes qu'un usager peut sélectionner.

Les vues fournissent une restriction horizontale et verticale de l'accès aux données usager

Le privilège select sur une vue n'implique pas le privilège select sur les tables sur lesquelles cette vue est basée.

Exemple : permettre à chaque usager de voir les noms et salaires des employés qu'il gère :

Chaque usager voit seulement les articles des employés qu'il gère, son nom étant alors dans la colonne 'gest'.

Ils ne s'appliquent pas aux administrateurs de base sur leurs propres bases.

Ils ne s'appliquent pas aux administrateurs sécurité dans l'installation OpenIngres entière.

Les privilèges peuvent être actifs ou inactifs.

Le niveau base prévaut sur le niveau installation (on peut autoriser localement une permission au niveau base alors qu'elle est ôtée au niveau installation).

Les privilèges sur l'installation OpenIngres sont gérés soit par l'administrateur système OpenIngres, soit par tout usager ayant le privilège "Security Administrator"

Tout ceci doit être effectué dans une session "terminal monitor" connectée à la base iidbdb.
 

[no]access	interdit/autorise l'accès usager
[no]create_table	interdit/autorise la création de tables
[no]create_procedure	interdit/autorise la création de procédures
[no]lockmode	interdit/autorise le changement de l'état du verrouillage
[no]query_row_limit	libère/limite le nombre de lignes qu'une requête peut fournir
[no]query_io_limit	libère/limite le nombre d'entrées-sorties possibles pour une requête
[no]db_admin	interdit/autorise la possibilité d'agir comme un DBA
[no]update_syscat	interdit/autorise la possibilité de modifier les catalogues systèmes

On donne ou enlève ces privilèges en utilisant grant et revoke.

Pour donner les privilèges de base et d'installation :

exemple : la variable dept de commune doit être comprise entre 00 et 98

  Ingres sait gérer en interne des déclenchements automatiques d'ensemble d'actions pré-programmées en langage sql (étendu) lorsque certains événements surviennent dans la base.

Une règle dira lors d'un événement dans la base de données, c'est à dire une insertion, une mise à jour ou une destruction, quel ensemble d'actions entreprendre ; cet ensemble d'actions est contenu dans des procédures, qui sont nommées et paramétrables.

On gère ces objets comme des tables, avec des ordres de création, de description, de destruction, et de gestion des droits (pour les procédures uniquement).

Ces règles permettent de créer des contraintes d'intégrité référentielles : par exemple, interdire la suppression d'un client de la table client s'il a encore des factures en cours dans la table facture.

Une autre utilisation permet, comme nous le verrons, de déclencher des mises à jour en cascade, ce qui peut permettre de retrouver une arborescence (comme la recherche de descendants dans un arbre généalogique).

nom_proc est le nom de la procedure;

nom_param est le nom d'un paramètre de la procédure; les paramètres peuvent être passés par valeur ou par référence;

type_param est le type du paramètre;

declare_section est une liste de variables locales à la procedure;

ordre : ordre SQL

Une procédure peut avoir zéro ou plusieurs paramètres d'entrée (nom_param), dont on doit préciser le type.

On doit indiquer ensuite après l'ordre declare, la liste des variables internes à la procédure, et leur type détaillé. 

Puis vient, à l'intérieur d'un bloc begin-end, la suite des ordres à effectuer. 

Certaines séquences de programmation sont possibles, en dehors des ordres sql usuels, comme les blocs if-then-else et les blocs while-endwhile. 

Deux ordres sql sont interdits seulement pour le déclenchement par des règles : commit, et rollback. 

On peut utiliser également des ordres de mise en erreur de l'événement déclencheur, avec message à l'utilisateur (voir exemples).

Appel d'une procédure en sql :

L'ordre de destruction d'une procédure est le suivant :

Les procédures sont déclenchées par des règles ou à l'intérieur de programmes 

(C, fortran, ..) ou dans des applications Windows4gl.

La procédure a trois paramètres d'entrée (corps, classe, echelon) et deux variables internes check_grille qui ne peut avoir que la valeur 1 ou 0 dans notre cas (existence unique ou absence du triplet corps-classe-echelon fourni) et une variable caractère qui contiendra le message d'erreur fourni à l'utilisateur, s'il a fourni des données erronées. 

On remarquera que les variables locales sont, uniquement à l'intérieur des ordres sql, précédées d'un ":" , afin de différencier colonnes de tables et variables locales.

Voici maintenant un exemple de mise à jour en cascade. Il s'agit de plusieurs arbres généalogiques sur une population, décrit de la façon suivante dans une table :

pers(num, nom, est_enfant_de, rang)

La colonne est_enfant_de contient le numéro (num) du parent, rang est une colonne numérique non renseignée qui nous servira au classement.

Notre problème est de trouver au sein de la population, aussi facilement que possible, tous les descendants d'une personne donnée, avec leur rang de descendance. Ceci sera possible en une seule requête, mais pour le moins étonnante, car nous passerons par une mise à jour (update) du rang de la personne dont on cherche la descendance, pour obtenir automatiquement le classement souhaité.

Nous allons créer un ensemble procédure-règle, qui, lorsque l'on mettra à jour la colonne rang avec la valeur 1 pour un individu donné, rangera ses descendants en augmentant rang de 1 à chaque génération (2 pour les enfants, 3 pour les petits-enfants,..), rang devenant null (manquant) pour tous les autres individus (ascendants ou autres familles). Une seule mise à jour (rang = 1 pour un numéro donné) va déclencher une cascade de mises à jour sur la table pers.

/* recherche de la descendance du numéro 7 */ 

  Avec l'extension objet de OpenIngres, on dispose d'une bibliothèque de nouveaux types de données, les objets spatiaux, qui comprennent le point (point), la boîte (box), le segment de droite (lseg), la ligne et la ligne longue (line et long line), le polygone et le polygone long (polygon et long polygon), le cercle (circle).

Ces types peuvent être utilisés dans les définitions d'attributs de tables. Il leur correspond des opérateurs et des fonctions qui permettent de les manipuler et de faire des opérations de type spatial.
 

  Il consiste en 2 valeurs de coordonnées x et y, chacune étant du type float8.

Exemple d'utilisation :

création d'une table :

  Une boîte est un rectangle dont les côtés sont parallèles aux axes du système de coordonnées. Elle est définie par deux points qui sont le point inférieur gauche et le point supérieur droit, par exemple ((1, 2.25), (5, 6,5)).

Exemple d'utilisation :

création d'une table :

  Un segment est défini par deux éléments : le point début et le point fin, par exemple 

((1, 2), (3.45, -4.0)).

Exemple d'utilisation :

création d'une table :

  Une ligne est définie par deux parties : le nombre de points qu'elle comporte et le tableau des points. Pour définir un attribut de type line, on donnera sa dimension. Pour créer les données d'une ligne, on donnera les coordonnées de ses points.

Remarque : une ligne ne peut avoir plus de 124 points (taille maximale d'un tuple).

Exemple d'utilisation :

création d'une table :

  Un polygone est défini par deux parties, comme le type line : le nombre de points qu'il comporte et le tableau des points. Mais les points doivent respecter la fermeture du polygone. Pour définir un attribut de type polygon, on donnera sa dimension. Pour créer les données d'un polygone, on donnera les coordonnées de ses points.

Remarque : un polygone ne peut avoir plus de 124 points (taille maximale d'un tuple).

Exemple d'utilisation :

création d'une table :

  Un polygone long permet d'étendre la taille maximale d'un polygone à 100 million de points environ, correspondant à un encombrement de 2Goctets.
 

  Un cercle est défini par deux éléments : le point centre du cercle et le rayon, le rayon étant de type float.

Exemple d'utilisation :

création d'une table :

• les opérateurs de comparaison d'égalité = et <>

• les opérateurs spatiaux binaires : inside et intersects

  Il y a trois types de fonctions : 

• les fonctions spatiales, telles que la fonction area, perimeter, etc. 
• les fonctions de conversions spatiales 
• les fonctions spatiales de conversions de types
• les fonctions spatiales

• les fonctions de conversions spatiales

• les fonctions spatiales de conversions de types, décrites par le tableau suivant :