BSM211 Veritabanı Yönetim Sistemleri - Celal ÇEKEN, İsmail ÖZTEL, Veysel Harun ŞAHİN

Veritabanı Güvenliği, Yetkilendirme

psql Kullanımı

psql, postgresql sunucuya bağlanıp işlemler yapmamızı sağlayan konsol uygulamasıdır.

psql Uygulamasını Çalıştırma

psql -U postgres -h localhost

psql -U ayse -d pagila -h localhost

psql -U postgres

VTYS_Comp:~ vtys$ psql -U postgres
Password for user postgres:
psql (9.5.4)
Type "help" for help.

postgres=#

psql ile veritabanlarını görüntüleme

postgres=# \l
                                       List of databases
         Name         |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
----------------------+----------+----------+-------------+-------------+-----------------------
 AlisVerisUygulamasi  | postgres | UTF8     | tr_TR.UTF-8 | tr_TR.UTF-8 | 
 Musteri              | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 Northwind            | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 OgrenciBilgiSistemi  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

psql ile veritabanına bağlanma

postgres=# \c Northwind
psql (10.3, server 12.1)
WARNING: psql major version 10, server major version 12.
         Some psql features might not work.
You are now connected to database "Northwind" as user "postgres".

psql ile tabloları görüntüleme

Northwind=# \d+
                                     List of relations
 Schema |              Name               |   Type   |  Owner   |    Size    | Description 
--------+---------------------------------+----------+----------+------------+-------------
 public | CustomersContactName            | table    | postgres | 16 kB      | 
 public | Musteriler                      | table    | postgres | 0 bytes    | 
 public | Musteriler_musteriNo_seq        | sequence | postgres | 8192 bytes | 
 public | OrderCustomerEmployee           | view     | postgres | 0 bytes    | 
 public | OrderCustomerEmployee1          | view     | postgres | 0 bytes    | 
 public | OrderCustomerEmployee3          | view     | postgres | 0 bytes    | 
 public | OrderCustomerEmployee4          | view     | postgres | 0 bytes    | 
 public | SiparisMusteriSatisTemsilcisi   | view     | postgres | 0 bytes    | 
 public | UrunDegisikligiIzle             | table    | postgres | 8192 bytes | 
 public | UrunDegisikligiIzle_kayitNo_seq | sequence | postgres | 8192 bytes | 
 public | categories                      | table    | postgres | 16 kB      | 
 public | customercustomerdemo            | table    | postgres | 8192 bytes | 
 public | customerdemographics            | table    | postgres | 8192 bytes | 
 public | customers                       | table    | postgres | 56 kB      | 
 public | employees                       | table    | postgres | 16 kB      | 
 public | employeeterritories             | table    | postgres | 8192 bytes | 
 public | order_details                   | table    | postgres | 120 kB     | 
 public | orders                          | table    | postgres | 144 kB     | 
 public | products                        | table    | postgres | 8192 bytes |

psql ile sql sorgusu çalıştırma

Northwind=# select "ProductID", "ProductName"  from products;
 ProductID |           ProductName            
-----------+----------------------------------
         5 | Chef Anton's Gumbo Mix
         7 | Uncle Bob's Organic Dried Pears
        12 | Queso Manchego La Pastora
        13 | Konbu

psql - Kullanıcıları/Rolleri Listeleme

postgres=# \du
                                   List of roles
                                   
 Role name |                         Attributes                         | Member of
 
-----------+------------------------------------------------------------+-----------

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 
 testk1    |                                                            | {}

psql Çıkış Örneği

postgres=# \q
postgres=#
VTYS_Comp:~ vtys$

Yetki/Yetkilendirme

Nesnelerle ilgili sahip olunan haklara yetki denir.
Kullanıcıların/rollerin, veritabanı yönetim sistemi ve veritabanı nesneleri (tablo, görünüm, fonksiyon vb.) üzerinde hangi haklara sahip olacağının belirlenmesine yetkilendirme adı verilir.
İki tür yetki vardır:
- Temel yetkiler (rol özellikleri)
- Nesne yetkileri

Temel Yetkiler (Rol Özellikleri)

Kullanıcıların/rollerin veritabanı yönetim sistemi üzerindeki haklarını ifade eder.
- SUPERUSER, CREATEDB, CREATEROLE, CREATEUSER, INHERIT, LOGIN, REPLICATION, BYPASSRLS, NOSUPERUSER, NOCREATEDB, NOCREATEROLE, NOCREATEUSER, NOINHERIT, NOLOGIN, NOREPLICATION, NOBYPASSRLS

