SQL Tips

[mohsen.net]

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

[mohsen.net]

رمز گذاری در SQL Server
SQL Server 2005 و SQL Server 2008 از یک ویژگی جدید برای رمز گزاری داده ها استفاده می کنند تا از داده ها در مقابل هکر ها محافظت نمایند . هکر ها ممکن است بتوانند در پایگاه داده یا جداول رخنه کنند ، اما با استفاده از رمز گذاری آنها نمی توانند از داده چیزی بفهمند یا از آن استفاده کنند . امروزه رمز گذاری داده های حساس امری ضروری به نظر می رسد زیرا تا زمانی که داده ها بین کلاینت و سرور در شبکه جابه جا می شوند امکان مشاهده انها وجود دارد .
رمز گذاری در سه سطح امکان پذیر است . این سه سطح مکانیزم های مختلفی برای امنیت داده روی شبکه و سرور های محلی فراهم می کنند . سطح های مختلف اجازه می دهند چندین instance از سرویس ها (برای مثال سرویس های SQL Server) روی یک سرور فیزیکی اجرا شوند .
• Windows Level - بالاترین سطح می باشد . از توابع Windows DP API برای رمز گذاری استفاده می کند.
• SQL Server Level - سطح میانی می باشد . از سرویس های Master Key برای رمز گذاری استفاده می کند .
• Database Level – پایین ترین سطح می باشد . از دیتابیس Master Key برای رمز گذاری استفاده می کند .
در رمز گذاری 2 نوع کلید وجود دارد :
• کلید متقارن (symmetric) : در سیستم رمزگذاری متقارن فرستنده و گیرنده یک پیام یک کلید مشترک را بین خود به اشتراک می گذارند که از آن برای رمز کردن پیام و شکستن رمز استفاده می شود . این شیوه پیاده سازی نسبتا آسانی دارد و هردو فرستنده و گیرنده می توانند پیام را رمز گذاری ورمز گشایی کنند .
• کلید نامتقارن (Asymmetric) : در این شیوه فرستنده و گیرنده یک جفت کلید عمومی و اختصاصی دارند که با استفاده از آنها رمزگذاری و رمزگشایی می کنند . این سیستم کمی پیچیده می باشد زیرا فرستنده می تواند از کلیدش استفاده کند پیام را رمز کند اما نمی تواند آن را رمز گشایی کند ، از طرف دیگر گیرنده می تواند با استفاده از کلیدش رمز گشایی کند اما نمی تواند رمز کند .

دو نوع رمز گذاری در SQL وجود دارد :
• سطح دیتابیس (Database Level) : دراین سطح تمام داده های دیتابیس به صورت امن رمزنگاری می شوند . اما هر بار که داده به خوانده یا نوشته می شود در دیتابیس ، تمام دیتابیس نیاز دارد تا رمزگشایی شود . این روش بسیار هزینه بر می باشد و یک راهکار واقعی نمی باشد .
• سطح ستون (یا سطر) : این روش به روش قبلی ارجحیت دارد ، زیرا تنها ستونهایی که شامل اطلاعات حساس و مهم هستند رمزنگاری می شوند . این شیوه در مقایسه با شبوه قبلی مصرف CPU را به شدت کاهش می دهد . اگر یک ستون کلید اصلی باشد یا ستونی باشد که در قسمت WHERE کویری ها یا Join ها استفاده می شود دیتابیس باید تمام ستون را رمزگشایی کند که هزینه زیادی در بر دارد .

حال با مثالی چگونگی رمزنگاری در SQL Server را مشاهده خواهید کرد . در مثال زیر از روش متقارن و الگوریتم Triple DES استفاده شده است . ابتدا یک دیتابیس نمونه می سازیم :



/* Create Database */
USE master
GO
CREATE DATABASE EncryptTest
GO


ابتدا یک جدول با چندین رکورد داده می سازیم . سپس یکی از ستون های جدول را رمز می کنیم .


/* Create table and insert data in the table */
USE EncryptTest
GO
CREATE TABLE TestTable (FirstCol INT, SecondCol VARCHAR(50))
GO
INSERT INTO TestTable (FirstCol, SecondCol)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'
GO
/* Check the content of the TestTable */
USE EncryptTest
GO
SELECT *
FROM TestTable
GO


هر دیتابیس می تواند یک شاه کلید (Master Key) داشته باشد . شاه کلید دیتابیس یک کلید متقارن است که برای محافظت از کلیدهای اختصاصی و کلیدهای نامنقارن موجود در دیتابیس از آن استفاده می شود . شاه کلید از الگوریتم Triple DES همراه با رمز عبور کاربر برای رمز کردن کلید ها استفاده می کند .


/* Create Database Master Key */
USE EncryptTest
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'MyEncryption'
GO


گواهینامه ها (Certificates) برای محافظت از کلیدهای رمز نگاری استفاده می شوند . SQL این قابلیت را دارد تا گواهینامه های self-signed X.509 را تولید کند .

