Kurs SQL: Role i uprawnienia

W tym kursie opiszę czym są, do czego służą oraz jak działają uprawnienia oraz role w bazie danych Oracle.

Ten wpis jest częścią Kursu SQL Oracle

Uprawnienia

Uprawnienia (GRANT) użytkownika to prawo do uruchomienia określonej instrukcji SQL lub prawo dostępu do obiektu( tabela, widok, schemat etc.), uruchomienia pakietu PL/SQL itd. Uprawnienia można podzielić na kategorie:

• Uprawnienia systemowe (System privileges) czyli prawo do wykonania określonej akcji lub wykonania akcji na dowolnym obiekcie określonego typu. Obiekty obejmują tabele, widoki, widoki zmaterializowane, synonimy, indeksy, sekwencje, grupy pamięci podręcznej, schematy replikacji oraz funkcje, procedury i pakiety PL/SQL. Tylko administrator instancji lub użytkownik z uprawnieniami ADMIN może nadawać lub odbierać uprawnienia systemowe.
• Uprawnienia do obiektu (Object privileges) czyli prawo do wykonania określonej akcji na obiekcie lub dostępu do obiektu innego użytkownika. Obiekty obejmują tabele, widoki, widoki zmaterializowane, indeksy, synonimy, sekwencje, grupy pamięci podręcznej, schematy replikacji oraz funkcje, procedury i pakiety PL/SQL. Właściciel obiektu ma wszystkie uprawnienia do tego obiektu, a uprawnień tych nie można cofnąć. Właściciel obiektu może nadać uprawnienia do tego obiektu innym użytkownikom bazy danych. Użytkownik z uprawnieniami ADMIN może nadawać i odbierać uprawnienia do obiektów użytkownikom, którzy nie są właścicielami obiektów, do których uprawnienia są przyznawane.

Uprawnienia systemowe

W poniższej tabeli zawarłem najpopularniejsze uprawnienia systemowe:

Nadawanie uprawnień systemowych odbywa się poprzez polecenie SQL:

GRANT [nazwa_uprawnienia] TO [nazwa_użytkownika];
odbieranie natomiast
REVOKE [nazwa_uprawnienia] FROM [nazwa_użytkownika];

Aby więc przetestować działanie utwórzmy w pierwszej kolejności użytkownika testowego poleceniem:

--wykonane jako sys
CREATE USER foo IDENTIFIED BY bar;

Właśnie utworzyliśmy użytkownika o loginie foo i haśle bar.

Spróbujmy więc zalogować się na niego przy pomocy sqlplusa poleceniem:

[oracle@localhost ~]$ sqlplus foo/bar@localhost:1521/orcl
 SQL*Plus: Release 18.0.0.0.0 - Production on Sun Sep 1 12:38:19 2019
 Version 18.3.0.0.0
 Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 ERROR:
 ORA-01045: user FOO lacks CREATE SESSION privilege; logon denied

Przy próbie zalogowania użytkownika foo otrzymaliśmy komunikat ORA-01045 który oznacza, że użytkownik nie ma uprawnień do łączenia się z bazą danych.

Nadajmy, więc użytkownikowi foo uprawnienie systemowe do łączenia się z bazą przy pomocy poniższego polecenia:

--wykonane jako sys
GRANT CREATE SESSION TO foo;

Właśnie nadaliśmy użytkownikowi foo uprawnienie systemowe CREATE SESSION które pozwoli mu połączyć się z bazą. Zobaczmy, czy teraz uda się połączyć do bazy:

[oracle@localhost ~]$ sqlplus foo/bar@localhost:1521/orcl
 SQL*Plus: Release 18.0.0.0.0 - Production on Sun Sep 1 13:25:10 2019
 Version 18.3.0.0.0
 Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 Last Successful login time: Sun Sep 01 2019 12:54:03 -04:00
 Connected to:
 Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
 Version 18.3.0.0.0
 SQL> 

Powyższy zrzut z SQLPlus pokazuje, że użytkownik foo połączył się z bazą danych.

Istnieją dwa widoki umożliwiające podgląd uprawnień systemowych:

• USER_SYS_PRIVS – pokazuje uprawnienia systemowe zalogowanego użytkownika
• DBA_SYS_PRIVS – pokazuje uprawnienia systemowe wszystkich użytkowników

Przykładowe sprawdzenie powyższych widoków:

--wykonane jako foo
SELECT
    *
FROM USER_SYS_PRIVS;

--wykonane jako sys
SELECT
    *
FROM DBA_SYS_PRIVS
WHERE GRANTEE = 'FOO';

Uprawnienia do obiektu

W poniższej tabeli zawarłem najpopularniejsze uprawnienia do obiektu:

Nadawanie uprawnień do obiektu odbywa się poprzez polecenie SQL:

GRANT [nazwa_uprawnienia] ON [nazwa_obiektu] TO [nazwa_użytkownika];
odbieranie natomiast
REVOKE [nazwa_uprawnienia] ON [nazwa_obiektu] FROM [nazwa_użytkownika];

Aby przetestować działanie uprawnień do obiektu musimy posiadać przynajmniej dwóch użytkowników. W tym celu wykorzystamy utworzonego wcześniej użytkownika foo oraz utworzymy nowego bar wraz z umożliwieniem mu tworzenia połączenia do bazy:

--wykonane jako sys
CREATE USER bar IDENTIFIED BY foo;
GRANT CREATE SESSION TO BAR;

Skoro mamy utworzonych dwóch użytkowników stwórzmy więc użytkownikiem foo tabelę testową:

--wykonane jako foo
CREATE TABLE TEST_TABLE (
    ID NUMBER GENERATED ALWAYS AS IDENTITY
  , FOO VARCHAR2(255)
  , BAR NUMBER
  , CONSTRAINT ID_PK PRIMARY KEY (ID)
);

