Introduction
L'outil SQL*Plus est incontournable dans la panoplie d'un DBA. Outil historique d'Oracle, il est puissant, rapide, léger, pas toujours exempt de problèmes (ce satané LINESIZE!) mais c'est vers lui que vous devez vous tourner si vous voulez travailler au plus près de votre base. Il existe d'autres logiciels, plus conviviaux, avec une belle interface graphique mais qui parfois agrègent ou filtrent les données du dictionnaire de données, comme le Cloud Control, SQL Developer, Toad...

Cet article va s'intéresser à un point de sécurité : comment interdire des commandes SQL, SQL*Plus ou PL/SQL dans SQL*Plus pour certains utilisateurs. Imaginons une entreprise où les responsables de la sécurité veulent vous obliger à accéder à la base via un logiciel qu'ils ont développé, avec des utilisateurs précis, des droits associés bien restrictifs et l'exécution uniquement de requêtes pré-saisies. De la sorte, ils contrôlent parfaitement vos actions sur la base et peuvent vous interdire de faire des DELETE, UPDATE ou même un SELECT avec ORDER BY. Malheureusement s'ils ne peuvent pas désinstaller SQL*Plus, alors les utilisateurs avertis pourront contourner cette politique de sécurité en se connectant directement à la base avec cet outil, qui fera ainsi office de quasi backdoor, pour lancer leurs commandes sans aucune contrainte.

Pour rétablir la sécurité et interdire certaines commandes sous SQL*Plus, Oracle a mis au point une fonctionnalité très simple se basant sur la table PRODUCT_USER_PROFILE. Vous me direz, mais pourquoi ne pas interdire ces opérations avec un REVOKE? Tout simplement parce qu'on souhaite que ces droits soient maintenus pour les users via l'application qu'ils doivent utiliser (SQL Developer, Toad ou une appli interne).


 

Points d'attention
NA.

 


 

Base de tests
N'importe quelle base Oracle

 



Exemples
============================================================================================
La table PRODUCT_USER_PROFILE
============================================================================================

La table PRODUCT_USER_PROFILE, du schéma SYSTEM, permet d'interdire des actions en insérant une ligne dedans, tout simplement. A chaque user qui se connecte, SQL*Plus regarde les règles qui lui sont associées dans cette table et les applique.

Il existe pourtant quelques restrictions :
          - les users se connectant avec les privilèges SYSDBA et SYSOPER ne sont pas concernés (dieu merci!)
          - seule une base locale peut être protégée ainsi, pas une base distante accessible via un DB Link

Voici la structure de la table.
          SQL> desc system.PRODUCT_USER_PROFILE
          Nom                                       NULL ?   Type
          ----------------------------------------- -------- ------------
          PRODUCT                                   NOT NULL VARCHAR2(30)
          USERID                                             VARCHAR2(30)
          ATTRIBUTE                                          VARCHAR2(240)
          SCOPE                                              VARCHAR2(240)
          NUMERIC_VALUE                                      NUMBER(15,2)
          CHAR_VALUE                                         VARCHAR2(240)
          DATE_VALUE                                         DATE
          LONG_VALUE                                         LONG

Colonne USERID : c'est elle qui permet de définir le ou les utilisateurs pour lesquels il faut restreindre les droits. Soit on saisit le nom en entier soit on utilise des caractères spéciaux comme % soit on utilise seulement % pour impacter tous les users.
Colonne PRODUCT : seule la valeur SQL*Plus peut être saisie.
Colonne ATTRIBUTE : la commande à interdire.

ATTENTION au fait suivant, il y a deux tables product_user_profile, une pour PUBLIC et une pour SYSTEM. En fait ce sont des synonymes et ils ne pointent pas vers le même objet. Celle que j'utilise appartient au schéma SYSTEM.
             SQL> select owner, object_type from dba_objects where lower(object_name) = 'product_user_profile';
             OWNER                          OBJECT_TYPE
             ------------------------------ -------------------
             PUBLIC                         SYNONYM
             SYSTEM                         SYNONYM

                      
             SQL> select * from dba_synonyms where SYNONYM_NAME = 'PRODUCT_USER_PROFILE';
             OWNER                          SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME          DB_LINK
             --------------------------------------------------------------------------------------------------------------------------------
             PUBLIC                         PRODUCT_USER_PROFILE           SYSTEM                         PRODUCT_PRIVS
             SYSTEM                         PRODUCT_USER_PROFILE           SYSTEM                         SQLPLUS_PRODUCT_PROFILE


============================================================================================
Les commandes pouvant être interdites
============================================================================================

