طَبِّقْهاتعلّم المبدأ، وطبّقه بيدكمقرر 122 · برمجة قواعد البيانات
الدرس 16 · الوحدة 7: مفاهيم الإجراءات المخزنة والدوال

المعاملات والأمان في البرامج المخزنة

برنامج مخزن آمن: لا يحفظ نصف عملية، ولا يفتح الجداول لمن لا يحتاجها. ⏱ نحو 40 دقيقة.

بعد هذا الدرس ستستطيع أن:
  • تشرح سلوك «الكل أو لا شيء» في البرامج المخزنة.
  • تستخدم COMMIT داخل الإجراء للمعالجة على دفعات.
  • تكتب دالة SECURITY DEFINER آمنة.
  • تمنح صلاحية التنفيذ وتسحبها بـ GRANT و REVOKE.
جرّب بنفسك أسئلة التقييم

١الكل أو لا شيء

كل استدعاء لدالة أو إجراء يعمل داخل معاملة Transaction: إن فشلت خطوة واحدة أُلغيت كل الخطوات قبلها. هذا الإجراء ينقل كمية من منتج إلى آخر، والمنتج 12 لا يملك إلا 3 قطع:

query.sql
CREATE PROCEDURE move_stock(from_id int, to_id int, n int)LANGUAGE plpgsqlAS $$BEGIN  UPDATE products SET stock = stock + n WHERE id = to_id;     -- الإضافة أولًا  UPDATE products SET stock = stock - n WHERE id = from_id;   -- قد تخالف CHECK (stock >= 0)END;$$; DO $$BEGIN  CALL move_stock(12, 11, 5);EXCEPTION WHEN check_violation THEN  RAISE NOTICE 'فشل النقل: المخزون غير كافٍ';END $$; SELECT id, name, stock FROM products WHERE id IN (11, 12) ORDER BY id;
✓ أُنشئ الإجراء المخزن
NOTICE: فشل النقل: المخزون غير كافٍ
✓ نُفّذت الكتلة
النتيجة · صفّان
idnamestock
11مكعبات بناء9
12لغز ألف قطعة3

الخطوة الأولى أضافت 5 قطع إلى المنتج 11، ثم فشلت الثانية. النتيجة: مخزون 11 بقي 9 كما كان. لم يُحفظ نصف العملية.

٢COMMIT داخل الإجراء: المعالجة على دفعات

الدالة لا تستطيع COMMIT، أما الإجراء فيستطيع إذا استُدعي بـ CALL خارج أي معاملة. الفائدة: عملية طويلة على ملايين الصفوف تُحفظ دفعةً دفعة، فلا يضيع كل العمل إن انقطعت في منتصفها، ولا تقفل الجدول طويلًا:

query.sql
CREATE TABLE archive_log (batch int, rows_moved int); CREATE PROCEDURE archive_in_batches()LANGUAGE plpgsqlAS $$DECLARE  n int;BEGIN  FOR b IN 1..3 LOOP    INSERT INTO archive_log VALUES (b, b * 100);    COMMIT;                       -- الدفعة b حُفظت نهائيًّا  END LOOP;END;$$; CALL archive_in_batches();SELECT * FROM archive_log;
✓ أُنشئ الجدول
✓ أُنشئ الإجراء المخزن
✓ نُفّذ الإجراء
النتيجة · 3 صفوف
batchrows_moved
1100
2200
3300
✗ خطأ
query.sql
CREATE FUNCTION f() RETURNS int LANGUAGE plpgsql AS $$BEGIN  INSERT INTO categories (name) VALUES ('عروض');  COMMIT;  RETURN 1;END; $$;SELECT f();
✓ أُنشئت الدالة
✗ خطأ (السطر 7): لا يمكن COMMIT أو ROLLBACK هنا: مسموح فقط داخل إجراء يُستدعى بـ CALL خارج أي معاملة
في الدالة f()، السطر 4 داخلها (COMMIT)
✓ صواب
query.sql
CREATE PROCEDURE p() LANGUAGE plpgsql AS $$BEGIN  INSERT INTO categories (name) VALUES ('عروض');  COMMIT;END; $$;CALL p();SELECT count(*) FROM categories;
✓ أُنشئ الإجراء المخزن
✓ نُفّذ الإجراء
النتيجة · صف واحد
count
6

COMMIT للإجراءات فقط.

٣الأمان: صلاحية التنفيذ بدل صلاحية الجداول

