Introduction
Parfois un ordre SQL mal écrit est exécuté sur une base de données sans qu'il soit possible de modifier son texte. Les raisons peuvent être multiples :
          ordre SQL issu du logiciel d'un éditeur tiers disparu
          ordre SQL venant d'un programme interne du client mais aucune nouvelle version ne sera livrée avant plusieurs mois
          ...

Les problèmes dans ces ordres SQL peuvent être nombreux :
          utilisation d'un hint qui ralentit les SELECT
          utilisation dans la clause WHERE d'un code TVA en dur devenu obsolète suite à une nouvelle réglementation; le SELECT ne ramène plus aucun enregistrement car en base le nouveau taux est appliqué
          ... 

Mais on peut aussi vouloir empêcher l'exécution d'un ordre un peu trop curieux par rapport à des informations sensibles : par exemple un compte utilisé par N personnes (donc impossible d'identifier le  coupable) veut afficher le nom et salaire des employés gagnant plus de 100 000€ par an. Dans ce cas là, on veut remplacer l'ordre SQL par un message d'avertissement!

Le DBA est donc obligé de trouver une solution pour remplacer un ordre SQL sur lequel il n'a pas la main!
La technique que je vais vous montrer est un package PL/SQL appelé DBMS_ADVANCED_REWRITE qui, comme son nom l'indique, permet d'intercepter un ordre SQL et de le réécrire sous une autre forme en lui substituant un autre ordre!


Quelques exemples d'utilisation.
Cacher des données sensibles.
Remplacer
          SELECT SALARY FROM EMPLOYEES WHERE SALARY > 100000;
par
          SELECT 0 FROM EMPLOYEES WHERE SALARY > 100000;


Supprimer des hints pour ne pas court-circuiter l'expertise du CBO.
Remplacer
          SELECT /*+ INDEX(EMPLOYEES emp_job_ix) */ first_name from employees where job_id > 'B';
par
          SELECT first_name from employees where job_id > 'B';


Ajouter des hints lorsque le développeur a de très bonnes raisons de court-circuiter l'expertise du CBO.
Remplacer
          SELECT first_name from employees where job_id > 'B';
par
          SELECT /*+ INDEX(EMPLOYEES emp_job_ix) */ first_name from employees where job_id > 'B';


Remplacer un critère de recherche devenu obsolète suite à une nouvelle règlementation.
Remplacer
          SELECT first_name from employees where commission_pct = 0.15;
par
          SELECT first_name from employees where commission_pct = 0.18;




Points d'attention
Ce package est très puissant et vous pouvez rendre fou les utilisateurs de la base! Donc soyez sérieux et évitez de l'utiliser pour faire des blagues.
Par exemple vous pouvez remplacer
          SELECT FIRST_NAME FROM EMPLOYEES;
par
          SELECT DEPARTMENT_NAME FROM DEPARTMENTS;
Vous imaginez la surprise et la colère des utilisateurs à votre encontre?




Base de tests
N'importe quelle base Oracle.

 



Exemples
============================================================================================
Pré-requis à mettre en place.

============================================================================================
Les pré-requis à l'utilisation de DBMS_ADVANCED_REWRITE sont nombreux.
Le user qui va créer les requêtes de remplacement doit recevoir plusieurs droits; il s'agit du user HR ci-dessous.
          SQL> GRANT QUERY REWRITE TO HR;
          SQL> GRANT EXECUTE ON DBMS_ADVANCED_REWRITE TO HR;
          SQL> GRANT CREATE MATERIALIZED VIEW TO HR;

Ensuite le user HR doit modifier sa session.
          SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
          SQL> ALTER SYSTEM SET QUERY_REWRITE_ENABLED = FORCE;

On vérifie que les paramètres de REWRITE ont bien été modifiés.
          SQL> SHOW PARAMETER REWRITE
          NAME                                  TYPE           VALUE
          ------------------------------------ ----------- ------------------------------
          query_rewrite_enabled           string           FORCE
          query_rewrite_integrity           string           TRUSTED

============================================================================================
Déclaration et test de la requête à remplacer par sa requête équivalente.

============================================================================================

Pour notre test, nous voulons empêcher qui que ce soit de sélectionner le nom des employés et remplacer ce nom par un message d'avertissement "Forget it!".

Requête et données à cacher.
          SQL> select name from emp;
          NAME
          --------------------------------------------------
          DUBOIS
          RUBIS
          DUPONT
          DUPAINT
          RUPRAT

Lister les requêtes de remplacement existantes.
Avant de créer une nouvelle requête de remplacement, on affiche celles existantes pour donner un nouveau nom et ne pas remplacer une requête déjà  traitée. La table à inspecter s'appelle DBA_REWRITE_EQUIVALENCES.
          SQL> desc DBA_REWRITE_EQUIVALENCES;
          Nom                     NULL ?                     Type
          ----------------------------------------- -------- ----------------------------
          OWNER               NOT NULL                 VARCHAR2(30)
          NAME                  NOT NULL                  VARCHAR2(30)
          SOURCE_STMT                                    CLOB
          DESTINATION_STMT                             CLOB
          REWRITE_MODE                                 VARCHAR2(10)

          SQL> select * from DBA_REWRITE_EQUIVALENCES;
          OWNER   NAME                             SOURCE_STMT                                                                      
          ------------------------------ ------------------------------ ---------------------------------------------------
          HR            NEW_REQUETE100       SELECT NAME FROM HR.EMP WHERE SALARY >= 100000     

          DESTINATION_STMT                                                                        REWRITE_MODE
          ------------------------------ ------------------------------ --------------------------------------------------------------------

          SELECT 'CETTE REQUETE EST INTERDITE!' FROM HR.EMP            TEXT_MATCH

Créer la requête de remplacement.
Le package utilisé est SYS.DBMS_ADVANCED_REWRITE et la fonction de ce package est DECLARE_REWRITE_EQUIVALENCE. Le nom de la requête équivalente à créer a le nom "new_query". ATTENTION, les deux requêtes ne doivent pas se terminer par un point virgule!
          SQL> exec sys.dbms_advanced_rewrite.declare_rewrite_equivalence( 'new_query', 'select name from emp', 'select ''Forget it!'' from emp', false);
          Procédure PL/SQL terminée avec succès.

Et voilà, ça marche!
          SQL> select name from emp;
          NAME
          --------------------
          Forget it!
          Forget it!
          Forget it!
          Forget it!
          Forget it!

Supprimer les polices existantes.
          SQL> EXEC SYS.DBMS_ADVANCED_REWRITE.drop_rewrite_equivalence (name => 'new_query');

============================================================================================
Limites et erreurs de DBMS_ADVANCED_REWRITE

============================================================================================

Le package DBMS_ADVANCED_REWRITE est très puissant car il permet d'intercepter un ordre SQL et de le remplacer par un autre. Néanmoins ce package a plusieurs limites.

Les limites
Pas de prise en compte des bind variables.
La bind variable SAL fait échouer l'ordre suivant.
          SQL> EXEC SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE('NEWQUERY','SELECT NAME FROM HR.EMP WHERE SALARY = :SAL','SELECT ''CETTE REQUETE EST INTERDITE!'' FROM HR.EMP', false);
          BEGIN SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE('NEWQUERY','SELECT NAME FROM HR.EMP WHERE SALARY = :SAL','SELECT ''CETTE REQUETE EST INTERDITE!'' FROM HR.EMP', false); END;         
          *

          ERREUR à la ligne 1 :
          ORA-30353: expression non prise en charge pour les réécritures d'interrogation
          ORA-06512: à "SYS.DBMS_ADVANCED_REWRITE", ligne 29
          ORA-06512: à "SYS.DBMS_ADVANCED_REWRITE", ligne 185
          ORA-06512: à ligne 1

Même nombre de colonnes obligatoire entre les deux requêtes.
Le test suivant échoue car la première requête comprend deux colonnes alors que la requête de remplacement n'en a qu'une seule.
          SQL> EXEC SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE('NEWQUERY','SELECT NAME, SALARY FROM HR.EMP','SELECT ''CETTE REQUETE EST INTERDITE!'' FROM HR.EMP', false);
          BEGIN SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE('NEWQUERY','SELECT NAME, SALARY FROM HR.EMP','SELECT ''CETTE REQUETE EST INTERDITE!'' FROM HR.EMP', false); END;

          *
          ERREUR à la ligne 1 :
          ORA-30389: l'instruction source n'est pas compatible avec l'instruction de destination
          ORA-01789: le bloc interrogation contient un nombre incorrect de colonnes résultat
          ORA-06512: à "SYS.DBMS_ADVANCED_REWRITE", ligne 29
          ORA-06512: à "SYS.DBMS_ADVANCED_REWRITE", ligne 185
          ORA-06512: à ligne 1


Le message affiché doit être de même type que la ou les colonnes de l'ordre SQL à remplacer.
Le test suivant échoue car on veut remplacer le champ salary, NUMBER, par une chaîne de caractères.
          SQL> desc hr.emp
          Nom NULL                     ?                     Type
          ----------------------------------------- -------- ----------------------------
          NAME                                                   VARCHAR2(50 CHAR)
          FIRST_NAME                                        VARCHAR2(50 CHAR)
          SALARY                                               NUMBER(8,2)
          HIRE_DATE                                           DATE

          BEGIN
                    SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE(
                    'NEW REQUETE',
                    'SELECT SALARY FROM HR.EMP',
                    'SELECT ''CETTE REQUETE EST INTERDITE!'' FROM HR.EMP',
                    false);
          END;
          /

          BEGIN
          *
          ERREUR à la ligne 1 :
          ORA-30389: l'instruction source n'est pas compatible avec l'instruction de destination
          ORA-01790: une expression doit être du même type que l'expression qui lui correspond
                    ORA-06512: à "SYS.DBMS_ADVANCED_REWRITE", ligne 29
          ORA-06512: à "SYS.DBMS_ADVANCED_REWRITE", ligne 185
          ORA-06512: à ligne 2


Deux erreurs bêtes à éviter!
Pas d'espace dans le nom de la nouvelle requête.
Le test suivant est KO car le nom de la requête ne doit pas avoir d'espace : 'NEW REQUETE' doit être remplacé par 'NEW_REQUETE' avec un underscore.
          BEGIN
                    SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE(
                    'NEW REQUETE',
                    'SELECT NAME FROM HR.EMP WHERE SALARY >= 100000',
                    'SELECT ''CETTE REQUETE EST INTERDITE!'' FROM HR.EMP',
                    false);
          END;
          /

          BEGIN
          *
          ERREUR à la ligne 1 :
          ORA-00905: mot-clé absent
          ORA-06512: à "SYS.DBMS_ADVANCED_REWRITE", ligne 29
          ORA-06512: à "SYS.DBMS_ADVANCED_REWRITE", ligne 185
          ORA-06512: à ligne 2

Pas de point virgule dans les requêtes.
Le test suivant est KO car les deux requêtes se terminent par un point-virgule (oui, je sais, ils sont durs à voir!).
          SQL> EXECUTE SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE('NEW_REQUETE100', 'SELECT NAME FROM HR.EMP WHERE SALARY >= 100000;', 'SELECT ''CETTE REQUETE EST INTERDITE!'' FROM HR.EMP;', false);
          BEGIN SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE('NEW_REQUETE100', 'SELECT NAME FROM HR.EMP WHERE SALARY >= 100000;', 'SELECT ''CETTE REQUETE EST INTERDITE!'' FROM HR.EMP;', false); END;
          *
          ERREUR à la ligne 1 :
          ORA-00911: caractère non valide
          ORA-06512: à "SYS.DBMS_ADVANCED_REWRITE", ligne 29
          ORA-06512: à "SYS.DBMS_ADVANCED_REWRITE", ligne 185
          ORA-06512: à ligne 1