Erişimi Denetleme
Kimin neyi görebildiğini izleyin.
Erişimi Denetleme, CoddyKit'te ücretsiz bir SQL Academy dersidir. Bu, 4 dersinin 4. dersidir. Aşağıdan dersin tamamını ücretsiz okuyabilir, sonra tarayıcıda yerleşik kod editörü ve 7/24 yapay zeka koçu ile uygulamalı olarak pratik yapabilirsin. Bu, SQL Academy öğrenme yolunun bir parçasıdır ve ilerlemeniz web ve CoddyKit uygulaması arasında senkronize olur. SQL Academy kursu toplamda 4 dersten oluşur.
Erişimi Neden Denetlemelisiniz
Kimlerin hangi verilere ne zaman eriştiğini bilmek, veritabanı güvenliğinin temel taşlarından biridir. Denetim, yetkisiz erişimi tespit edebilmeniz, olayları araştırabilmeniz ve GDPR, HIPAA veya SOC 2 gibi uyumluluk gereksinimlerini karşılayabilmeniz için güvenilir bir olay izi oluşturur.
Bu derste denetim tablolarını nasıl tasarlayacağınızı, erişim olaylarını tetikleyicilerle otomatik olarak nasıl yakalayacağınızı, PostgreSQL'in yerleşik günlük kaydı özelliklerini nasıl kullanacağınızı ve şu soruyu yanıtlamak için denetim izini nasıl sorgulayacağınızı öğreneceksiniz: kim neyi görebilir?
Denetim Günlüğü Tablosu Tasarlama
İlk adım, kayda değer her olayı kaydeden özel bir tablo oluşturmaktır. İyi bir denetim günlüğü; tablo adını, işlem türünü, eski ve yeni değerleri, işlemi gerçekleştiren kullanıcıyı ve kesin zaman damgasını depolar.
Aşağıdaki örnek, satır anlık görüntülerini depolamak için JSONB sütunlarını kullanan genel amaçlı bir audit_log tablosu oluşturur; bu yapı, şema değişikliklerine gerek kalmadan her tabloyu işleyebilecek kadar esnektir.
CREATE TABLE audit_log (
id BIGSERIAL PRIMARY KEY,
event_time TIMESTAMPTZ NOT NULL DEFAULT now(),
db_user TEXT NOT NULL DEFAULT current_user,
app_user TEXT,
table_name TEXT NOT NULL,
operation TEXT NOT NULL CHECK (operation IN ('INSERT','UPDATE','DELETE','SELECT')),
row_id BIGINT,
old_data JSONB,
new_data JSONB
);Geçerli Kullanıcıyı Kaydetme
PostgreSQL, bir sorguyu kimin çalıştırdığını belirlemek için çeşitli yerleşik işlevler sağlar. current_user, herhangi bir SET ROLE sonrasında geçerli olan rol adını döndürür. session_user ise rol değiştirme gerçekleşse bile her zaman ilk giriş yapılan rolü döndürür.
Tek bir paylaşılan veritabanı rolü kullanan ancak uygulama düzeyindeki kullanıcıyı SET LOCAL app.current_user aracılığıyla aktaran uygulamalarda bu ayarı current_setting() ile okuyabilirsiniz.
-- Who is the database user right now?
SELECT current_user,
session_user;
-- Read an application-level user injected by the app layer
SELECT current_setting('app.current_user', true) AS app_user;Denetim Tetikleyicisi İşlevi Yazma
Tetikleyici işlevi, veri değişikliği olaylarını yakalamanın en güvenilir yoludur; çünkü otomatik olarak çalışır ve hiçbir uygulama kodu onu atlayamaz. Aşağıdaki işlev, bağlı olduğu herhangi bir tablodaki her INSERT, UPDATE ve DELETE işlemini günlüğe kaydeder ve eski ve yeni satır değerlerini JSONB olarak depolar.
Tetikleyiciyi çalıştıran tabloyu belirten TG_TABLE_NAME kullanımına ve satır değerlerini depolanabilir bir biçime dönüştüren row_to_json() işlevine dikkat edin.
CREATE OR REPLACE FUNCTION fn_audit_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
INSERT INTO audit_log (
db_user,
app_user,
table_name,
operation,
row_id,
old_data,
new_data
) VALUES (
current_user,
current_setting('app.current_user', true),
TG_TABLE_NAME,
TG_OP,
COALESCE(NEW.id, OLD.id),
CASE WHEN TG_OP = 'INSERT' THEN NULL ELSE row_to_json(OLD)::JSONB END,
CASE WHEN TG_OP = 'DELETE' THEN NULL ELSE row_to_json(NEW)::JSONB END
);
RETURN NULL;
END;
$$;Tetikleyiciyi Tabloya Bağlama
Tetikleyici işlevi oluşturulduktan sonra, denetlemek istediğiniz her tabloya onu bir CREATE TRIGGER ifadesiyle bağlarsınız. AFTER kullanılması, günlük girdisi oluşturulmadan önce verilerin gerçekten yazılmış olmasını sağlar. FOR EACH ROW yan tümcesi, tetikleyiciyi değiştirilen her satır için bir kez çalıştırır.
Burada tetikleyici, her INSERT, UPDATE ve DELETE işlemini otomatik olarak kaydetmek üzere varsayımsal bir patients tablosuna uygulanır.
CREATE TRIGGER trg_audit_patients
AFTER INSERT OR UPDATE OR DELETE
ON patients
FOR EACH ROW
EXECUTE FUNCTION fn_audit_changes();SELECT Sorgularını Denetleme
Veri değişikliği tetikleyicileri yalnızca yazma işlemlerini yakalar. Okuma erişimini denetlemek için farklı bir yaklaşıma ihtiyacınız vardır. Bir seçenek, belirli bağlamlarda PostgreSQL 14+ tarafından desteklenen AFTER SELECT ifade düzeyinde bir tetikleyicidir. Daha yaygın bir yaklaşım, hassas tabloyu saran bir işlev veya görünüm içinde okumaları açıkça günlüğe kaydetmektir.
Aşağıdaki örnek, hassas bir tabloyu sonuçları döndürmeden önce her okumayı günlüğe kaydeden bir işlevle sarar.
CREATE OR REPLACE FUNCTION get_patient_record(p_id INT)
RETURNS SETOF patients
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
-- Log the read access
INSERT INTO audit_log (db_user, app_user, table_name, operation, row_id)
VALUES (
current_user,
current_setting('app.current_user', true),
'patients',
'SELECT',
p_id
);
RETURN QUERY
SELECT * FROM patients WHERE id = p_id;
END;
$$;PostgreSQL Yerleşik Günlük Kaydı
PostgreSQL'in postgresql.conf dosyası, uygulama kodu gerektirmeyen güçlü sunucu tarafı günlük kaydı özellikleri sunar. log_min_duration_statement ayarlandığında, bir eşiği aşan tüm sorgular günlüğe kaydedilir. log_connections ve log_disconnections ayarlandığında, kimlerin oturum açtığı ve kapattığı kaydedilir.
Aşağıdaki sorgu, o anda etkin oturumları görmek için pg_stat_activity sistem görünümünü kullanır; bu, canlı erişimi izlemenin hafif bir biçimidir.
-- See who is currently connected and what they are running
SELECT pid,
usename AS db_user,
application_name,
client_addr,
state,
query_start,
LEFT(query, 80) AS current_query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY query_start DESC;Denetim Günlüğünü Sorgulama
Bir denetim günlüğü, etkili bir şekilde sorgulayabiliyorsanız değerlidir. Yaygın sorular arasında şunlar bulunur: bir kayda en son hangi kullanıcı erişti, zaman içinde bir satırda neler değişti ve son 24 saat içinde kaç hassas okuma gerçekleşti?
Aşağıdaki sorgu, belirli bir hasta kaydına erişen tüm kullanıcıları bulur ve en yeniden en eskiye doğru sıralar.
SELECT event_time,
db_user,
app_user,
operation,
old_data,
new_data
FROM audit_log
WHERE table_name = 'patients'
AND row_id = 42
ORDER BY event_time DESC
LIMIT 20;Şüpheli Erişim Örüntülerini Algılama
Denetim verileri toplandıktan sonra, anormallikleri işaretleyen sorgular yazabilirsiniz. Örneğin, bir kullanıcının aniden normalden çok daha fazla satır okuması veya aynı hassas kayda kısa bir zaman aralığında birden çok kez erişilmesi, veri dışarı aktarma girişimine işaret edebilir.
Aşağıdaki sorgu, son bir saat içindeki SELECT olaylarını her uygulama kullanıcısı için sayar ve 100'den fazla satır okuyan herkesi öne çıkarır.
SELECT app_user,
COUNT(*) AS records_accessed
FROM audit_log
WHERE operation = 'SELECT'
AND event_time >= now() - INTERVAL '1 hour'
GROUP BY app_user
HAVING COUNT(*) > 100
ORDER BY records_accessed DESC;Denetim Günlüğünün Korunması
Değiştirilebilen bir denetim günlüğü güvenilir değildir. Normal kullanıcıların ve uygulama rollerinin satırları silmesini veya güncellemesini engellemek için günlüğü kilitlemelisiniz. En güvenli yaklaşım, uygulama rolüne yalnızca INSERT izni vermek ve SELECT iznini özel bir denetçi rolüne ayırmaktır.
Bir denetim satırını güncellemeye veya silmeye yönelik herhangi bir girişimde istisna oluşturan bir tetikleyiciyle değişmezliği de zorunlu kılabilirsiniz.
-- Only the app role may insert; nobody may update or delete
REVOKE ALL ON audit_log FROM PUBLIC;
GRANT INSERT ON audit_log TO app_role;
GRANT SELECT ON audit_log TO auditor_role;
-- Trigger to block any tampering
CREATE OR REPLACE FUNCTION fn_protect_audit()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
RAISE EXCEPTION 'audit_log rows are immutable';
RETURN NULL;
END;
$$;
CREATE TRIGGER trg_protect_audit
BEFORE UPDATE OR DELETE ON audit_log
FOR EACH ROW EXECUTE FUNCTION fn_protect_audit();RLS İlke Kararlarını Denetleme
Satır Düzeyi Güvenlik etkin olduğunda PostgreSQL, hata oluşturmak yerine satırları sessizce gizler. Bu nedenle bir kullanıcının görmesine izin verilmeyen bir satırı okumayı deneyip denemediğini bilmek zorlaşır. Bir teknik, kısıtlayıcı ilke satırları filtrelemeden önce her zaman bir denetim kaydı ekleyen izin verici bir ilke eklemektir.
Aşağıdaki sorgu, bir tabloda hangi RLS ilkelerinin bulunduğunu ve bunların hangi rollere uygulandığını inceleme yöntemini gösterir.
-- View all RLS policies on the patients table
SELECT polname AS policy_name,
polcmd AS command,
polroles::TEXT AS applies_to,
polqual::TEXT AS using_expression,
polwithcheck::TEXT AS with_check_expression
FROM pg_policy
WHERE polrelid = 'patients'::REGCLASS
ORDER BY polname;Bilgi Sınaması
SQL'de erişimi denetleme konusundaki anlayışınızı sınayın.
Ders Özeti
Bu derste PostgreSQL'de eksiksiz bir erişim denetleme sisteminin nasıl oluşturulacağını incelediniz:
- Denetim günlüğü tablosu — kimin, neyi ve ne zaman yaptığını kaydeden JSONB tabanlı bir tablo.
- Tetikleyici işlevi —
TG_TABLE_NAME,row_to_json()vecurrent_userkullanarak kendisine bağlanan herhangi bir tablo için INSERT, UPDATE ve DELETE olaylarını otomatik olarak günlüğe kaydeder. - Okuma denetimi — verileri döndürmeden önce SELECT olaylarını günlüğe kaydeden işlevlerle hassas tabloları sarmalama.
- Dahili izleme —
pg_stat_activitycanlı oturumları gösterir; sunucu tarafı günlük kaydı, kod değişikliği yapmadan sorguları kaydeder. - Anormallik algılama — denetim günlüğü üzerindeki toplulaştırma sorguları olağandışı erişim hacmini işaretleyebilir.
- Değişmezlik — denetim tablosundaki UPDATE/DELETE izinlerini kaldırın ve müdahaleyi önlemek için engelleyici bir tetikleyici ekleyin.
İyi tasarlanmış bir denetim izi, kimin neye eriştiği sorusunu yanıtlamak için en güvenilir aracınızdır ve her türlü uyumluluk veya güvenlik soruşturmasının temelini oluşturur.
Yapay zeka eğitmeniyle SQL öğren — ücretsiz
Tarayıcında gerçek kod yaz ve çalıştır, 7/24 yapay zeka eğitmeninden anında yardım al; web'de ya da uygulamada kaldığın yerden devam et.
- Kurslar
- 46
- Dersler
- 183
Sıkça Sorulan Sorular
“Erişimi Denetleme” dersi ücretsiz mi?
Evet — “Erişimi Denetleme” dersin tüm metni burada web'de ücretsiz olarak okunabilir. Etkileşimli olarak pratik yapmak (yerleşik kod editörü ve 7/24 yapay zeka koçu) ve SQL Academy kursunun geri kalanını açmak için CoddyKit PRO'ya yükselt. SQL Academy kursu toplamda 4 dersten oluşur.
“Erişimi Denetleme” dersinde ne öğreneceğim?
Kimin neyi görebildiğini izleyin. SQL Academy ile uygulamalı kodu tarayıcıda doğrudan çalıştırarak pratik yaparsın ve 7/24 yapay zeka koçu dersi çalışırken sorularını yanıtlar.
SQL Academy öğrenmeye başlamak için deneyim gerekli mi?
Önceden deneyim gerekmez. CoddyKit'te SQL Academy, başlangıçtan ileri seviyeye kadar yapılandırıldığı için buradan başlayabilir veya başından başlayıp kendi hızında ilerleme yapabilirsin. Bu, 4 dersinin 4. dersidir.
“Erişimi Denetleme” dersi ne kadar sürer?
Çoğu CoddyKit dersi yaklaşık 5–10 dakika sürer. Her biri kısa ve etkileşimli olduğu için sabit ilerleme yaparsın ve web ile uygulama arasında tam olarak bıraktığın yerden devam edebilirsin.
Bu SQL Academy dersinde kod yazıp çalıştırabilir miyim?
Evet. Her SQL Academy dersi yerleşik bir kod editörü içerir, bu sayede tarayıcıda gerçek kod yazıp çalıştırabilir ve anlık yapay zeka geri bildirimi alırsın — yerel kurulum gerekli değildir.