Discussion :

[Kasko]

Bonjour à tous,
Je viens vers vous car je sais pas comment résoudre mon problème.En fait, j'aimerai accorder le droit de modification à un gestionnaire sur une table Client mais que sur les clients dont il est gestionnaire.

Par exemple j'ai une une table client et j'ai créé un utilisateur "Jean" avec la commande CREATE USER, j'aimerai donc que "Jean" puisse modifier tous les clients dont il est gestionnaire.

Merci d'avance pour vos aides.

[struchon]

Une solution possible mais pas très belle est d'utiliser une fonction ou procédure pour autoriser ou pas les modifications.

Je place dans le schéma "CLIENT", la table à sécuriser, ainsi que la fonction pour y accéder en écriture.

Avec un compte qui peut faire un CREATE USER.

Code :

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

1
2
3
4
5
 
create user client identified by client1 default tablespace users temporary tablespace temp;
grant resource to client;
grant create session to client;
alter user client quota 1G on users;

Ici je me connecte dans CLIENT et je crée les tables.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
sqlplus client/client1
 
create table client(no number, nom varchar2(20));
create table gestionnaire(no number, nom varchar2(20));
create table autorisation(no_client number, no_gestionnaire varchar2(20));
insert into client values(1, 'Client de Jean');
insert into gestionnaire values(1, 'JEAN');
insert into gestionnaire values(2, 'PIERRE');
-- Je donne accès de modification du client 1 au gestionnaire 1. (Jean pourra modifier le client 1, mais Pierre ne pourra pas)
insert into autorisation values(1,1);
commit;

Toujours en étant connecté avec l'utilisateur CLIENT, je crée la fonction qui va servir d'API pour modifier la table CLIENT. (La colonne NOM dans cet exemple)
Désolé si le code n'est pas très beau, je ne suis pas développeur.

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
 
create or replace function modifier_nom_client(p_no in client.no%TYPE,p_nom in client.nom%TYPE)
return boolean as
  v_autorisation number := 0;
begin
select count(1) into v_autorisation
  from autorisation,
       gestionnaire
 where autorisation.no_client = p_no
   and autorisation.no_gestionnaire = gestionnaire.no
   and gestionnaire.nom = (select sys_context('USERENV','SESSION_USER') from dual);
 
if v_autorisation = 1 then
  update client set nom = p_nom where no = p_no;
  return true;
else
  return false;
end if;
end modifier_nom_client;
/

Je crée le compte de Jean et de Pierre avec un utilisateur qui a les droits (SYSTEM ou autres).

Code :

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

1
2
3
4
5
6
7
8
 
create user jean identified by jean1 default tablespace users temporary tablespace temp;
grant create session to jean;
grant execute on client.modifier_nom_client to jean;
 
create user pierre identified by pierre1 default tablespace users temporary tablespace temp;
grant create session to pierre ;
grant execute on client.modifier_nom_client to pierre;

Je teste le tout avec un utilisateur qui n'a pas le droit de modifier (PIERRE)

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
 
 
sqlplus pierre/pierre1
 
set serveroutput on;
declare
retour boolean := false;
begin
retour := client.modifier_nom_client(1,'test');
if retour then
 dbms_output.put_line('Client modifié');
else
 dbms_output.put_line('Pas autorisé');
end if;
end;
/

Résultat: Pas autorisé


Je teste le tout avec l'utilisateur JEAN qui a l'autorisation pour le client 1.

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
 
 
sqlplus jean/jean1
 
set serveroutput on;
declare
retour boolean := false;
begin
retour := client.modifier_nom_client(1,'test');
if retour then
 dbms_output.put_line('Client modifié');
else
 dbms_output.put_line('Pas autorisé');
end if;
end;
/

Résultat: Client modifié

Ne pas oublier de faire un commit ou un rollback;


En espérant le tout conforme!

[al1_24]

Une idée parmi d'autres :
Ajouter à la table Client une colonne Createur qui prend par défaut l'identifiant de l'utilisateur qui crée la ligne.
Placer un sur cette table un trigger BEFORE UPDATE qui vérifie que la colonne Createur est bien égale à l'utilisateur courant.

[Baldrick]

Bonjour,

Pour moi, la solution la plus adaptée, si elle est possible, serait de créer une vue simple, qui ne récupère que les lignes pouvant être modifiées, assortie de la clause WITH CHECK OPTION, qui interdit qu'on utilise la vue pour modifier des lignes qui ne sont pas sélectionnées, et d'accorder le droit de faire des modifications sur cette vue et non directement sur la table.

Donc, quelque chose comme ça :

Code :

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

1
2
3
4
5
6
7
8
CREATE VIEW V_CLIENT
AS
SELECT * 
FROM CLIENT
WHERE <la_condition_qui_permet_de_selectionner_les_clients_de_Jean>
WITH CHECK OPTION ;
 
GRANT UPDATE ON V_CLIENT TO jean;

[SQLpro]

Et pourquoi pas une vue WITH CHECK OPTION ?

A +