Otrzymamy wynik:

Error report -
 ORA-01031: insufficient privileges
 00000 -  "insufficient privileges"
 *Cause:    An attempt was made to perform a database operation without
        the necessary privileges.
 *Action:   Ask your database administrator or designated security
        administrator to grant you the necessary privileges 

Ponieważ nie posiadamy uprawnień do tworzenia tabel. Nadajmy więc użytkownikowi foo takie uprawnienie:

--wykonane jako sys
GRANT CREATE ANY TABLE TO foo;
GRANT CREATE ANY SEQUENCE TO foo;

W powyższym dodaliśmy również uprawnienie do tworzenia sekwencji ponieważ kolumna ID tworzona jest wraz z sekwencją.

Ponówmy próbę utworzenia tabeli.

Table TEST_TABLE created.

Zobaczmy więc czy użytkownik bar ma do niej dostęp:

--wykonane jako bar
SELECT * FROM foo.TEST_TABLE;

Otrzymuje komunikat:

ORA-00942: table or view does not exist
 00000 -  "table or view does not exist"
 *Cause:    
 *Action:
 Error at Line: 1 Column: 19 

Dzieje się tak ponieważ użytkownik bar nie ma uprawnień do podglądu danych z tabeli TEST_TABLE użytkownika foo.

Jako użytkownik foo utworzyliśmy tabelę dlatego też użytkownik foo ma możliwość nadawania uprawnień do tej tabeli innym użytkownikom np.

--wykonane jako foo
GRANT SELECT ON TEST_TABLE TO bar;

Teraz gdy użytkownik bar wykona polecenie SELECT na tabeli TEST_TABLE otrzyma wyniki.

Użytkownik bar jednak nadal nie może dodawać ani modyfikować danych w ramach tabeli TEST_TABLE. przy próbie modyfikacji lub dodania rekordu otrzyma komunikat:

SQL Error: ORA-01031: insufficient privileges
 00000 -  "insufficient privileges"
 *Cause:    An attempt was made to perform a database operation without
        the necessary privileges.
 *Action:   Ask your database administrator or designated security
        administrator to grant you the necessary privileges 

Role

W bazie danych zazwyczaj mamy wielu użytkowników oraz wiele obiektów. Dodając nowego użytkownika należy nadać mu podstawowe uprawnienia do obiektów w bazie natomiast tworząc nowy obiekt powinniśmy nadać wszystkim zainteresowanym użytkownikom dostęp do niego. Przy 100 użytkwonikach w systemie przy tworzeniu nowego obiektu powinnyśmy wykonać operację 100 grantów. Jest to nieefektywne oraz trudne w zarządzaniu dlatego też w Oracle istnieją ROLE. Rola jest zgrupowaniem uprawnień oraz innych ról.

W Oracle możemy wyróżnić role predefiniowane oraz utworzone przez użytkowników. Najpopularniejsze role predefiniowane to:

• CONNECT – rola uprawnia do połączenia z bazą danych
• PUBLIC – rola którą posiadają wszyscy użytkownicy
• DBA – Rola posiadająca wszystkie uprawnienia umożliwiające zarządzanie bazą danych

Tworzenie roli oraz usunięcie roli odbywa się poprzez polecenia SQL:

--rola
CREATE ROLE [nazwa_roli]
--rola zabezpieczona hasłem
CREATE ROLE [nazwa_roli] IDENTIFIED BY [hasło]
--usunięcie roli
DROP ROLE [nazwa_roli]

Aby roli nadać lub odebrać uprawnienie wykonujemy polecenia SQL:

--Nadajemy uprawnienie do roli
--dla uprawnienia systemowego
GRANT [nazwa_uprawnienia] TO [nazwa_użytkownika];
--dla uprawnienia do obiektu
GRANT [nazwa_uprawnienia] ON [nazwa_obiektu] TO [nazwa_użytkownika];

--Odbieramy uprawnienie roli
--dla uprawnienia systemowego
REVOKE [nazwa_uprawnienia] FROM [nazwa_użytkownika];
--dla uprawnienia obiektowego
REVOKE [nazwa_uprawnienia] ON [nazwa_obiektu] FROM [nazwa_użytkownika];

Jeżeli wykonałeś wszystkie skrypty dotychczas to posiadasz użytkownika foo wraz z tabelą TEST_TABLE oraz użytkownika bar nie posiadającego obiektów. Utwórzmy więc użytkownikowi foo dwie tabele:

-- wykonane jako foo
CREATE TABLE T1 (
    COL1 VARCHAR2(255)
);

CREATE TABLE T2 (
    COLN VARCHAR(6)
);

Nadajmy użytkownikowi foo uprawnienie do tworzenia rol:

--wykonane jako sys
GRANT CREATE ROLE TO foo;

Oraz utwórzmy rolę która pozwoli na operację SELECT, INSERT oraz UPDATE do tych tabel:

--wykonane jako foo
CREATE ROLE MY_ROLE;

GRANT SELECT, INSERT, UPDATE ON T1 TO MY_ROLE;
GRANT SELECT, INSERT, UPDATE ON T2 TO MY_ROLE;

GRANT MY_ROLE TO bar;

Po przelogowaniu się użytkownika bar będzie miał dostęp do tabel T1 oraz T2.

Podsumowanie

• Do nadawania uprawnień służy komenda SQL GRANT
• Do odbierania uprawnień służy komenda SQL REVOKE
• Uprawnienia dzielimy na uprawnienia systemowe oraz do obiektów
• Aby grupować uprawnienia używamy ROLE
• Role może zawierać uprawnienia systemowe, do obiektów oraz inne role