Discussion :

[Pedrassi]

Bonjour.

Je suis en Oracle V8.1.7.

Je souhaite attribuer le privilège "select" pour tous les utilisateurs dès la création d'une table dans un schéma spécifique.

Dans ce but, je pensais créer un trigger sur l'évenement "AFTER CREATE ON DATABASE" qui, après avoir contrôlé si l'objet créé est une table dans le schéma choisi, exécute la commande "GRANT SELECT TO PUBLIC" pour la table en cours.

C'est sur la commande "execute immediate" que le message ORA-04097 apparait.

N'y a-t-il pas moyen d'exécuter une commande DDL dans un trigger déclenché par un évenement DDL ?
Y a-t-il un autre moyen pour attribuer le provilège "SELECT TO PUBLIC" en automatique (notamment lors d'une commande d'import qui crée les tables dans un schéma) ?

Merci de votre aide.

[Jaouad]

Pouvez vous fournir le code aux personnes qui désirent vous aider

Merci

[Pedrassi]

Voici la déclaration du trigger :

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
CREATE OR REPLACE TRIGGER TA_CREATE
  AFTER CREATE
  ON DATABASE
DECLARE
 
  -- variables de reception
  v_type      VARCHAR2(20);
  v_name      VARCHAR2(50);
  v_owner     VARCHAR2(30);
  v_ordre     VARCHAR2(150);
  v_ref_owner VARCHAR2(30) := 'IMPUSR';
  v_ref_type  VARCHAR2(30) := 'TABLE';
 
BEGIN
    SELECT SUBSTR(ora_dict_obj_name,1,50), RTRIM(ora_dict_obj_type), RTRIM(ora_dict_obj_owner)
      into v_name, v_type, v_owner
      from dual;
 
    IF v_type = v_ref_type AND v_owner = v_ref_owner THEN
       v_ordre := 'GRANT SELECT ON ' || v_owner || '.' || v_name || ' TO PUBLIC';
 
       EXECUTE IMMEDIATE v_ordre;
    END IF;
 
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;

Problème subsidiaire : Il me paraissait logique de déclarer ce trigger sur le schéma "SYS". Mais il semble qu'il n'est pas possible pour un utilisateur (même DBA) d'attribuer des privilèges sur des objets ne lui appartenant pas.
Mais étant connecté "IMPUSR" (schéma propriétaire de la table créée) lors du déclenchement du trigger, la commande GRANT sera-t-elle acceptée ?

Merci.

[Pedrassi]

Ne trouvant pas de solution dans ce mode, j'avais l'espoir de passer par la bidouille suivante (toujours en Oracle 8.1.7) :

1. Je crée une table "tabcmd" qui contient des commandes à exécuter.

Code :

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

1
2
3
4
CREATE TABLE TABCMD
  (
    COMMAND  VARCHAR2 (200)
  )

2. Je crée un trigger sur cette table pour qu'il exécute la commande lors de son insertion.

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 TRIGGER TI_TABCMD
  AFTER INSERT
  ON TABCMD
  REFERENCING NEW AS new
  FOR EACH ROW
BEGIN
 
  begin
    EXECUTE IMMEDIATE :new.COMMAND;
  exception
     when others then
	   DBMS_OUTPUT.Put_Line(SQLERRM(SQLCODE));
  end;
END;

3. Problème : Lorsque j'insère manuellement un enregistrement dans cette table, j'obtient l'erreur suivante :

Code :

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

1
2
insert into tabcmd values ('CREATE TABLE TAB1 (c1 char(1))');
ORA-04092: cannot COMMIT in a trigger

Quelqu'un peut-il me confirmer qu'il n'est pas possible de lancer des commandes DDL par trigger, ou est-ce que je me trompe quelque part ?

Merci

[SheikYerbouti]

Il faut utiliser une procédure stockée autonome. (PRAGMA AUTONOMOUS_TRANSACTION) et appeler cette procédure depuis votre trigger.
Pour rappel, un trigger ne peut contenir d'ordre commit ou rollback.

[Pedrassi]

Merci. La procédure stockée autonome débloque l'erreur initiale (DDL conflict).
Pour info, le message d'erreur précédent apparaît alors que le trigger ne contient ni COMMIT ni ROLLBACK, mais l'exécution d'une commande DDL.

Je viens également de m'apercevoir que je me suis trompé sur l'interprétation de l'option "ON SCHEMA" du trigger : Le trigger ne se déclenche pas quand un objet est créé sur le schéma "IMPUSR", mais lorsque l'utilisateur "IMPUSR" crée un objet (même sur un autre schéma).