/* Create Encryption Certificate */
USE EncryptTest
GO
CREATE CERTIFICATE EncryptTestCert
WITH SUBJECT = 'MyEncryption'
GO


کلید متقارن به وسیله روش های گوناگونی مانند گواهینامه ، رمز عبور ، کلید متقارن و کلید نامتقارن رمز می شود . الگوریتم های متفاوتی می تواند برای رمز کردن کلید استفاده شود . الگوریتم هایی که پشتیبانی می شوند DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192, و AES_256 می باشند .

/* Create Symmetric Key */
USE EncryptTest
GO
CREATE SYMMETRIC KEY TestTableKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE EncryptTestCert
GO


حال یک ستون از نوع varbinary به جدول اصلی اضافه می کنیم که مقدار رمز شده فیلد SecondCol را نگه می دارد .


/* Encrypt Data using Key and Certificate
Add Columns which will hold the encrypted data in binary */
USE EncryptTest
GO
ALTER TABLE TestTable
ADD EncryptSecondCol VARBINARY(256)
GO


قبل از اینکه از کلید استفاده شود باید با همان شیوه ای که رمز شده رمز گشایی شود . در مثال ما ، ما از یک گواهینامه برای رمزکردن کلید استفاده کردیم . پس از همان گواهینامه برای باز کردن کلید استفاده می کنیم. پس از اینکه کلید باز شد و قابل استفاده شد ، می توانیم برای ذخیره مقادیر رمز شده در دیتابیس از تابع EncryptKey استفاده کنیم .


/* Update binary column with encrypted data created by certificate and key */
USE EncryptTest
GO
OPEN SYMMETRIC KEY TestTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
UPDATE TestTable
SET EncryptSecondCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SecondCol)
GO


حال می توانیم ستون SecondCol را Drop کنیم زیرا مقادیر آن به صورت رمز شده در ستون EncryptSecondCol وجود دارند. اگر ستون را حذف نکنید می توانید وقتی داده رمز گشایی شد از آن برای تست صحت عملیات استفاده کنید .

/* DROP original column which was encrypted for protect the data */
USE EncryptTest
GO
ALTER TABLE TestTable
DROP COLUMN SecondCol
GO


حال با یک SELECT ساده می توانیم ببینیم آیا داده های ما به خوبی رمز شده اند یا خیر . اکنون اگر هکر ها راهی برای رسیدن به داده ها داشته باشند امکان استفاده از آن را ندارند .

/* Check the content of the TestTable */
USE EncryptTest
GO
SELECT *
FROM TestTable
GO


کاربرانی که اعتبار سنجی شده اند می توانند از تابع DecryptbyKey برای بازگرداندن داده اصلی از داده رمز شده استفاده کنند . اگر کلید متقارن برای رمزگشایی باز نشده باشد ، باید از گواهینامه ای که کلید را با آن رمزکرده ایم استفاده کنیم . نکته مهم در اینجا این است که ستون اصلی و ستون رمز گشایی شده باید یک DataType داشته باشند . اگر DataType های متفاوتی داشته باشند ممکن است مقادیر متفاوتی تولید شود . در مثال ما ستون SecondCol نوع داده اش Varchar بود .


/* Decrypt the data of the SecondCol */
USE EncryptTest
GO
OPEN SYMMETRIC KEY TestTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptSecondCol) ) AS DecryptSecondCol
FROM TestTable
GO


دقت کنید بعد از استفاده از کلید حتما آن را ببندید . اگر کلید ها و گواهینامه ها فقط یک بار استفاده شده اند یا دیگر استفاده نمی شوند بهتر است آن ها را حذف کنید .

/* Clean up database */
USE EncryptTest
GO
CLOSE SYMMETRIC KEY TestTableKey
GO
DROP SYMMETRIC KEY TestTableKey
GO
DROP CERTIFICATE EncryptTestCert
GO
DROP MASTER KEY
GO


خلاصه
رمز نگاری یک ویژگی مهم امنیتی در SQL می باشد . کلید های بزرگتر و نامتقارن باعث ایجاد رمزنگاری غیرقابل حمله و قویتری می شوند که این خود باعث مصرف بیشتر CPU می شود . وفتی حجم زیادی از داده برای رمزنگاری وجود دارد ، توصیه می شود از کلید متقارن استفاده شود . از آنجایی که داده رمز شده نمی تواند فشرده (Compress) شود توصیه می شود قبل از رمز نگاری داده تا حد ممکن فشرده شود .

[mohsen.net]

معرفی ابزار های رایگان SQL برای DBA ها

بخش اول SSMS Tools
علارغم امکانات بسیاری که SQL برای DBAها به همراه داشت ، متاسفانه SSMS از ضعف هایی در کمک به برنامه نویسان در هنگام نوشتن کویری بهره می برد . intellisence ضعیف نسبت به Visual Studio توقع برنامه نویسان را بشدت افزایش داده است . در نسخه 2008 بخشی از انتظارات برآورده شد ، اما باگ های نیز به همراه داشت (عدم کارکرد بعد از نصب VS2010 SP1 یا بعد از offline شدن یک دیتابیس)

