آموزش Microsoft SQL Server - # جلسه دوم - امنیت

جلسه دوم

Security:

• Authentication (اهراز هویت – Log In)
• Authorization (دسترسی ها – Permissions)

بحث امنیت شامل دو مبحث عمده است که بخش اول آن تعیین می کند که چه کاربرانی می توانند به یک پایگاه داده وصل شوند این اهراز هویت صرف نظر از جزییات اعمالی است که کاربر می تواند انجام دهد. بخش دوم تعیین می کند کاربری که در قدم اول به پایگاه داده وصل شده قادر به انجام چه اعمالی است این دسترسی و محدودیت ها طبق نقش ها و دسترسی های مستقیمی که کاربر دارد تعیین می گردد.

اهراز هویت - Authentication

درMicrosoft SQL Server به دو روش کاربران شناسایی می شوند:

• Windows Authentication
• SQL Server Authentication

در روش اول کاربر توسط ویندوز شناسایی می شود به همین خاطر از کاربر Pass & User خواسته نمی شود در واقع شناسایی بر اساس نام کاربری که به ویندوز لاگین کرده است انجام می شود، در روش دوم اطلاعات کاربر توسط Server SQL نگه داری می شود به همین خاطر کاربر بایستی برای شناسایی شدن Pass & User خود رابه SQL بدهد تا اهراز هویت شود. مواردی که Authentication Windows قابل استفاده نیست:

• درصورت عدم وجود Domain
• زمانی که نمی خواهیم همه اعضای Domain به SQL Server دسترسی داشته باشند

البته همیشه می توان به ماشین جاری که یک نسخه SQL Server روی آن نصب شده به صورت ویندوزی وصل شد، در نظر داشته باشید که این روش اهراز هویت در هر دو روش قابل دسترس است.

لاگین SA قویترین لاگین SQL Server است که نه می توان آن را حذف کرد و نه تغییری در دسترسی آن ایجاد کرد. (منظور عدم توانایی در کاهش دسترسی های این اکانت است)

هم چنین کاربری که صاحب (Owner) یک شی (Job, Database, Object)  است قابل حذف کردن نیست.

نسخه های مختلف SQL Server و شماره آنها:

تعریف یک کاربر جدید:

برای تعریف یک new User در قسمت Object Explorer بخش Security رفته و در آنجا زیر منوی Logins را انتخاب می کنیم و سپس روی آن کلیک راست کرده و آیتم new Login را انتخاب می کنیم.

فقط زمانی می توانیم یک یوزر را از نوع Windows Authentication انتخاب کنیم که یوزری با همین نام قبلا در Domain ویندوز همین ماشین تعریف شده باشد، آیتم های بعدی شامل Change Must User, Pass Enforce  فقط در ویندوز 7 و بالاتر قابل استفاده هستند و در ویندوز اکس پی باعث بروز خطا می شوند.

Authorization:

بعد از Authenticate شدن بایستی Authorize شویم. یعنی به یوزر دسترسی Permission اعطا شود، راه های مختلفی برای  Authorize شدن وجود دارد:

1-عضویت در یک Server Role

برای اینکه به یک User نقش بدهیم بایستی یک DBA آنرا عضو یک Role کند. اینکار را هم می توان زمان تعریف یوزر هم پس از آن انجام داد. برای اینکار در قسمت Logins روی User مورد نظر دابل کلیک می کنیم در زیر منوی Server Roles،همه یوزرها عضو رول Public هستند یوزری را نمی توان از آن خارج کرد البته هیچ دسترسی Permission خاصی ندارد، مهم ترین Role که در Server SQL وجود دارد sysadmin است که معادل Sa قدرت دارد.

برای Replication کافی است یوزر Owner دیتابیس مورد نظر باشد. یوزری که sysadmin است نیازی به عضویت در Role دیگری ندارد. دیگر Role ها دسترسی های ریزتری دارند.

برای مثال:

2-ایجاد ID User دریک دیتابیس خاص ودادن دسترسی Permission به آن در دیتابیس

در قسمت Mapping User مشخص می کنیم یک Login خاص به جه دیتابیس هایی دسترسی داشته باشد. در قسمت User می توان نام متفاوتی برای یوزری که از این Login استفاده می کند وارد کرد. دراینجا هم اگر برای لاگینی به یک دیتابیس دسترسی بدهیم فقط می تواند وارد آن دیتابیس شود و برای دسترسی های بیشتر کمترین آن دیدن جداول بایستی دربخش پایینی این فرم آن لاگین را عضو