Nesne Yetkileri

Kullanıcıların/rollerin veritabanı nesneleri (tablo, görünüm, fonksiyon vb.) üzerindeki haklarını ifade eder.
Nesnelerin türüne göre (tablo, görünüm, fonksiyon) aşağıdaki yetkiler verilir.
- SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER,
- CREATE, CONNECT, TEMPORARY, EXECUTE, USAGE ...

Kullanıcı/Rol Katalogları

Oturum yetkilendirmesini postgres rolü olarak ayarla (Bu işlemin yapılabilmesi için superuser yetkisi gereklidir).
Böylelikle işlemler, postgres rolünün yetkileriyle yapılabilecektir.

SET SESSION AUTHORIZATION "postgres";

pg_authid ve pg_roles kataloğunu sorgula.
Bu kataloglarda roller hakkında bilgi mevcuttur.

SELECT * FROM "pg_authid";

SELECT * FROM "pg_roles";

pg_user kataloğunu sorgula.

SELECT * FROM "pg_user";

Kullanıcı/Rol Oluşturma/Değiştirme İşlemleri

Hiç bir yetkisi olmayan rol oluştur.
Rolün aynı zamanda şifesi de mevcut değildir.

CREATE ROLE "rol1";

SUPERUSER yetkisi olan rol oluştur.
SUPERUSER, nesnelerle ilgili herseyi yapma yetkisine sahiptir.

CREATE ROLE "rol2" WITH SUPERUSER;

Roller oluşturulduktan sonra düzenlenebilir.

ALTER ROLE "rol1" WITH SUPERUSER CREATEDB;

ALTER ROLE "rol1" WITH NOSUPERUSER;

ALTER ROLE "rol1" WITH LOGIN;

ALTER ROLE "rol1" WITH NOLOGIN;

abc şifresine sahip "kullanici1" adında bir kullanıcı oluştur.
abc şifresi MD5 algoritması ile kodlanır (Kullanılacak algoritma postgresql.conf içerisinde değiştirilebilir).

CREATE USER "kullanici1" WITH PASSWORD 'abc';

CREATE USER, CREATE ROLE ifadesinin bir takma isimdir.
Aralarındaki fark LOGIN seçeneğidir.
CREATE USER ifadesi varsayılan olarak LOGIN yetkili rol oluşturur.
CREATE ROLE ifadesi varsayılan olarak LOGIN yetkisi olmayan rol oluşturur.
8.1 ile birlikte user ve group kavramı yerine rol kavramı getirildi.
Bir rol, user olabilir, group olabilir veya ikisi birden olabilir.

CREATE ROLE "rol3" WITH PASSWORD 'abc' LOGIN;

Şifre kodlanarak saklanır.
Şifrenin son geçerlilik tarihi de belirtilir.

CREATE ROLE "kullanici4" WITH PASSWORD 'abc' VALID UNTIL '2020-01-01';

psql - Kullanıcı/Rol Oluşturma/Değiştirme İşlemleri

Örnek

postgres=# CREATE USER testk1 WITH PASSWORD '111111';

CREATE ROLE

postgres=#

postgres=# \du

                                   List of roles
                                   
 Role name |                         Attributes                         | Member of
 
-----------+------------------------------------------------------------+-----------

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 
 testk1    |                                                            | {}

postgres=#

Örnek
- CREATE USER ifadesi, CREATE ROLE ifadesinin bir takma isimdir.
- Aralarındaki fark LOGIN seçeneğidir.
- https://www.postgresql.org/docs/current/sql-createrole.html

postgres=# CREATE ROLE testk2 WITH PASSWORD '111111';
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 testk1    |                                                            | {}
 testk2    | Cannot login                                               | {}

postgres=#

Örnek

postgres=# CREATE ROLE testk3 WITH PASSWORD '111111' LOGIN;
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 testk1    |                                                            | {}
 testk2    | Cannot login                                               | {}
 testk3    |                                                            | {}

postgres=#

Örnek

postgres=# CREATE ROLE testk4 WITH PASSWORD '111111' LOGIN CREATEDB;
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 testk1    |                                                            | {}
 testk2    | Cannot login                                               | {}
 testk3    |                                                            | {}
 testk4    | Create DB                                                  | {}

postgres=#

Örnek

postgres=# ALTER ROLE testk2 WITH LOGIN;
ALTER ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 testk1    |                                                            | {}
 testk2    |                                                            | {}
 testk3    |                                                            | {}
 testk4    | Create DB                                                  | {}

