vendredi 10 août 2012

Privilèges, roles et PL/SQL

Cet article traite d'un problème très vicieux bien connu chez les développeurs utilisant une base Oracle.

Le problème est le suivant: vous avez besoin d'un privilège objet (sur une table d'un autre schéma par exemple) ou d'un privilège système. Votre DBA grant ce privilège à un role qui est lui même granté au user applicatif avec lequel votre application se connecte à la base. Vous vous connectez à la base pour tester et ça fonctionne. Toutefois lorsque vous utilisez ce privilège au sein d'une procédure stockée ou d'une fonction ça ne fonctionne plus. C'est comme si le privilège n'était pas granté. Pourquoi?

Avant de répondre à cette question, illustrons le problème avec un exemple.

J'ai un utilisateur TOTO qui veut donner au user TITI  un accès à sa table T1 via le role ROLE_TITI:
-- connecté à TOTO
SQL> create table t1 as select * from all_objects where rownum<=5;

Table created.

SQL> create role ROLE_TITI;

Role created.

SQL> grant select on t1 to ROLE_TITI;

Grant succeeded.

SQL> grant ROLE_TITI to TITI;

Grant succeeded.

Maintenant je me connecte à TITI et j'exécute une commande SELECT sur la table T1 de TOTO:
-- connecté TITI

SQL> select count(1) from toto.T1;

  COUNT(1)
----------
         5

Pas de soucis ça fonctionne. Puisque le privilege SELECT est granté au role ROLE_TITI et que ce role est granté au user TITI il est normal que TITI puisse accéder à cette table.

Maintenant connectons nous à TITI et effectuons le SELECT au sein d'une procédure stockée:
-- connecté à TITI

SQL> create or replace procedure test_proc is
  2  v_number number;
  3  begin
  4
  5     select count(1) into v_number from toto.T1;
  6
  7  end test_proc;
  8  /

Warning: Procedure created with compilation errors.

SQL> sho errors
Errors for PROCEDURE TEST_PROC:

LINE/COL ERROR
-------- ---------------------------------------------------
5/2      PL/SQL: SQL Statement ignored
5/42     PL/SQL: ORA-00942: table or view does not exist


La procédure ne compile pas car le user TITI ne voit plus la table T1 de TOTO, et pourtant le privilege SELECT est toujours granté au role ROLE_TITI:
SQL> select table_name,privilege from ROLE_TAB_PRIVS where role like 'ROLE_TITI';

TABLE_NAME                     PRIVILEGE
------------------------------ ----------------------------------------
T1                             SELECT

La problème vient du fait que les privilèges grantées via un role ne sont pas visibles dans le code d'une procédure ou d'une fonction car les rôles sont désactivées lors de leurs exécution.
La solution consiste à granter directement le privilège au USER qui en a besoin.

Dans notre exemple nous allons granter le privilège SELECT de la table T1 directement au user TITI:
-- connecté à TOTO

SQL> grant select on t1 to TITI;

Grant succeeded.

Voyons maintenant ce que donne la compilation de la procédure du côté de TITI:
-- connecté à TITI

SQL> create or replace procedure test_proc is
  2  v_number number;
  3  begin
  4
  5     select count(1) into v_number from toto.T1;
  6
  7  end test_proc;
  8  /

Procedure created.

La procédure compile bien car le privilège est désormais visible.

Lorsqu'on a la solution c'est facile mais je peux vous dire que lorsque vous rencontrez ce problème pour la première fois (comme ce fût le cas pour moi il y' a quelques années de ça) vous avez de quoi vous tapez la tête contre le clavier :-)

Remarques:
1) Le problème se pose aussi bien pour les privilèges objets que pour les privilèges systèmes
2) Le problème ne se pose pas pour les blocs PL/SQL anonymes
3) Le problème se pose aussi lors de la création de vues

Aucun commentaire:

Enregistrer un commentaire