یکی از Role های مربوط به این دیتابیس کرد. در این بخش Role Public هم مانند بخش قبل است یعنی هیچ دسترسی ندارد و همه پیش فرض عضو آن هستند. مگر اینکه دسترسی هایی به Role Public داده شود که معمول نیست.

رول db_owner مهم ترین Role در دیتابیس است که معاد SA اما فقط در یک دیتابیس دسترسی دارد.

3-راه سوم، در DB Properties یا Table Properties دسترسی ها را به ریز تعیین کنیم

ریز دسترسی های یک Role Or User در این بخش قابل تعیین است که می تواند شامل دسترسی در یک دیتابیس یا جدول باشد. این کار برای هر شی در دیتابیس قابل انجام است کافی است به منوی Permission در Properties آن شی برویم.

Grant : اگر دسترسی ندارد به آن دسترسی داده شود

With Grant : این لاگین یا رول بتواند این دسترسی را به بقیه بدهد

Deny : اگر دسترسی دارد از او گرفته شود

باتوجه به اینکه روش های متعددی برای دادن دسترسی به الگین ها یا رول ها وجود دارد نمی توان تعیین کرد که دسترسی موجود چگونه است به همین خاطر به این شکل دسترسی ها طراحی شده اند.

برای User هایی که قرار است توسط Application استفاده شوند برای اتصال به دیتابیس، اگر برای هر بخش از اپلیکیشن یک دیتابیس جداگانه ساخته شده باشد پس یوزری که با آن به دیتابیس وصل می شویم بایستی Owner آن دیتابیس باشد اما اگر دیتابیس کلی وجود داشته باشد بایستی برای هر بخش از یوزری استفاده کرد که Owner  شمای مربوط به همان بخش است. پس محدودیت های که در اپلیکیشن اعمال می شود توسط خود اپلیکیشن مدیریت می شود و اطلاعات آن مانند دیگر داده ها در جداول بانام Users   Permissions & ذخیره می گردد، پس اپلیکیشن با دسترسی حداکثر به دیتابیس وصل می شود.

برای گرفتن کل دسترسی ها از لاگین های ویندوزی باید تمام Login های مربوط به ویندوز یا همان windows authentication ها را حذف کنیم.

Data Base:

دیتابیس برای نگه داری جداول و روابط و محدودیت های جداول Constraints و برنامه هایی که برای پردازش آن اطلاعات در سرور نوشته می شوند. مهم ترین شی که در دیتابیس نگه داری می شود جدول است.

دو روش برای ایجاد دیتابیس وجود دارد : روش اول برای هر زیر سیستم یک دیتابیس ساخته شود، روش دوم یک دیتابیس جامع بسازیم که همه زیر سیستم ها را در برگیرد (سیستم جامع ERP)

ERP: سیستم مدیریت تجاري یكپارچه شامل تمام زمینه ها و روش هاي كامپیوتري كه براي برنامه ریزي موثر و مدیریت یك حرفه و تجارت لازم است. Enterprise Resource Planning

معروف ترین ERP دنیا SAP در کشور آلمان است. مثال های دیگر: Oracle یک ERP می باشد اما ماکروسافت نیست ماکروسافت CRM است.

Schema: تقسیم بندی منطقی اشیا درون یک دیتابیس است. بیشترین استفاده از اسکیما دادن دسترسی می باشد. شما یا اسکیما در 2005 SQL اضافه شد، با استفاده از آن می توان دیتابیس یک سیستم یکپارچه را به چندین بخش منطقی تقسیم کرد.

سکیمای dbo (Owner Base Data) یک اسکیمای پیش فرض Default است، اگر آیتم تولید کنیم و مشخص نکنیم مربوط به کدام اسکیمای تعریف شده است در اسکیمای dbo قرار می گیرد. وجود dbo برای سازگاری با 2000 SQL است چون آنجا اسکیما نداشتیم.

اسکیما در SQL معادل Namespace در برنامه نویسی شی گراست، برای تعیین اسکیمای یک آبجکت در زمان Design  با استفاده از کلید F4 وارد پنجره Properties می شویم آنجا می توان اسکیمای آبجکت را مشخص کرد. می توان برای اسکیمای آبجکت های موجود در دیتابیس یک درخت سلسله مراتب Hierarchy ساخت دقیقا مانند namespace  در برنامه نویسی شی گرا.

