8. CVIČENÍ

Pohledy

Na pohled se lze dívat jako na dynamickou tabulku vzniklou provedením různých operací nad jednou nebo více tabulkami. Je to vlastně virtuální relace, která fyzicky neexistuje v databázi, ale je vytvořena na zálkadě požadavku uživatele. Pohled je možné považovat za jistou formu podprogramu v SQL.

Pohled lze definovat příkazem CREATE VIEW :

CREATE VIEW jméno_pohledu [ (jméno_sloupce [, ...]) ]
  AS dotaz [ WITH [ CASCADED | LOCAL ] CHECK OPTION ];

• Horizontální pohled :

CREATE VIEW cd_beatles AS
  SELECT  *
    FROM  cd
   WHERE  interpret like '%Beatles%';

SQL> desc cd_beatles
 Name                            Null?    Type
 ------------------------------- -------- ----
 INTERPRET                                VARCHAR2(35)
 TITUL                                    VARCHAR2(50)
 STOPA                                    NUMBER(2)
 PISEN                                    VARCHAR2(50)
 D_MIN                                    NUMBER(2)
 D_SEC                                    NUMBER(2)
 POZNAMKA                                 VARCHAR2(30)
 ID                                       NUMBER(5)

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID                               
------------------------------ ------- ----------                               
CD                             TABLE                                            
CD_BEATLES                     VIEW                                             
PISNE                          TABLE                                            
TITULY                         TABLE                                            

4 rows selected.

SELECT  stopa, pisen
  FROM  cd_beatles
 ORDER  BY stopa;

SELECT  COUNT(*) FROM cd_beatles;

• Vertikální pohled :

CREATE VIEW cd_prehled ( skladatel, dilo ) AS
  SELECT  DISTINCT interpret, titul
    FROM  cd;

• V pohledu lze použít i agregační funkce, seskupování záznamů a spojení více tabulek :

CREATE VIEW cd_stat ( skladatel, dilo, pocet_stop, delka ) AS
  SELECT  a.interpret, a.titul, count(b.stopa), sum(b.d_min)
    FROM  tituly a, pisne b
   WHERE  a.id = b.id
   GROUP  by a.interpret, a.titul;

Zrušení pohledu :

Pohled lze zrušit příkazem DROP VIEW :

DROP VIEW cd_beatles;

Aktualizovatelné pohledy

ISO standard specifikuje následující vlastnosti aktualizovatelného pohledu :

• V dotazu nesmí být klauzule DISTINCT, tj. nesmí být eliminovány duplicitní záznamy.
• Každý jeho sloupec musí být přímo sloupcem tabulky, tj. nesmí to být ani výraz ani agregační funkce a každý sloupec tabulky smí být vybrán jen jednou.
• Pohled je specifikován nad jedinou tabulkou a v případě, že je nad pohledem musí tento pohled splňovat všechny tyto podmínky. To znamená, že pohled nesmí být spojením více tabulek ani jejich sjednocením, průnikem či rozdílem (UNION, INTERSECT, EXCEPT).
• Klauzule WHERE nesmí obsahovat vnořený příkaz SELECT, který se odkazuje na tabulku specifikovanou v klauzuli WHERE.
• V dotazu nesmí být použita klauzule GROUP BY nebo HAVING.

Aktualizovatelné pohledy se používají především pro zajištění nezávislosti dat a jejich bezpečnosti. Následující příklad ukazuje obě výhody na konkrétním případu. V tabulce predmety jsou uloženy informace k jednotlivým předmětům a každá katedra do ní přistupuje přes svůj pohled.

Pozn.: v následujícím příkladě není ukázáno řešení přístupových práv k tabulce předměty. Nastavování přístupových práv bude ukázáno níže (příkaz GRANT).

CREATE TABLE predmety (
  zkratka       VARCHAR2(10) NOT NULL,
  nazev         VARCHAR2(30) NOT NULL,
  garant        VARCHAR2(20),
  kredity       NUMBER(2),
  katedra       VARCHAR2(3)  NOT NULL
);

