Discussion :

[claclapub]

Bonjour à tous,

J'ai 2 user dans la même instance :
sunopsis et odshr

Je me connecte avec odshr pour donner le droit suivant à sunopsis :
grant create any procedure to sunopsis ;

Je me connecte avec sunopsis et je lance le script suivant :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE OR REPLACE PROCEDURE odshr.Script_desactive_PK_ODS IS
CURSOR c_table IS
SELECT table_name, constraint_name
FROM SYS.ALL_CONSTRAINTS 
WHERE OWNER = 'ODSHR'
and table_name like 'HRA_%'
and constraint_type = 'P' ;
i c_table%ROWTYPE;
BEGIN
FOR i IN c_table
LOOP
EXECUTE IMMEDIATE 'alter table ODSHR.' || i.table_name || ' disable constraint ' || i.constraint_name ;
END LOOP;
END;

Mais j'obtiens l'erreur suivante :
Error on line 0
CREATE OR REPLACE PROCEDURE odshr.Script_desactive_PK_ODS IS
CURSOR c_table I
ORA-01031: insufficient privileges

Je n'y comprends rien...
Note : la procédure fonctionne très bien si je la lance avec odshr

[philcero]

Tu essaye de voir une vue du dictionnaire DBA/SYS avec un compte basique, et en plus pour voir tes propres objets. Logique qu'il te jette.

Changes ta requête.

Code :

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

1
2
3
4
5
 CURSOR c_table IS
SELECT table_name, constraint_name
FROM USER_CONSTRAINTS 
WHERE table_name like 'HRA_%'
and constraint_type = 'P' ;

[claclapub]

Je te remercie pour ta réponse, mais le souci ne vient pas de là il me semble.
Si je lance la requete seule (dans SQL+, sans passer par la procedure) je n'ai aucun souci pour lire dans la table SYS.ALL_CONSTRAINTS avec le user sunopsis.

Dans la procedure, si je remplace ce bout de code par le tiens, j'ai toujours le même message d'erreur.
Error on line 0
CREATE OR REPLACE PROCEDURE odshr.Script_desactive_PK_ODS IS
CURSOR c_table I
ORA-01031: insufficient privileges

[claclapub]

[QUOTE=philcero;3348948]Tu essaye de voir une vue du dictionnaire DBA/SYS avec un compte basique, et en plus pour voir tes propres objets. /QUOTE]
Non, je regarde les objets du user odshr, en étant connecté sunopsis.

[sdanvbnet]

Regarde ce bout de code :

Code :

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

1
2
 
EXECUTE IMMEDIATE 'alter table ODSHR.' || i.table_name || ' disable constraint ' || i.constraint_name ;

Es-tu sûr que sunopsis a les privilèges nécessaires (alter) sur les objets de odshr ?

Merci de vérifier. Je crois que c'est là le problème.

[plalm]

Hypothèse à tester
Comme tu les sais, les views

• USER_% correspondent aux objets du user connecté
• ALL_% correspondent aux objets sur lesquels le user connecté possède un droit

Deux users différents ont donc chacun accès à ALL_% et USER_% mais ils n'accèdent pas aux mêmes informations.

A partir de ce principe, il me paraît incongru (je vois pas quel mot utiliser), connecté comme sunopsis, de créer une procédure dans le schéma odshr qui utilise cette view ALL_%. Parce que, à la compilation, Oracle sait que odshr ne connait pas la view ALL_% accessible à sunopsis.

La solution serait de travailler avec les views DBA_% qui correspondent à tous les objets du dictionnaire - et donc qui sont identiques quel que soit le user connecté. Inconvénient : les deux users doivent avoir l'accès au dictionnaire ce qui n'est pas toujours souhaité (j'ai jamais bien compris pourquoi d'ailleurs).

[claclapub]

Es-tu sûr que sunopsis a les privilèges nécessaires (alter) sur les objets de odshr ?
Merci de vérifier. Je crois que c'est là le problème.

J'ai bien sûr vérifier un minimum de chose, en lançant par exemple des bouts de codes en dur.
Sunopsis a les droits et peut tout à fait activer ou désactiver les clefs de ODSHR.
De façon générale, sunopsis voit tout ce qu'il faut et peut "altérer" les tables de ODSHR, d'où mon incompréhension.

[claclapub]

A partir de ce principe, il me paraît incongru (je vois pas quel mot utiliser), connecté comme sunopsis, de créer une procédure dans le schéma odshr qui utilise cette view ALL_%. Parce que, à la compilation, Oracle sait que odshr ne connait pas la view ALL_% accessible à sunopsis.

