Requete correcte mais invalide dans le package

**libuma** · 06/04/2010, 16h52

Bonjour,

Difficile de trouver un titre adapté avec peu de caractères

Voilà; j'ai une requête SQL que j'exécute sous SQL*PLUS et qui fonctionne correctement.

Je tente de l'exécuter à l'intérieur d'un package.. j'ai un joli message Table ou vue inexistante.

J'ai testé la requête, elle est 100% pareille..
J'ai testé le package avec les 2 types d'AUTH ID..
L'utilisateur qui exécute la requête sous SQL*PLUS, qui complile le package et qui appelle la fonction sont pareils ! (j'ai même fait un (user()) pour être certain.. vive la paranoïa).

Voici la requête..

Code :

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

1
2
3
4
5
6
 
	SELECT SUM(a.BYTES) /1024 /1024 ,b.AUTOEXTENSIBLE, b.MAXBYTES 
	INTO schemaSize, isExtensible, maxSize 
	FROM sys.dba_segments a,sys.dba_data_files b 
	WHERE  a.OWNER = 'WEI' AND a.tablespace_name = b.tablespace_name 
	GROUP BY a.OWNER,b.AUTOEXTENSIBLE,b.MAXBYTES;

Une idée ?

**sgora** · 06/04/2010, 17h47

A mon avis, c'est un problème de droits, tu n'as pas accès aux droits donnés par rôles quand tu utilises une procédure PL/SQL.
Vu que tu utilises des vues du dictionnaire, tu dois donc explicitement donner le privilège SELECT CATALOG ROLE à l'utilisateur concerné.

Oups, au temps pour moi, c'est SELECT_CATALOG_ROLE en fait

**pifor** · 06/04/2010, 21h28

Il faut donner le privilège sur la table ou la vue directement (càd sans utiliser un rôle):

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
SQL> connect / as sysdba
Connecté.
SQL> grant select_catalog_role to hr;
 
Autorisation de privilèges (GRANT) acceptée.
 
SQL>
SQL> connect hr/hr
Connecté.
SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
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> select count(*) from dba_segments;
 
  COUNT(*)
----------
      3556
 
SQL>
SQL> create or replace function cnt return number
  2  is
  3  v_cnt number;
  4  begin
  5  select count(*) into v_cnt from dba_segments;
  6  return v_cnt;
  7  end;
  8  /
 
Fonction créée.
 
SQL> show errors
Pas d''erreur.
SQL> select cnt from dual;
select cnt from dual
       *
ERREUR à la ligne 1 :
ORA-00942: Table ou vue inexistante
ORA-06512: à "HR.CNT", ligne 5
 
 
SQL>
SQL> connect / as sysdba
Connecté.
SQL> grant select on dba_segments to hr;
 
Autorisation de privilèges (GRANT) acceptée.
 
SQL>
SQL>
SQL> connect hr/hr
Connecté.
SQL> select cnt from dual;
 
       CNT
----------
      3556
 
SQL>

**libuma** · 07/04/2010, 09h56

Etrange qu'on ait pas accès aux droits des rôles..

Encore une bêtise qui m'a fait chercher pas mal de temps, merci beaucoup

**libuma** · 07/04/2010, 10h41

Re bonjour,

Petite question..

Supposons que l'utilisateur A à les droits pour effectuer cette requête..
Dans le schema de l'utilisateur B, on crée un package avec AUTH ID DEFINER..
Donc les droits d'execution des fonctions sont définies par l'utilisateur qui a compiler le package..

Si A crée une fonction dans ce package appartenant à B et le compile alors que B n'a pas les permissions nécessaires pour executer la requête contenue dans la fonction, est-ce que c'est possible malgré tout ?

Je suppose que non mais bon

**sgora** · 07/04/2010, 11h34

C'est pas possible qu'un utilisateur crée une fonction dans un package d'un autre utilisateur. Toutes les fonctions et procédures d'un package appartiennent obligatoirement au même schéma.

**libuma** · 07/04/2010, 13h16

Je veux dire par là qu'un utilisateur A fait un "create or replace package B.PACKAGE_NAME AUTH ID DEFINER" sur le schema de l'utilisateur A, ce qui est tout à fait possible.

**sgora** · 07/04/2010, 14h03

Dans ce cas là , le package appartiendra bien à B et pas à A, et A ne pourra rajouter de fonction supplémentaire qu'en remplaçant le package, qui appartiendra après remplacement toujours à B. Donc toujours le même comportement.