Oracle distingue parmi les commandes à intercepter les commandes SQL*Plus, SQL et PL/SQL.          
          Commandes SQL*Plus qui peuvent être interdites
          ACCEPT      DEFINE      PASSWORD    SHUTDOWN
          APPEND      DEL   PAUSE SPOOL
          ARCHIVE LOG DESCRIBE    PRINT START (@, @@)
          ATTRIBUTE   DISCONNECT  PROMPT      STARTUP
          BREAK EDIT  RECOVER     STORE
          BTITLE      EXECUTE     REMARK      TIMING
          CHANGE      EXIT/QUIT   REPFOOTER   TTITLE
          CLEAR GET   REPHEADER   UNDEFINE
          COLUMN      HELP (?)    RUN   VARIABLE
          COMPUTE     HOST  SAVE  WHENEVER OSERROR
          CONNECT     INPUT SET   WHENEVER SQLERROR
          COPY  LIST (;)    SHOW  XQUERY
           
          Commandes SQL qui peuvent être interdites
          ALTER DELETE      MERGE SET CONSTRAINTS
          ANALYZE     DISASSOCIATE      NOAUDIT     SET ROLE
          ASSOCIATE   DROP  PURGE SET TRANSACTION
          AUDIT EXPLAIN     RENAME      TRUNCATE
          CALL  FLASHBACK   REVOKE      UPDATE
          COMMENT     GRANT ROLLBACK    VALIDATE
          COMMIT      INSERT      SAVEPOINT
          CREATE      LOCK  SELECT
           
          Commandes PL/SQL qui peuvent être interdites
          BEGIN DECLARE
                  

============================================================================================
Exemples d'utilisation de PRODUCT_USER_PROFILE
============================================================================================

Interdire des commandes SQL*Plus
Commençons par interdire des commandes SQL*Plus comme SPOOL, DESCRIBE... A noter que les commandes SHUTDOWN et STARTUP peuvent être désactivées, ce qui évite à n'importe qui ayant le privilège DBA de faire un A/R de la base s'il ne s'est pas connecté avec les privilèges SYSDBA ou SYSOPER.
          SQL> create user ZZ01 identified by test12345;
          SQL> grant create session to ZZ01;
          SQL> grant connect to zz01;
          SQL> grant create table to zz01;
          SQL> ALTER USER zz01 QUOTA UNLIMITED ON DBATOOLS;

          SQL> insert into SYSTEM.product_user_profile(product,userid,attribute,char_value) values ('SQL*Plus','ZZ01','SELECT','DISABLED');
          SQL> insert into SYSTEM.product_user_profile(product,userid,attribute,char_value) values ('SQL*Plus','ZZ01','DESCRIBE','DISABLED');
          SQL> insert into SYSTEM.product_user_profile(product,userid,attribute,char_value) values ('SQL*Plus','ZZ01','SPOOL','DISABLED');
          
Liste des commandes gérées.
          SELECT * FROM SYSTEM.product_user_profile order by USERID;
          PRODUCT                        USERID                         ATTRIBUTE                      SCOPE      NUMERIC_VALUE CHAR_VALUE DATE_VAL LONG_VALUE
          ------------------------------ ------------------------------ ------------------------------ ---------- ------------- ---------- -------- ----------
          SQL*Plus                       ZZ01                           SELECT                                                  DISABLED
          SQL*Plus                       ZZ01                           SPOOL                                                   DISABLED
          SQL*Plus                       ZZ01                           DESCRIBE                                                DISABLED

On vérifie si cette fonctionnalité est bien opérationnelle. La commande CREATE TABLE est OK car elle n'a pas été interdite, en revanche le DESCRIBE et le SPOOL sont bien impossibles à exécuter. A noter le message d'erreur qui n'est pas un ORA- mais un SP2-, ce qui indique un message SQL*Plus. Vous noterez aussi que le message est suffisament flou pour ne pas indiquer à un utilisateur lambda où est positionnée la sécurité pour qu'il la désactive via un DELETE.
          SQL> connect zz01

          SQL> create table test01(id number) tablespace DBATOOLS;
          Table creee.
           
          SQL> desc TEST01;
          SP2-0544: Commande "describe" desactivee dans le profil utilisateur du produit
           
          SQL> spool zz01.txt
          SP2-0544: Commande "spool" desactivee dans le profil utilisateur du produit

Pour remettre les droits, il suffit de faire un DELETE dans la table.
          SQL> delete from SYSTEM..PRODUCT_USER_PROFILE where userid='ZZ01' and ATTRIBUTE = 'DESCRIBE';
          SQL> commit;
           
Cette fois le DESCRIBE est OK.
          SQL> connect zz01

          SQL> describe test01;
          Nom                                                                                 NULL ?   Type
          ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
          ID                                                                                           NUMBER
           

Interdire des commandes SQL         
Voyons maintenant si les commandes SQL comme SELECT peuvent être interdites sous SQL*Plus et pas dans d'autres outils comme SQL Developer! Car je le répète, l'objectif de cette fonctionnalité est bien de limiter les actions sous SQL*Plus et UNIQUEMENT sous SQL*Plus, ce n'est pas de révoquer des droits.
          