INSERT INTO predmety VALUES ('PC', 'Programovani v C', 'Herout', 5, 'KIV');
INSERT INTO predmety VALUES ('KP', 'Konstrukce pocitacu', 'Dudak', 5, 'KIV');
INSERT INTO predmety VALUES ('PL', 'Plavani', 'Plavcik', 2, 'KTS');
COMMIT;

CREATE VIEW kiv_predm AS
  SELECT  zkratka, nazev, garant, kredity, katedra
    FROM  predmety
   WHERE  katedra = 'KIV';

INSERT INTO kiv_predm VALUES ('HB', 'Hra na banjo', 'Mladek', 2, 'KHK');
ROLLBACK;

CREATE VIEW kiv_predm AS
  SELECT  *
    FROM  predmety
   WHERE  katedra = 'KIV'
WITH CHECK OPTION;

INSERT INTO kiv_predm VALUES ('HB', 'Hra na banjo', 'Mladek', 2, 'KHK');

INSERT INTO kiv_predm VALUES ('HB', 'Hra na banjo', 'Mladek', 2, 'KIV');
COMMIT;

ALTER TABLE predmety ADD pocet_studentu NUMBER(3) DEFAULT 0;

INSERT INTO kiv_predm VALUES ('PD', 'Prenos dat', 'Max', 5, 'KIV');
COMMIT;
SELECT * FROM kiv_predm;

Řízení přístupu

SQL umožňuje i řídit přístup ke sdíleným objektům (tabulkám, pohledům, uloženým procedurám apod.). Přístupová práva, popř. roli lze cizímu uživateli povolit pomocí příkazu GRANT, odebrat je lze pomocí příkazu REVOKE. Syntax těchto příkazů je následující :

GRANT <práva|role> [,<práva|role>] TO <uživatel> [, <uživatel>] [WITH ADMIN OPTION];

REVOKE <práva|role> [,<práva|role>] FROM <uživatel> [, <uživatel>];

GRANT SELECT ON potraviny TO kasak;

GRANT DELETE ON potraviny TO kasak;

REVOKE DELETE ON potraviny FROM kasak;

GRANT select ON verejne TO PUBLIC;

CREATE ROLE cteni_tab;

GRANT SELECT ON tab_a TO cteni_tab;
GRANT SELECT ON tab_b TO cteni_tab;

CREATE ROLE zapis_tab;

GRANT INSERT ON tab_a TO zapis_tab;
GRANT INSERT ON tab_b TO zapis_tab;

GRANT cteni_tab TO kasak;
GRANT cteni_tab TO jarda;
GRANT zapis_tab TO jarda;

REVOKE zapis_tab FROM jarda;

Jaká práva můžete přidělovat uživateli v RDBMS Oracle ukazuje přehledová tabulka včetně již předdefinovaných rolí.

Malá případová studie

Představme si malou databázi, ve které jsou v tabulce PREDMETY uloženy informace o jednotlivých předmětech. Do této databáze mají přístup všichni garanti předmětů. Chceme, aby každý garant mohl dle libosti přidávat nové předměty, aktualizovat a mazat pouze vlastní. Samozřejmě by měl mít možnost prohlížet si i ostatní předměty.

K těmto účelům si vytvoříme dva pohledy :

• vsechny_predmety, jehož prostřednictvím si bude moci každý garant pouze číst informace o předmětech a
• moje_predmety, ve kterém bude mít plnou kontrolu pouze nad vlastními předměty.

CREATE TABLE predmety (
  zkratka   VARCHAR2(3)  NOT NULL,
  nazev     VARCHAR2(25) NOT NULL,
  kredity   NUMBER(5),
  garant    VARCHAR2(30) NOT NULL
);

CREATE VIEW vsechny_predmety AS
  SELECT  *
    FROM  predmety;

CREATE VIEW moje_predmety AS
  SELECT  *
    FROM  predmety
   WHERE  garant = user
  WITH CHECK OPTION;

GRANT SELECT ON vsechny_predmety TO PUBLIC;

GRANT SELECT, INSERT, UPDATE, DELETE ON moje_predmety TO PUBLIC;

INSERT INTO admin.moje_predmety VALUES ('PD', 'Prenos dat', 6, 'OTTA');