**libuma** · 07/04/2010, 14h24

On est bien d'accord

Ma question était :
A rajoute une fonction supplémentaire qui inclus la requête suivante en remplaçant le package de B.

Code :

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

1
2
3
4
5
6
 
SELECT SUM(a.BYTES) /1024 /1024 ,b.AUTOEXTENSIBLE, b.MAXBYTES 
	INTO schemaSize, isExtensible, maxSize 
	FROM sys.dba_segments a,sys.dba_data_files b 
	WHERE  a.OWNER = 'WEI' AND a.tablespace_name = b.tablespace_name 
	GROUP BY a.OWNER,b.AUTOEXTENSIBLE,b.MAXBYTES;

On est donc bien sur le schema de B.
A a les privilèges select sur dba_segments et sys.dba_data_files .
B n'a pas les privilèges.

Est-ce que A à le droit de faire ça sur le schema de B alors que celui-ci n'a pas les privilèges suffisants et donc ne connait pas ces vues?

**sgora** · 07/04/2010, 14h38

Bien sûr, si A a les droits suffisants pour créer un package dans le schéma B, il peut le faire, ce qui ne veut pas dire que B pourra se servir de la fonction : il doit avoir les droits sur les objets sous-jacents.

**libuma** · 07/04/2010, 14h48

Etrange alors..

Lorsqu'avec l'utilisateur A ( qui a les privilèges de la requête concernée..) je compile le package suivant, j'ai un table ou vue inexistante..

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
 
CREATE OR REPLACE PACKAGE BODY B.PACKPRIVE AS
 
 
	-- return 1 if the size is OK or auto extensible
	-- return 0 if the size is about to be reached
FUNCTION checkSpace RETURN NUMBER AS
	schemaSize				NUMBER;
	maxSize					NUMBER;
	margin					NUMBER;
	isExtensible			VARCHAR2(10);
	sql_stmt1				VARCHAR2(500);
	curseur					T_CURSOR;	
BEGIN
	-- set the margin to 100 MB.
	margin:=100;
 
	/*
	sql_stmt1:='SELECT SUM(a.BYTES) /1024 /1024 ,b.AUTOEXTENSIBLE, b.MAXBYTES '
	|| 'FROM sys.dba_segments a,sys.dba_data_files b '
	|| 'WHERE  a.OWNER = ''B'' AND a.tablespace_name = b.tablespace_name '
	|| 'GROUP BY a.OWNER,b.AUTOEXTENSIBLE,b.MAXBYTES ';
 
	SELECT SUM(a.BYTES) /1024 /1024 ,b.AUTOEXTENSIBLE, b.MAXBYTES 
	INTO schemaSize, isExtensible, maxSize 
	FROM sys.dba_segments a,sys.dba_data_files b 
	WHERE  a.OWNER = 'B' AND a.tablespace_name = b.tablespace_name 
	GROUP BY a.OWNER,b.AUTOEXTENSIBLE,b.MAXBYTES;
	*/
 
	SELECT BYTES /1024 /1024 , MAX_BYTES 
	INTO schemaSize, maxSize 
	FROM sys.DBA_TS_QUOTAS  
	WHERE username='B';
 
	--EXECUTE IMMEDIATE sql_stmt1;
	--EXECUTE IMMEDIATE sql_stmt INTO schemaSize,isExtensible,maxSize ;
 
	--INSERT INTO WEI.DEBUG values('Max: ' || maxSize || ' - schemaSize: ' || schemaSize || ' - isExt?: ' || isExtensible);commit;
 
	IF(maxSize = -1 OR ((maxSize /1024 /1024)-margin) > schemaSize )  THEN
		RETURN 1;
	ELSE
		RETURN 0;
	END IF;
 
		EXCEPTION
		WHEN DEAD_LOCK THEN
			RAISE_APPLICATION_ERROR(DL_ErrorCode, DL_ErrorMsg,TRUE);
		WHEN NO_DATA_FOUND THEN		 --lors du SELECT
			RAISE_APPLICATION_ERROR(FVR_ErrorCode,FVR_ErrorMsg,TRUE);
		WHEN DATE_MISMATCH THEN
			RAISE_APPLICATION_ERROR(DM_ErrorCode,DM_ErrorMsg,TRUE);
		WHEN FIELD_TOO_LARGE THEN
			RAISE_APPLICATION_ERROR(FTL_ErrorCode,FTL_ErrorMsg,TRUE);
		WHEN UPDATE_TO_NULL THEN
			RAISE_APPLICATION_ERROR(FVR_ErrorCode,FVR_ErrorMsg,TRUE);
		WHEN OTHERS THEN
			RAISE;