postgres=#

Örnek

postgres=# DROP ROLE testk2;
DROP ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 testk1    |                                                            | {}
 testk3    |                                                            | {}
 testk4    | Create DB                                                  | {}

postgres=#

Kullanıcı/Rol Silme İşlemleri

DROP USER "kullanici1";

DROP ROLE "rol1";

Nesne oluşturulurken, CREATE komutunu çalıştıran rol, bu nesnenin sahibi olarak belirlenir.
Nesne sahibi (ya da SUPERUSER) nesne üzerindeki tüm haklara sahiptir.
Veritabanı sahibi olan bir rolü silmeden önce veritabanı sahipliğini başka bir role aktarmalıyız.
Bu işlemi ALTER DATABASE ile yapabiliriz.

CREATE ROLE "rol1";

ALTER DATABASE "NorthWind" OWNER TO "rol1";

DROP ROLE "rol1";

-- Kernel error: ERROR:  role "rol1" cannot be dropped because some objects depend on it DETAIL: owner of database NorthWind

ALTER DATABASE "NorthWind" OWNER TO "postgres";

DROP ROLE "rol1";

Veritabanı sahibi olan bir rolü silmeden önce veritabanı sahipliğini başka bir role aktarmalıyız.
Bu işlemi REASSIGN ile de yapabiliriz.

CREATE ROLE "rol1";

ALTER DATABASE "NorthWind" OWNER TO "rol1";

DROP ROLE "rol1";

-- Kernel error: ERROR:  role "rol1" cannot be dropped because some objects depend on it DETAIL:  owner of database NorthWind

"rol1" in sahibi olduğu tüm nesnelerin yeni sahibini "postgres" olarak belirle.

REASSIGN OWNED BY "rol1" TO "postgres";

DROP ROLE "rol1";

Bir rolü grup gibi kullanabiliriz.
Diğer rollerin bu rolden yetkilerini kalıtım olarak almasını temin edebiliriz.

CREATE ROLE "gruprol";

CREATE ROLE "rol1";

rol1 isimli rolün yetkilerine gruprol isimli rolün yetkilerini de ekle.
Temel yetkiler kalıtım olarak alınmaz. Yalnızca grubun nesneler üzerindeki yetkileri kalıtım olarak alınır.

GRANT "gruprol" TO "rol1";

Bunun yapılabilmesi için rol1 isimli rolün kalıtım alma özelliğine sahip olması gerekir. (Postgresql in yeni sürümlerinde rol oluşturulduğunda INHERIT yetkisi veriliyor)
Diğer bir deyişle INHERIT yetkisine sahip olması gerekir.
Bu yetki yoksa, yetkiler kalıtım alınmaz.

ALTER ROLE "rol1" WITH INHERIT;

CREATE ROLE "rol2" WITH INHERIT;

GRANT "gruprol" TO "rol2";

rol1 isimli role verilmiş yetkilerin (gruprol yetkileri) geri alınması.

REVOKE "gruprol" FROM "rol1";

Yetkilendirme İşlemleri

PUBLIC: Tüm roller / kullanıcılar.
kullaniciAdi: Tek bir kullanıcı.
ALL: Tüm yetkiler.

rol1 isimli role customers tablosu üzerinde seçim yapma yetkisi ver.

GRANT SELECT ON "customers" TO "rol1";

Tüm rollere customers tablosu üzerinde kayıt ekleme yetkisi ver.

GRANT INSERT ON "customers" TO PUBLIC;

rol1 isimli kullanıcıya customers tablosu üzerinde tüm yetkileri ver.

GRANT ALL ON "customers" TO "rol1";

rol1 isimli rolün customers tablosu üzerindeki güncelleme yetkisini geri al.

REVOKE UPDATE ON "customers" FROM "rol1";

rol1 isimli rolün customers tablosu üzerindeki tüm yetkilerini geri al.

REVOKE ALL ON "customers" FROM "rol1";

rol1 kullanicisinin Sema1 icerisindeki nesnelere ait tüm yetkileri geri alinir.

REVOKE ALL ON SCHEMA "Sema1" FROM "rol1";

Herhangi bir nesne üzerinde yetkiye sahip olan bir rolü silemeyiz.

CREATE ROLE "rol1";

GRANT SELECT ON "customers" TO "rol1";

DROP ROLE "rol1"; 

-- Kernel error: ERROR:  role "rol4" cannot be dropped because some objects depend on it DETAIL:  privileges for table customers

