Käyttöoikeuksien määritys (DCL)

Luku videona

Video on koostettu Toni Taipaluksen kevään 2023 luentonauhoitteista. Videolla mainitut viittaukset demoihin tai tenttiin eivät pidä paikkaansa keväällä 2024. Ajankohtaiset tiedot kurssin suorittamisesta löytyvät etusivulta.

Luku tekstinä

# luku-4.5

4.5 DCL

Valtuuttaminen (Data Control Language, DCL) perustuu SQL-kielessä käyttäjien ja käyttäjäryhmien oikeuksiin. Oikeuksia voidaan myöntää erilaisiin tietokantaobjekteihin, kuten tauluihin, sarakkeisiin, näkymiin tai triggereihin. Tässä alaluvussa käsitellään valtuuttamiseen liittyvää teoriaa ja SQL-komentoja. DCL-komentoja on SQL-kielessä kaksi: GRANT ja REVOKE. Lisäksi tässä alaluvussa käsitellään roolien luonti.

4.5.1 Käyttäjät ja käyttäjäryhmät

Relaatiotietokannan käyttäjät voidaan tunnistaa SQL-rajapinnan kautta. SQL-standardi ei määritä käskyjä käyttäjien tai käyttäjäryhmien luomiseksi, mutta yleistää nämä molemmat ns. rooleiksi. Roolit voidaan määrittää toimimaan sekä käyttäjinä että käyttäjäryhminä. Käyttäjäryhmänä toimivan roolin tunnistaa tavallisesti siitä, että sitä käyttäen ei voida kirjautua sisään tietokantaan.

Rooleja voidaan myöntää toisille rooleille. Tällöin rooli perii sille myönnetyn roolin oikeudet. Tällä tavalla voidaan muodostaa käyttäjien ja käyttäjäryhmien hierarkioita, ja suurten käyttäjämäärien hallinnoiminen on vaivattomampaa.

Kuvio 4.6: Käyttöoikeuksien periytyminen.

Yllä olevassa kuviossa roolit B ja C perivät kaikki roolin A oikeudet. Edelleen roolit G ja H perivät kaikki C:n oikeudet, ts. roolilla H on kaikki C:n ja A:n oikeudet. Kun roolit K myöntää uuden oikeuden roolille C, oikeus periytyy automaattisesti myös rooleille G ja H.

Rooli luodaan komennolla CREATE ROLE, jota seuraa roolin nimi. Tavallisesti rooleja voivat luoda vain järjestelmänvalvojat. Esimerkiksi PostgreSQL-tietokannanhallintajärjestelmällä roolin voi luoda seuraavalla tavalla:

CREATE ROLE masa 
WITH PASSWORD 'jkl0088' 
IN ROLE varastotyontekijat 
LOGIN 
VALID UNTIL 'May 8 11:30:00 2016';

Komento loisi uuden roolin salasanoineen, myöntäisi roolin varastotyontekijat oikeudet uudelle roolille, sallisi roolin kirjautua ja asettaisi salasanan vanhenemaan toukokuun 8. päivä.

Rooli poistetaan komennolla DROP ROLE, jota seuraa roolin nimi. Vain roolin omistaja tai järjestelmänvalvoja voi poistaa roolin.

4.5.2 Käyttöoikeudet

Jokaisella tietokantaobjektilla on omistaja, ja omistaja on oletusarvoisesti se rooli, joka on luonut tietokantaobjektin. Omistajalla on kaikki oikeudet tietokantaobjektiinsa, ja omistaja voi antaa ja poistaa muilta rooleilta oikeuksia tähän objektiin. Alla olevassa taulukossa on listattu yleisimmät standardin mukaiset oikeudet selitteineen.

Oikeus	Oikeuttaa
CONNECT	Muodostamaan tietokantayhteyden.
SELECT	Näkemään dataa.
INSERT	Lisäämään dataa.
UPDATE	Päivittämään dataa.
DELETE	Poistamaan dataa.
EXECUTE	Käynnistämään rutiineja.
ALL PRIVILEGES	Kaikki yllä mainitut paitsi CONNECT.