می توان اسکیمای شی ساخته شده را هم با دستور همچنین توسط واسط گرافیکی تغییر داد، اما بایستی در وهله اول Permission های آنرا به روز رسانی کرد هم چنین در برنامه های موجود که نام شی با اسکیمای قبلی آمده است بایستی به اسکیمای جدید تغییر کند. این مشکل را می توان با استفاده از Synonym موقتا حل کرد تا زمانی که برنامه ها به روز رسانی شود.

برای ایجاد و محدود کردن دسترسی لاگین ها از طریق اسکیما، ابتدا بایستی در آیتم Security مربوط به سرور روی لاگین مورد نظر دبل کلیک کرده در فرم Properties لاگین به قسمت Mapping User مراجعه کرده در آنجا دسترسی لاگین را به دیتابیس مورد نظر ایجاد می کنیم سپس به منوی اسکیما ها در دیتابیس مورد نظر مراجعه کرده در پنجره Properties Schema به قسمت Permissions مراجعه کرده در آنجا لاگین مورد نظر را انتخاب و دسترسی های مورد نظر را ایجاد می کنیم. اگر لاگین را به عنوان Owner اسکیما تعیین کنیم لاگین مورد نظر در این دیتابیس همه دسترسی ها را دارد.

وقتی از 2000 SQL به 2008 SQL کوچ کرده ایم، به صورت خودکار اسکیمای همه اشیا در دیتابیس به dbo تبدیل می شود، اکنون می خواهیم برای جداول اسکیما قرار دهیم، چه باید کرد؟

از 2005 SQL به بعد Feature اضافه شده به نام Synonym( واژه مترادف(، مثال جدولی داریم به نام Customer که در اسکیمای dbo قرار دارد. اگر بخواهیم اسکیمای آن را تغییر دهیم به Schema new برنامه هایی که نام Customer در آنها ذکر شده نیز باید به روز رسانی شوند، برای حل مشکل یک Synonym برای Customer تعریف می کنیم. که معادل Customer در اسکیمای dbo باشد.

وقتی جدولی ساخته می شود اگر اسکیمای آن مشخص نشود، به صورت پیش فرض dbo است، می توان این پیش فرض را تغییر داد.

نام دیتابیس و نام هر شی دیگری 121 حرف یونیکد را پشتیبانی می کند، یونیکد یعنی تمام زبان های دنیا را پشتیبانی می کند. به یاد داشته باشیم که بهتر است از نام گذاری فارسی اشیا پرهیز کنیم هم چنین از Space در نام اشیا نیز همین طور، چون استفاده از آنها دشوار است، چون همگی بایستی بین کروشه بیایند.

ایجاد یک دیتابیس جدید:

زمان ایجاد دیتابیس بعد از نام دیتابیس، آیتم بعدی تعیین Owner دیتابیس است، به صورت پیش فرض کسی که آن را ایجاد کرده است به عنوان Owner مشخص می شود.

آیا فرقی بین Owner دیتابیس بودن و عضو Role_owner_db باشد؟

هیچ تفاوتی ندارد پس بهتر است همیشه زمان ایجاد دیتابیس، صاحب آن همان Sa باشد، سپس لاگین هایی که قرار است صاحب دیتابیس باشند عضو Role_owner_db می شوند، فایده این کار این است که اگر زمان ایجاد لاگینی Owner یک دیتابیس تعیین شود وقتی بخواهیم لاگین را حذف کنیم چنین اجازه ای به ما داده نمی شود چون آن لاگین صاحب یک شی است، برای حل مشکل بایستی Owner اشیایی که آن لاگین صاحب آنهاست را تغییر بدهیم.

طراحی فیزیکی دیتابیس:

هر دیتابیس حداقل شامل دو فایل فیزیکی است، شامل )Log , Data )فایل دیتا را در 2008 SQL با نام Data Rows می شناسیم چون نوع دیتا جدیدی به وجود آمده به نام Stream File که مربوط به اطالعات حجیم است مثل عکس و فیلم و ویدیو، فایل دیتا با  پسوند (Master Data File) .mdf و فایل Log به پسوند (Log Data File) .ldfذخیره می شوند. اگر چندین فایل دیتا داشته باشیم به جز فایل اصلی بقیه با پسوند .ndf ذخیره می شوند. یک mdf ، یک ldf ، چند ndg

