حينما فشلت المساعي بين القائد المغولي هولاكو خان، والخليفة العباسي المستعصم، بمنع الحرب وتجنب المواجهة، عقد هولاكو النية على احتلال بغداد عاصمة الخلافة الإسلامية، فأعطى أوامره بانتقال جيوش جرماجون وبايجو نوبان من معاقلهما في آسيا الصغرى، وأن تسير على ميمنة الجيوش الزاحفة على بغداد إلى الموصل عن طريق أربيل، ثم تعبر جسر الموصل وتعسكر في الجانب الغربي من بغداد، حتى إذا جاءت الجيوش من المشرق تخرج إليها من هذه الناحية، ويزحف على رأس الجناح الأيسر كل من كيتوبوقا، وقدسون، وترك إيلكا ـ كبار قادة هولاكو ـ من حدود إيران باتجاه بغداد مباشرة.
أما هولاكو نفسه فقد كان على رأس قلب الجيش، وتحرك في أوائل المحرم سنة 655هـ الموافق 18 يناير 1258م نحو نهر دجلة، ومر هولاكو في طريقه على مدينة كرمنشاة فدمرها على طريقة المغول التقليدية، وتعمد هولاكو بهذا التدمير البربري إلقاء الرعب في النفوس، ورسالة إنذار للبغداديين بسوء المصير المنتظر.
عسكر قلب الجيش على شاطئ نهر حلوان في التاسع من شهر ذي الحجة، وأقاموا هناك حتى الثاني والعشرين منه، حتى يتمكن جيش كيتو بوقا من إنجاز مهامه، والوصول إلى مواقعه المتفق عليها في الوقت المحدد، كما تمكنت عدة فرق أخرى من عبور نهر دجلة في الوقت المقرر.
في ذلك الوقت كان مجاهد الدواتدار القائد العام لجيش الخليفة معسكرا بجنوده بين بعقوبة وباجسري، ولما سمعوا بقدوم المغول إلى الضفة الأخرى عبروا نهر دجلة واشتبكوا في قتال شرس مع المغول في حدود الأنبار على باب قصر المنصور، على بعد تسعة أميال من بغداد.
في تلك النواحي كان يوجد نهر عليه سد كبير فتحه المغول، فغمرت المياه كل الصحراء الواقعة خلف جيش الخليفة، وفي فجر يوم الخميس من المحرم دهم المغول الجيش المحاصر بين مائين، وأوقعوا به هزيمة منكرة، بلغ عدد القتلى فيها اثنا عشر ألف مسلم، فضلا عمن غرق أو قضى نحبه في الوحل.
فر قائد جيش الخليفة مع عدد قليل من جنوده، وعادوا إلى بغداد، كما هرب البعض إلى المدن الأخرى مثل الحلة والكوفة، وفي يوم الثلاثاء منتصف المحرم تقدم قادة الجيش المغولي بوقا تيمور وبايجو وسونجاق، تقدموا إلى بغداد واستولوا على الجانب الغربي منها، ونزلوا في ضواحي المدينة على شواطىء نهر دجلة، وفي الوقت نفسه وصل بوقا نويان من ناحية صرصر، وتحرك هولاكو من خانقين حيث كان معسكرا، وواصل سيره نحو بغداد حتى نزل في الجهة الشرقية منها، ثم تدفقت بعد ذلك باقي الجيوش كالنمل والجراد من كل جهة وناحية، فحاصروها من جميع أطرافها حتى أصبحوا حولها كسور بشري.
وفي صباح يوم 22 من المحرم 656هـ الموافق 30 يناير 1258م، أعطيت الأوامر لقادة الميدان باقتحام المدينة من جميع الجهات، فقام المغول بشن هجوم كاسح على أسوار بغداد، استخدموا فيه المنجانيق على نطاق واسع، وأحدث ثقل الحجارة وقوة اندفاعها ثغرات كبيرة في برج العجمي ـ أحد أكبر الأبراج بسور بغداد ـ ورافق هذا الهجوم الوحشي قرع الطبول وصراخ المغول المرعب وصيحاتهم الحادة، فانهارت أعصاب الخليفة، وعندئذ أرسل إلى هولاكو يخبره بموافقته على جميع شروطه للتسليم، فرد هولاكو على رسل الخليفة: هذه الشروط طلبتها وأنا على باب همدان، أما الآن فأنا على باب بغداد.
وفي يوم الجمعة 26 من المحرم إنهار برج العجمي، فانطلق المغول كالإعصار يجرفون كل حي في طريقهم، وفي يوم الاثنين 28 منه تسلق بعضهم السور عنوة، وأخلوا الأسوار من جند الخليفة، وما كان المساء يحل حتى تسلم المغول جميع الأسوار الشرقية.
على إثر ذلك أمر هولاكو بإقامة جسر في أعلى بغداد وفي أسفلها، وإعداد السفن ونصب المنجانيق، وكان بوقا تيمور قد رابط مع عشرة آلاف جندي على طريق المدائن والبصرة ليصد كل من يحاول الهرب بالسفن، ولما حمي وطيس القتال داخل بغداد، وضاق الحال بالمسلمين، أراد الدواتدار أن يركب سفينة ويهرب، لكن جنود المغول أفشلوا هروبه وأعادوه مهزوما مكسورا.
وفي ليلة الثلاثاء التاسع والعشرين من المحرم، خرج من بغداد للقاء هولاكو أبوالفضل عبدالرحمن بن الخليفة المستعصم، يرافقه جماعة من كبار رجال الدولة، ومعهم أموال طائلة وهدايا ثمينة لم تقبل منهم، وفي اليوم التالي خرج بن الخليفة الأكبر مع جماعة من المقربين للشفاعة فلم يجدوا آذانا صاغية.
أخيرا أرسل هولاكو برسالة قصيرة للخليفة يقول فيها: إن الرأي للخليفة، فله أن يخرج أو لايخرج، وسيكون جيش المغول مقيما على الأسوار إلى أن يخرج سليمان شاة والدواتدار، وفي يوم الخميس خرج الرجلان لمقابلة هولاكو، ولكنه أعادهما مرة أخرى ليخرجا أتباعهما، وكل ما يخصهما، بحجة أنهم سينفون إلى مصر والشام، وخرج معهما جند بغداد وكثير من السكان، وكانوا خلقا لا يحصى، مؤملين أن يجدوا طريقا للخلاص، بيد أن أملهم قد خاب، فقد قتلهم المغول بلا رحمة.
وفي يوم الجمعة الثامن من صفر قتل مجاهد الدواتدار وسليمان شاة وجميع أتباعهم وأشياعهم، وأرسلت رؤوس الثلاثة على يد الملك الصالح بن بدر الدين لؤلؤ إلى الموصل، وكان بدر الدين صديقا لسليمان شاة فبكى، ولكنه علق رؤوسهم خوفا على حياته.
المشهد الأخير
ثم حلت الفاجعة الكبرى، في يوم عد على أنه من أسود الأيام في تاريخ الأمة الإسلامية، يوم الأحد 14 من صفر سنة 656 هـ الموافق 10 فبراير سنة 1258م، إذ خرج الخليفة المستعصم من بغداد ومعه أبناؤه الثلاثة أبوالفضل عبدالرحمن، وأبوالعباس أحمد، وأبوالمناقب مبارك، يرافقهم ثلاثة آلاف من السادات والأئمة، والقضاة والأكابر والأعيان، لتسليم أنفسهم وعاصمة الخلافة الإسلامية بلا قيد ولا شرط، ورافق هذا الخروج الجماعي للاستسلام الذليل، صراخ وندب وصياح من النساء، وارتفعت أكف عشرات الآلاف من المسلمين في وقت واحد تتضرع إلى الله أن يرفع عنهم الغمة، في جو قاتم مشحون بالرعب والدماء ورائحة الموت.
استقبل هولاكو الخليفة استقبالا لا ينم عن غضب منه، بل سأله بأسلوب مهذب عن صحته، وكلمه بالحسنى، وطلب منه أن يأمر بخروج كل سكان المدينة من منازلهم ومخابئهم حتى يحصوهم، فخالت على الخليفة الخدعة، وأذعن لطلبه، وخرج المنادون في كل أحياء بغداد ليعلنوا على المسلمين، أن كل من يود إنقاذ حياته وصيانة ماله وعرضه، فليخرج من المدينة، ويسلم ما في حوزته من سلاح للمغول.. ووقع الناس في بلبلة كبيرة، فمنهم من صدق وسلم سلاحه، ومنهم من ارتاب من سلامة الأوامر وصحتها، فاحتفظ واعتصم بداره وبقي بجانب عائلته.
نصب المغول خياما على امتداد نهر دجله لاستلام السلاح، وجاء المسلمون جماعات يسلمون سلاحهم، وكل من دخل خيمة لتسليم سلاحه خرج من الناحية الأخرى جثة هامدة، وأدرك الأهالي أن الفخ المنصوب لهم، وأن المغول يذبحونهم كقطعان الغنم، ويرمون بجثثهم في مجار مائية متفرعة من نهر دجلة، فانتشر الخبر بسرعة، وعاد الناس للاختفاء عنهم.
وفي يوم الأربعاء 17 من صفر 656 هـ الموافق 13 فبراير 1258م، أعطيت الأوامر بإباحة المدينة بالكامل، وتم توزيع قادة المغول والضباط وفرق الجيش على أحياء ودروب بغداد، يفعلون فيها مايشاءون، فاجتاح المغول المدينة بلا أية ضوابط، فقتلوا الرجال والأطفال، وهتكوا أعراض النساء، وبقروا بطون الحوامل، وقتلوا كل حي رأوه، ولم يسلم منهم إلا من اختفى منهم في باطن الأرض، أو تصنع الموت ونام بين الجثث المقتولة !!.
ولم يقف الأمر عند هذا فقط، بل راحوا يخربون مباني المدينة، فهدموا جامع الخليفة، ومشهد الإمام موسى الكاظم، ونبشوا قبور الخلفاء في الرصافة، ودمروا المساجد ليستولوا على الذهب المزينة به قبابها، وهدموا القصور بعد أن استولوا على كل ما فيها من تحف نادرة ومجوهرات، ، ثم أضرموا النار في المدينة لتأتي على الأخضر واليابس فيها.
رواية شاهد عيان
من بين شهود العيان الذين كتبت لهم النجاة من هذا اليوم الرهيب، عبدالمؤمن مغني الخليفة المستعصم ونديمه، قال في روايته عن هذا اليوم:
طلب هولاكو من رؤساء البلد أن يقسموا دروب بغداد ومحالها وبيوت ذوي يسارها على أمراء دولته فقسموها، وجعلوا كل محلة أو محلتين أو سوقين باسم أمير كبير، فوقع الدرب الذي أسكنه في حصة أمير مقدم عشرة آلاف فارس اسمه “بانوانوين”، وكان هولاكو قد رسم لبعض الأمراء أن يقتل ويأسر وينهب مدة ثلاثة أيام، ولبعضهم يوم حسب طبقتهم، فلما دخل الأمراء إلى بغداد ، فأول درب جاء إليه الدرب الذي أنا ساكنه، وكان قد اجتمع عندي كثير من ذوي اليسار، واجتمع عندي نحو خمسين جوقة من أعيان المغاني من ذوي المال والجمال، فوقف بانوانوين على باب الدرب، وهو مدبس بالأخشاب والتراب، فطوقوا الباب وقالوا:
● افتحوا لنا الباب وادخلوا في الطاعة ولكم الأمان، وإلا حرقنا الباب وقتلناكم
● قلت: السمع والطاعة
ففتحت الباب وخرجت إليه وحدي وعليّ ثياب وسخة، وأنا أنتظر الموت، فقبلت الأرض بين يديه، فقال للترجمان: إذا أردتم السلامة من الموت فاحملوا لنا كذا وكذا، وطلب شيئا كثيرا، فقبلت الأرض مرة ثانية، وقلت: كل ما طلب الأمير يحضر، وقد صار كل ما في الدرب بحكمك، فمر جيوشك ينهبون باقي الدروب المعينة، وأنزل حتى أضيفك ومن تريد من خواصك، فأجمع لك ماطلبت.
فشاور أصحابه ونزل في نحو ثلاثين رجلا، فأتيت به إلى داري، وفرشت له الفرش الخليفية الفاخرة، والسور المطرزة بالزركش، وأحضرت له في الحال أطعمة، مقليات ومشويات وحلوى، فلما فرغ من الأكل عملت له مجلسا ملوكيا، وأحضرت له الأواني المذهبة من الزجاج، وأواني فضة فيها شراب مروق، فلما دارت الأقداح وسكر قليلا، اخترت عشر جوق كلهم نساء، كل جوقة تغني بملهاة غير ملهاة أختها، وأمرتهم فغنوا كلهم، فارتج المجلس وطرب، وانبسطت نفسه، وضم واحدة من المغنيات أعجبته، فواقعها في المجلس ونحن نشاهده.
وتم يومه في غاية الحسن، فلما كان وقت العصر، حضر أصحابه بالنهب والسبايا، فقدمت له ولأصحابه الذين كانوا معه تحفا جليلة من أواني الذهب والفضة، ومن الأقمشة الفاخرة، واعتذرت له على التقصير، وقلت له جئتني على غفلة، لكن غدا أعمل للأمير دعوة أحسن من هذه، فركب وقبلت ركابه، ولما رجعت جمعت أهل الدرب من أهل اليسار وقلت لهم انظروا لأنفسكم هذا الرجل غدا عندي وبعد غد وكل يوم، أريد أضعاف المتقدم، فجمعوا لي من بينهم ما يساوي خمسين ألف دينار من أنواع الذهب والأقمشة الفاخرة والسلاح.
فلما طلعت الشمس إلا وقد جاءني ومعه نساؤه، فرأى ما أذهله، فقدمت له ولنسائه من الذخائر والنقد ما قيمته عشرون ألف دينار، وقدمت له في اليوم الثالث لآلىء نفيسة وجواهر ثمينة وبغلة جميلة، وقدمت لجميع من معه، وقلت له هذا الدرب قد صار بحكمك، فإن تصدقت على أهله بأرواحهم، فقال من أول يوم وهبتهم أرواحهم، وما حدثتني نفسي بقتلهم ولا سبيهم، لكن أنت تجهز معي قبل كل شيء إلى حضرة الخان، فقد ذكرتك عنده، وقدمت له شيئا من المستظرفات التي قدمتها لي، فأعجبته فرسم بحضورك.
ويواصل مغني الخليفة ونديمه شهادته فيقول:
دخل هولاكو بغداد لمشاهدة قصر الخليفة، فأعجب ببنائه وهندسته والسجاد العجمي الذي يكسو جدرانه، ثم أشار بإحضار الخليفة المستعصم، فجيء به مكبلا بالسلاسل شاحب اللون حزين، فقال له هولاكو ساخرا: إنك الآن مضيف ونحن الضيوف، فهيا أحضر ما يليق بنا، ومن فرط الخوف صدق الخليفة هذا الكلام, وبلغ من الدهشة درجة لم يعد يعرف أين وضع مفاتيح الخزائن، فأمر بكسر الأقفال وأخرج منها ألفي ثوب، وعشرة آلاف دينار، ونفائس ومرصعات وعددا من الجواهر، قدمها جميعا إلى هولاكو الذي لم يعر تلك الأشياء التفاتا، ومنحها كلها للأمراء والحاضرين، ثم قال للخليفة: إن هذه الأموال التي تملكها على الأرض ظاهرة، وهي ملك عبيدنا، لكن أذكر ما تملكه من الدفائن وأين توجد؟
عندئذ اعترف الخليفة بوجود حوض مملوء بالذهب في ساحة القصر، فحفروا الأرض حتى وجدوه، وكان بالفعل مملوء بالجواهر المؤلفة من الإبريز والذهب والفضة والألماس والأحجار الكريمة، مما أدخره العباسيون من خزائن قصر بغداد وجمعوها خلال خمسة قرون، وكانت كل قطعة منها تزن مائة مثقال.
700 زوجة وسرية للخليفة!!
في يوم الجمعة 19 صفر أصدر هولاكو أوامره بإحصاء نساء الخليفة، فعدوا سبعمائة زوجة وسرية وألف خادمة، فلما اطلع الخليفة على تعداد نسائه جاء إلى هولاكو متضرعا وقال له: مٌن علىَ بأهلي حرمي اللاتي لم تطلع عليهن الشمس والقمر، فأخرج الخليفة مائة امرأة من أقاربه والمحببات إليه، وألحق هولاكو ما استرعين نظره بحريمه، ثم وزع الباقي على الأمراء وقادة جيشه.
وبعدما تم قتل ما يقرب من ثمانمائة ألف نفس في بغداد، وتخريب آلاف المباني والقصور، صدرت الأوامر بالكف عن القتل، فخرج من تحت الأرض من كان مختبئا كأنهم موتى، حتى أنكر بعضهم بعضا، ووجدوا أرض بغداد مخضبة بالدم، مليئة بجثث القتلى، وهواؤها فاسدا عفنا من رائحة الجثث المترممة ولم تجد من يدفنها، وانتشرت الأوبئة والأمراض من جراء ذلك، حتى أن هولاكو نفسه غادر بغداد هربا من رائحتها إلى قرية وقف، وهناك استدعى الخليفة العباسي الذي يأس من إنقاذ حياته، ولذلك استأذن هولاكو في الذهاب إلى الحمام ليجدد اغتساله، فأمر هولاكو بأن يصحبه خمسة من المغول، لكن الخليفة رفض قائلا: أنا لا أريد أن أذهب بصحبة خمسة من الزبانية، وفي مساء نفس اليوم 24 صفر 656 هـ الموافق 20 فبراير سنة 1258 م ، قتل الخليفة وكان عمره 46 سنة، وابنه الأكبر أبوالعباس أحمد وعمره كان 25 سنة، وابنه الأوسط أبوالفضل عبدالرحمن وكان عمره 23 سنة، وأسر ولده الأصغر مبارك، وأسرت إخواته الثلاث فاطمة وخديجة ومريم، وقتل خمسة من الخدم كانوا في معية الخليفة، وقتلوا كل من وجدوه حيا من العباسيين، وتم أسر ما يقرب من ألف بكر من نساء دار الخلافة، وفي يوم الجمعة 23 من ربيع الأول رحل هولاكو عن بغداد قاصدا معسكره في خانقين، حيث أرسل لأخيه منكوبان كثيرا من الغنائم والأموال التي نهبت من بغداد.
يقول ابن كثير: اختلف الناس في عدد من قتل ببغداد من المسلمين في هذه الواقعة، فمنهم من قال ثمانمائة ألف، ومنهم من قال ألف ألف وثمانمائة ألف، وقيل بلغ القتلى ألفي ألف نفس، وقال: كانت جثث القتلى في الطرقات كأنها التلول، وقد سقط عليها المطر، فتغيرت صورهم، وأنتنت من جيفهم البلد، وتغير الهواء، فحصل بسببه الوباء الشديد، حتى تعدى وسرى في الهواء إلى بلاد الشام، فمات خلق كثير من تغير الجو وفساد الريح، فاجتمع من بقي على الوباء والغلاء والفناء، والطعن والطاعون، فإنا لله وإنا إليه راجعون
مصطفى سنجر
Thursday, April 30, 2009
Thursday, April 23, 2009
How We Can Started Database in EXCLUSIVE mode
A database is started in EXCLUSIVE mode by default. Therefore, the
ORA-01102 error is misleading and may have occurred due to one of the
following reasons:
- there is still an "sgadef.dbf" file in the "ORACLE_HOME/dbs"
directory
- the processes for Oracle (pmon, smon, lgwr and dbwr) still exist
- shared memory segments and semaphores still exist even though the
database has been shutdown
- there is a "ORACLE_HOME/dbs/lk" file
The "lk" and "sgadef.dbf" files are used for locking shared memory.
It seems that even though no memory is allocated, Oracle thinks memory is
still locked. By removing the "sgadef" and "lk" files you remove any knowledge
oracle has of shared memory that is in use. Now the database can start.
POSSIBLE SOLUTION:
Verify that the database was shutdown cleanly by doing the following:
1. Verify that there is not a "sgadef.dbf" file in the directory
"ORACLE_HOME/dbs".
% ls $ORACLE_HOME/dbs/sgadef.dbf
If this file does exist, remove it.
% rm $ORACLE_HOME/dbs/sgadef.dbf
2. Verify that there are no background processes owned by "oracle"
% ps -ef | grep ora_ | grep $ORACLE_SID
If background processes exist, remove them by using the Unix
command "kill". For example:
% kill -9
3. Verify that no shared memory segments and semaphores that are owned
by "oracle" still exist
% ipcs -b
If there are shared memory segments and semaphores owned by "oracle",
remove the shared memory segments
% ipcrm -m
and remove the semaphores
% ipcrm -s
NOTE: The example shown above assumes that you only have one
database on this machine. If you have more than one
database, you will need to shutdown all other databases
before proceeding with Step 4.
4. Verify that the "$ORACLE_HOME/dbs/lk" file does not exist
5. Startup the instance
ORA-01102 error is misleading and may have occurred due to one of the
following reasons:
- there is still an "sgadef
directory
- the processes for Oracle (pmon, smon, lgwr and dbwr) still exist
- shared memory segments and semaphores still exist even though the
database has been shutdown
- there is a "ORACLE_HOME/dbs/lk
The "lk
It seems that even though no memory is allocated, Oracle thinks memory is
still locked. By removing the "sgadef" and "lk" files you remove any knowledge
oracle has of shared memory that is in use. Now the database can start.
POSSIBLE SOLUTION:
Verify that the database was shutdown cleanly by doing the following:
1. Verify that there is not a "sgadef
"ORACLE_HOME/dbs".
% ls $ORACLE_HOME/dbs/sgadef
If this file does exist, remove it.
% rm $ORACLE_HOME/dbs/sgadef
2. Verify that there are no background processes owned by "oracle"
% ps -ef | grep ora_ | grep $ORACLE_SID
If background processes exist, remove them by using the Unix
command "kill". For example:
% kill -9
3. Verify that no shared memory segments and semaphores that are owned
by "oracle" still exist
% ipcs -b
If there are shared memory segments and semaphores owned by "oracle",
remove the shared memory segments
% ipcrm -m
and remove the semaphores
% ipcrm -s
NOTE: The example shown above assumes that you only have one
database on this machine. If you have more than one
database, you will need to shutdown all other databases
before proceeding with Step 4.
4. Verify that the "$ORACLE_HOME/dbs/lk
5. Startup the instance
Tuesday, April 21, 2009
My Skills
1-Good understanding of the Oracle database, related utilities and tools
2-Good understanding of the underlying operating system
3-Good knowledge of the physical database design
4-Ability to perform both Oracle and operating system performance tuning and monitoring
5-Knowledge of ALL Oracle backup and recovery scenarios
6-Good knowledge of Oracle security management
7-Good knowledge of how Oracle acquires and manages resources
8-Good knowledge Oracle data integrity
9-Sound knowledge of the implemented application systems
10-Experience in code migration, database change management and data management through the various stages of the development life cycle
11-Sound knowledge of both database and system performance tuning
12-Sound communication skills with management, development teams, vendors and systems administrators
13-Provide a strategic database direction for the organisation
14-Ability to handle multiple projects and deadlines
15-Sound understanding of the business
2-Good understanding of the underlying operating system
3-Good knowledge of the physical database design
4-Ability to perform both Oracle and operating system performance tuning and monitoring
5-Knowledge of ALL Oracle backup and recovery scenarios
6-Good knowledge of Oracle security management
7-Good knowledge of how Oracle acquires and manages resources
8-Good knowledge Oracle data integrity
9-Sound knowledge of the implemented application systems
10-Experience in code migration, database change management and data management through the various stages of the development life cycle
11-Sound knowledge of both database and system performance tuning
12-Sound communication skills with management, development teams, vendors and systems administrators
13-Provide a strategic database direction for the organisation
14-Ability to handle multiple projects and deadlines
15-Sound understanding of the business
My Roles and Responsibilities
The job of the DBA seems to be everything that everyone else either doesn't want to do, or doesn't have the ability to do. DBAs get the enviable task of figuring out all of the things no one else can figure out. More seriously though, here is a list of typical DBA responsibilities:
1-Installation, configuration and upgrading of Oracle server software and related products
2-Evaluate Oracle features and Oracle related products
3-Establish and maintain sound backup and recovery policies and procedures
4-Take care of the Database design and implementation
5-Implement and maintain database security (create and maintain users and roles, assign privileges)
6-Perform database tuning and performance monitoring
7-Perform application tuning and performance monitoring
8-Setup and maintain documentation and standards
9-Plan growth and changes (capacity planning)
10-Work as part of a team and provide 7x24 support when required
11-Perform general technical trouble shooting and give consultation to development teams
12-Interface with Oracle Corporation for technical support.
13-Patch Management and Version Control
1-Installation, configuration and upgrading of Oracle server software and related products
2-Evaluate Oracle features and Oracle related products
3-Establish and maintain sound backup and recovery policies and procedures
4-Take care of the Database design and implementation
5-Implement and maintain database security (create and maintain users and roles, assign privileges)
6-Perform database tuning and performance monitoring
7-Perform application tuning and performance monitoring
8-Setup and maintain documentation and standards
9-Plan growth and changes (capacity planning)
10-Work as part of a team and provide 7x24 support when required
11-Perform general technical trouble shooting and give consultation to development teams
12-Interface with Oracle Corporation for technical support.
13-Patch Management and Version Control
Wednesday, April 8, 2009
Add New File System In Linux
1-Identify device
fdisk -l
2-Partition device
fdisk /dev/sdb
mkdir /u02
3-Make filesystem
mkfs.ext3 -c /dev/sdb
4-Mount new filesystem
mount -t ext2 /dev/sdb /u02
5-Create entry in /etc/fstab
vi /etc/fstab
add
/dev/sdb /u02 ext3 defaults 0 0
fdisk -l
2-Partition device
fdisk /dev/sdb
mkdir /u02
3-Make filesystem
mkfs.ext3 -c /dev/sdb
4-Mount new filesystem
mount -t ext2 /dev/sdb /u02
5-Create entry in /etc/fstab
vi /etc/fstab
add
/dev/sdb /u02 ext3 defaults 0 0
Add Swap Space In Linux
1-Determine the size of the new swap file and multiple by 1024 to determine the block size. For example, the block size of a 1024 MB (1G) swap file is 1048576.
At a shell prompt as root, type the following command with count being equal to the desired block size:
dd if=/dev/zero of=/swapfile bs=1024 count=1048576
2-Setup the swap file with the command:
mkswap /swapfile
3-To enable the swap file immediately but not automatically at boot time:
swapon /swapfile
4-To enable it at boot time, edit /etc/fstab to include:
/swapfile swap swap defaults 0 0
The next time the system boots(or type mount -a ), it will enable the new swap file.
5-After adding the new swap file and enabling it, make sure it is enabled by viewing the output of the command cat /proc/swaps or free.
At a shell prompt as root, type the following command with count being equal to the desired block size:
dd if=/dev/zero of=/swapfile bs=1024 count=1048576
2-Setup the swap file with the command:
mkswap /swapfile
3-To enable the swap file immediately but not automatically at boot time:
swapon /swapfile
4-To enable it at boot time, edit /etc/fstab to include:
/swapfile swap swap defaults 0 0
The next time the system boots(or type mount -a ), it will enable the new swap file.
5-After adding the new swap file and enabling it, make sure it is enabled by viewing the output of the command cat /proc/swaps or free.
check log files
To chack many log files for backup in linux use this command:
grep -il unsuccessfully *.LOG > unsuccessfull_backups.txt
grep -il unsuccessfully *.LOG > unsuccessfull_backups.txt
Tuesday, April 7, 2009
RMAN backup
1:create user
SQL> create user rman identified by rman ;
SQL> alter user rman default tablespace tools temporary tablespace temp ;
SQL> alter user rman quota unlimited on tools ;
SQL> grant connect,resource,recovery_catalog_owner to rman ;
SQL> exit ;
===========================================================================
2:login to rman and create cataloge schema
$ rman catalog rman/rman
RMAN> create catalog tablespace tools ;
RMAN> exit ;
===========================================================================
3:put database in archive log
sqlplus login as sysdba
SQL>select log_mode from v$database ;
select name from v$database;
SQL> shutdown immediate ;
SQL> startup mount ;
SQL>alter database archivelog ;
SQL>alter database open ;
SQL>select log_mode from v$database ;
============================================================================
4:register the database in the catalog
$ rman catalog rman/rman target sys/sys
RMAN>register database;
============================================================================
5:create script to make backup
RMAN> create script daily_backup{
backup database plus archivelog;
delete noprompt obsolete;
}
============================================================================
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/backup/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/backup/RMAN_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 2 G;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'F:\RMANBACKUP\SNCFSULAPP1.ORA';
RMAN>
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT 'f:\RmanBackup\backup_db_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 2 G;
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT 'f:\RmanBackup\backup_db_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 2 G;
================================================================================
7: execute script to backup
RMAN> run {execute script daily_backup;}
=================================================================================
Complete Restore / Recover Example
1/create folder in any directory for exampel d:\backupdatabase
change the configration of rman
rman catalog rman/rman target sys/sys
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'd:\backupdatabase\%F';
RMAN>CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'd:\backupdatabase\backup_db_%d_S_%s_P_%p_T_%t';
RMAN>CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'd:\backupdatabase\SNCFORCL.ORA';
exit;
2/connect to sql
sql> sys/sys as sysdba
shutdown immediate
startup nomount
exit;
3/connect to rman
rman target sys/sys
RMAN>set dbid=1134214906 {this is the id of your database}
RMAN>set controlfile autobackup format for device type disk to 'D:\backup\%F'
RMAN>restore controlfile from autobackup;
RMAN>mount database;
RMAN>restore database;
RMAN>recover database;
RMAN>alter database open resetlogs;
RMAN>exit;
rman catalog rman/rman target sys/sys
RMAN>reset database;
exit;
create batch file
rman catalog rman/rman target sys/sys @d:\cmdfile.txt
create text file in d:\ name cmdfile and write in it rman command
set controlfile autobackup format for device type disk to 'D:\backup\%F'
RMAN>Friday backup
run {
backup incremental level 0 database ;
sql 'alter system archive log current';
backup archivelog all delete input;
delete noprompt obsolete;
}
RMAN>Daily backup
run {
backup incremental level 1 database ;
sql 'alter system archive log current';
backup archivelog all delete input ;
delete noprompt obsolete;
}
RMAN>MONDAY
run {
backup incremental level 2 database ;
sql 'alter system archive log current';
backup archivelog all delete input;
delete noprompt obsolete;
}
RMAN>Wed backup
run {
backup incremental level 2cumulative database ;
sql 'alter system archive log current';
backup archivelog all delete input;
delete noprompt obsolete;
}
ppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp
Check whether backup pieces, proxy copies, or disk copies still exist.
CROSSCHECK BACKUP;
CROSSCHECK COPY;
CROSSCHECK backup of database;
CROSSCHECK backup of controlfile;
CROSSCHECK archivelog all;
uuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuu
List the backups and copies recorded in the repository.
LIST BACKUP;
RMAN> LIST BACKUP OF DATABASE;
RMAN> LIST BACKUP SUMMARY;
RMAN> LIST INCARNATION;
RMAN> LIST BACKUP BY FILE;
RMAN> LIST COPY OF DATABASE ARCHIVELOG ALL;
RMAN> LIST COPY OF DATAFILE 1, 2, 3;
RMAN> LIST BACKUP OF DATAFILE 11 SUMMARY;
RMAN> LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 1437;
RMAN> LIST CONTROLFILECOPY "/tmp/cntrlfile.copy";
RMAN> LIST BACKUPSET OF DATAFILE 1;
[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[
list incarnation of database;
reset database to incarnation Your_KEY;
reset database;
////////////////////////////////////////////////////////
ALTER SYSTEM ARCHIVE LOG START;
In AIX and Linux
rman target rman/*****@SID rcvcat rman/****@CAT_SID
run
{
allocate channel t1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)' CONNECT 'rman/*****@SID1';
allocate channel t2 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)' CONNECT 'rman/*****@SID2';
backup format "%d_full_%s_%p" filesperset 5 database;
sql "alter system archive log current";
backup format "%d_arch_%s_%p" filesperset 20 (archivelog all delete input);
release channel t1;
release channel t2;
}
delete noprompt obsolete;
SQL> create user rman identified by rman ;
SQL> alter user rman default tablespace tools temporary tablespace temp ;
SQL> alter user rman quota unlimited on tools ;
SQL> grant connect,resource,recovery_catalog_owner to rman ;
SQL> exit ;
===========================================================================
2:login to rman and create cataloge schema
$ rman catalog rman/rman
RMAN> create catalog tablespace tools ;
RMAN> exit ;
===========================================================================
3:put database in archive log
sqlplus login as sysdba
SQL>select log_mode from v$database ;
select name from v$database;
SQL> shutdown immediate ;
SQL> startup mount ;
SQL>alter database archivelog ;
SQL>alter database open ;
SQL>select log_mode from v$database ;
============================================================================
4:register the database in the catalog
$ rman catalog rman/rman target sys/sys
RMAN>register database;
============================================================================
5:create script to make backup
RMAN> create script daily_backup{
backup database plus archivelog;
delete noprompt obsolete;
}
============================================================================
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/backup/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/backup/RMAN_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 2 G;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'F:\RMANBACKUP\SNCFSULAPP1.ORA';
RMAN>
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT 'f:\RmanBackup\backup_db_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 2 G;
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT 'f:\RmanBackup\backup_db_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 2 G;
================================================================================
7: execute script to backup
RMAN> run {execute script daily_backup;}
=================================================================================
Complete Restore / Recover Example
1/create folder in any directory for exampel d:\backupdatabase
change the configration of rman
rman catalog rman/rman target sys/sys
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'd:\backupdatabase\%F';
RMAN>CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'd:\backupdatabase\backup_db_%d_S_%s_P_%p_T_%t';
RMAN>CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'd:\backupdatabase\SNCFORCL.ORA';
exit;
2/connect to sql
sql> sys/sys as sysdba
shutdown immediate
startup nomount
exit;
3/connect to rman
rman target sys/sys
RMAN>set dbid=1134214906 {this is the id of your database}
RMAN>set controlfile autobackup format for device type disk to 'D:\backup\%F'
RMAN>restore controlfile from autobackup;
RMAN>mount database;
RMAN>restore database;
RMAN>recover database;
RMAN>alter database open resetlogs;
RMAN>exit;
rman catalog rman/rman target sys/sys
RMAN>reset database;
exit;
create batch file
rman catalog rman/rman target sys/sys @d:\cmdfile.txt
create text file in d:\ name cmdfile and write in it rman command
set controlfile autobackup format for device type disk to 'D:\backup\%F'
RMAN>Friday backup
run {
backup incremental level 0 database ;
sql 'alter system archive log current';
backup archivelog all delete input;
delete noprompt obsolete;
}
RMAN>Daily backup
run {
backup incremental level 1 database ;
sql 'alter system archive log current';
backup archivelog all delete input ;
delete noprompt obsolete;
}
RMAN>MONDAY
run {
backup incremental level 2 database ;
sql 'alter system archive log current';
backup archivelog all delete input;
delete noprompt obsolete;
}
RMAN>Wed backup
run {
backup incremental level 2cumulative database ;
sql 'alter system archive log current';
backup archivelog all delete input;
delete noprompt obsolete;
}
ppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp
Check whether backup pieces, proxy copies, or disk copies still exist.
CROSSCHECK BACKUP;
CROSSCHECK COPY;
CROSSCHECK backup of database;
CROSSCHECK backup of controlfile;
CROSSCHECK archivelog all;
uuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuu
List the backups and copies recorded in the repository.
LIST BACKUP;
RMAN> LIST BACKUP OF DATABASE;
RMAN> LIST BACKUP SUMMARY;
RMAN> LIST INCARNATION;
RMAN> LIST BACKUP BY FILE;
RMAN> LIST COPY OF DATABASE ARCHIVELOG ALL;
RMAN> LIST COPY OF DATAFILE 1, 2, 3;
RMAN> LIST BACKUP OF DATAFILE 11 SUMMARY;
RMAN> LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 1437;
RMAN> LIST CONTROLFILECOPY "/tmp/cntrlfile.copy";
RMAN> LIST BACKUPSET OF DATAFILE 1;
[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[
list incarnation of database;
reset database to incarnation Your_KEY;
reset database;
////////////////////////////////////////////////////////
ALTER SYSTEM ARCHIVE LOG START;
In AIX and Linux
rman target rman/*****@SID rcvcat rman/****@CAT_SID
run
{
allocate channel t1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)' CONNECT 'rman/*****@SID1';
allocate channel t2 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)' CONNECT 'rman/*****@SID2';
backup format "%d_full_%s_%p" filesperset 5 database;
sql "alter system archive log current";
backup format "%d_arch_%s_%p" filesperset 20 (archivelog all delete input);
release channel t1;
release channel t2;
}
delete noprompt obsolete;
Audit In Oracle
Introduction
This article will introduce the reader to the basics of auditing an Oracle database. Oracle's RDBMS is a functionally rich product and there are a number of auditing alternatives available to the reader. Because auditing Oracle is such a huge subject, doing all of it justice would take an entire book, so this paper will cover the basics of why, when and how to conduct an audit. It will also use a couple of good example cases to illustrate how useful Oracle audit can be to an organization.
The Issues
There are a number of basic issues that should be considered when contemplating using Oracle's auditing features. These are as follows:
• Why is audit needed in Oracle?
Is this a strange question? Well, lots of companies don't actually use the internal audit features of Oracle. Or, when they do use them, they are so overwhelmed with choice, they turn on everything for good measure, then realise there is far too much output to read and digest so they quickly turn it all off again. It is quite common to use firewalls, intrusion detection systems (IDS) and other security tools to determine if the network or operating system is being misused or abused. So why not audit what users are doing to the "crown jewels" of an organization, the data. Oracle audit can help detect unauthorized access and internal abuse of the data held in the database.
• When should Oracle users be audited?
A simple basic set of audit actions should be active all the time. The ideal minimum is to capture user access, use of system privileges and changes to the database schema structure. This basic set will not show attempted access to specific data that shouldn't be accessed; however, it will give a reasonably simple overview of "incorrect" access and use of privileges. If an employee is suspected of inappropriate actions or if an attack has been suspected then more detailed audit can be turned on for specific tables. From a data management point of view, auditing data changes for all tables in the database is not really practical and could also affect performance. Monitoring data change access on critical tables (such as salaries in a HR database) should be considered.
• How can Oracle users be audited?
The standard audit commands allow all system privileges to be audited along with access at the object level to any table or view on the database for select, delete, insert or update. Audit can be run for either successful or unsuccessful attempts or both. It can be for each individual user or for all users and it can also be done at the session level or access level. At action level a single record is created per action and at session level one record is created for all audit actions per session.
• What are the performance and complexity issues?
Audit is generally perceived to be complex and slow. The reason for this is usually ignorance. If many or all options are turned on, then the resultant audit trail produced can be large and difficult to interpret and manage. Furthermore, if audit is used on all tables and views in the database, then this can have an effect on performance. Every time an action performed is auditable a record is written to the database; clearly the more audit is used, the more records will be written to the system tablespace purely for audit. In some cases double the amount of access to the database can be performed: the original write and the audit record being written.
The watchword here is simplicity and caution. Use only the audit that is needed to give an overall view of what is happening and for detailed monitoring of critical data and objects. The simpler the audit trail set-up, the more likely it is that the data will be analyzed and be of some use. It is important to define what actions or abuses are being checked for so that simple reports can be written to filter the audit trail for these actions. A default installation of Oracle has audit turned off by default and Oracle does not come with any standard default audit settings or reports to analyse any audit trail produced. These reasons, and the fact that there are many options available are, in my opinion, why audit is perceived to be complex.
The standard audit commands do not allow audit to be performed at row level. It is also not possible to audit the actions of privileged users such as SYS and "as sysdba" until Oracle 9iR2.
Oracles Audit Facilities
The task of auditing an Oracle database does not have to be limited only to the audit commands; other techniques can be employed as well. Here are some of the main methods that can be used to audit an Oracle database:
• Oracle audit
This is really the subject of this paper. All privileges that can be granted to a user or role within the database can be audited. This includes read, write and delete access on objects at the table level. For more detailed audit, the database triggers need to be employed.
• System triggers
These were introduced with Oracle and allow the writing of database triggers that fire when system events take place. These include start- up and shutdown of the database, log-on and log-off attempts, and creation, altering and dropping of schema objects. With the aid of autonomous transactions, these allow a log to be written for the above system events.
• Update, delete, and insert triggers
This is the second line of defence in trying to understand users' actions at a more detailed row level. Database triggers need to be written to capture changes at the column and row level. It is possible to write complete rows of data before and after the change being made to a log table in the database. The use of this type of logging is very resource intensive, as many extra records are written and stored. The one failing with this method is that read access cannot be captured with normal database triggers.
• Fine-grained audit
Fine-grained audit solves the problem of capturing read access. This feature is also based on internal triggers that fire when any piece of SQL is parsed. This is very efficient, as the SQL is parsed once for audit and execution. The feature uses predicates that are defined and tested each time the relevant object is accessed. Fine-grained audit is managed by a PL/SQL package called DBMS_FGA. A PL/SQL procedure is executed every time a "match" is made with the predicate. This method allows the audit to be performed down to the row and column level and to also for read statements. Readers should be forewarned that use of this feature requires programming skills.
• System logs
Oracle generates many log files and many of them can provide useful information to assist in auditing the database. One good example is the alert log used by the database to record start-up and shutdown as well as any structural changes such as adding a datafile to the database.
This paper is going to explore only the standard built-in audit commands. The other options will be left for future articles.
Some Examples
Because of the myriad of possibilities, auditing an Oracle database can be a daunting task. In order to try and simplify the discussion of what can be done, we will discuss a couple of simple examples that we will explore and work through.
• Auditing database access
This is a fundamental check to find out who accesses the database, from where and when. Log-on failures can be captured as well as log- ons at strange (anomolous) times of the day.
• Auditing changes to the database structure
In a production database, no user should ever change the schema structure. DBAs should make changes for upgrades at specific times; any other changes should be regarded as suspicious. Watching for structural changes can turn up indicators of incorrect use of the database.
A third simple example that could have been employed here is to audit any use of system privileges. However, this example is left to the reader to explore.
The final group of audit commands that can be employed is to audit any data changes to objects themselves. Unfortunately, as the requirements are very application and installation specific, this is beyond the scope of this paper.
Audit within Oracle is broken into three areas: statement auditing such as CREATE TABLE or CREATE SESSION, privilege auditing such as ALTER USER, and object level auditing such as SELECT TABLE.
Basic Configuration
The audit trail can be either written to the database or to the operating system. Writing the audit trail to the operating system is, in some senses, more secure but the implementation is not available on all platforms and is platform specific. In this article we will concentrate on using the database to store the audit trail.
Audit is turned on for writing to the database by adding the following line to the init.ora file. A symbolic link to it can usually be found in $ORACLE_HOME/dbs
audit_trail = db
The database now needs to be restarted. A simple check will show that audit is indeed now turned on.
SQL> select name,value from v$parameter
where name like 'audit%';
NAME VALUE
------------------------------ ------------------------------
audit_trail DB
audit_file_dest ?/rdbms/audit
No audit actions are captured yet until audit actions are defined; that is, except for privileged access to the database, starting and stopping of the database, and structural changes such as adding a datafile. These are logged to operating system files in $ORACLE_HOME/rdbms/audit unless audit_file_dest is redefined in the init.ora file. On Windows these events appear in the Event Viewer. To check if any privilege or statement audit actions are enabled, do the following:
SQL> select * from dba_stmt_audit_opts
union
select * from dba_priv_audit_opts;
no rows selected
To find out what objects are being audited, query the view dba_obj_audit_opts.
The Worked Examples
Let us now work through our two example cases and see what can be learned. First, turn on audit for the access attempts to the database:
SQL> audit create session;
Audit succeeded.
The above command will capture access by all users by access and whether successful or unsuccessful. The default for this command is by access.
Note: The format of all audit commands from the Oracle documentation is as follows:
audit {statement_option|privilege_option} [by user] [by
{session|access}] [ whenever {successful|unsuccessful}]
Only the statement_option or privilege_option part is mandatory. The other clauses are optional and enabling them allows audit be more specific.
For a user to define audit statements, the privilege "AUDIT SYSTEM" needs to have been granted first. The users that have this privilege can be checked as follows:
SQL> select *
from dba_sys_privs
where privilege like '%AUDIT%';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CTXSYS AUDIT ANY NO
CTXSYS AUDIT SYSTEM NO
DBA AUDIT ANY YES
DBA AUDIT SYSTEM YES
IMP_FULL_DATABASE AUDIT ANY NO
MDSYS AUDIT ANY YES
MDSYS AUDIT SYSTEM YES
WKSYS AUDIT ANY NO
WKSYS AUDIT SYSTEM NO
9 rows selected.
The above results are for a 9i database, the default users MDSYS, CTXSYS and WKSYS would likely be good targets for attackers, as any audit actions could be turned off as one of these users to hide any actions undertaken.
Now that audit will capture all access attempts, we need to wait for some users to log in to do work. So while they do that, let's set up the audit to capture alterations to the schema. For the sake of brevity, in this example, not all schema object changes will be captured. Changes to tables, indexes, clusters, views, sequences, procedures, triggers, libraries and many more can be captured. In this example, audit will be enabled on an example set. Turning on the audit can be performed as a two-stage process, generate the audit commands and then run them as follows:
set head off
set feed off
set pages 0
spool aud.lis
select 'audit '||name||';'
from system_privilege_map
where (name like 'CREATE%TABLE%'
or name like 'CREATE%INDEX%'
or name like 'CREATE%CLUSTER%'
or name like 'CREATE%SEQUENCE%'
or name like 'CREATE%PROCEDURE%'
or name like 'CREATE%TRIGGER%'
or name like 'CREATE%LIBRARY%')
union
select 'audit '||name||';'
from system_privilege_map
where (name like 'ALTER%TABLE%'
or name like 'ALTER%INDEX%'
or name like 'ALTER%CLUSTER%'
or name like 'ALTER%SEQUENCE%'
or name like 'ALTER%PROCEDURE%'
or name like 'ALTER%TRIGGER%'
or name like 'ALTER%LIBRARY%')
union
select 'audit '||name||';'
from system_privilege_map
where (name like 'DROP%TABLE%'
or name like 'DROP%INDEX%'
or name like 'DROP%CLUSTER%'
or name like 'DROP%SEQUENCE%'
or name like 'DROP%PROCEDURE%'
or name like 'DROP%TRIGGER%'
or name like 'DROP%LIBRARY%')
union
select 'audit '||name||';'
from system_privilege_map
where (name like 'EXECUTE%INDEX%'
or name like 'EXECUTE%PROCEDURE%'
or name like 'EXECUTE%LIBRARY%')
/
spool off
@@aud.lis
This will generate a set of audit commands that can be captured to a spool file, which is then run to enable the audit commands.
Another solution would be to audit the actual permissions granted to users by generating the audit commands from the database view dba_sys_privs. While this may seem to be a better solution and potentially involve less audit commands, it would not allow for the case when new permissions are granted to users. In this case, audit would also need to be enabled at the time the privileges are granted.
Now that all of the sample audit is now enabled, the settings can be viewed with this SQL:
select audit_option,success,failure
from dba_stmt_audit_opts
union
select privilege,success,failure
from dba_priv_audit_opts
SQL> /
AUDIT_OPTION SUCCESS FAILURE
---------------------------------------- ---------- ----------
ALTER ANY CLUSTER BY ACCESS BY ACCESS
ALTER ANY INDEX BY ACCESS BY ACCESS
ALTER ANY INDEXTYPE BY ACCESS BY ACCESS
ALTER ANY LIBRARY BY ACCESS BY ACCESS
EXECUTE ANY LIBRARY BY SESSION BY SESSION
EXECUTE ANY PROCEDURE BY SESSION BY SESSION
38 rows selected.
Every time a user attempts anything in the database where audit is enabled the Oracle kernel checks to see if an audit record should be created or updated (in the case or a session record) and generates the record in a table owned by the SYS user called AUD$. This table is, by default, located in the SYSTEM tablespace. This in itself can cause problems with potential denial of service attacks. If the SYSTEM tablespace fills up, the database will hang.
The AUD$ table is rare, as it is the only SYS owned table from which Oracle allows records to be deleted. If the audit trail is turned on and written to the database, then the numbers of records in this table need to be monitored carefully to ensure it doesn't grow too fast and fill the system tablespace. A purging strategy needs to be adopted to keep the size of the table in check and, if needed, to archive off audit trail records for future reference. One tactic could be to copy the records to summary tables that allow specific checks for abuse to be performed offline. These summary tables can be in a separate database for added security. Once copied, sys.aud$ can be truncated.
SYS.AUD$ can be moved to a different tablespace other than SYSTEM but check with Oracle support first, as this action is no longer supported.
Only users who have been granted specific access to SYS.AUD$ can access the table to read, alter or delete from it. This is usually just the user SYS or any user who has had permissions. There are two specific roles that allow access to SYS.AUD$ for select and delete, these are DELETE_CATALOG_ROLE and SELECT_CATALOG_ROLE. These roles should not be granted to general users.
Back to the examples, our users have been logging on and working throughout the day and created some audit records. These audit records can be viewed in a number of ways:
• By selecting from SYS.AUD$ - This is the raw audit trail
• By selecting from dba_audit_trail - This is a DBA view showing the raw audit trail.
• By selecting from dba_audit_session - This view shows just log-on and log-off actions.
A simple piece of SQL can show details of the connection attempts:
SQL> get check_create_session
--
-- check_create_session.sql
--
col username for a15
col terminal for a6
col timestamp for a15
col logoff_time for a15
col action_name for a8
col returncode for 9999
select username,
terminal,
action_name,
to_char(timestamp,'DDMMYYYY:HHMISS') timestamp,
to_char(logoff_time,'DDMMYYYY:HHMISS') logoff_time,
returncode
from dba_audit_session
USERNAME TERMIN ACTION_N TIMESTAMP LOGOFF_TIME RETURNCODE
--------------- ------ -------- --------------- --------------- ----------
SYS pts/1 LOGOFF 09042003:051046 09042003:051641 0
ZULIA pts/1 LOGON 09042003:051641 1017
SYS pts/1 LOGOFF 09042003:051649 09042003:053032 0
SYS pts/2 LOGOFF 09042003:052622 09042003:053408 0
ZULIA pts/1 LOGON 09042003:053032 1017
There are a number of simple abuses that can be checked for in the area of user access to the database. As examples for this paper we will look at the following:
• Failed log-on attempts
This can indicate fat fingers or attackers' attempts to gain unauthorized access the database. The following SQL highlights this:
SQL> select count(*),username,terminal,to_char(timestamp,'DD-MON-YYYY')
from dba_audit_session
where returncode<>0
group by username,terminal,to_char(timestamp,'DD-MON-YYYY');
COUNT(*) USERNAME TERMIN TO_CHAR(TIM
---------- --------------- ------ -----------
1 BILL pts/3 09-APR-2003
3 FRED pts/3 09-APR-2003
4 ZULIA pts/1 09-APR-2003
This shows two possible abuses, the first is the user Zulia attempting to log on and failing four times on the same day. This could be a forgotten password or it could be someone trying to guess his or her password. A change to the SQL as follows gives a bit more detail:
SQL> select count(*),username,terminal,to_char(timestamp,'DD-MON-YYYY'),returncode
from dba_audit_session
group by username,terminal,to_char(timestamp,'DD-MON-YYYY'),returncode;
COUNT(*) USERNAME TERMIN TO_CHAR(TIM RETURNCODE
---------- --------------- ------ ----------- ----------
1 BILL pts/3 09-APR-2003 1017
1 EMIL pts/1 09-APR-2003 0
1 EMIL pts/2 09-APR-2003 0
1 EMIL pts/3 09-APR-2003 0
1 EMIL pts/4 09-APR-2003 0
3 FRED pts/3 09-APR-2003 1017
3 SYS pts/1 09-APR-2003 0
1 SYS pts/2 09-APR-2003 0
1 SYSTEM pts/5 09-APR-2003 0
4 ZULIA pts/1 09-APR-2003 1017
1 ZULIA pts/1 09-APR-2003 0
11 rows selected.
This reveals that the user successfully logged on on the same terminal on the same day. A number of failed log-ons should be agreed as part of these checks and the above SQL run every day. Those users with failure numbers above the threshold should be investigated.
• Attempts to access the database with non-existent users
One interesting extension to the above SQL is to find attempts to log in where the user doesn't exist. An audit record is still created in this case. The following SQL illustrates:
SQL>select username,terminal,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS')
from dba_audit_session
where returncode<>0
and not exists (select 'x'
from dba_users
where dba_users.username=dba_audit_session.username)
USERNAME TERMIN TO_CHAR(TIMESTAMP,'D
--------------- ------ --------------------
FRED pts/3 09-APR-2003 17:31:47
FRED pts/3 09-APR-2003 17:32:02
FRED pts/3 09-APR-2003 17:32:15
BILL pts/3 09-APR-2003 17:33:01
This is probably abuse. All attempts to log on with a user that doesn't exist should be checked each day and investigated.
• Attempts to access the database at unusual hours
Checks should be made for any attempts to access the database outside of working hours. These accesses could be genuine overtime work or maintenance but they could just as easily be unauthorized access attempts and should be checked as follows:
SQL> select username,
terminal,
action_name,
returncode,
to_char(timestamp,'DD-MON-YYYY HH24:MI:SS'),
to_char(logoff_time,'DD-MON-YYYY HH24:MI:SS')
from dba_audit_session
where to_date(to_char(timestamp,'HH24:MI:SS'),'HH24:MI:SS') <
to_date('08:00:00','HH24:MI:SS')
or to_date(to_char(timestamp,'HH24:MI:SS'),'HH24:MI:SS') >
to_date('19:30:00','HH24:MI:SS')
USERNAME TERMIN ACTION_N RETURNCODE TO_CHAR(TIMESTAMP,'D TO_CHAR(LOGOFF_TIME,
---------- ------ -------- ---------- -------------------- --------------------
SYS pts/1 LOGOFF 0 09-APR-2003 20:10:46 09-APR-2003 20:16:41
SYSTEM pts/5 LOGOFF 0 09-APR-2003 21:49:20 09-APR-2003 21:49:50
ZULIA pts/5 LOGON 0 09-APR-2003 21:49:50
EMIL APOLLO LOGON 0 09-APR-2003 22:49:12
The above SQL shows any connections before 8:00 AM and after 7:30 PM. Any connections, particularly those made by privileged users such as SYS and SYSTEM, should be investigated. Particular attention can be made to the location from which the access was made. For instance, if privileged access is made from machines that are not in the administrator department, the administrator needs to find out why.
• Check for users sharing database accounts
The following SQL looks for users who are potentially sharing database accounts:
SQL> select count(distinct(terminal)),username
from dba_audit_session
having count(distinct(terminal))>1
group by username
COUNT(DISTINCT(TERMINAL)) USERNAME
------------------------- ----------
4 EMIL
3 SYS
3 ZULIA
This shows that three users have accessed their accounts from more than one location. A further check could be to add a time component to see if they are accessed simultaneously and also to restrict the check per day. The above SQL gives some idea of the potential without complicating it too much. Again, these accounts and users should be investigated.
• Multiple access attempts for different users from the same terminal
The final example checks to find where multiple database accounts have been used from the same terminal. The SQL is again simple and could be extended to group by day and also to print out the users per terminal. This is a simple test to illustrate the abuse idea:
SQL> select count(distinct(username)),terminal
from dba_audit_session
having count(distinct(username))>1
group by terminal
COUNT(DISTINCT(USERNAME)) TERMIN
------------------------- ------
3 pts/1
2 pts/2
3 pts/3
3 pts/5
This could indicate someone trying to gain access by trying many accounts and passwords, or it could indicate legitimate users sharing accounts for certain aspects of their work. In either case, the admin should investigate further.
There are, of course, many other scenarios that could indicate possible abuses. Checking for those is as simple as the cases depicted above. It will be left to the reader to experiment. Let me know what you find useful.
The second example case that audit actions were set for is to detect changes made to the database schema. This could include new objects being added or attempts to change existing objects within the database.
A simple piece of SQL will show any audit trail items that relate to objects being created or changed as follows:
col username for a8
col priv_used for a16
col obj_name for a22
col timestamp for a17
col returncode for 9999
select username,
priv_used,
obj_name,
to_char(timestamp,'DD-MON-YYYY HH24:MI') timestamp,
returncode
from dba_audit_trail
where priv_used is not null
and priv_used<>'CREATE SESSION'
SQL> @check_obj.sql
ZULIA CREATE TABLE STEAL_SALARY 09-APR-2003 20:07 0
PETE CREATE PROCEDURE HACK 09-APR-2003 20:42 0
This simple example shows that the user ZULIA has created a table and the user PETE has been writing PL/SQL procedures. Any changes such as this that are found should be investigated in a production database. Many more specific abuses can be checked for in relation to object and schema changes but, in general, no user should be able to alter the database schema in a production database. As a result, the check can remain pretty simple.
Protecting the Database Against These Abuses
The two examples given are just two of many possible scenarios that could be detected using Oracle's auditing facilities. Turning on and managing audit is one of the first steps to securing the database. Using audit should be part of an overall organization security plan and policy that includes Oracle. The database should be audited regularly for misconfiguration or known vulnerabilities that could allow security breaches to take place.
Because of its complex nature and vast number of different ways it can be used and configured, the best approach to securing Oracle will always be to follow the principle of least privilege. Once the database is part of the overall security plan and is configured correctly and checked regularly, then auditing it should be considered an important part of the strategy.
In general, do not grant any privileges to general users in a production database, remove most of the PUBLIC privileges and delete or lock and change the passwords of any default accounts. Ensure that users obey password policies and that the password management features of Oracle are employed.
It is important that the audit actions are planned from a performance and usability point of view and that the audit trail is managed. It is also important that the audit trail data is understood in terms of detecting abuse.
The author's recent book by the SANS Institute "Oracle security step- by-step - A survival guide for Oracle security" gives excellent guidelines on how to configure Oracle securely.
example:::::\
SQL> SHOW PARAMETER AUDIT
SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
SQL> SHUTDOWN immediate;
SQL> startup;
First we create a new user called AUDIT_TEST.
SQL> CREATE USER audit_test IDENTIFIED BY audit_test
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;
GRANT connect TO audit_test;
GRANT create table, create procedure TO audit_test;
Next we audit all operations by the AUDIT_TEST user.
CONNECT sys/password AS SYSDBA
AUDIT ALL BY user_name BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY user_name BY ACCESS;
AUDIT EXECUTE PROCEDURE BY audit_test BY ACCESS;
Next, we perform some operations that will be audited.
CONN audit_test/password
CREATE TABLE test_tab (id NUMBER);
INSERT INTO test_tab (id) VALUES (1);
UPDATE test_tab SET id = id;
SELECT * FROM test_tab;
DELETE FROM test_tab;
DROP TABLE test_tab;In the next section we will look at how we view the contents of the audit trail.
COLUMN username FORMAT A10
COLUMN owner FORMAT A10
COLUMN obj_name FORMAT A10
COLUMN extended_timestamp FORMAT A35
SELECT username,
extended_timestamp,
owner,
obj_name,
action_name
FROM dba_audit_trail
WHERE owner = 'AUDIT_TEST'
ORDER BY timestamp;
AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;
select OS_USERNAME, USERNAME, USERHOST, TERMINAL, TIMESTAMP,
OWNER, OBJ_NAME, ACTION_NAME, NEW_OWNER, NEW_NAME,
SES_ACTIONS, COMMENT_TEXT, SESSIONID, ENTRYID, STATEMENTID,
RETURNCODE, PRIV_USED, CLIENT_ID, SESSION_CPU,
EXTENDED_TIMESTAMP, PROXY_SESSIONID, GLOBAL_UID, INSTANCE_NUMBER,
OS_PROCESS, TRANSACTIONID, SCN, SQL_BIND, SQL_TEXT
from dba_audit_trail
where ACTION_NAME like '%INSERT%'
This article will introduce the reader to the basics of auditing an Oracle database. Oracle's RDBMS is a functionally rich product and there are a number of auditing alternatives available to the reader. Because auditing Oracle is such a huge subject, doing all of it justice would take an entire book, so this paper will cover the basics of why, when and how to conduct an audit. It will also use a couple of good example cases to illustrate how useful Oracle audit can be to an organization.
The Issues
There are a number of basic issues that should be considered when contemplating using Oracle's auditing features. These are as follows:
• Why is audit needed in Oracle?
Is this a strange question? Well, lots of companies don't actually use the internal audit features of Oracle. Or, when they do use them, they are so overwhelmed with choice, they turn on everything for good measure, then realise there is far too much output to read and digest so they quickly turn it all off again. It is quite common to use firewalls, intrusion detection systems (IDS) and other security tools to determine if the network or operating system is being misused or abused. So why not audit what users are doing to the "crown jewels" of an organization, the data. Oracle audit can help detect unauthorized access and internal abuse of the data held in the database.
• When should Oracle users be audited?
A simple basic set of audit actions should be active all the time. The ideal minimum is to capture user access, use of system privileges and changes to the database schema structure. This basic set will not show attempted access to specific data that shouldn't be accessed; however, it will give a reasonably simple overview of "incorrect" access and use of privileges. If an employee is suspected of inappropriate actions or if an attack has been suspected then more detailed audit can be turned on for specific tables. From a data management point of view, auditing data changes for all tables in the database is not really practical and could also affect performance. Monitoring data change access on critical tables (such as salaries in a HR database) should be considered.
• How can Oracle users be audited?
The standard audit commands allow all system privileges to be audited along with access at the object level to any table or view on the database for select, delete, insert or update. Audit can be run for either successful or unsuccessful attempts or both. It can be for each individual user or for all users and it can also be done at the session level or access level. At action level a single record is created per action and at session level one record is created for all audit actions per session.
• What are the performance and complexity issues?
Audit is generally perceived to be complex and slow. The reason for this is usually ignorance. If many or all options are turned on, then the resultant audit trail produced can be large and difficult to interpret and manage. Furthermore, if audit is used on all tables and views in the database, then this can have an effect on performance. Every time an action performed is auditable a record is written to the database; clearly the more audit is used, the more records will be written to the system tablespace purely for audit. In some cases double the amount of access to the database can be performed: the original write and the audit record being written.
The watchword here is simplicity and caution. Use only the audit that is needed to give an overall view of what is happening and for detailed monitoring of critical data and objects. The simpler the audit trail set-up, the more likely it is that the data will be analyzed and be of some use. It is important to define what actions or abuses are being checked for so that simple reports can be written to filter the audit trail for these actions. A default installation of Oracle has audit turned off by default and Oracle does not come with any standard default audit settings or reports to analyse any audit trail produced. These reasons, and the fact that there are many options available are, in my opinion, why audit is perceived to be complex.
The standard audit commands do not allow audit to be performed at row level. It is also not possible to audit the actions of privileged users such as SYS and "as sysdba" until Oracle 9iR2.
Oracles Audit Facilities
The task of auditing an Oracle database does not have to be limited only to the audit commands; other techniques can be employed as well. Here are some of the main methods that can be used to audit an Oracle database:
• Oracle audit
This is really the subject of this paper. All privileges that can be granted to a user or role within the database can be audited. This includes read, write and delete access on objects at the table level. For more detailed audit, the database triggers need to be employed.
• System triggers
These were introduced with Oracle and allow the writing of database triggers that fire when system events take place. These include start- up and shutdown of the database, log-on and log-off attempts, and creation, altering and dropping of schema objects. With the aid of autonomous transactions, these allow a log to be written for the above system events.
• Update, delete, and insert triggers
This is the second line of defence in trying to understand users' actions at a more detailed row level. Database triggers need to be written to capture changes at the column and row level. It is possible to write complete rows of data before and after the change being made to a log table in the database. The use of this type of logging is very resource intensive, as many extra records are written and stored. The one failing with this method is that read access cannot be captured with normal database triggers.
• Fine-grained audit
Fine-grained audit solves the problem of capturing read access. This feature is also based on internal triggers that fire when any piece of SQL is parsed. This is very efficient, as the SQL is parsed once for audit and execution. The feature uses predicates that are defined and tested each time the relevant object is accessed. Fine-grained audit is managed by a PL/SQL package called DBMS_FGA. A PL/SQL procedure is executed every time a "match" is made with the predicate. This method allows the audit to be performed down to the row and column level and to also for read statements. Readers should be forewarned that use of this feature requires programming skills.
• System logs
Oracle generates many log files and many of them can provide useful information to assist in auditing the database. One good example is the alert log used by the database to record start-up and shutdown as well as any structural changes such as adding a datafile to the database.
This paper is going to explore only the standard built-in audit commands. The other options will be left for future articles.
Some Examples
Because of the myriad of possibilities, auditing an Oracle database can be a daunting task. In order to try and simplify the discussion of what can be done, we will discuss a couple of simple examples that we will explore and work through.
• Auditing database access
This is a fundamental check to find out who accesses the database, from where and when. Log-on failures can be captured as well as log- ons at strange (anomolous) times of the day.
• Auditing changes to the database structure
In a production database, no user should ever change the schema structure. DBAs should make changes for upgrades at specific times; any other changes should be regarded as suspicious. Watching for structural changes can turn up indicators of incorrect use of the database.
A third simple example that could have been employed here is to audit any use of system privileges. However, this example is left to the reader to explore.
The final group of audit commands that can be employed is to audit any data changes to objects themselves. Unfortunately, as the requirements are very application and installation specific, this is beyond the scope of this paper.
Audit within Oracle is broken into three areas: statement auditing such as CREATE TABLE or CREATE SESSION, privilege auditing such as ALTER USER, and object level auditing such as SELECT TABLE.
Basic Configuration
The audit trail can be either written to the database or to the operating system. Writing the audit trail to the operating system is, in some senses, more secure but the implementation is not available on all platforms and is platform specific. In this article we will concentrate on using the database to store the audit trail.
Audit is turned on for writing to the database by adding the following line to the init.ora file. A symbolic link to it can usually be found in $ORACLE_HOME/dbs
audit_trail = db
The database now needs to be restarted. A simple check will show that audit is indeed now turned on.
SQL> select name,value from v$parameter
where name like 'audit%';
NAME VALUE
------------------------------ ------------------------------
audit_trail DB
audit_file_dest ?/rdbms/audit
No audit actions are captured yet until audit actions are defined; that is, except for privileged access to the database, starting and stopping of the database, and structural changes such as adding a datafile. These are logged to operating system files in $ORACLE_HOME/rdbms/audit unless audit_file_dest is redefined in the init.ora file. On Windows these events appear in the Event Viewer. To check if any privilege or statement audit actions are enabled, do the following:
SQL> select * from dba_stmt_audit_opts
union
select * from dba_priv_audit_opts;
no rows selected
To find out what objects are being audited, query the view dba_obj_audit_opts.
The Worked Examples
Let us now work through our two example cases and see what can be learned. First, turn on audit for the access attempts to the database:
SQL> audit create session;
Audit succeeded.
The above command will capture access by all users by access and whether successful or unsuccessful. The default for this command is by access.
Note: The format of all audit commands from the Oracle documentation is as follows:
audit {statement_option|privilege_option} [by user] [by
{session|access}] [ whenever {successful|unsuccessful}]
Only the statement_option or privilege_option part is mandatory. The other clauses are optional and enabling them allows audit be more specific.
For a user to define audit statements, the privilege "AUDIT SYSTEM" needs to have been granted first. The users that have this privilege can be checked as follows:
SQL> select *
from dba_sys_privs
where privilege like '%AUDIT%';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
CTXSYS AUDIT ANY NO
CTXSYS AUDIT SYSTEM NO
DBA AUDIT ANY YES
DBA AUDIT SYSTEM YES
IMP_FULL_DATABASE AUDIT ANY NO
MDSYS AUDIT ANY YES
MDSYS AUDIT SYSTEM YES
WKSYS AUDIT ANY NO
WKSYS AUDIT SYSTEM NO
9 rows selected.
The above results are for a 9i database, the default users MDSYS, CTXSYS and WKSYS would likely be good targets for attackers, as any audit actions could be turned off as one of these users to hide any actions undertaken.
Now that audit will capture all access attempts, we need to wait for some users to log in to do work. So while they do that, let's set up the audit to capture alterations to the schema. For the sake of brevity, in this example, not all schema object changes will be captured. Changes to tables, indexes, clusters, views, sequences, procedures, triggers, libraries and many more can be captured. In this example, audit will be enabled on an example set. Turning on the audit can be performed as a two-stage process, generate the audit commands and then run them as follows:
set head off
set feed off
set pages 0
spool aud.lis
select 'audit '||name||';'
from system_privilege_map
where (name like 'CREATE%TABLE%'
or name like 'CREATE%INDEX%'
or name like 'CREATE%CLUSTER%'
or name like 'CREATE%SEQUENCE%'
or name like 'CREATE%PROCEDURE%'
or name like 'CREATE%TRIGGER%'
or name like 'CREATE%LIBRARY%')
union
select 'audit '||name||';'
from system_privilege_map
where (name like 'ALTER%TABLE%'
or name like 'ALTER%INDEX%'
or name like 'ALTER%CLUSTER%'
or name like 'ALTER%SEQUENCE%'
or name like 'ALTER%PROCEDURE%'
or name like 'ALTER%TRIGGER%'
or name like 'ALTER%LIBRARY%')
union
select 'audit '||name||';'
from system_privilege_map
where (name like 'DROP%TABLE%'
or name like 'DROP%INDEX%'
or name like 'DROP%CLUSTER%'
or name like 'DROP%SEQUENCE%'
or name like 'DROP%PROCEDURE%'
or name like 'DROP%TRIGGER%'
or name like 'DROP%LIBRARY%')
union
select 'audit '||name||';'
from system_privilege_map
where (name like 'EXECUTE%INDEX%'
or name like 'EXECUTE%PROCEDURE%'
or name like 'EXECUTE%LIBRARY%')
/
spool off
@@aud.lis
This will generate a set of audit commands that can be captured to a spool file, which is then run to enable the audit commands.
Another solution would be to audit the actual permissions granted to users by generating the audit commands from the database view dba_sys_privs. While this may seem to be a better solution and potentially involve less audit commands, it would not allow for the case when new permissions are granted to users. In this case, audit would also need to be enabled at the time the privileges are granted.
Now that all of the sample audit is now enabled, the settings can be viewed with this SQL:
select audit_option,success,failure
from dba_stmt_audit_opts
union
select privilege,success,failure
from dba_priv_audit_opts
SQL> /
AUDIT_OPTION SUCCESS FAILURE
---------------------------------------- ---------- ----------
ALTER ANY CLUSTER BY ACCESS BY ACCESS
ALTER ANY INDEX BY ACCESS BY ACCESS
ALTER ANY INDEXTYPE BY ACCESS BY ACCESS
ALTER ANY LIBRARY BY ACCESS BY ACCESS
EXECUTE ANY LIBRARY BY SESSION BY SESSION
EXECUTE ANY PROCEDURE BY SESSION BY SESSION
38 rows selected.
Every time a user attempts anything in the database where audit is enabled the Oracle kernel checks to see if an audit record should be created or updated (in the case or a session record) and generates the record in a table owned by the SYS user called AUD$. This table is, by default, located in the SYSTEM tablespace. This in itself can cause problems with potential denial of service attacks. If the SYSTEM tablespace fills up, the database will hang.
The AUD$ table is rare, as it is the only SYS owned table from which Oracle allows records to be deleted. If the audit trail is turned on and written to the database, then the numbers of records in this table need to be monitored carefully to ensure it doesn't grow too fast and fill the system tablespace. A purging strategy needs to be adopted to keep the size of the table in check and, if needed, to archive off audit trail records for future reference. One tactic could be to copy the records to summary tables that allow specific checks for abuse to be performed offline. These summary tables can be in a separate database for added security. Once copied, sys.aud$ can be truncated.
SYS.AUD$ can be moved to a different tablespace other than SYSTEM but check with Oracle support first, as this action is no longer supported.
Only users who have been granted specific access to SYS.AUD$ can access the table to read, alter or delete from it. This is usually just the user SYS or any user who has had permissions. There are two specific roles that allow access to SYS.AUD$ for select and delete, these are DELETE_CATALOG_ROLE and SELECT_CATALOG_ROLE. These roles should not be granted to general users.
Back to the examples, our users have been logging on and working throughout the day and created some audit records. These audit records can be viewed in a number of ways:
• By selecting from SYS.AUD$ - This is the raw audit trail
• By selecting from dba_audit_trail - This is a DBA view showing the raw audit trail.
• By selecting from dba_audit_session - This view shows just log-on and log-off actions.
A simple piece of SQL can show details of the connection attempts:
SQL> get check_create_session
--
-- check_create_session.sql
--
col username for a15
col terminal for a6
col timestamp for a15
col logoff_time for a15
col action_name for a8
col returncode for 9999
select username,
terminal,
action_name,
to_char(timestamp,'DDMMYYYY:HHMISS') timestamp,
to_char(logoff_time,'DDMMYYYY:HHMISS') logoff_time,
returncode
from dba_audit_session
USERNAME TERMIN ACTION_N TIMESTAMP LOGOFF_TIME RETURNCODE
--------------- ------ -------- --------------- --------------- ----------
SYS pts/1 LOGOFF 09042003:051046 09042003:051641 0
ZULIA pts/1 LOGON 09042003:051641 1017
SYS pts/1 LOGOFF 09042003:051649 09042003:053032 0
SYS pts/2 LOGOFF 09042003:052622 09042003:053408 0
ZULIA pts/1 LOGON 09042003:053032 1017
There are a number of simple abuses that can be checked for in the area of user access to the database. As examples for this paper we will look at the following:
• Failed log-on attempts
This can indicate fat fingers or attackers' attempts to gain unauthorized access the database. The following SQL highlights this:
SQL> select count(*),username,terminal,to_char(timestamp,'DD-MON-YYYY')
from dba_audit_session
where returncode<>0
group by username,terminal,to_char(timestamp,'DD-MON-YYYY');
COUNT(*) USERNAME TERMIN TO_CHAR(TIM
---------- --------------- ------ -----------
1 BILL pts/3 09-APR-2003
3 FRED pts/3 09-APR-2003
4 ZULIA pts/1 09-APR-2003
This shows two possible abuses, the first is the user Zulia attempting to log on and failing four times on the same day. This could be a forgotten password or it could be someone trying to guess his or her password. A change to the SQL as follows gives a bit more detail:
SQL> select count(*),username,terminal,to_char(timestamp,'DD-MON-YYYY'),returncode
from dba_audit_session
group by username,terminal,to_char(timestamp,'DD-MON-YYYY'),returncode;
COUNT(*) USERNAME TERMIN TO_CHAR(TIM RETURNCODE
---------- --------------- ------ ----------- ----------
1 BILL pts/3 09-APR-2003 1017
1 EMIL pts/1 09-APR-2003 0
1 EMIL pts/2 09-APR-2003 0
1 EMIL pts/3 09-APR-2003 0
1 EMIL pts/4 09-APR-2003 0
3 FRED pts/3 09-APR-2003 1017
3 SYS pts/1 09-APR-2003 0
1 SYS pts/2 09-APR-2003 0
1 SYSTEM pts/5 09-APR-2003 0
4 ZULIA pts/1 09-APR-2003 1017
1 ZULIA pts/1 09-APR-2003 0
11 rows selected.
This reveals that the user successfully logged on on the same terminal on the same day. A number of failed log-ons should be agreed as part of these checks and the above SQL run every day. Those users with failure numbers above the threshold should be investigated.
• Attempts to access the database with non-existent users
One interesting extension to the above SQL is to find attempts to log in where the user doesn't exist. An audit record is still created in this case. The following SQL illustrates:
SQL>select username,terminal,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS')
from dba_audit_session
where returncode<>0
and not exists (select 'x'
from dba_users
where dba_users.username=dba_audit_session.username)
USERNAME TERMIN TO_CHAR(TIMESTAMP,'D
--------------- ------ --------------------
FRED pts/3 09-APR-2003 17:31:47
FRED pts/3 09-APR-2003 17:32:02
FRED pts/3 09-APR-2003 17:32:15
BILL pts/3 09-APR-2003 17:33:01
This is probably abuse. All attempts to log on with a user that doesn't exist should be checked each day and investigated.
• Attempts to access the database at unusual hours
Checks should be made for any attempts to access the database outside of working hours. These accesses could be genuine overtime work or maintenance but they could just as easily be unauthorized access attempts and should be checked as follows:
SQL> select username,
terminal,
action_name,
returncode,
to_char(timestamp,'DD-MON-YYYY HH24:MI:SS'),
to_char(logoff_time,'DD-MON-YYYY HH24:MI:SS')
from dba_audit_session
where to_date(to_char(timestamp,'HH24:MI:SS'),'HH24:MI:SS') <
to_date('08:00:00','HH24:MI:SS')
or to_date(to_char(timestamp,'HH24:MI:SS'),'HH24:MI:SS') >
to_date('19:30:00','HH24:MI:SS')
USERNAME TERMIN ACTION_N RETURNCODE TO_CHAR(TIMESTAMP,'D TO_CHAR(LOGOFF_TIME,
---------- ------ -------- ---------- -------------------- --------------------
SYS pts/1 LOGOFF 0 09-APR-2003 20:10:46 09-APR-2003 20:16:41
SYSTEM pts/5 LOGOFF 0 09-APR-2003 21:49:20 09-APR-2003 21:49:50
ZULIA pts/5 LOGON 0 09-APR-2003 21:49:50
EMIL APOLLO LOGON 0 09-APR-2003 22:49:12
The above SQL shows any connections before 8:00 AM and after 7:30 PM. Any connections, particularly those made by privileged users such as SYS and SYSTEM, should be investigated. Particular attention can be made to the location from which the access was made. For instance, if privileged access is made from machines that are not in the administrator department, the administrator needs to find out why.
• Check for users sharing database accounts
The following SQL looks for users who are potentially sharing database accounts:
SQL> select count(distinct(terminal)),username
from dba_audit_session
having count(distinct(terminal))>1
group by username
COUNT(DISTINCT(TERMINAL)) USERNAME
------------------------- ----------
4 EMIL
3 SYS
3 ZULIA
This shows that three users have accessed their accounts from more than one location. A further check could be to add a time component to see if they are accessed simultaneously and also to restrict the check per day. The above SQL gives some idea of the potential without complicating it too much. Again, these accounts and users should be investigated.
• Multiple access attempts for different users from the same terminal
The final example checks to find where multiple database accounts have been used from the same terminal. The SQL is again simple and could be extended to group by day and also to print out the users per terminal. This is a simple test to illustrate the abuse idea:
SQL> select count(distinct(username)),terminal
from dba_audit_session
having count(distinct(username))>1
group by terminal
COUNT(DISTINCT(USERNAME)) TERMIN
------------------------- ------
3 pts/1
2 pts/2
3 pts/3
3 pts/5
This could indicate someone trying to gain access by trying many accounts and passwords, or it could indicate legitimate users sharing accounts for certain aspects of their work. In either case, the admin should investigate further.
There are, of course, many other scenarios that could indicate possible abuses. Checking for those is as simple as the cases depicted above. It will be left to the reader to experiment. Let me know what you find useful.
The second example case that audit actions were set for is to detect changes made to the database schema. This could include new objects being added or attempts to change existing objects within the database.
A simple piece of SQL will show any audit trail items that relate to objects being created or changed as follows:
col username for a8
col priv_used for a16
col obj_name for a22
col timestamp for a17
col returncode for 9999
select username,
priv_used,
obj_name,
to_char(timestamp,'DD-MON-YYYY HH24:MI') timestamp,
returncode
from dba_audit_trail
where priv_used is not null
and priv_used<>'CREATE SESSION'
SQL> @check_obj.sql
ZULIA CREATE TABLE STEAL_SALARY 09-APR-2003 20:07 0
PETE CREATE PROCEDURE HACK 09-APR-2003 20:42 0
This simple example shows that the user ZULIA has created a table and the user PETE has been writing PL/SQL procedures. Any changes such as this that are found should be investigated in a production database. Many more specific abuses can be checked for in relation to object and schema changes but, in general, no user should be able to alter the database schema in a production database. As a result, the check can remain pretty simple.
Protecting the Database Against These Abuses
The two examples given are just two of many possible scenarios that could be detected using Oracle's auditing facilities. Turning on and managing audit is one of the first steps to securing the database. Using audit should be part of an overall organization security plan and policy that includes Oracle. The database should be audited regularly for misconfiguration or known vulnerabilities that could allow security breaches to take place.
Because of its complex nature and vast number of different ways it can be used and configured, the best approach to securing Oracle will always be to follow the principle of least privilege. Once the database is part of the overall security plan and is configured correctly and checked regularly, then auditing it should be considered an important part of the strategy.
In general, do not grant any privileges to general users in a production database, remove most of the PUBLIC privileges and delete or lock and change the passwords of any default accounts. Ensure that users obey password policies and that the password management features of Oracle are employed.
It is important that the audit actions are planned from a performance and usability point of view and that the audit trail is managed. It is also important that the audit trail data is understood in terms of detecting abuse.
The author's recent book by the SANS Institute "Oracle security step- by-step - A survival guide for Oracle security" gives excellent guidelines on how to configure Oracle securely.
example:::::\
SQL> SHOW PARAMETER AUDIT
SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
SQL> SHUTDOWN immediate;
SQL> startup;
First we create a new user called AUDIT_TEST.
SQL> CREATE USER audit_test IDENTIFIED BY audit_test
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;
GRANT connect TO audit_test;
GRANT create table, create procedure TO audit_test;
Next we audit all operations by the AUDIT_TEST user.
CONNECT sys/password AS SYSDBA
AUDIT ALL BY user_name BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY user_name BY ACCESS;
AUDIT EXECUTE PROCEDURE BY audit_test BY ACCESS;
Next, we perform some operations that will be audited.
CONN audit_test/password
CREATE TABLE test_tab (id NUMBER);
INSERT INTO test_tab (id) VALUES (1);
UPDATE test_tab SET id = id;
SELECT * FROM test_tab;
DELETE FROM test_tab;
DROP TABLE test_tab;In the next section we will look at how we view the contents of the audit trail.
COLUMN username FORMAT A10
COLUMN owner FORMAT A10
COLUMN obj_name FORMAT A10
COLUMN extended_timestamp FORMAT A35
SELECT username,
extended_timestamp,
owner,
obj_name,
action_name
FROM dba_audit_trail
WHERE owner = 'AUDIT_TEST'
ORDER BY timestamp;
AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;
select OS_USERNAME, USERNAME, USERHOST, TERMINAL, TIMESTAMP,
OWNER, OBJ_NAME, ACTION_NAME, NEW_OWNER, NEW_NAME,
SES_ACTIONS, COMMENT_TEXT, SESSIONID, ENTRYID, STATEMENTID,
RETURNCODE, PRIV_USED, CLIENT_ID, SESSION_CPU,
EXTENDED_TIMESTAMP, PROXY_SESSIONID, GLOBAL_UID, INSTANCE_NUMBER,
OS_PROCESS, TRANSACTIONID, SCN, SQL_BIND, SQL_TEXT
from dba_audit_trail
where ACTION_NAME like '%INSERT%'
Sunday, April 5, 2009
Subscribe to:
Posts (Atom)