END checkSpace;
END A.PACKPRIVE

Si je remplace

Code :

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

1
2
 
CREATE OR REPLACE PACKAGE BODY B.PACKPRIVE AS

par

Code :

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

1
2
 
CREATE OR REPLACE PACKAGE BODY A.PACKPRIVE AS

Donc que je le crée dans le schema de A, ça fonctionne parfaitement..

**sgora** · 07/04/2010, 14h56

Parce que quand A utilise un package de B, il le fait par défaut (c'est l'authid definer) avec les droits de B, qui n'a pas les autorisations sur les objets sys.
Pour que ça marche, il faut que tu précises la clause

authid current_user

et A pourra alors exécuter la procédure de B avec ses propres privilèges.

Mais le plus simple si j'ai compris ce que tu veux faire, est de créer le package ou la fonction sous A, puis donner les droits d'exécution à B, ainsi les deux schémas pourront exécuter le code !

**libuma** · 07/04/2010, 15h03

Mon but est bien d'utiliser l'authid definer, je suis conscient que c'est la méthode par défaut.

Le problème n'est pas un problème d'accès, mais un problème lors du replace, j'ai une erreur de compilation "table ou vue inexistante" .

Si je mets la requête dans une variable (sql_stmt1) et que je l'execute grâce à EXECUTE IMMEDIATE sql_stmt1, alors j'arrive à compiler sans erreurs.

Et dans mon application, même B qui n'a normalement pas les privilèges arrivent à exécuter la fonction, ça fonctionne parfaitement.

**sgora** · 07/04/2010, 15h25

Au risque de me répéter, quand tu crées un package sous un utilisateur qui n'a pas les droits sur les objets sous-jacents, il y a une erreur de compilation. C'est le fonctionnement normal, je ne vois pas ce que je peux te dire d'autre.

Quant à l'execute immediate, il faudrait que tu publies le code, ainsi que les droits du propriétaire et de l'utilisateur du code pour voir ce qui se passe.
Mais si B fait l'execute immediate d'un code appartenant à A qui lui possède les bons droits, c'est également le comportement normal et par défaut.

**libuma** · 07/04/2010, 15h40

C'est quand même étrange comme comportement d'être bloqué à cause de droits sous jacent alors qu'en passant par une variable ça fonctionne très bien :o

A remplace le package de B :

Ceci bloque

Code :

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

1
2
3
4
5
 
	SELECT BYTES /1024 /1024 , MAX_BYTES 
	INTO schemaSize, maxSize 
	FROM sys.DBA_TS_QUOTAS  
	WHERE username='B';

Ceci fonctionne

Code :

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

1
2
3
4
5
6
7
8
9
 
 
	sql_stmt1:=''
	|| 'SELECT BYTES /1024 /1024 , MAX_BYTES '
	|| 'INTO schemaSize, maxSize '
	|| 'FROM sys.DBA_TS_QUOTAS  '
	|| 'WHERE username=''B''';
 
	EXECUTE IMMEDIATE sql_stmt1;

Droits de A :

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
 
TABLE_NAME
------------------------------
MVIEW_WORKLOAD
MVIEW_FILTER
MVIEW_LOG
MVIEW_FILTERINSTANCE
MVIEW_RECOMMENDATIONS
MVIEW_EVALUATIONS
MVIEW_EXCEPTIONS
DBA_SEGMENTS
DBA_DATA_FILES
DBA_TS_QUOTAS
DEF$_AQCALL
 
TABLE_NAME
------------------------------
DEF$_ERROR
DEF$_DESTINATION
DEF$_CALLDEST
DEF$_LOB
DEF$_TEMP$LOB
DEF$_TEMP$LOB
DEF$_TEMP$LOB
DEF$_TEMP$LOB
REPCAT$_REPSCHEMA
REPCAT$_REPPROP

Droits de B :

Code :

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

1
2
3
4
 
TABLE_NAME
------------------------------
TYPE_UTIL

**sgora** · 07/04/2010, 15h44

Mais tu ne précises pas quel utilisateur fait l'execute immediate, et tu ne listes pas les privilèges systèmes, qui peuvent être importants.
Précision : l'execute immediate est exécuté dans une procédure, ou dans un bloc anonyme PL/SQL ?

