Exécution d'une commande dans un Trigger

Version imprimable

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:

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.

06/04/2006, 17h01
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 ? :koi:
06/04/2006, 17h32
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 ...

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

Sinon, pourquoi ne pas remplacer :

Code:

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:

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;

06/04/2006, 17h45
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:

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]
06/04/2006, 17h52
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
08/04/2006, 00h14
plabrevo

Re: Exécution d'une commande dans un Trigger

Citation:

Envoyé par Perr1

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.