Le problème n'est pas là je pense. En effet, si je lance exactement la même procédure avec le user ODSHR, tout fonctionne parfaitement. Il crée la procédure sans message d'erreur et peut la lancer avec le résultat attendu au bout.

[claclapub]

De façon générale, tout le code et les droits semblent fonctionner. Je les ai testé en jouant des ordres SQL "en dur" avec sunopsis et odshr.
Une seule chose m'est refusé : créer la procédure sous odshr avec le user sunopsis, malgré les droits donnés (grant any procédure to sunopsis ; lancé par odshr.)

Je reconnais que cela est bizarre, d'où mon incompréhension (et ma fristration ! ). Je crois pas au bug Oracle, il y a bien un truc que j'ai mal fait.

Je reste preneur de toutes vos idées et les testerai toutes. Merci encore pour votre aide.

[pifor]

Quelle est la version d'Oracle utilisée ?

Que donne avec le compte sunopsis:

CREATE OR REPLACE PROCEDURE odshr.test IS
BEGIN
NULL;
END;

En général, lorsqu'une instruction SQL retourne ORA-01031 en PL/SQL mais fonctionne en SQL, c'est parce qu'un droit nécessaire a été donné par un rôle et que les rôles sont par défaut désactivés en PL/SQL: il faut donc donner le droit sans passer par un rôle pourqu'il soit pris en compte par PL/SQL.

[claclapub]

Quelle est la version d'Oracle utilisée ?
Que donne avec le compte sunopsis:
En général, lorsqu'une instruction SQL retourne ORA-01031 en PL/SQL mais fonctionne en SQL, c'est parce qu'un droit nécessaire a été donné par un rôle et que les rôles sont par défaut désactivés en PL/SQL: il faut donc donner le droit sans passer par un rôle pourqu'il soit pris en compte par PL/SQL.

Ta piste fleure bon la solution !
Effectivement, l'instruction que tu proposes passe sans problème. J'ai pu créer la procédure.
Pas contre, la mienne ne passe toujours pas.

Le souci ne vient donc pas du fait de vouloir créer une procédure, mais plutôt du contenu de la procédure :
En SQL, pas de souci pour lire dans la table SYS.ALL_CONSTRAINTS et pas de souci pour "latérer" les tables du schéma odshr.

Il semblerait que le problème vienne du curseur ?

[claclapub]

Quelle est la version d'Oracle utilisée ?

10G

[pifor]

Essayez avec:

Code :

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

grant select on all_contraints to odshr;

[plalm]

Ton user odshr possède-t-il le droit de gérer ses propres tables ?
J'ai essayé et cela fonctionne chez moi

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
SQL> create user sunopsis identified by sunopsis 
  2  default tablespace data 
  3  temporary tablespace temp; 
 
User created. 
 
SQL> grant resource to sunopsis; 
 
Grant succeeded. 
 
SQL> grant create session to sunopsis; 
 
Grant succeeded. 
 
SQL> grant create any procedure to sunopsis; 
 
Grant succeeded. 
 
SQL> create user odshr identified by odshr 
  2  default tablespace data 
  3  temporary tablespace temp; 
 
User created. 
 
SQL> grant resource to odshr; 
 
Grant succeeded. 
 
SQL> grant create session to odshr; 
 
Grant succeeded. 
 
SQL> grant create table to odshr;  -- le user doit pouvoir faire ALTER TABLE 
 
Grant succeeded. 
 
SQL> connect sunopsis/sunopsis@&TNS 
Connected. 
SQL> create or replace procedure odshr.script is 
  2     cursor c_table is select table_name, constraint_name 
  3                       from all_constraints 
  4                       where owner = 'ODSHR' ; 
  5     i c_table%ROWTYPE; 
  6  begin 
  7     for i in c_table 
  8     loop 
  9        EXECUTE IMMEDIATE 'alter table ODSHR.' || i.table_name || ' disable c 
onstraint ' || i.constraint_name ; 
 10     end loop ; 
 11  end; 
 12  / 
 
Procedure created. 
 
SQL> connect odshr/odshr@&TNS 
Connected. 
SQL> desc odshr.script 
PROCEDURE odshr.script

[philcero]

Tu peux donner s.v.p le résultat de ça :

Code :

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

SELECT grantee, granted_role, admin_option FROM dba_role_privs WHERE grantee IN ('ODSHR','SUNOPSIS');

[claclapub]

En fait, j'ai réussi à "creuser" le problème.
Quand la procédure n'existe pas, Sunopsis peut la créer dans le schéma odshr (create procedure).
A partir de là, quand je relance create or replace procedure (avec exactement lê même contenu dans la procédure) j'ai le message d'erreur.