**libuma** · 07/04/2010, 16h04

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
 
CREATE OR REPLACE PACKAGE BODY B.PACKPRIVE AS
 
 
	-- return 1 if the size is OK or auto extensible
	-- return 0 if the size is about to be reached
FUNCTION checkSpace RETURN NUMBER AS
	schemaSize				NUMBER;
	maxSize					NUMBER;
	margin					NUMBER;
	isExtensible			VARCHAR2(10);
	sql_stmt1				VARCHAR2(500);
	curseur					T_CURSOR;	
BEGIN
	-- set the margin to 100 MB.
	margin:=100;
 
		sql_stmt1:=''
	|| 'SELECT BYTES /1024 /1024 , MAX_BYTES '
	|| 'INTO schemaSize, maxSize '
	|| 'FROM sys.DBA_TS_QUOTAS  '
	|| 'WHERE username=''WEI''';
 
	EXECUTE IMMEDIATE sql_stmt1;
 
	IF(maxSize = -1 OR ((maxSize /1024 /1024)-margin) > schemaSize )  THEN
		RETURN 1;
	ELSE
		RETURN 0;
	END IF;
 
		EXCEPTION
		WHEN DEAD_LOCK THEN
			RAISE_APPLICATION_ERROR(DL_ErrorCode, DL_ErrorMsg,TRUE);
		WHEN NO_DATA_FOUND THEN		 --lors du SELECT
			RAISE_APPLICATION_ERROR(FVR_ErrorCode,FVR_ErrorMsg,TRUE);
		WHEN DATE_MISMATCH THEN
			RAISE_APPLICATION_ERROR(DM_ErrorCode,DM_ErrorMsg,TRUE);
		WHEN FIELD_TOO_LARGE THEN
			RAISE_APPLICATION_ERROR(FTL_ErrorCode,FTL_ErrorMsg,TRUE);
		WHEN UPDATE_TO_NULL THEN
			RAISE_APPLICATION_ERROR(FVR_ErrorCode,FVR_ErrorMsg,TRUE);
		WHEN OTHERS THEN
			RAISE;
END checkSpace;
 
END PACKPRIVE;

La différence entre l'execute immediate et l'écriture directe de la requête, est qu'il n'y a pas d'erreur de compilation.(compilé par A)

Dans le cas de l'execute immediate, puisque le package est compilé, je peux appeler la fonction.

Que ce soit A ou B qui l'appelle, J'ai une nouvelle fois "table ou vue inexistante" ..

Pourtant c'est A qui compile et puisque c'est AuthID Definer, ça devrait ça devrait passer puisque A à les droits...
Mais puis que c'est le schema de B et qu'il n'a pas les droits des éléments sous jacents, je suppose que je suis obligé de donner les droits à B ou de créer le package sous A ..

**sgora** · 07/04/2010, 16h26

Pourtant c'est A qui compile et puisque c'est AuthID Definer, ça devrait ça devrait passer puisque A à les droits...
Mais puis que c'est le schema de B et qu'il n'a pas les droits des éléments sous jacents, je suppose que je suis obligé de donner les droits à B ou de créer le package sous A ..

Non, c'est pas ça, dans ce cas l'authid definer c'est le propriétaire, c'est à dire B. Donc ça plante.

Effectivement, il est beaucoup plus simple de créer la fonction sous A et de la faire exécuter par B.

Et la différence avec l'execute immediate, c'est que la requête SQL n'est évaluée qu'à l'exécution, donc ça compile avec execute immediate, mais ça ne fonctionne pas à l'exécution

**libuma** · 07/04/2010, 16h51

Envoyé par sgora

Non, c'est pas ça, dans ce cas l'authid definer c'est le propriétaire, c'est à dire B. Donc ça plante.

Effectivement, il est beaucoup plus simple de créer la fonction sous A et de la faire exécuter par B.

Et la différence avec l'execute immediate, c'est que la requête SQL n'est évaluée qu'à l'exécution, donc ça compile avec execute immediate, mais ça ne fonctionne pas à l'exécution

Ok alors c'est un mistake de ma part avec l'Auth ID.
Pour moi c'était celui qui compilait, pas le propriétaire du schema :/

Merci pour tout

Requete correcte mais invalide dans le package

Oracle

Discussions similaires

Partager

Partager