SQL-standardi ei määritä komentoja DDL-käskyjen kuten CREATE, ALTER ja DROP valtuuttamiseen, mutta useimmat tuotteet määrittävät. Oletuksena kuitenkin käyttäjä voi suorittaa DDL-käskyjä omistamassaan skeemassa. Jos halutaan oikeuttaa tai poistaa oikeuksia hienorakeisemmalla tasolla, voidaan luoda näkymä, ja myöntää tarvittavat oikeudet siihen.

4.5.2.1 Käyttöoikeuksien myöntäminen

Käyttöoikeudet myönnetään komennolla GRANT. Komennon yleinen syntaksi on seuraava:

GRANT oikeus[, oikeus]* [(sarake[,sarake]*)]?
ON tietokantaobjekti
TO rooli[, rooli]*
[WITH GRANT OPTION]?;

GRANT-avainsanaa seuraa oikeus yllä olevan taulukon mukaisesti, ja oikeuksia voidaan myöntää yhdellä komennolla useita. Tietokantaobjekti voi olla esimerkiksi taulu, näkymä, proseduuri tai katalogi. Näkymien avulla oikeuksia voidaan myöntää hienojakoisemmin kuin taulujen avulla on mahdollista. WITH GRANT OPTION oikeuttaa myöntämään annettua roolia edelleen. Tarkastellaan optiota esimerkin avulla:

GRANT SELECT, UPDATE
ON tyontekija
TO matti, maija
WITH GRANT OPTION;

Nyt käyttäjät (roolit) matti ja maija voivat myöntää GRANT-komennolla sekä katselu- että päivitysoikeutta tyontekija-tauluun muille käyttäjille.

SQL-standardin mukaisesti lisäys- ja päivitysoikeutta voidaan myöntää myös sarakekohtaisesti, jos valtuutus kohdistuu tauluun tai näkymään. Tällöin oikeuden nimeä seuraa sulkeiden sisällä pilkkulista, joka sisältää sarakkeiden nimet, esimerkiksi:

GRANT UPDATE (palkka, osasto)
ON tyontekija
TO johtoporras;

Rooli voidaan myöntää toisille rooleille samalla komennolla seuraavan yleisen syntaksin mukaisesti:

GRANT rooli[, rooli]*
TO rooli[, rooli]*;

4.5.2.2 Käyttöoikeuksien poistaminen

Käyttöoikeuksia poistetaan komennolla REVOKE. Komennon yleinen syntaksi on seuraava:

REVOKE [GRANT OPTION FOR]? oikeus[, oikeus]*
ON tietokantaobjekti
FROM rooli[, rooli]*;

Valinnainen GRANT OPTION FOR -lisämääre poistaa ainoastaan edelleenvaltuutusoikeuden. Jos sitä ei käytetä, poistetaan varsinainen oikeus. Jos käyttäjältä poistetaan rooli, ja käyttäjä on valtuuttanut jotakin oikeuttaan edelleen, myös edelleen valtuutetuilta poistetaan kyseiset oikeudet:

Kuvio 4.7: Oikeuksien poistaminen, kun roolilla J on ollut edelleenvaltuutusoikeus.

Yllä olevan kuvion mukaisesti

Rooli C myöntää ensin oikeuden roolille J WITH GRANT OPTION -lisämääreellä.
Rooli J myöntää saamansa oikeuden roolille D.
Rooli C päättääkin poistaa myöntämänsä oikeuden roolilta J, jolloin
tietokannanhallintajärjestelmä poistaa automaattisesti oikeuden myös roolilta D.

Rooli voidaan poistaa toiselta roolilta seuraavan syntaksin mukaisesti:

REVOKE rooli[, rooli]*
FROM rooli[, rooli]*;

# monidcl