Tests sous SQL*Plus.
          SQL> select * from cat;
          SP2-0544: Commande "select" desactivee dans le profil utilisateur du produit
           
          SQL> select * from test01;
          SP2-0544: Commande "select" desactivee dans le profil utilisateur du produit
           
Plus subtil, le SELECT est interdit même s’il est dans un .sql. Bien vu!
          SQL> ! more zz01.sql
          select * from cat;
           
          SQL> @zz01.sql
          SP2-0544: Commande "select" desactivee dans le profil utilisateur du produit
         
           
En revanche le SELECT dans un bloc PL/SQL est OK… Nous verrons plus loin comment interdire cela.
          SQL> set serveroutput on
          SQL> declare sysdt date;
            2  begin
            3  select sysdate into sysdt from dual;
            4  dbms_output.put_line(sysdt);
            5  end;
            6  /
          20/12/17
          Procedure PL/SQL terminee avec succes.
           

Comme prévu, le même user peut faire des SELECT dans un outil autre que SQL*Plus, SQL Developer par exemple.
Canalblog_DBA_Oracle_PRODUCT_USER_PROFILE01
        
Interdire des commandes PL/SQL          
Pour interdire les commandes PL/SQL, il faut interdire le mot clé BEGIN, qui débute tout bloc PL/SQL. On peut aussi interdire DECLARE mais attention, on peut avoir des programmes PL/SQL sans partie DECLARE.
          SQL> connect / as sysdba
          SQL> insert into SYSTEM.product_user_profile(product,userid,attribute,char_value) values ('SQL*Plus','ZZ01','BEGIN','DISABLED');
          SQL> select * from SYSTEM.PRODUCT_USER_PROFILE ;
             PRODUCT                        USERID                         ATTRIBUTE                      SCOPE      NUMERIC_VALUE CHAR_VALUE DATE_VAL LONG_VALUE
             ------------------------------ ------------------------------ ------------------------------ ---------- ------------- ---------- -------- ----------
             SQL*Plus                       ZZ01                           SELECT                                                  DISABLED
             SQL*Plus                       ZZ01                           SPOOL                                                   DISABLED
             SQL*Plus                       ZZ01                           BEGIN                                                   DISABLED
             SQL*Plus                       ZZ01                           DECLARE                                                 DISABLED
           
A noter que le test est case insensitive.
          SQL> connect zz01
          SQL> BEGIN
          SP2-0544: Commande "begin" desactivee dans le profil utilisateur du produit
          SQL> begin
          SP2-0544: Commande "begin" desactivee dans le profil utilisateur du produit
          
          
Test avec DECLARE.
          SQL> connect zz01
          SQL> declare sysdt date;
          SP2-0544: Commande "declare" desactivee dans le profil utilisateur du produit
           
          
Bloquer N users avec le caractère joker %
Oracle nous donne la possibilité d'utiliser le caractère joker % pour bloquer N users en une fois. Attention, il est possible de bloquer tous les users avec le code % dans la colonne USERID, sauf ceux se connectant avec les privilèges SYSDBA ou SYSOPER!

Dans ce test, nous bloquons tous les users dont le nom commence par ZZ.
          SQL> select username from dba_users where username like 'ZZ%';
          USERNAME
          ------------------------------
          ZZ01
          ZZ02
           
          SQL> select * from SYSTEM.product_user_profile;
             PRODUCT                        USERID                         ATTRIBUTE                      SCOPE      NUMERIC_VALUE CHAR_VALUE DATE_VAL LONG_VALUE
             ------------------------------ ------------------------------ ------------------------------ ---------- ------------- ---------- -------- ----------
             SQL*Plus                       ZZ%                            SELECT                                                  DISABLED
           
Et c’est OK!
          SQL> connect zz01
          SQL> select * from user_tables;
          SP2-0544: Commande "select" desactivee dans le profil utilisateur du produit

          SQL> connect zz02
          SQL> select * from cat;
          SP2-0544: Commande "select" desactivee dans le profil utilisateur du produit
           
           
Impossible de bloquer les users dans un autre outil que SQL*Plus
Comme précisé au début de cet article, la colonne PRODUCT ne doit être renseignée qu'avec le libellé SQL*Plus. Néanmoins aucun contrôle n’est effectué sur les valeurs entrées. Testons avec le libellé SQL Developer.
          SQL> insert into SYSTEM.product_user_profile(product,userid,attribute,char_value) values ('SQL DEVELOPER','ZZ01','SELECT','DISABLED');

Sous SQL*Plus et sous SQL Developer le SELECT passe bien, preuve que seul le produit SQL*Plus peut être paramétré.
          SQL> connect zz01

          SQL> select * from cat;
          TABLE_NAME                     TABLE_TYPE
          ------------------------------ -----------
          TEST01                         TABLE

Test sous SQL Developer.

Canalblog_DBA_Oracle_PRODUCT_USER_PROFILE02


Voilà, amusez-vous bien avec cette fonctionnalité de sécurité trop peu connue.