rol1 in sahibi olduğu tüm nesneleri sil (kısıtlar ihlal edilemez)

DROP OWNED BY "rol1";

DROP ROLE "rol1";

Örnek (Northwind Veri Tabanı)

CREATE ROLE "rol1";

SET SESSION AUTHORIZATION "rol1";

SELECT * FROM "customers"; 

-- Kernel error: ERROR:  permission denied for relation customer

rol1 seçme hakkına sahip olmadigi icin hata oluşur.

GRANT SELECT ON "customers" TO "rol1";

Yetkilendirme yapabilmek için oturum yetkilendirmesini "postgres" kullanıcısı şeklinde ayarla.

SET SESSION AUTHORIZATION "postgres";

rol1, kullanıcısına "customers" tablosu üzerinde seçme yetisi ver.

GRANT SELECT ON "customers" TO "rol1";

SET SESSION AUTHORIZATION "rol1";

Sorgu çalışır.

SELECT * FROM "customers";

Fonksiyonlar ve Yetkilendirme

CREATE OR REPLACE FUNCTION "milKMDonustur"(degerMil REAL, OUT degerKM REAL)
AS $$
BEGIN
    degerKM = degerMil * 1.6;
END;
$$
LANGUAGE plpgsql;

SET SESSION AUTHORIZATION "rol1";

SELECT * FROM "milKMDonustur"(3);

SET SESSION AUTHORIZATION "postgres";

REVOKE ALL ON FUNCTION "milKMDonustur"(REAL, OUT REAL) FROM "rol1";

Aşağıdaki ifade çalışır. Fonksiyonlar PUBLIC grubu için varsayılan olarak çalıştırılırlar.

SELECT * FROM milKMDonustur(3);

SET SESSION AUTHORIZATION "postgres";

REVOKE ALL ON FUNCTION "milKMDonustur"(REAL, OUT REAL) FROM PUBLIC;

SET SESSION AUTHORIZATION "rol1";

Aşağıdaki ifade çalışmaz.

SELECT * FROM milKMDonustur(3);

Şifreleme

Kullanici şifreleri ve gizli bilgiler açık olarak saklanmamalıdır.
Linux

sudo apt-get install postgresql-contrib

Kripto eklentisini oluştur.

CREATE EXTENSION "pgcrypto";

"sifrem" şifresini sha512 algoritması ile kodla.

SELECT ENCODE(DIGEST('sifrem', 'sha512'), 'hex');

"sifrem" şifresini md5 algoritması ile kodla ve sonucu "md5" ifadesi ile birleştir.

SELECT 'md5'|| MD5('sifrem');

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

VYS14.md

VYS14.md

Veritabanı Güvenliği, Yetkilendirme

psql Kullanımı

psql Uygulamasını Çalıştırma

psql ile veritabanlarını görüntüleme

psql ile veritabanına bağlanma

psql ile tabloları görüntüleme

psql ile sql sorgusu çalıştırma

psql - Kullanıcıları/Rolleri Listeleme

psql Çıkış Örneği

Yetki/Yetkilendirme

Temel Yetkiler (Rol Özellikleri)

Nesne Yetkileri

Kullanıcı/Rol Katalogları

Kullanıcı/Rol Oluşturma/Değiştirme İşlemleri

psql - Kullanıcı/Rol Oluşturma/Değiştirme İşlemleri

Kullanıcı/Rol Silme İşlemleri

Yetkilendirme İşlemleri

Örnek (Northwind Veri Tabanı)

Fonksiyonlar ve Yetkilendirme

Şifreleme

Files

VYS14.md

Latest commit

History

VYS14.md

File metadata and controls

Veritabanı Güvenliği, Yetkilendirme

psql Kullanımı

psql Uygulamasını Çalıştırma

psql ile veritabanlarını görüntüleme

psql ile veritabanına bağlanma

psql ile tabloları görüntüleme

psql ile sql sorgusu çalıştırma

psql - Kullanıcıları/Rolleri Listeleme

psql Çıkış Örneği

Yetki/Yetkilendirme

Temel Yetkiler (Rol Özellikleri)

Nesne Yetkileri

Kullanıcı/Rol Katalogları

Kullanıcı/Rol Oluşturma/Değiştirme İşlemleri

psql - Kullanıcı/Rol Oluşturma/Değiştirme İşlemleri

Kullanıcı/Rol Silme İşlemleri

Yetkilendirme İşlemleri

Örnek (Northwind Veri Tabanı)

Fonksiyonlar ve Yetkilendirme

Şifreleme