Exemple :
Utilisateur "TOTO" crée une table sur le schéma "IMPUSR" -> Le trigger ne se déclenche pas.
Utilisateur "IMPUSR" crée une table sur le schéma "TOTO" -> Le trigger se déclenche.
Je vais donc appliquer mon trigger avec l'option "ON DATABASE" et tester le propriétaire de l'objet créé.

J'ai créé le trigger TA_CREATE avec le schéma SYSTEM :

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
CREATE OR REPLACE TRIGGER TA_CREATE
  AFTER CREATE
  ON DATABASE
DECLARE
  -- variables de reception
  v_type      VARCHAR2(20);
  v_name      VARCHAR2(50);
  v_owner     VARCHAR2(30);
  v_ordre     VARCHAR2(150);
 
BEGIN
  SELECT SUBSTR(ora_dict_obj_name,1,50), LTRIM(RTRIM(ora_dict_obj_type)), LTRIM(RTRIM(ora_dict_obj_owner))
    into v_name, v_type, v_owner
    from dual;
 
  IF v_owner = 'IMPUSR' AND v_type = 'TABLE' THEN
    v_ordre := 'GRANT SELECT ON ' || v_owner || '.' || v_name || ' TO PUBLIC';
 
    CMD_GRANT (v_ordre);
  END IF;
 
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.Put_Line(SQLERRM(SQLCODE)); 
END;

Et j'ai définis la procédure stockée autonome CMD_GRANT :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
CREATE OR REPLACE procedure CMD_GRANT ( psOrdre VARCHAR2 )
  IS
  PRAGMA AUTONOMOUS_TRANSACTION;
    sDebug VARCHAR2(50);
 
  BEGIN
    EXECUTE IMMEDIATE psOrdre;
 
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.Put_Line(SQLERRM(SQLCODE));
  END CMD_GRANT;

Mais malheureusement, je tombe sur une nouvelle erreur sur l'exécution de la procédure CMD_GRANT qui essaye d'attribuer le privilège SELECT :

Code :

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

1
2
create table test (c1 char(1));
ORA-04020: deadlock detected while trying to lock object IMPUSR.TEST

Toutes les idées sont les bienvenues car je commence à être à cours d'imagination...
Merci

[LeoAnderson]

Pour info, le message d'erreur précédent apparaît alors que le trigger ne contient ni COMMIT ni ROLLBACK, mais l'exécution d'une commande DDL.

C'est normal : un DDL fait un commit implicitement ! ;-)

par contre, votre idée de vouloir automatiquement donner un droit à toute table nouvellement créée, ça me semble un peu compliqué et dangereux !!!

[Pedrassi]

Pour l'aspect compliqué, je confirme et je le regrette car il me semble légitime de pouvoir automatiser certaines actions liées à la création d'un table (justement par rapport aux privilèges associées).

Pour l'intérêt, il est assez simple :
Sur un serveur, on a mis en place un schéma de production et un schéma spécifique (IMPUSR) censé être vide au départ.

L'idée est de pouvoir utiliser ce schéma pour importer sur le serveur un fichier dmp.
Dans ce but, on lui associe comme DEFAULT_TABLESPACE un tablespace spécifique, et on interdit l'utilisation des autres tablespace. Ainsi, toutes les tables créées par un import sont affectées dans ce tablespace, limitant les impacts sur l'environnement de production.

2 objectifs possibles :
1. Remonter une ancienne sauvegarde sans écraser l'environnement de production et permettre ainsi de comparer les 2 environnements ou de récupérer le différentiel d'une ou plusieurs tables.
2. Intégrer les données de la même application en provenance d'un autre serveur, en conservant la possibilité de ne pas charger les données brutes. C'est le cas par exemple d'un changement de serveur, ou d'un regroupement de données, où il peut s'avérer utile d'adapter ou sélectionner les données avant insertion.

Pour pouvoir faire un rapprochement entre les 2 schéma, mon idée était d'attribuer le seul privilège de SELECT de chaque table créée au schéma de production (ce qui me parait acceptable d'un point de vue sécurité).
Etant donné que les tables seront créées lors de l'import du fichier dmp, et que le contenu des fichiers importés n'est pas figé, il me semble intéressant d'automatiser la création du privilège par un trigger exécuté au moment de l'import.

Bien évidemment, il est également possible d'écrire une commande, pour attribuer les privilèges, qui devra être lancé après import, mais ça me parait beaucoup plus pratique que cette action soit réalisée dynamiquement.