Discussion :

[Perr1]

Bonjour,

Je cherche à exécuter une commande de type : Grant select on xxx to xxx et create public synonym for xxx dans un Trigger lors de la création d'un objet de type table.
Je dois faire ceci pour permettre à des utilisateurs d'accéder aux tables en lecture de certains comptes.
A priori c'est dû à Oracle 8.1.7 et 9i, le droit de "select any table", suffisant en Oracle 7.3.4, ne suffit plus avec ces nouvelles versions. Il faut que le propriétaire des objets donne les droits et j'en profite pour créer un synonyme.

En consultant les forums j'ai trouvé une solution avec un Trigger qui appelle une Procédure (pour éviter les problèmes de commit dans un Trigger).
J'ai donc rédigé ce script :

- La Procédure :

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 procedure PCD_TEST_GRANTS( psOrdre VARCHAR2, psDate DATE )
  IS
  -- PRAGMA AUTONOMOUS_TRANSACTION; -- N.B. : A priori, je dois inhiber cette commande, sinon elle prends un temps fou !!!
 
  BEGIN
    insert into ref.TEST values(psOrdre, psDate);
    EXECUTE IMMEDIATE psOrdre;
 
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.Put_Line(SQLERRM(SQLCODE));
  END PCD_TEST_GRANTS;
 
- Le Trigger qui l'utilise :
 
CREATE OR REPLACE TRIGGER TRG_TEST_GRANTS
  AFTER CREATE ON SCHEMA
declare
  -- Variables de travail
  vType      VARCHAR2(20);
  vName      VARCHAR2(50);
  vOwner     VARCHAR2(30);
  vOrdre     VARCHAR2(150);
  vdate      DATE;
  vRefType   VARCHAR2(20)   := 'TABLE';
 
begin
   -- Requête pour connaître l'objet à traiter
   select SUBSTR(ora_dict_obj_name,1,50), LTRIM(RTRIM(ora_dict_obj_type)), LTRIM(RTRIM(ora_dict_obj_owner))
      into vName, vType, vOwner
      from dual;
   -- La date courante
   select sysdate into vDate from dual;
 
   -- Donner les droits ?
   if vOwner = 'REF' and vType = vRefType then
      -- Ordre des droits en lecture
      vOrdre := 'grant select on ' || vOwner || '.' || vName || ' to public';
      -- Appel de la procédure pour exécuter l'ordre
      PCD_TEST_GRANTS (vOrdre, vDate);
      -- Ordre de la création du synonyme
      vOrdre := 'create public synonym ' || vName || ' for ' || vOwner || '.' || vName ;
      -- Appel de la procédure pour exécuter l'ordre
      PCD_TEST_GRANTS (vOrdre, vDate);
   end if;
 
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.Put_Line(SQLERRM(SQLCODE));
END TRG_TEST_GRANTS;
 
Mais lorsque j'exécute une série de commandes pour déclancher le Trigger :
 
truncate table ref.test
/
drop table ref.tempo
/
create table ref.tempo ( Champ1 varchar2(255))
/
select * from ref.test
/

N.B. : La table Test sert à vérifier l'ordre qui sera exécuté.
j'obtiens bien, dans la table Test, les commandes :
grant select on REF.TEMPO to public 06/04/2006 16:21:42
create public synonym TEMPO for REF.TEMPO 06/04/2006 16:21:42
Mais en consultant dba_synonyms et dba_tab_privs il n'y a pas d'articles pour REF.TEMPO et aucun autre utilisateur ne peut la lire "ORA-00942: table or view does not exist".

Si quelqu'un a une solution. Merci.

[orafrance]

merci de penser aux balises CODES à l'avenir

Un EXECUTE IMMEDIATE ne fonctionne pas ???

c'est quoi cette heure qui traine en fin de commande ?

[Perr1]

Non la commande execute immediate ne donne pas de résultat puisque je ne trouve pas de grant, ni de synonyme dans dba_synonyms et dba_tab_privs ... et que les autres utilisateurs ne peuvent pas accéder à l'objet !!

J'ai oublié de préciser que la procédure et le trigger ont été créé par le compte SYSTEM.

La date est pour savoir si la commande a été insérée dans la table Test :
... insert into ref.test ...

[orafrance]

mais il n'y a pas d'EXECUTE IMMEDIATE dans la procedure

Sinon, pourquoi ne pas remplacer :

Code :

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

1
2
3
4
5
6
-- Requête pour connaître l'objet à traiter
   select SUBSTR(ora_dict_obj_name,1,50), LTRIM(RTRIM(ora_dict_obj_type)), LTRIM(RTRIM(ora_dict_obj_owner))
      into vName, vType, vOwner
      from dual;
   -- La date courante
   select sysdate into vDate from dual;

par

Code :

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

1
2
3
4
-- Requête pour connaître l'objet à traiter
   select SUBSTR(ora_dict_obj_name,1,50), LTRIM(RTRIM(ora_dict_obj_type)), LTRIM(RTRIM(ora_dict_obj_owner)),sysdate 
      into vName, vType, vOwner,vDate 
      from dual;

[Perr1]

La date est uniquement ajoutée pour vérifier l'horodatage du traitement en phase de mise au point. Je l'enlèverai ensuite et ne passerai plus qu'un seul paramètre "vOrdre" à la procédure.

La commande "execute immediate" est dans la procédure, après la commande "insert into ...". J'ai essayé aussi la commande :

Code :

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

1
2
3
 
   dbms_sql.parse(cursor_ref,psOrdre,0);
   h_cr := dbms_sql.execute(cursor_ref);

dans la procédure, à la place du execute immediate, mais j'obtiens le même résultat, c'est à dire aucun !![/b]

[xavi]

Je ne connais pas Oracle 9 (ou 10), mais en versions 7 et 8, c'est sûr que les instructions DDL sont interdites. A moins que le GRANT ne soit pas considéré comme du DDL, je ne vois pas de solution.

Pour résumer, un trigger permet seulement de faire :
INSERT
DELETE
UPDATE
SELECT INTO

http://download-west.oracle.com/docs...g13trg.htm#710

[plabrevo]

EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.Put_Line(SQLERRM(SQLCODE));

La premiere chose a faire est de supprimer le EXCEPTION WHEN OTHERS, a banir de tout code PL/SQL lorsqu'il n'est pas suivi d'une gestion effective de l'exception. Dans le cas present, l'erreur est tout simplement ignoree.

J'aime moyenement l'aspect synchrone entre la detection de la creation de la table avec le traitement qui s'en suit. Je prefererais laisser au trigger, pourquoi pas, la gestion de la capture de l'evenement, mais je stockerais cet evenement dans une queue, et je ferais gerer cette queue par un process independant et asynchrone, utilsant dbms_job ou dbms_scheduler.