Oracle 10g: grant select sur un schema?

Version imprimable

21/05/2008, 14h04
Bonnie38

Oracle 10g: grant select sur un schema?
Bonjour.
Ma base de données: 10.2.0.1.0
Y a t'il une façon de donner à user1 le droit SELECT sur toutes les tables de user2?
Je ne cherche pas à faire un script qui me construit un autre script contenant les commandes pour chaque table. Mais plutôt une commande à passer à l'installation du poste.
Je voudrais que user1 puisse faire un select sur les "futures" tables de user2, c'est-à-dire des tables non encores créées lorsque je passe ma commande du grant.
Pour l'instant je m'en sors avec (sous system):
Code:

1 2 GRANT SELECT ANY TABLE TO user1
Mais d'après ce que j'ai vu sur les forums, faut éviter.

Sinon encore une question concernant les droits:
sans donner le "grant select any table" au user1, j'ai le comportement suivant:
je suis connectée sous user1.
je fais à la ligne de commande un select sur la table user2.matable => ça marche
j'exécute un bloc PL/SQL faisant la même chose => ça marche
j'exécute une procédure faisant la même chose => je ne vois pas la table.
J'avoue que je n'arrive pas à expliquer ça. Si quelqu'un a une idée.

Merdi d'avance.
21/05/2008, 14h16
philcero

Citation:

Je voudrais que user1 puisse faire un select sur les "futures" tables de user2

Je pense que ce n'est pas possible. Seule soluce, dans le cas de multiples bénéficiaires, leur attribuer un rôle et mettre à jour ce rôle au fur et à mesure de la création de nouveaux objets.

Citation:

j'exécute une procédure faisant la même chose => je ne vois pas la table.

Les GRANTS ne sont pas hérités lors de l'exécution de procédures PL/SQL.
21/05/2008, 14h29
Bonnie38

Merci pour la rapidité!

Pour le 1er point, je pensais bien que ce n'était pas possible. Dommage.

Par contre, pour passer la commande

Code:

grant select on user2.matable to user1

après la création de matable, forcément il faut être connecté user2.
Mais je n'arrive pas à me connecter sous user2.
En faut il faudrait que je le fasse dans une procédure de user1, mais ça ne marche pas, à la compilation le "connect user2/mdp ne passe pas.

Je précise que je créé la table user2.matable dans une procédure de user1, via un import avec datapump en changeant le schéma par la commande REMAP_SCHEMA.
21/05/2008, 14h34
philcero
Citation:

après la création de matable, forcément il faut être connecté user2. Mais je n'arrive pas à me connecter sous user2.

Ca s'appelle la sécurité !

:mouarf:

Peux-tu alimenter un peu plus s.v.p :
1. Tu as un compte DBA sur la base ou tu n'est qu'invité ?
2. Tu n'as pas ou bien tu as perdu le mot de passe de user2 ?
21/05/2008, 14h50
Bonnie38

