-------------------------------------------------------------------------------
-- ex_ind_sans_confid.sql
-------------------------------------------------------------------------------

drop table billet cascade constraints;
create table billet(client varchar2(10), dest varchar2(10), prix integer);
insert into billet values('Riton', 'Rio', 100);
insert into billet values('Rita', 'Saint Trop', 50);
insert into billet values('Riton', 'Saint Trop', 50);
select * from billet; 

create or replace view vbillet as
        select client from billet where dest = 'Saint Trop';

-- je donne à Toto :
select * from vbillet;

--  select client from billet where dest = 'Saint Trop';

-------------------------------------------------------------------------------

drop table voyage;
drop table train;
create table voyage(client varchar2(10), idt integer); 
create table train(idt integer, dest varchar2(10), prix integer); 
insert into voyage values('Riton', 1);
insert into voyage values('Riton', 2);
insert into voyage values('Rita', 2);
insert into train values(1, 'Rio', 100);
insert into train values(2, 'Saint Trop', 50);
drop table billet;
select * from voyage;
select * from train;

create or replace view vbillet as
        select client
	    from voyage, train
	    where voyage.idt = train.idt
	      and dest = 'Saint Trop';

-------------------------------------------------------------------------------

select * from billetsainttrop;
insert into billet values('Riton', 'Saint Trop', 50);
select * from billetsainttrop;
delete billet where client = 'Riton';
select * from billetsainttrop;

create or replace view billetsainttrop as
    select client
        from billet
	where dest = 'Saint Trop';


-------------------------------------------------------------------------------
-- situation :
--------------

-- waller_a est le programmeur de l'application, qui maintient aussi la base
-- waller2_a est le secretaire

-- waller_a programme l'application :
drop table billet cascade constraints;
create table billet(client varchar2(10), dest varchar2(10), prix integer); 
insert into billet values('Riton', 'Rio', 100);
insert into billet values('Rita', 'Saint Trop', 50);
select * from billet;

grant select on billet to c##ewaller2_a;

-- ewaller2_a utilise l'application :
select client from c##ewaller_a.billet where dest = 'Saint Trop';

-------------------------------------------------------------------------------
-- problemes :
--------------

-- confidentialite :
-- ewaller2_a tape :
select * from ewaller_a.billet where dest <> 'Saint Trop';
select prix from ewaller_a.billet where dest = 'Saint Trop';
select * from ewaller_a.billet;

-- independance des niveaux :
-- ewaller_a tape :
drop table billet;
drop table voyage;
drop table train;
create table voyage(client varchar2(10), idt integer); 
create table train(idt integer, dest varchar2(10), prix integer); 
insert into voyage values('Riton', 1);
insert into voyage values('Rita', 2);
insert into train values(1, 'Rio', 100);
insert into train values(2, 'Saint Trop', 50);
select * from voyage;
select * from train;
grant select on voyage to c##ewaller2_a;
grant select on train to c##ewaller2_a;

-- ewaller2_a tape :
select client from c##ewaller_a.billet where dest = 'Saint Trop';
--                          *
-- ERROR at line 1:
-- ORA-00942: table or view does not exist

--------------------------------------------------------------------------------
-- gestion parfaite :
---------------------

-- ewaller_a programme l'application comme suit des le debut
-- ewaller_a tape :

-- creation table billet ci-dessus

revoke select on billet from ewaller2_a; 
  -- sauf si jamais donne

create or replace view billetsainttrop as
select client from billet where dest = 'Saint Trop';

grant select on billetsainttrop to ewaller2_a;

-- confidentialite parfaite :
-- ewaller2_a tape :
select * from ewaller_a.billetsainttrop;
-- ok
select client from ewaller_a.billet where dest = 'Saint Trop';
-- ERROR at line 1:
-- ORA-00942: table or view does not exist
-- a fortiori :
select * from ewaller_a.billet where dest <> 'Saint Trop';
-- ERROR at line 1:
-- ORA-00942: table or view does not exist
-- concept : 
select * from ewaller_a.billetsainttrop where dest <> 'Saint Trop';

-- independance des niveaux parfaite :
-- ewaller_a tape : 
drop table billet;
drop table voyage;
drop table train;
create table voyage(client varchar2(10), idt integer); 
create table train(idt integer, dest varchar2(10), prix integer); 
insert into voyage values('Riton', 1);
insert into voyage values('Rita', 2);
insert into train values(1, 'Rio', 100);
insert into train values(2, 'Saint Trop', 50);
select * from voyage;
select * from train;
create or replace view billetsainttrop as
select client from voyage, train
  where voyage.idt = train.idt 
    and dest = 'Saint Trop';

select * from ewaller_a.billetsainttrop;
-- rem : inutile refaire grant
-- ewaller2_a tape :
select * from ewaller_a.billetsainttrop;
-- ok

--------------------------------------------------------------------------------
