filtre et limitation d'accès sur une table
******************************************

Il est parfois utile de limiter la visualisation d'une table pour un utilisateur particulier.

Un exemple simple est le suivant: un commercial ne souhaite voir que c'est client.

la table client est composé comme cela:

* id
* lib
* commercial

Il y a trois possiblités:

* faire un filtre à chaque requête sql la charge de travail est du côté du développeur
* utiliser une **RULE**: la charge de travail est limité au concepteur de la base
* utilisation de **trigger**

Filtre par requête
==================

.. code-block:: sql

    select id, lib from client where commercial = 'TUTU';

Utilisation de rule
===================

.. code-block:: sql

    CREATE TABLE client (
        id         serial CONSTRAINT key_clt PRIMARY KEY,
        lib        varchar(100) NOT NULL,
        commercial varchar(100) NOT NULL
    );
    CREATE VIEW clt_view AS SELECT * FROM client WHERE commercial=current_user;
    
    CREATE OR REPLACE RULE example_1 AS
    ON INSERT TO clt_view
    DO INSTEAD INSERT INTO client (lib, commercial) VALUES (new.lib, current_user);
    
    CREATE OR REPLACE RULE example_2 AS
    ON UPDATE TO clt_view
    DO INSTEAD UPDATE client  SET lib=new.lib,  commercial= current_user where id=new.id;    
    
    CREATE OR REPLACE RULE example_3 AS
        ON DELETE TO clt_view
        DO INSTEAD DELETE FROM client  where id=old.id and commercial= current_user;
    
    INSERT INTO client(lib, commercial) VALUES ('client1','userA');
    INSERT INTO client(lib, commercial) VALUES ('client2','userB');
    INSERT INTO client(lib, commercial) VALUES ('client3','postgres');

.. warning::

    notre filtre utilise comme utilisateur le user de connexion à la base
    il faut donc pour avoir un suivi fin avoir un user de connexion par utilisateur

Maintenant suivant l'utilisateur connecté le select sur la vue clt_view  ramènera plus ou moins de lignes.

L'update et le delete ne prend en compte que l'utilisateur connecté
(un commercial ne peut supprimer un client qui ne lui appartient pas).

Le developpeur utilise la vue clt_view et nom la table client.

.. code-block:: sql

    INSERT INTO clt_view(lib) VALUES('fred');
    UPDATE clt_view SET lib='fred2' where id=4;
    DELETE FROM clt_view  where id=2;
    DELETE FROM clt_view  where id=4;

.. note::

    si on souhaite que l'update d'un client qui ne nous appertient pas retourne un erreur on
    peut utiliser le code suivant

.. code-block:: sql

    CREATE FUNCTION updateclt_trigger(old clt, new clt) RETURNS VOID AS
    $$
    BEGIN
        UPDATE client  SET lib=new.lib,  commercial= current_user where id=new.id; 
        IF found THEN
            RETURN;
        END IF;
        RAISE EXCEPTION 'Le client % n\'existe pas ou vous n\'avez pas les bonnes autorisations', new.id; 
    END;

    CREATE OR REPLACE RULE example_2 AS
    ON UPDATE TO clt_view 
    DO INSTEAD SELECT updateclt_trigger(OLD,NEW);    

utilisation de trigger
======================
Dans notre exemple seul l'utilisateur **postgres** peut modifier la table cpte

.. code-block:: sql

    CREATE TABLE cpte ( 
        id        serial CONSTRAINT key_cpte PRIMARY KEY, 
        lib       varchar(100) NOT NULL, 
        actif     boolean DEFAULT true 
    ); 
    
    CREATE OR REPLACE FUNCTION  maj_trigger() RETURNS TRIGGER AS 
     $BODY$ 
    DECLARE 
        b boolean; 
    BEGIN   
        select into b true where 'postgres'= current_user; 
        IF b = true THEN 
            RETURN NEW; 
        END IF; 
        RAISE EXCEPTION 'vous n\'avez pas les autorisations necessaire pour creer ou modifier % ', new.lib; 
    END ; 
     $BODY$ 
     LANGUAGE 'plpgsql' ; 
    
    CREATE TRIGGER insert BEFORE INSERT ON cpte 
        FOR EACH ROW EXECUTE PROCEDURE maj_trigger(); 

.. note::

    dans cette dernière utilisation la gestion du select doit être réalisé par une vue
    
    on peut imaginer de faire des triggers sur la vue cpte_view pour gérer les insert / update et delete sur la table cpte