بدون شک یکی از بهترین محصولات برای رفع این نقیصه محصول شرکت red-gate است ، اما این ابزار رایگان نیست و نسخه کرک شده آن هم با اشکالاتی همراه است .
SSMS Tools یک افزونه رایگان است که امکانات زیادی برای توسعه دهندگان به همراه دارد. در زیر برخی از امکانات آن بررسی خواهد شد :
• تحلیل Execution plan (و ساخت دستورات مورد نیاز ، مثلا ساخت index هایی که پیشنهاد می شوند)
• تاریخچه اجرای دستورات
• snippet
• فرمت کردن کد های T-Sql
• Template برای باز شدن کویری جدید
• تولید Sp های CREATE , READ , UPDATE , DELETE
• تعریف Region در کد
• تولید دستورات Insert برای جدول یا کل بانک
• جستجو در Grid نتایج
• کپی Execution plan در کلیپ بورد یا ذخیره آن در فایل
• جستجوی یک مقدار در تمام جداول (یا view ها) در بانک

برای دانلود این ابزار از این سایت (http://www.ssmstoolspack.com/Download)می توانید استفاده کنید

[mohsen.net]

رزرو شده برای پست های بعدی 2

[mohsen.net]

رزرو شده برای پست های بعدی 4

[mohsen.net]

هنگامی که یک دستور "* SELECT" برای ایک جدول اجرا می شود انتظار این است که همیشه تمام ستون های آن جدول در خروجی قرار گیرد . اما همیشه این گونه نیست .
ستون های از نوع sparse در SQL Server 2008 معرفی شده اند . وقتی "* SELECT" بر روی یک جدول که دارای ستون هایی از نوع sparse و sparse column set می باشد اجرا شود ، تمام ستونهای غیر sparse و ستونی که به عنوان sparse column set تعریف شده است در خروجی قرار می گیرند . تمام ستونهایی که sparse هستند و مقادیری غیر از NULL دارند ، به عنوان یک Column set در فرمت xml نمایش داده می شوند .

یک column set داده ای با فرمت untyped XML می باشد ، که تمام ستون های از نوع sparse یک جدول را به یک خروجی دارای ساختار مشخص ترکیب می کند . در واقع column set مانند یک ستون محاسباتی می باشد که به صورت فیزیکی در جدول ذخیره نمی شود، اما column set مستقیما قابلیت بروزرسانی دارد .
در زیر مثالی از نحوه عملکرد ستون های sparse می آید :


IF OBJECT_ID('tempdb..#temp','U') IS NOT NULL DROP TABLE #temp;
CREATE TABLE #temp (
RowID INT IDENTITY PRIMARY KEY CLUSTERED,
Name VARCHAR(25),
Column1 INT SPARSE,
Column2 INT SPARSE,
Column3 INT SPARSE,
Column4 INT SPARSE,
ColumnSet XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);

INSERT INTO #temp (Name, Column1) VALUES ('Row1', 1);
INSERT INTO #temp (Name, Column1, Column2) VALUES ('Row2', 2, 2);
INSERT INTO #temp (Name, Column1, Column2, Column3) VALUES ('Row3', 3, 3, 3);
INSERT INTO #temp (Name, Column1, Column2, Column3, Column4) VALUES ('Row4', 4, 4, 4, 4);
INSERT INTO #temp (Name, Column1, Column3) VALUES ('Row5', 5, 5);
INSERT INTO #temp (Name, Column3, Column4) VALUES ('Row6', 6, 6);

SELECT * FROM #temp;

http://mohsen326.persiangig.com/image/sparse.JPG

[desatir7316]

سلام دوست عزیز ممنون به خاطر مطالب مفیدت
چرا تاپیکو دیگه ادامه نمیدی؟
راستی میتونی به من کمک کنی تا به جواب سوالاتم توی این تاپیک برسم؟

http://barnamenevis.org/showthread.php?348520-%D8%B3%D9%88%D8%A7%D9%84-%DA%A9%D8%AF%D8%A7%D9%85-%DB%8C%DA%A9-%D8%A7%D8%B2-%D8%B1%D9%88%D8%B4-%D9%87%D8%A7%DB%8C-%D8%B1%D9%85%D8%B2-%D9%86%DA%AF%D8%A7%D8%B1%DB%8C-%D8%A8%D9%87%D8%AA%D8%B1-%D8%A7%D8%B3%D8%AA%D8%9F&p=1537159#post1537159

ممنون

[mohsen.net]

ادامه نمی دهم چون سرم شلوغه و بیشتر دارم را SSAS کار می کنم تا SQL
اما اگر مطلبی در مورد SQL تهیه کردم می گذارم اینجا
بلاگم Update تر هست