دیتا شامل هم اطلاعات است، هم برنامه های موجود که در جداول سیستمی ذخیره شده اند.

سابقه یا Log :

سابقه تراکنش هایی که دربانک اجرا می شوند را نگه می دارد، تراکنش )Transaction )یعنی هر تغییری که دردیتا داده می شود یا فرآیندی که موجب تغییر اطالعات می شود. زمان ایجاد یک رکورد جدید اول در الگ نوشته می شود سپس در دیتا، هم چنین زمان به روز رسانی یا آپدیت یک رکورد، رکورد قبل از آپدیت و بعد از آن در الگ نوشته می شود.

هدف از نگه داری سابقه تراکنش ها در لاگ؟

مثال اجرای دستوری برای کاهش 7 دلاری از قیمت های جدول Product ،برای اینکه قیمت های موجود در جدول منفی نشود یک Constraint Check وجود دارد که از منفی شدن جلوگیری کند، پس این دستور با خطا مواجه می شود و رکورد ها تاجایی که به خطا برخورده تغییر کرده اند، این تراکنش بایستی Rollback شود، برای این کار SQL اگر تراکنشی با موفقیت انجام شود، آنرا ذخیره می کند Commit در غیر این صورت بایستی رکوردهای تغییر کرده به حالت اول برگردند. (ره گیری تراکنش)

وبرای این کار از لاگ تراکنش مقدار قبلی داده ها را می خواند و تراکنش ناموفق را Rollback می کند.

هر دستور در SQL یک تراکنش است، اما اگر بخواهیم چند دستور طی یک تراکنش انجام شود چه باید کرد؟

ایجاد تراکنش مرکب:

مجموعه دستوراتی که به عنوان یک دستور واحد درنظر گرفته می شود.

Begin Tran

If Every Thing Ok Commit Tran

If Error Rollback Tran

از 2005 SQL به بعد می توان از Try Catchنیز استفاده کرد

آیا بعد ازانجام یک تراکنش، بالفاصله توسط SQL ذخیره می گردد؟

خیر، برای بهینه سازی دسترسی به دیسک فقط لاگ بلافاصله ذخیره می گردد، دیتا در رم نگه داری می شوند، سپس طی فرایندی به نام Check Point محتویات رم در دیسک ذخیره می شوند

Check Point: طی زمان های تعیین شده توسط SQL محتویات رم در هارد ذخیره می شوند

خاموش شدن ناگهانی سرور (Server Down) بر اثر یک اتفاق:

زمان روشن شدن بعد از خاموشی ناگهانی، قبل از دردسترس قرار گرفتن دراختیار کاربران، وارد مرحله ای به نام Data Base Recoveryمی شود در این فرآیند چندین کار انجام می گیرد:

برگرداندن تراکنش های ناقص Rollback Incomplete Transactions

ذخیره فرایندهای کامل شده که دررم مانده براساس لاگ Rollforward Complete not Checkpoint Trans

یک تراکنش را اجرا کرده سپس سرور Down می شود چگونه بفهیم تراکنش ذخیره شده یا نه؟

یکی از رکورد های را چک می کنیم اگر تغییر ایجاد شده پس کل تراکنش انجام شده است

 آیا می توان فایل لاگ (Log File)را خواند؟

فقط ابزارهای خاصی می توانند این کار را انجام دهند Log Explorer

برای دیدن محتویات لاگ مراجعه کنید به سایتapexsql.com ، ابزاری به نام APexSQL Log 2010

برگرداندن تراکنش های ناقص Rollback Incomplete Transactions

ذخیره فرایندهای کامل شده که دررم مانده براساس لاگ Rollforward Complete not Checkpoint Trans

یک تراکنش را اجرا کرده سپس سرور Down می شود چگونه بفهیم تراکنش ذخیره شده یا نه؟

یکی از رکورد های را چک می کنیم اگر تغییر ایجاد شده پس کل تراکنش انجام شده است

 آیا می توان فایل لاگ (Log File)را خواند؟

فقط ابزارهای خاصی می توانند این کار را انجام دهند Log Explorer

برای دیدن محتویات لاگ مراجعه کنید به سایتapexsql.com ، ابزاری به نام APexSQL Log 2010