تطبيق نقطة البيع يحتاج أن يعرف مخزون المنتج، ولا نريد أن يقرأ جدول المنتجات كاملًا أو يعدّله. الحل: دالة بصلاحيات مالكها (SECURITY DEFINER)، ونعطي التطبيق صلاحية تنفيذها فقط:

query.sql
CREATE ROLE pos_app;                       -- دور تطبيق نقطة البيع CREATE FUNCTION stock_of(pid int) RETURNS intLANGUAGE sql STABLESECURITY DEFINER                           -- تعمل بصلاحيات مالكهاSET search_path = public                   -- تثبيت المخطط: ممارسة أمانAS $$ SELECT stock FROM products WHERE id = pid $$; REVOKE ALL ON FUNCTION stock_of(int) FROM PUBLIC;GRANT EXECUTE ON FUNCTION stock_of(int) TO pos_app; SET ROLE pos_app;                          -- نتصرف الآن كالتطبيقSELECT stock_of(1) AS مخزون_الحاسب;
✓ أُنشئ الدور
✓ أُنشئت الدالة
✓ عُدِّلت الصلاحيات
✓ عُدِّلت الصلاحيات
✓ ضُبط الإعداد
النتيجة · صف واحد
مخزون_الحاسب
7

الدالة نجحت لأنها تعمل بصلاحيات مالكها.

query.sql
CREATE ROLE pos_app;SET ROLE pos_app;SELECT * FROM products;
✓ أُنشئ الدور
✓ ضُبط الإعداد
✗ خطأ (السطر 3): لا تملك صلاحية على الجدول products (GRANT)

والدور نفسه لا يقرأ الجدول مباشرة.

المعنى
SECURITY INVOKER (الافتراضي)تعمل بصلاحيات من يستدعيها
SECURITY DEFINERتعمل بصلاحيات مالكها؛ أضف معها SET search_path
GRANT EXECUTE ON FUNCTION f TO rيسمح للدور r بتنفيذها
REVOKE ALL ON FUNCTION f FROM PUBLICيسحب التنفيذ الممنوح للجميع افتراضيًّا

لاحظ: الافتراضي معكوس. Oracle و MySQL تعملان بصلاحيات المالك ما لم تقل غير ذلك، و PostgreSQL بصلاحيات المستدعي.

توقّع وتحقّق

فشلت الخطوة الثالثة من إجراء. ماذا يحدث للخطوتين الأولى والثانية؟

أين يُسمح بـ COMMIT؟

SECURITY DEFINER يعني أن البرنامج يعمل بصلاحيات:

الافتراضي في PostgreSQL:

الخلاصة

تحدٍّ برمجي: دالة الأسعار لتطبيق الجوال

متقدم

تطبيق الجوال يتصل بالقاعدة بالدور mobile_app. نريده أن يعرف سعر المنتج بعد الضريبة فقط، دون أي صلاحية على الجداول.

المطلوب:
  • أنشئ الدور mobile_app.
  • دالة public_price(pid int) تُرجع round(price * 1.15, 2) للمنتج، بصلاحيات مالكها و SET search_path = public.
  • اسحب التنفيذ من PUBLIC وامنحه لـ mobile_app.
👀 اعرض الناتج المطلوب
✓ التحقق الآلي من حلّك:
  • الكود يعمل بلا أخطاء
  • الدالة بصلاحيات مالكها
  • mobile_app يحصل على السعر: المنتج 4 ← 86.25
  • mobile_app لا يقرأ جدول المنتجات
  • سحب الصلاحية من PUBLIC
  • تثبيت search_path

💡 تلميح 1
ابدأ من مثال stock_of في الدرس.
💡 تلميح 2
لا تمنح mobile_app أي صلاحية على الجدول.

حاول بنفسك أولًا؛ للمسألة أكثر من حلّ صحيح.

solution.sql
CREATE ROLE mobile_app; CREATE FUNCTION public_price(pid int) RETURNS numericLANGUAGE sql STABLESECURITY DEFINERSET search_path = publicAS $$ SELECT round(price * 1.15, 2) FROM products WHERE id = pid $$; REVOKE ALL ON FUNCTION public_price(int) FROM PUBLIC;GRANT EXECUTE ON FUNCTION public_price(int) TO mobile_app;
انتقل إلى تقييم الدرساختيار من متعدد، وصح وخطأ، وأكمل الفراغ، وتتبّع الناتج.ابدأ التقييم ←

📝 معلم؟ أرسل ملاحظة للمؤلف عن هذا الدرس