4D v11 SQL et les schémas SQL

4D v11 SQL et les schémas SQL
Par Timothy PENNER, Technical Services Team Member, 4D Inc.
Note technique 4D-200901-01-FR
Version 1 - Date 1 janvier 2009
Résumé
Note traduite de l'anglais par Stéphanie Tirtiat (4D S.A.S., Support Technique International).
Cette note technique décrit les concepts généraux des schémas dans la version 3 de 4D v11 SQL (11.3), et
ultérieure. Elle vous fournit des exemples pour créer, modifier et supprimer des schémas. Une base exemple
sous forme de composant propose un moyen encore plus simple pour le développeur de créer et modifier des
schémas. Ces informations devraient permettre aux développeurs 4D v11 SQL d'aborder et d'utiliser les
schémas SQL.
4D Notes techniques
Copyright © 1985-2009 4D SAS - Tous droits réservés
Tous les efforts ont été faits pour que le contenu de cette note technique présente le maximum de fiabilité possible.
Néanmoins, les différents éléments composant cette note technique, et le cas échéant, le code, sont fournis sans garantie d'aucune sorte.
L'auteur et 4D S.A. déclinent donc toute responsabilité quant à l'utilisation qui pourrait être faite de ces éléments, tant à l'égard de leurs
utilisateurs que des tiers.
Les informations contenues dans ce document peuvent faire l'objet de modifications sans préavis et ne sauraient en aucune manière engager 4D
SA. La fourniture du logiciel décrit dans ce document est régie par un octroi de licence dont les termes sont précisés par ailleurs dans la licence
électronique figurant sur le support du Logiciel et de la Documentation afférente. Le logiciel et sa documentation ne peuvent être utilisés, copiés
ou reproduits sur quelque support que ce soit et de quelque manière que ce soit, que conformément aux termes de cette licence.
Aucune partie de ce document ne peut être reproduite ou recopiée de quelque manière que ce soit, électronique ou mécanique, y compris par
photocopie, enregistrement, archivage ou tout autre procédé de stockage, de traitement et de récupération d'informations, pour d'autres buts que
l'usage personnel de l'acheteur, et ce exclusivement aux conditions contractuelles, sans la permission explicite de 4D SA.
4D, 4D Calc, 4D Draw, 4D Write, 4D Insider, 4ème Dimension ®, 4D Server, 4D Compiler ainsi que les logos 4e Dimension, sont des marques
enregistrées de 4D SA.
Windows,Windows NT,Win 32s et Microsoft sont des marques enregistrées de Microsoft Corporation.
Apple, Macintosh, Power Macintosh, LaserWriter, ImageWriter, QuickTime sont des marques enregistrées ou des noms commerciaux de Apple
Computer,Inc.
Mac2Win Software Copyright © 1990-2002 est un produit de Altura Software,Inc.
4D Write contient des éléments de "MacLink Plus file translation", un produit de DataViz, Inc,55 Corporate drive,Trumbull,CT,USA.
XTND Copyright 1992-2002 © 4D SA. Tous droits réservés.
XTND Technology Copyright 1989-2002 © Claris Corporation.. Tous droits réservés ACROBAT © Copyright 1987-2002, Secret Commercial
Adobe Systems Inc.Tous droits réservés. ACROBAT est une marque enregistrée d'Adobe Systems Inc.
Tous les autres noms de produits ou appellations sont des marques déposées ou des noms commerciaux appartenant à leurs propriétaires
respectifs.
1 / 12
4D v11 SQL et les schémas SQL
Introduction
La troisième édition de 4D v11 SQL (11.3) apporte son lot d'améliorations et d'implémentations pour la
gamme de produit 4D v11 SQL. Une fonctionnalité particulièrement utile est le support des schémas par
l'interpréteur SQL intégré dans cette gamme. L'implémentation de cette fonction a suscité des modifications
dans l'interface et l'introduction de nouvelles commandes SQL. Cette note technique décrit ces changements
de l'interface et explique comment utiliser chacune des nouvelles commandes du langage SQL. Nous
comparerons également la gestion des droits d'accès entre cette version 11.3 et les versions précédentes. Une
base exemple accompagne cette note technique.
Qu’est-ce que les schémas SQL ?
Les schémas SQL sont une manière de sécuriser les accès extérieurs - par exemple via ODBC ou une
connexion SQL - à votre base de données.
Comparaison avec les versions précédentes (11.0, 11.1, 11.2)
Avant la version 11.3 de 4D v11 SQL, les droits d’accès pour les connexions extérieures via SQL ou ODBC
ne pouvaient s’appliquer que globalement pour toute la base de données. Ces accès se paramétraient dans les
préférences SQL.
2 / 12
Préférences SQL dans les versions 11.0, 11.1 et 11.2
A partir de la version 4D v11.3 SQL, les droits d'accès pour les connexions externes sont définies par
schéma et chaque table peut être configurée pour utiliser un schéma. Les paramètres accessibles dans les
préférences SQL ne sont maintenant utilisées que pour le schéma par défaut, DEFAULT_SCHEMA, qui est
appliqué automatiquement à chaque table nouvelle créée ainsi qu'à toute table qui existait dans la base de
données avant la mise à jour vers cette troisième édition de 4D v11 SQL.
Chaque schéma a trois droits d’accès qui peuvent être assigné chacun à un groupe d’utilisateurs :
• Lecture seulement (Données)
• Lecture/Ecriture (Données)
• Complet (Données & Structure)
Note :
comme dans les versions précédentes, le contrôle d'accès ne s'applique qu'aux connexions provenants de
l'extérieur. Les commandes internes Debut SQL/Fin SQL, EXECUTER SQL, CHERCHER PAR
SQL,… gardent un accès total aux tables.
Note du traducteur :
Pour information, à partir de la version 11.3, une nouvelle option Autoriser les requêtes Flash Player,
3 / 12
permet d’activer le mécanisme de prise en charge des requêtes Flash Player par le serveur SQL de 4D (voir
addendum pour plus d'information 4D v11 addendum r3).
Pourquoi utiliser les schémas ?
Les schémas devraient être utilisés dans toute base de données qui est sensée être interrogée par des sources
extérieures - via SQL ou ODBC par exemple - dans un souci de sécurité des données.
De plus, les schémas sont assez simples à utiliser et à paramétrer, soit par le code SQL soit à l’aide de
composants.
Comment fonctionnent-ils ?
Un schéma est un objet virtuel contenant des tables.
En SQL, le but des schémas est d’assigner des droits d’accès spécifiques à différents ensembles d’objets de
la base. Toute table, vue, procédure est en fait toujours créée dans un schéma.
Les schémas divisent la base de données en entités indépendantes. En d’autres termes, une table appartient
toujours à un seul schéma.
Quand une base de données est créée ou convertie en 4D v11.3 SQL (ou version ultérieure), un schéma par
défaut est créé et regroupe toutes les tables de la base : DEFAULT_SCHEMA. Il ne peut être ni supprimé ni
renommé.
Les modifications du DEFAULT_SCHEMA peuvent se faire soit par le code SQL soit par le dialogue des
préférences SQL.
4 / 12
Préférences SQL dans la version 11.3
Note :
Seuls le Super_Utilisateur et l’Administrateur de la base de données peuvent créer, modifier et supprimer des
schémas. Si aucun mot de passe n’est nécessaire pour ouvrir la base, alors tout utilisateur peut accéder aux
schémas sans restriction.
Les schémas sont créés, modifiées et supprimés via les commandes SQL. Un nouveau menu popup dans
l’inspecteur de structure permet d’assigner un schéma à une table.
5 / 12
Créer un schéma
Pour créer un schéma, on utilise cette syntaxe :
Debut SQL
CREATE SCHEMA monSchema;
Fin SQL
Lecode ci-dessus va créer un schéma nommé monSchema.
Modifier un schéma
Un schéma se modifie par programmation en utilisant les commandes SQL.
Pour rappel, le schéma par défaut, DEFAULT_SCHEMA peut être modifié par programmation mais aussi
par le dialogue des préférences SQL.
Les paragraphes suivants insistent sur la syntaxe spécifique de ces diverses opérations.
Renommer un schéma
Renommer un schéma se fait de cette manière :
Debut SQL
ALTER SCHEMA monSchema RENAME TO SchemaTest;
6 / 12
Fin SQL
Le code ci-dessus renomme le schéma monSchema en SchemaTest.
Accorder des droits de lecture
Accorder des droits de lecture sur un schéma se fait de cette manière :
Debut SQL
GRANT READ ON SchemaTest to Groupe_Utilisateurs;
Fin SQL
On accorde dans cet exemple des droits de lecture sur le schéma pour le groupe nommé
<Groupe_Utilisateurs>.
Note :
Attention aux caractères utilisés dans le nom du groupe. S’il comporte des espaces ou des accents, il faudra
respecter la règle des noms_sql et utiliser les crochets.
http://www.4d.fr/documentation/4DdocV11/CMF/CMF18480.HTM
Par exemple :
GRANT READ ON SchemaTest to [Groupe des Développeurs];
Accorder des droits de lecture et écriture
Accorder des droits de lecture et écriture sur un schéma se fait de cette manière :
Debut SQL
GRANT READ WRITE ON SchemaTest to Groupe_ChefsDeProjets;
Fin SQL
Le code ci-dessus accorde un accès en lecture et écriture sur le schéma nommé SchemaTest pour le groupe
nommé <Groupe_ChefsDeProjets>.
Accorder tous les droits
Accorder tous les droits sur un schéma se fait de cette manière :
Debut SQL
7 / 12
GRANT ALL ON SchemaTest to Groupe_Developpeurs;
Fin SQL
Le code ci-dessus accorde un accès total au groupe nommé <Groupe_Developpeurs> pour le schéma
nommé SchemaTest.
Note :
Dans la version courante de 4D v11 SQL, il n’est pas possible de donner accès au pseudo-groupe <Tout le
monde>.
• Une solution est de créer un groupe contenant tous les utilisateurs et de donner les accès à ce groupe ;
• Une autre solution est de recréer le schéma puisque les nouveaux schémas créés utilisent le groupe
<Tout le monde> pour les accès READ et READ_WRITE.
Révoquer les droits d’accès
Révoquer un accès à un schéma équivaut à définir le niveau d'accès spécifié à <Personne>. Cela se fait de
cette manière :
Debut SQL
REVOKE READ WRITE ON SchemaTest;
Fin SQL
Ce code révoque les droits de lecture/écriture pour le schéma nommé SchemaTest, c’est-à-dire que
READ_WRITE est appliqué à <Personne>.
Appliquer un schéma
Les schémas créés peuvent être appliqués aux tables :
• soit par l’éditeur de structure via l’inspecteur,
• soit en utilisant la commande SQL ALTER TABLE.
Pour appliquer un schéma par code :
Debut SQL
ALTER Table Table_1 SET SCHEMA SchemaTest;
Fin SQL
Le code ci-dessus applique le schéma SchemaTest pour la table [Table_1].
8 / 12
La copie d'écran ci-dessous montre la nouvelle propriété dans la fenêtre de l'inspecteur pour appliquer un
schéma par la structure :
Supprimer un schéma
Supprimer un schéma se fait de cette manière :
Debut SQL
DROP SCHEMA SchemaTest;
Fin SQL
Le code ci-dessus supprime le schéma nommé SchemaTest.
D'autres façons de sécuriser votre base de données
En plus des droits d'accès globaux disponibles dans les versions précédentes de 4D v11 SQL, vous avez
aussi la possibilité d’utiliser la méthode base Sur Authentification SQL pour sécuriser votre base de
données. Cette méthode base a été introduite à partir de la version 4D v11.2. Elle permet de capter les
connexions SQL et de procéder à des authentifications. Ce mécanisme est bien sûr toujours disponible dans
la version 4D v11.3 SQL, et ultérieure.
Méthode base Sur authentification SQL
http://www.4d.fr/documentation/4DdocV11/CMF/CMF10034.HTM
Extrait de la documentation :
9 / 12
La Méthode base Sur authentification SQL permet de filtrer les requêtes adressées au serveur SQL intégré
de 4D. Le filtrage peut être effectué sur la base du nom, du mot de passe ainsi que (facultativement) de
l'adresse IP de l'utilisateur.
(...)
Lorsqu'elle existe, la Méthode base Sur authentification SQL est automatiquement appelée par 4D ou 4D
Server à chaque connexion externe au serveur SQL.
La base exemple
La base exemple qui accompagne cette note technique se présente en deux parties :
- la base matrice comportant le source du composant
- la base-composant
La base-composant peut être intégrée dans une base 4D v11.3 SQL (ou ultérieure) existante. Il vous
permettra de visualiser et de modifier plus aisément les schémas de la base hôte. La méthode composant
"SE_OPEN_SCHEMA_EDITOR" est utilisée pour ouvrir l'interface de l'éditeur.
La copie d'écran ci-dessous montre l'éditeur de schémas ouvert dans une base hôte qui n'a que le schéma par
défaut DEFAULT_SCHEMA :
La copie d'écran ci-dessous montre l'éditeur de schéma ouvert dans la base hôte qui possède quelques
schémas créés par l'utilisateur :
10 / 12
Ajouter un schéma
Avec ce composant "Schema Editor", vous pouvez ajouter un schema via l'interface utilisateur. Pour cela,
cliquez simplement sur le bouton "Ajouter" dans l'éditeur de schémas pour obtenir le dialogue suivant :
Modifier un schéma
Avec ce même composant, vous pouvez modifier un schéma existant en double-cliquant sur le schéma que
vous voulez modifier. Le dialogue suivant s'affichera :
11 / 12
Notes :
Comme déjà écrit plus haut, il n’est pas possible de donner accès au pseudo-groupe <Tout le monde> dans
la version courante de 4D v11 SQL. Si vous essayez, vous aurez un message d'alerte. Une solution est de
créer un groupe contenant tous les utilisateurs et de donner les accès à ce groupe.
Les schémas nouvellement créés utilisent aussi le groupe <Tout le monde> pour les accès en lecture et en
lecture-écriture. Donc, recréer simplement le schéma est une autre solution. Cependant, supprimer un
schéma aura pour conséquence que toutes les tables qui appartenaient à ce schéma reviendront au schéma par
défaut DEFAULT_SCHEMA. Vous aurez alors besoin de réassigner le schéma pour chaque table revenue
au schéma par défaut.
NDT :
Le composant 4DPop offre aussi la possibilité de créer, modifier et appliquer ces schémas par son module
4D Pop sqlSchemas.
http://www.4d.fr/products/4dpop.html
Extrait de la documentation :
Etablissez via une interface graphique les schémas SQL utilisés lors des connexions externes en mode SQL
(ODBC, SQL Pass-through natif, 4D for Flex, etc.). Les schémas sont pris en compte lorsque la méthode de
base Sur Authentification SQL est définie.
Conclusion
Cette note technique vous a décrit les concepts généraux des schémas dans la version 3 de 4D v11 SQL
(11.3), et ultérieure. Elle vous a fourni des exemples pour créer, modifier et supprimer des schémas. Une
base exemple sous forme de composant propose un moyen plus simple pour le développeur de créer et
modifier des schémas. Ces informations devraient permettre aux développeurs 4D v11 SQL d'aborder et
d'utiliser les schémas SQL.
12 / 12