A l'installation j'ai tous les droits que je veux puisque je suis mon propre DBA (enfin j'essaye :?)
Pour faire tourner l'appli, je créé 2 utilisateurs:
user1 : utilisateur de l'appli, donc tout le code s'exécute sous cette connexion
user2 : juste pour pouvoir importer (par datapump) des tables.
Ces 2 users sont créés avec les droits:
connect, resource, exp_full_database, imp_full_database.

Donc j'ai tous les mots de passe.

En fait j'ai 2 machines distinctes, chacune avec sa base, chacune avec le même utilisateur user1.
Je fais un export de tables de user1 sur la machine1.
J'importe ces tables sur ma machine 2. Mais j'importe ces tables dans le schéma user2, puis je fais un select sur ces tables pour alimenter les tables de user1.

Je suis obligée de faire ça, car lors de l'import, si j'ai bien compris, soit on écrase toute la table (option REPLACE), soit on ajoute les données non présentes et on ignore les autres (option APPEND). Alors que moi, je voudrais ajouter les nouvelles données et remplacer les données existantes.
De plus il y a certaines tables sans clé unique, donc même avec une option en plus, ça ne le ferait pas.

http://asktom.oracle.com/pls/asktom/...D:646423863863

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create or replace trigger do_grant
after CREATE on schema
declare
    l_str varchar2(255);
    l_job number;
begin
    if ( ora_dict_obj_type = 'TABLE' )
    then
        l_str := 'execute immediate "grant select on ' ||
                                     ora_dict_obj_name ||
                                    ' to scott";';
        dbms_job.submit( l_job, replace(l_str,'"','''') );
    end if;
end;
/

21/05/2008, 15h27
Bonnie38

Ah oui!!! Pas bête!
Comme sur l'appli que j'ai reprise il n'y a que des procédures et aucun trigger, je n'ai pas du tout pensé à ça.
Je teste ça et je reviens fermer mon post.

Merci!
22/05/2008, 09h58
Bonnie38

Re
Ben finalement, ce n'est pas aussi simple que ça.
Comme la table user2.matable est créée par une procédure (lancée à partir d'une connexion user1) qui fait un import via le package dbms_datapump, avec l'option REMAP_SCHEMA pour l'importer dans le schéma user2, je n'arrive pas à détecter la création de ma table.
J'ai ajouté des traces dans un trigger BEFORE CREATE ON DATABASE qui affichent les données suivantes:
- ora_dict_obj_type
- ora_dict_obj_owner
- ora_dict_obj_name
Et je ne vois aucune trace concernant user2.matable.
Je vois uniquement des traces concernant des tables et des vues manipulées par le package datapump (création de la table dont le nom est le job_name utilisé dans dbms_datapump, manipulation des vues DATAPUMP_QUETAB et DATAPUMP_QUETAB_F).
J'ai créé mon trigger de trace dans les schémas user1, user2 et sys, pour être sure de ne rien rater (j'ai évidemment les mêmes traces dans les 3 triggers).

J'ai fait ça la semaine passée.
Voici le script :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE
  	CURSOR C_TABLE IS
    		SELECT TABLE_NAME
    		FROM ALL_TABLES
    		WHERE OWNER = 'USER1';
 	req STRING(200);
BEGIN
 
  	FOR CUR IN C_TABLE LOOP
		req := 'GRANT SELECT ON USER1.' || CUR.TABLE_NAME || ' TO USER2';
    		execute immediate (req);
  	END LOOP;
END;
/

22/05/2008, 11h53
Bonnie38

Mais mon problème c'est ou et quand lancer ce code.
A l'installation je peux pas car la table n'existe pas encore.
Dans mon appli, 2 problèmes:
- mon appli est lancée sous user1 alors qu'il faut être user2 pour faire ça
- les triggers ne voient pas la création de la table si elle est créée avec dbms_datapump.
22/05/2008, 13h21
philcero

Je présume que ton import DATAPUMP est piloté par un SHELL. De là pourquoi ne pas à la suite du lancement de DATAPUMP lancer un PL/SQL qui va regarder la liste de tous les objets et GRANTS associés de ton USER2 et combler les manques ?

Un trigger se déclenche suite à un événement , ici il s'agit de la creation de ta table

Par exemple deux schema DVP et DVP2 , on veut que DVP2 puisse voir tous les objest (en lecture seulement ) de DVP

Création des users et grant

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL> create user dvp identified by dvp ; 
 
Utilisateur créé.
 
SQL> create user dvp2 identified by dvp2 ;
 
Utilisateur créé.
 
SQL> grant dba to dvp ; 
 
Autorisation de privilèges (GRANT) acceptée.
 
SQL> grant dba to dvp2 ; 
 
Autorisation de privilèges (GRANT) acceptée.

Création du trigger sous DVP:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
QL> r
  1  CREATE OR REPLACE TRIGGER dvp.do_grant
  2  after CREATE ON schema
  3  declare
  4      l_str varchar2(255);
  5  begin
  6      IF ( ora_dict_obj_type = 'TABLE' )
  7      then
  8  execute immediate 'grant select on ' ||ora_dict_obj_name ||' to dvp2' ;
  9      end IF;
 10* end;
 
Déclencheur créé.

Création d'une table sous DVP:

Code:

1
2
3
4
5
6
SQL> create table test ( a varchar2(10));
 
Table créée.
 
SQL> show user
USER est "DVP"

Vérification sous DVP2

Code:

1
2
3
4
5
6
7
SQL> desc dvp.test
 Nom                                       NULL ?   Type
 ----------------------------------------- -------- ----------------------------
 A                                                  VARCHAR2(10)
 
SQL> show user
USER est "DVP2"

22/05/2008, 15h02
Bonnie38

Disons que c'est beaucoup plus compliqué que ça.
A mon niveau je fais des procédures stockées, mais elles sont englobées dans une grosse appli avec plein de langages différents, et pas mal de contraintes vu que c'est pour du nucléaire. Donc à mon niveau je peux pas faire. Faut que je vois avec les autres équipes je crois.
Merci quand même.