Annuler des opérations DML sur une vue avec un trigger et le mot clé NULL - Cancel DML operations on a view with a trigger
Introduction
Nous allons voir dans cet article comment annuler au niveau d'une vue certaines opérations DML et ce sans avoir à annuler les droits sur la table sous-jacente. En effet, si Oracle liste dans son dictionnaire de données qui a reçu tel droit, via quel user, sur quel objet etc etc, en revanche il ne mémorise pas les REVOKEs! Et donc si on révoque les droits DML sur une vue ou table, il sera impossible de revenir en arrière à moins de développer des programmes pour justement enregistrer les REVOKEs, mais j'estime que c'est trop lourd à mettre en place et que c'est une méthode. comment dire, sans finesse!
La solution que je vous propose est bien plus élégante car non seulement le plus gros du travail est fait par Oracle et le reste consiste en un seul mot clé, magique, qui est NULL :-)
Points d'attention
NA.
Base de tests
N'importe quelle base Oracle. Testé sur une 12.1.
Exemples
L'environnement de test
Notre table de test et sa vue sont hyper basiques.
SQL> CREATE TABLE TEST_VUE01 (ID NUMBER, NOM VARCHAR2(30 CHAR), PRENOM VARCHAR2(30 CHAR), VILLE VARCHAR2(30 CHAR));
Table TEST_VUE01 created.
On fait quelques INSERTs.
SQL> SELECT * FROM test_vue01 ORDER BY id;
ID NOM PRENOM VILLE
---------- ------------------------------ ------------------------------ ---------
1 DUPONT MARC TOURS
2 DUPONDERRE PHILIPPE BORDEAUX
3 MARCELAINE PIERRE BORDEAUX
Maintenant on crée la vue sur la table.
SQL> CREATE VIEW view_test01 AS SELECT nom, prenom FROM test_vue01;
View VIEW_TEST01 created.
SQL> SELECT * FROM view_test01 ORDER BY nom;
NOM PRENOM
------------------------------ ------------------------------
DUPONDERRE PHILIPPE
DUPONT MARC
MARCELAINE PIERRE
Les opérations DML sont OK sur la vue. INSERT OK car aucun champ absent de la vue n'est PK ou NOT NULL, idem pour DELETE, UPDATE.
SQL> INSERT INTO view_test01 VALUES ('MARCOUL', 'PIERROT');
1 row inserted.
SQL> DELETE FROM view_test01 WHERE nom = 'DUPONT';
1 row deleted.
SQL> UPDATE view_test01 SET prenom = 'DAVID' WHERE prenom = 'PHILIPPE';
1 row updated.
Interdire toutes les opérations DML : mettre la table ou vue en mode READ ONLY
Notre objectif final est d'interdire les INSERTs et DELETEs sur la vue mais pas les UPDATEs. Attention, je ne parle pas de la table mais que de l'objet auquel les utilisateurs ont accés, c'est à dire la vue.
Voyons d'abord ce qu'on aurait pu faire si on avait voulu interdire toutes les opérations DML. La solution la plus simple est de mettre la table en READ ONLY.
Test sur la vue? Ah non, c'est KO, ma vue n'a pas été créée avec la bonne option...
SQL> ALTER VIEW view_test01 READ ONLY;
ALTER VIEW view_test01 READ ONLY
*
ERROR at line 1:
ORA-42310: Implementation restriction: this operation is only permitted on Editioning views
Bon, ce n'est pas grave, on va travailler sur la table.
SQL> ALTER TABLE test_vue01 READ ONLY;
Table TEST_VUE01 altered.
On reteste les opérations sur la table et la vue avec cette configuration.Comme prévu, tous les ordres DML sont impossibles à exécuter sur la table puisqu'elle est en READ ONLY.
SQL> INSERT INTO test_vue01 VALUES (3, 'DUPART', 'PIERRE');
INSERT INTO test_vue01 VALUES (3, 'DUPART', 'PIERRE')
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "HR"."TEST_VUE01"
SQL> DELETE FROM test_vue01;
DELETE FROM test_vue01
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "HR"."TEST_VUE01"
SQL> UPDATE test_vue01 SET nom = 'TEST';
UPDATE test_vue01 SET nom = 'TEST'
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "HR"."TEST_VUE01"
Idem sur la vue, opérations impossibles et on a bien le nom de l'objet sur lequel pointe la vue. Seul souci, Oracle nous parle d'"update operation" dans chaque cas mais bon, on ne va pas pinailler.
SQL> INSERT INTO view_test01 VALUES ('MARCOUL', 'PIERROT');
INSERT INTO view_test01 VALUES ('MARCOUL', 'PIERROT')
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "HR"."TEST_VUE01"
SQL> DELETE FROM view_test01 WHERE nom = 'DUPONT';
DELETE FROM view_test01 WHERE nom = 'DUPONT'
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "HR"."TEST_VUE01"
SQL> UPDATE view_test01 SET prenom = 'DAVID' WHERE prenom = 'PHILIPPE';
UPDATE view_test01 SET prenom = 'DAVID' WHERE prenom = 'PHILIPPE'
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "HR"."TEST_VUE01"
Conclusion : le fait de mettre la table en READ ONLY empêche bien toutes les modifications sur la table, en direct ou via la vue.
Interdire seulement certaines opérations DML : utilisation d'un trigger
Maintenant, on change le besoin, sinon c'est trop facile, mettre une table en READ ONLY est à la portée de n'importe qui :-)
Notre besoin est maintenant de pouvoir faire un UPDATE sur la table, via la vue mais en interdisant les INSERTs et DELETEs toujours via la vue; attention, je ne parle pas des opérations sur la table, celles-ci sont gérées via des GRANTs mais ça dépasse le périmètre de cet article.
Pour répondre à ce besoin, il faut trouver une autre solution que mettre en READ ONLY la table!
On commence par remettre la table en mode READ WRITE.
ALTER TABLE test_vue01 READ WRITE;
Table altered.
SQL> INSERT INTO view_test01 VALUES ('MARCOUL', 'PIERROT');
1 row created.
Et maintenant, la solution que je vous propose est de créer un trigger sur les évènements DELETE et INSERT de la vue.
Faites bien attention au code qui sera exécuté, c'est amusant :-)
SQL> CREATE OR REPLACE TRIGGER TRIG_MAJ_view_test01 INSTEAD OF INSERT OR DELETE ON view_test01
BEGIN
null;
END;
/
Trigger created.
Allez, on reteste. Voici le contenu de la vue avant mes tests.
SQL> SELECT * FROM view_test01 ORDER BY nom;
NOM PRENOM
------------------------------ ------------------------------
DUPONDERRE DAVID
MARCELAINE PIERRE
MARCOUL PIERROT
MARCOUL PIERROT
L'INSERT est bien inactif :-)
Vous avez vu le piège? Oracle affiche "1 row created." mais rien n'a été créé.
SQL> INSERT INTO view_test01 VALUES ('GRABER', 'LUCIE');
1 row created.
SQL> commit;
Commit complete.
SQL> SELECT * FROM view_test01 ORDER BY nom;
NOM PRENOM
------------------------------ ------------------------------
DUPONDERRE DAVID
MARCELAINE PIERRE
MARCOUL PIERROT
MARCOUL PIERROT
SQL> SELECT * FROM test_vue01;
ID NOM PRENOM VILLE
---------- ------------------------------ ----------------------
2 DUPONDERRE DAVID BORDEAUX
3 MARCELAINE PIERRE BORDEAUX
MARCOUL PIERROT
MARCOUL PIERROT
Le DELETE est lui aussi neutralisé, avec encore un message non adéquat :-)
SQL> DELETE view_test01 WHERE nom = 'MARCELAINE';
1 row deleted.
SQL> commit;
Commit complete.
SQL> SELECT * FROM test_vue01;
ID NOM PRENOM VILLE
---------- ------------------------------ ----------------------
2 DUPONDERRE DAVID BORDEAUX
3 MARCELAINE PIERRE BORDEAUX
MARCOUL PIERROT
MARCOUL PIERROT
Quid de l'UPDATE? Il est OK, mon trigger fonctionne donc parfaitement bien!
SQL> UPDATE view_test01 SET prenom = 'NATHALIE' WHERE prenom = 'DAVID';
1 row updated.
SQL> commit;
Commit complete.
SQL> SELECT*FROM test_vue01;
ID NOM PRENOM VILLE
---------- ------------------------------ -------------------
2 DUPONDERRE NATHALIE BORDEAUX
3 MARCELAINE PIERRE BORDEAUX
MARCOUL PIERROT
MARCOUL PIERROT
La beauté de la solution c'est d'utiliser le INSTEAD OF du trigger sur vue et laisser Oracle annuler les INSERTs et DELETEs.
Si on avait fait un trigger sur table, un de ses mots clé est BEFORE ou AFTER, ce qui indique bien que Oracle exécutera le trigger EN PLUS de l'ordre SQL (sauf si un des deux échoue). Mais dans le cas d'un trigger sur vue, pas de BEFORE ou AFTER mais INSTEAD OF, qui se traduit par "Au lieu de", "En remplacement de"; ceci signifie que Oracle exécute le trigger en remplacement de l'ordre SQL! C'est cool, plus besoin d'annuler l'ordre SQL, Oracle ne l'exécute même pas!
Pourquoi avoir mis le NULL dans le body? Tout simplement parce qu'Oracle ne compile pas un bloc PL/SQL dont le body est vide. Et donc quelle commande mettre autre que NULL pour justement ne rien faire?
Testons ce que je viens de dire : body vide --> erreur Oracle :-)
SQL> CREATE OR REPLACE TRIGGER TRIG_MAJ_view_test01 INSTEAD OF UPDATE ON view_test01
BEGIN
END;
/
Warning: Trigger created with compilation errors.
SQL> show errors
Errors for TRIGGER TRIG_MAJ_VIEW_TEST01:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1 PLS-00103: Encountered the symbol "END" when expecting one of the
following:
( begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge
json_exists json_value json_query json_object json_array
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/https%3A%2F%2Fprofilepics.canalblog.com%2Fprofilepics%2F1%2F5%2F1555182.jpg)