Discussion :

[Cpas2latarte]

Bonjour,
J’ai une procédure qui crée une table temporaire de la manière suivante

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
Create Or Replace procedure maProcedure
IS
  test number(10);
Begin
  Select count(*) into test from user_tables where table_name = 'MATABLE';
  if test>0 Then
    Execute immediate 'Truncate Table maTable;';
    Execute immediate 'Drop Table maTable;';
  End if; 
  Execute immediate 'Create global temporary Table maTable on commit preserve rows as select 5 monchamp from dual';
  commit;
end;

Evidement le code réel est plus complexe, mais même cet exemple extrêmement simple génère le même problème.

Lorsque j’exécute ma procédure (sous sqlplus) j’ai le message d’erreur suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
SQL> execute maprocedure;
BEGIN maprocedure; END;
 
*
ERREUR Ó la ligne 1 :
ORA-01031: privilÞges insuffisants
ORA-06512: Ó "MONSCHEMA.MAPROCEDURE", ligne 10
ORA-06512: Ó ligne 1
 
 
SQL>

L’erreur est levée sur la ligne 10 de la procédure, c'est-à-dire sur le « execute immediate »
En revanche lorsque je fait la même chose mais dans un script ,et non dans une procédure stockée, la tout passe nickel :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
Declare
  test number(10);
Begin
  Select count(*) into test from user_tables where table_name = 'MATABLE';
  if test>0 Then
    Execute immediate 'Truncate Table maTable;';
    Execute immediate 'Drop Table maTable;';
  End if; 
  Execute immediate 'Create global temporary Table maTable on commit preserve rows as select 5 monchamp from dual';
  commit;
end;

Ce script est un copier collé du corps de la procédure.
J’ai évidemment forcé un GRANT EXECUTE de mon utilisateur pour cette procédure, sans effet.

Merci de vos lumières

[pifor]

Quelle est la version d'Oracle ?
Que donne pour l'utilisateur qui crée la procédure et l'exécute:

SELECT * FROM v$session_privs;
SELECT * FROM v$session_roles;

Quels sont les contenus des rôles, s'il y en a ?

[Cpas2latarte]

Quelle est la version d'Oracle ?
Que donne pour l'utilisateur qui crée la procédure et l'exécute:

Quels sont les contenus des rôles, s'il y en a ?

Pour la version :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
 
SQL>

En revanche, si je fait

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM v$session_privs;

ou même si je fait

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select * from v$session_roles

j'ai le message

ORA-00942: Table ou vue inexistante

J'ai testé ces 2 requêtes sous sqlplus, dabbord connecté avec mon utilisateur, puis avec l'utilisateur sys en DBA. Dans les 2 cas, j'ai le même résultat.

[pifor]

Désolé:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT * FROM session_privs;
SELECT * FROM session_roles;

Dans une procédure stockée, par défaut les rôles ne sont pas pris en compte et la procédure s'exécute avec les privilèges du propriétaire de la procédure et non pas les priviléges de l'utilisateur connecté. Dans un bloc PL/SQL anonyme comme en SQL pur, les rôles sont activés et le code s'exécute avec tous les priviléges de l'utilisateur connecté.

[Cpas2latarte]

pas de blème
connecté sous sqlplus avec mon utilisateur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL> select * from session_privs;
 
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
 
9 ligne(s) sÚlectionnÚe(s).

et pour l'autre cela donne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SQL> select * from session_roles;
 
ROLE
------------------------------
CONNECT
RESOURCE

voili

[pifor]

Il faut donc que l'utilisateur ait le privilège CREATE TABLE donné directement (sans passer par un rôle) ou utiliser la clause AUTHID CURRENT_USER ou plus simplement ne pas créer d'objets dans une procédure stockée mais simplement en SQL

[Cpas2latarte]

BINGO
un grant Create table résous le problème

Merci de votre assitance.

En revanche, ce comportement m'étonne.
Pour moi le privilège "create table " était implicite du au rôle RESOURCE.

Je suis surpris qu'il faille par moment le spécifier de manière explicite par un grant Create table ...! :s

Y a t'il une sorte de changement de "contexte d'exécution" ou quelque chose du genre lorsque on fait un execute immediate dans une procédure stocké?

Ce qui me vient à l'esprit c'est un comportant étrange que j'ai observé sur certaines procédures de mon appli ou je construit dynamiquement des requête (de mise à jour) que j'exécute avec un EXECUTE IMMEDIATE.
Il arrive que ces requêtes se bloquent ou semble l'être. J'ai parfois laisser l'exécution plusieurs heures sur mon serveur oracle de dev dont je suis le seul utilisateur.
Ces même requêtes exécutées en "dure" (sans exécute immédiate) ne dure tout au plus que quelques minutes....
Ce supposé changement de contexte, expliquerai t'il ce genre de comportement?

Les execute immediate simplifient énormément mon application, j'aimerai ne pas avoir à m'en passer ...

[pifor]

Y a t'il une sorte de changement de "contexte d'exécution" ou quelque chose du genre lorsque on fait un execute immediate dans une procédure stocké ?

La règle fondamentale "du changement de contexte" c'est celle là:
Dans une procédure stockée, par défaut les rôles ne sont pas pris en compte et la procédure s'exécute avec les privilèges du propriétaire de la procédure et non pas les priviléges de l'utilisateur connecté. Dans un bloc PL/SQL anonyme comme en SQL pur, les rôles sont activés et le code s'exécute avec tous les priviléges de l'utilisateur connecté.

Ce supposé changement de contexte, expliquerai t'il ce genre de comportement?

A priori, non si vous récupérez systématiquement les exceptions PL/SQL.

[Cpas2latarte]

C'est noté
Merci encore de votre aide et pour ces informations