[claclapub]

Tu peux donner s.v.p le résultat de ça :

Code :

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

SELECT grantee, granted_role, admin_option FROM dba_role_privs WHERE grantee IN ('ODSHR','SUNOPSIS');

J'obtiens :

GRANTEE // GRANTED_ROLE // ADMIN_OPTION
------------------------------
ODSHR // R_ODSHR_ADM // NO
SUNOPSIS // R_SUNOPSIS // NO
ODSHR // DBA // NO
ODSHR // RESOURCE // NO
SUNOPSIS // RESOURCE // NO
SUNOPSIS // R_ODSHR_ADM // NO

[pifor]

Je confirme ce que dit plalm. Je peux réexécuter son script plusieurs fois de suite (avec les bonnes instructions DROP USER au début) avec une 10.2.0.1 sous Linux.

Il semblerait que le problème vienne du curseur ?

Dans votre cas, peut-être mais à priori l'accès aux vues ALL_CONSTRAINTS et USER_CONSTRAINTS ne nécessitent aucun privilège ou rôle que ce soit en SQL ou PL/SQL (à la différence de DBA_CONSTRAINTS):

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
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
 
SQL> 
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 Linux: Version 10.2.0.1.0 - Production                                  
NLSRTL Version 10.2.0.1.0 - Production                                          
 
SQL> 
SQL> drop user u cascade;
 
Utilisateur supprime.
 
SQL> 
SQL> whenever sqlerror exit failure;
SQL> 
SQL> CREATE user u IDENTIFIED BY u
  2  DEFAULT tablespace DATA
  3  TEMPORARY tablespace temp;
 
Utilisateur cree.
 
SQL> 
SQL> grant create session to u;
 
Autorisation de privileges (GRANT) acceptee.
 
SQL> grant create procedure to u;
 
Autorisation de privileges (GRANT) acceptee.
 
SQL> 
SQL> connect u/u;
Connecte.
SQL> 
SQL> 
SQL> CREATE OR REPLACE procedure p_all IS
  2       cursor c_table IS SELECT table_name, constraint_name
  3                         FROM all_constraints
  4                         WHERE owner = 'U' ;
  5       i c_table%ROWTYPE;
  6      begin
  7        null ;
  8     end;
  9  /
 
Procedure creee.
 
SQL> show errors
Pas d'erreur.
SQL> 
SQL> CREATE OR REPLACE procedure p_user IS
  2       cursor c_table IS SELECT table_name, constraint_name
  3                         FROM  user_constraints ;
  4       i c_table%ROWTYPE;
  5      begin
  6        null ;
  7     end;
  8  /
 
Procedure creee.
 
SQL> show errors
Pas d'erreur.
SQL> 
SQL> CREATE OR REPLACE procedure p_dba IS
  2       cursor c_table IS SELECT table_name, constraint_name
  3                         FROM  dba_constraints
  4                         WHERE owner = 'U' ;
  5       i c_table%ROWTYPE;
  6      begin
  7        null ;
  8     end;
  9  /
 
Avertissement : Procedure creee avec erreurs de compilation.
 
SQL> show errors
Erreurs pour PROCEDURE P_DBA :
 
LINE/COL                                                                        
--------------------------------------------------------------------------------
ERROR                                                                           
--------------------------------------------------------------------------------
2/13                                                                            
PLS-00341: declaration de curseur 'C_TABLE' incomplete ou mal structuree        
 
2/24                                                                            
PL/SQL: SQL Statement ignored                                                   
 
3/30                                                                            
PL/SQL: ORA-00942: Table ou vue inexistante                                     
 
 
LINE/COL                                                                        
--------------------------------------------------------------------------------
ERROR                                                                           
--------------------------------------------------------------------------------
5/8                                                                             
PL/SQL: Item ignored

Essayez de créer un scénario complet avec la création des rôles et de donner votre version exacte d'Oracle (numéro de version à 4 chiffres).

[claclapub]

Ca y est, j'ai trouvé tout seul, comme un grand

Je pouvais créer une procédure, grâce au droit
grant cre

[claclapub]

Ca y est, j'ai trouvé tout seul, comme un grand

Je pouvais créer une procédure, grâce au droit :
grant create any procedure

Je pouvais supprimer une procédure, grâce au droit :
grant drop any procedure

Il me manquait grant alter any procedure pour pouvoir écraser une procédure avec l'instruction replace

Merci à tous pour votre aide et vos pistes