Vous n'êtes pas identifié(e).
Pages : 1
Bonjour,
J'implémente une base de données avec interface web en RoR et j'essai de mettre en place une gestion de droits sur ces données. Je m'explique :
Un scientifique a fait des manips et stocke ses données dans la base. Seuls lui et son labo ont le droit de voir ces données. Mais maintenant il veut pouvoir partager ses données avec un autre labo.
En clair, je voudrais gérer les droits de SELECT sur les tuples d'une table et non sur une table entière (ou une colonne comme le permettra très bientôt postgreSQL 8.4). J'ai opté pour une vue qui est générée pour chaque labo par une requête SQL que je stocke dans une table.
Oracle propose la gestion de droits sur les données, et je voudrais savoir si quelqu'un a déjà essayé (et réussi même) à faire cela avec Postgres?
Merci d'avance pour votre aide. 
Hors ligne
Vu que PostgreSQL-SE a été refusé pour la 8.4, le seul moyen a ma connaissance est une vue en ce qui concerne le droit sur les lignes. La vue ne doit renvoyer que les lignes de current_user. S'il vous faut plus d'infos, je peux détailler.
Guillaume.
Hors ligne
Je veux bien plus de détails.
J'ai déjà essayé l'option de générer une vue pour l'utilisateur (enfin, le labo de mon côté) courant. Je le fais pour chaque table avec des données nécessitant de la gestion de droit. J'ai en tout 148 vues. C'est beaucoup.
Hors ligne
Voici comment je fais pour une table.
guillaume@laptop$ createdb audreyjg
guillaume@laptop$ psql audreyjg
Bienvenue dans psql 8.3.7, l'interface interactive de PostgreSQL.
Saisissez:
\copyright pour les termes de distribution
\h pour l'aide-mémoire des commandes SQL
\? pour l'aide-mémoire des commandes psql
\g ou point-virgule en fin d'instruction pour exécuter la requête
\q pour quitter
audreyjg=# create user u1;
CREATE ROLE
audreyjg=# create user u2;
CREATE ROLE
audreyjg=# create table t1 (id serial, contenusecret text, user_autorise text);
NOTICE: CREATE TABLE will create implicit sequence "t1_id_seq" for serial column "t1.id"
CREATE TABLE
audreyjg=# insert into t1 (contenusecret, user_autorise) values ('secret de u1', 'u1');
INSERT 0 1
audreyjg=# insert into t1 (contenusecret, user_autorise) values ('secret de u2', 'u2');
INSERT 0 1
audreyjg=# insert into t1 (contenusecret, user_autorise) values ('deuxième secret de u1', 'u1');
INSERT 0 1J'ai donc deux utilisateurs. Chacun ne doit voir que les lignes correspondants à ses droits dans une table. Le droit est fait en indiquant le code de l'utilisateur dans une colonne de cette table.
audreyjg=# select current_user;
current_user
--------------
guillaume
(1 ligne)
audreyjg=# revoke select on t1 from public;
REVOKE
audreyjg=# grant select on t1 to guillaume;
GRANT
audreyjg=# select current_user;
current_user
--------------
guillaume
(1 ligne)
audreyjg=# select * from t1;
id | contenusecret | user_autorise
----+-----------------------+---------------
1 | secret de u1 | u1
2 | secret de u2 | u2
3 | deuxième secret de u1 | u1
(3 lignes)L'utilisateur guillaume est mon super utilisateur, il a droit de tout voir, et il est nécessaire d'en avoir un. Je vais créer deux fonctions qui me permettront d'accéder à la table pour les deux autres utilisateurs.
audreyjg=# create language plpgsql;
CREATE LANGUAGE
audreyjg=# create or replace function protection_t1(i_user text) returns setof t1 security definer language plpgsql as $$
begin
return query (select * from t1 where user_autorise=i_user);
end$$;
CREATE FUNCTION
audreyjg=# create or replace function protection_t1() returns setof t1 language plpgsql as $$
begin
return query (select * from protection_t1(current_user));
end$$;
CREATE FUNCTIONLa première fonction sert à récupérer les lignes de l'utilisateur indiqué en argument. Elle est security definer pour que le SELECT sur la table soit accepté. La deuxième appelle la première en lui fournissant le nom de l'utilisateur à rechercher. On utiliser pour cela la variable current_user initialisé par PostgreSQL à la connexion. On est obligé de passer par deux fonctions car si on utilisait current_user dans la première, le current_user serait réinitialisé automatiquement à guillaume à cause de l'option security definer.
audreyjg=# select * from protection_t1('');
id | contenusecret | user_autorise
----+---------------+---------------
(0 lignes)
audreyjg=# select * from protection_t1('u1');
id | contenusecret | user_autorise
----+-----------------------+---------------
1 | secret de u1 | u1
3 | deuxième secret de u1 | u1
(2 lignes)
audreyjg=# select * from protection_t1('u2');
id | contenusecret | user_autorise
----+---------------+---------------
2 | secret de u2 | u2
(1 ligne)Ça fonctionne quand on appelle la première fonction. Connectons-nous avec les autres utilisateurs et essayons d'exécuter la deuxième fonction.
audreyjg=# \c audreyjg u1
Vous êtes maintenant connecté à la base de données « audreyjg »comme utilisateur « u1 ».
audreyjg=> select * from t1;
ERROR: permission denied for relation t1
audreyjg=> select * from protection_t1();
id | contenusecret | user_autorise
----+-----------------------+---------------
1 | secret de u1 | u1
3 | deuxième secret de u1 | u1
(2 lignes)
audreyjg=> \c audreyjg u2
Vous êtes maintenant connecté à la base de données « audreyjg »comme utilisateur « u2 ».
audreyjg=> select * from protection_t1();
id | contenusecret | user_autorise
----+---------------+---------------
2 | secret de u2 | u2
(1 ligne)
audreyjg=> select * from t1;
ERROR: permission denied for relation t1Autant on n'arrive pas à accéder à t1 directement, autant on y arrive en passant par la procédure stockée. Ensuite, on peut créer une vue qui appelle directement la fonction pour faciliter l'accès aux données. On peut aussi ajouter des règles sur la vue pour que les insertions/mises à jour/suppressions fonctionnent.
Guillaume.
Hors ligne
On est obligé de passer par deux fonctions car si on utilisait current_user dans la première, le current_user serait réinitialisé automatiquement à guillaume à cause de l'option security definer.
Plus exactement, on n'est pas obligé d'avoir la seconde si on la remplace par une vue.
Guillaume.
Hors ligne
Attention, cette solution vous protège sur votre applicatif. Le premier utilisateur qui se connecte directement à la base de données, et utilise la fonction protection_t1('un autre user') verra les lignes de l'utilisateur « un autre user ».
Guillaume.
Hors ligne
Je suis en train de regarder un tutoriel PostgreSQL sur la gestion des accès à pgCon2009. Le conférencier vient de dire qu'il existe un moyen plus simple de faire ça. Une vue serait préférable. Comme ceci :
create view v_t1 as select * from t1 where user_autorise=current_user;Une vue est toujours exécutée en tant que celui qui l'a créé.
Guillaume.
Hors ligne
Pages : 1