db_venik

Function: ps_etendre_compte

Schema

public

Owner

postgres

Descriptions

Fonction d'inscription d'un compte utilisateur existant dans un groupe prédéfini de la base courante et définition des droits par défaut pour ce compte

Options

Definition

CREATE OR REPLACE FUNCTION public.ps_etendre_compte (
 compte text,
 statut integer = 0
)
RETURNS text LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY DEFINER
PARALLEL UNSAFE
COST 100
AS'
-- Auteur : Alain Benard
-- Date de mise à jour : 29/06/2022
-- Cette procédure sert à octroyer des droits sur la base courante à un role qui a déjà été créé avec la procédure ps_creer_compte (pour une autre base certainement). Elle définit également les droits par défaut
-- pour les objets qui seront créés par le role concerné au sein de la base courante.
--
-- paramètres :
-- compte : login de l''utilisateur à autoriser
-- statut : 0 pour une inscription dans le groupe de lecture
--  1 pour une inscription dans le groupe d''écriture

-- Mises à jour :
-- - 17/11/2020 : création de la procédure
-- - 29/06/2022 : mise en commentaire de l''inscription du role admin dans le role utilisateur qui introduit un effet de bord sur la sécurité.
-- Un groupe habilité à exécuter cette fonction doit être créé. (nombase_admin) et les droits associés à cette procédure doivent être positionnés tels que ci-dessous :
-- ALTER FUNCTION public.ps_creer_compte(compte text, mdp text, statut integer) OWNER TO postgres;
-- GRANT EXECUTE ON FUNCTION public.ps_creer_compte(compte text, mdp text, statut integer) TO base_admin;
-- REVOKE EXECUTE ON FUNCTION public.ps_creer_compte(compte text, mdp text, statut integer) FROM PUBLIC;

-- déclaration des variables de traitements
DECLARE
role_lecture text; -- nom du groupe bénéficiant des droits en lecture sur la base
   role_ecriture text; -- nom du groupe bénéficiant des droits en écriture sur la base
role_admin text; -- nom du groupe bénéficiant des droits d''exécution des procédures stockées d''administration sur la base
   req text;
base text; -- nom de la base de données courante sans le prefixe

BEGIN
select into base substring(current_database() from 4);
role_lecture = base || ''_lecture'';
role_ecriture = base || ''_ecriture'';
role_admin = base || ''_admin'';

 CASE -- Préparation de la requête à exécuter
     WHEN statut = 0 THEN
         req := $$GRANT $$ || role_lecture || $$ to $$ || compte ||  $$ ;$$;
     WHEN statut = 1 THEN
         req := $$GRANT $$ || role_ecriture || $$ to $$ || compte ||  $$ ;$$;
     else
         return ''paramètre statut doit prendre la valeur 0 pour lecture ou 1 pour écriture'';
 END CASE;

 EXECUTE req;
 
 -- Inscription du groupe des gestionnaires comme membre du role cree pour permettre  appropriation et gestion des droits sur les objets que le nouveau role pourra creer.
 -- Ce code est finalement commenté car il introduit une faille. En effet le role admin étant membre du role utilisateur il hérite de l''appartenance
 -- de ce role à des roles concernant des bases ( celà ose problème notamment dans le cas où il y a des role admin de différentes bases
 -- qui ne se recouvrent pas et que chacun veut accorder des droits à un même usager.
 -- Au final la procédure s''exécutant en tant que postgres les droits par défaut peuvent bien être définis. Seule une future appropriation
 -- des objets possédés ne sera pas possible.
 --req := $$GRANT $$ || compte || $$ to $$ || role_admin || $$ WITH ADMIN OPTION;$$;
 --EXECUTE req;
 
 -- Definition des droits par defaut sur les tables et vues
 req := $$ ALTER DEFAULT PRIVILEGES FOR ROLE $$ || compte || $$ GRANT SELECT ON TABLES TO $$ || role_lecture || $$;$$;
 EXECUTE req;
 req := $$ ALTER DEFAULT PRIVILEGES FOR ROLE $$ || compte || $$ GRANT ALL ON TABLES TO $$ || role_ecriture || $$;$$;
 EXECUTE req;
 
 RAISE NOTICE ''Extension des droits du compte - % '',req;
 return ''Extension des droits OK '' || req;

EXCEPTION
WHEN OTHERS THEN
raise notice ''% % - %'', sqlstate, sqlerrm,req;
RETURN ''Extension des droits en erreur '' || req || '' - '' || sqlerrm;
END;
';

COMMENT ON FUNCTION public.ps_etendre_compte(compte text, statut integer)
IS 'Fonction d''inscription d''un compte utilisateur existant dans un groupe prédéfini de la base courante et définition des droits par défaut pour ce compte';

This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com)