Procédure & insert / select dans un autre schéma [Résolu]

[punkoff]

Bonjour,

Je viens de perdre pas mal de temps pour réussir à insérer des données dans un schéma autre que le schéma de l'user courrant via une procédure.

J'ai "contourner" le problème en indiquant des droits explicite (et non via role) à l'utilisateur.

Je me demandais s'il n'y avait pas un autre moyen que ceci ?

Je trouve ca assez lourd comme contrainte au vu de la gestion des rôles.

edit: oracle 11gr2

[skuatamad]

Pour les procédures stockées il faut avoir les droits en direct et non via un rôle.

Les rôles seront utiles pour des users se connectant en direct ou pour des users applicatifs avec des droits execute sur certains packages, mais pas pour les users générant l'API, mais j'avoue c'est un peu naze...

[punkoff]

Merci pour les précisions.

Envoyé par skuatamad

des droits execute sur certains packages

Pourrai-tu développer ce point là ?

Mon contexte est le suivant :
J'ai un package qui dispose de X procedure / fonction afin de réaliser une migration de donnée.

ce package n'est appelable que via une procédure public et tout le reste ce fait avec des fonctions / procédures privées.

Je n'ai pas d'autre choix que de donner les droits select / insert à l'utilisateur qui va appeler ma procédure public sur les tables du schéma de destination donc ?

[skuatamad]

Envoyé par punkoff

Je n'ai pas d'autre choix que de donner les droits select / insert à l'utilisateur qui va appeler ma procédure public sur les tables du schéma de destination donc ?

Non justement c'est bon, seul le user qui crée le package a besoin des droits directs, les autres, il leur suffit d'avoir le droit execute sur le package donné directement ou indirectement via PUBLIC.

Ci-dessous un exemple j'espère qu'il est clair :

Code :

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
105
106
107
108
109
110
111
SQL> CREATE user a IDENTIFIED BY a;
 
User created.
 
SQL> GRANT CREATE session,CREATE TABLE TO a;
 
GRANT succeeded.
 
SQL> ALTER user a quota unlimited ON users;
 
User altered.
 
SQL> disconnect
 
SQL> conn a/a
Connected.
SQL> CREATE TABLE test AS SELECT * FROM all_users WHERE username='A';
 
TABLE created.
 
SQL> SELECT * FROM test;
 
USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
A                                     101 27-APR-11
 
SQL> exit
 
[...]$ sqlplus /
 
SQL> CREATE user b IDENTIFIED BY b;
 
User created.
 
SQL> GRANT connect,resource TO b;
 
GRANT succeeded.
 
SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON a.test TO b;
 
GRANT succeeded.
 
SQL> disconnect 
 
SQL> conn b/b
Connected.
SQL> CREATE OR REPLACE package b_pkg AS
  procedure p_pub;
end;
/  2    3    4  
 
Package created.
 
SQL> CREATE OR REPLACE package body b_pkg AS
  procedure p_priv AS
  begin
    DELETE a.test;
    INSERT INTO a.test SELECT * FROM all_users WHERE username='B';
    commit;
  end p_priv;
 
  procedure p_pub AS
  begin
    p_priv;
  end p_pub;
end;
/  2    3    4    5    6    7    8    9   10   11   12   13   14  
 
Package body created.
 
SQL> GRANT execute ON b_pkg TO public;
 
GRANT succeeded.
 
SQL> exit
 
[...]$ sqlplus /
 
SQL> CREATE user c IDENTIFIED BY c;
 
User created.
 
SQL> GRANT connect TO c;
 
GRANT succeeded.
 
SQL> disconnect
 
SQL> conn c/c
Connected.
SQL> exec b.b_pkg.p_pub;
 
PL/SQL procedure successfully completed.
 
SQL> SELECT * FROM a.test;
SELECT * FROM a.test
                *
ERROR at line 1:
ORA-00942: TABLE OR VIEW does NOT exist
 
SQL> disconnect
 
SQL> conn a/a
Connected.
SQL> SELECT * FROM test;
 
USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
B                                     102 27-APR-11
 
SQL>

[edit]Au fait j'avais mal lu, les droits ne sont pas donnés à PUBLIC mais seule la procédure est publique, donc avec les droits execute donnés directement au user c:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> REVOKE execute ON b.b_pkg FROM public;
 
REVOKE succeeded.
 
SQL> GRANT execute ON b.b_pkg TO c;
 
GRANT succeeded.
 
SQL> disconnect
Disconnected FROM Oracle DATABASE 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
WITH the Partitioning, OLAP, DATA Mining AND Real Application Testing options
SQL> conn c/c
Connected.
SQL> exec b.b_pkg.p_pub;
 
PL/SQL procedure successfully completed.
 
SQL>

Et pour l'execution les rôles fonctionnent :

Code :

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
SQL> CREATE role exec_pkg;
 
Role created.
 
SQL> GRANT execute ON b.b_pkg TO exec_pkg;
 
GRANT succeeded.
 
SQL> REVOKE execute ON b.b_pkg FROM c;
 
REVOKE succeeded.
 
SQL> GRANT exec_pkg TO c;
 
GRANT succeeded.
 
SQL> disconnect
Disconnected FROM Oracle DATABASE 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
WITH the Partitioning, OLAP, DATA Mining AND Real Application Testing options
SQL> conn c/c
Connected.
SQL> exec b.b_pkg.p_pub;
 
PL/SQL procedure successfully completed.
 
SQL>

[punkoff]

Ok compris.

Merci pour la démo