رفع OrphanedUser در SQL Server راهنمای جامع امنیت و نگهداری دیتابیس

رفع OrphanedUser در SQL Server: راهنمای جامع برای امنیت و نگهداری دیتابیس

مدیریت صحیح کاربران و مجوزها در SQL Server برای حفظ امنیت و پایداری دیتابیس ضروری است. یکی از مشکلات رایجی که مدیران دیتابیس (DBA ها) با آن مواجه می‌شوند، پدیده “OrphanedUser” (Orphaned Users) است. این مقاله به بررسی دقیق مفهوم OrphanedUser، علل ایجاد آن‌ها و ارائه راه‌حل‌های گام به گام برای شناسایی و رفع این مشکل می‌پردازد تا دیتابیس‌های شما به بهترین شکل محافظت شوند.

OrphanedUser در SQL Server چیستند؟

یک کاربر دیتابیس Orphaned زمانی ایجاد می‌شود که یک لاگین (Login) مربوطه در سطح سرور SQL Server وجود نداشته باشد یا SID (Security Identifier) کاربر دیتابیس با SID لاگین سرور مطابقت نداشته باشد. هر کاربر دیتابیس با یک SID منحصر به فرد به یک لاگین در سطح سرور نگاشت می‌شود. هنگامی که یک دیتابیس به سرور دیگری منتقل یا بازیابی (restore) می‌شود، ممکن است لاگین‌های سرور در سرور مقصد وجود نداشته باشند یا SIDهای آن‌ها با SIDهای موجود در دیتابیس منتقل شده مطابقت نداشته باشند. در نتیجه، کاربر دیتابیس نمی‌تواند به لاگین سرور خود متصل شود و “Orphaned” می‌شود.

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

شناسایی OrphanedUser

اولین گام برای رفع مشکل، شناسایی OrphanedUser است. SQL Server ابزارهایی برای انجام این کار فراهم می‌کند. می‌توانید با اجرای یک کوئری ساده، لیست کاربران دیتابیس که فاقد لاگین‌های متناظر در سطح سرور هستند را پیدا کنید:

USE YourDatabaseName;
GO

SELECT 
    dp.sid AS DatabaseSID,
    dp.name AS DatabaseUser,
    sp.sid AS ServerLoginSID,
    sp.name AS ServerLoginName
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type  'R' -- Exclude roles
AND dp.sid IS NOT NULL -- Exclude users without SID (e.g., guest, dbo, information_schema)
AND sp.sid IS NULL; -- Where there is no matching server login

این کوئری تمام کاربران دیتابیس را (به جز نقش‌ها و کاربران سیستمی مانند guest و dbo) که SID آن‌ها در جدول sys.server_principals مطابقت ندارد، برمی‌گرداند. فیلدهای DatabaseSID و DatabaseUser به ترتیب SID و نام کاربر Orphaned را در دیتابیس نشان می‌دهند.

همچنین، می‌توانید از رویه ذخیره‌شده سیستمی sp_change_users_login با پارامتر 'Report' برای شناسایی OrphanedUser استفاده کنید. این روش ساده‌تر بوده و نتایج مشابهی ارائه می‌دهد:

USE YourDatabaseName;
GO

EXEC sp_change_users_login 'Report';

اجرای این فرمان لیستی از OrphanedUser را در دیتابیس فعلی گزارش می‌کند.

رفع OrphanedUser

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

1. نگاشت کاربر Orphaned به یک لاگین سرور موجود

اگر لاگین سرور مربوطه از قبل در سرور SQL Server مقصد وجود دارد، می‌توانید کاربر دیتابیس Orphaned را به آن لاگین نگاشت کنید. این کار SID کاربر دیتابیس را با SID لاگین سرور موجود هماهنگ می‌کند. برای این منظور، از دستور ALTER USER استفاده می‌شود:

USE YourDatabaseName;
GO

ALTER USER [OrphanedUserName] WITH LOGIN = [ExistingServerLoginName];

در این دستور، [OrphanedUserName] نام کاربر Orphaned در دیتابیس و [ExistingServerLoginName] نام لاگین موجود در سطح سرور است که می‌خواهید کاربر به آن نگاشت شود. این روش امن‌ترین و توصیه شده‌ترین راه‌حل است، زیرا از SID لاگین موجود استفاده می‌کند.

روش قدیمی‌تر (که هنوز هم کار می‌کند اما کمتر توصیه می‌شود) استفاده از sp_change_users_login با پارامتر 'Update_One' است. این رویکرد فقط در صورتی کار می‌کند که یک لاگین با همان نام وجود داشته باشد و SID آن در دیتابیس با SID لاگین مطابقت نداشته باشد:

USE YourDatabaseName;
GO

EXEC sp_change_users_login 'Update_One', 'DatabaseUserName', 'ServerLoginName';

همچنین می‌توانید از پارامتر 'Auto_Fix' برای sp_change_users_login استفاده کنید تا به صورت خودکار کاربر Orphaned را به لاگین سرور با همان نام متصل کند:

USE YourDatabaseName;
GO

EXEC sp_change_users_login 'Auto_Fix', 'DatabaseUserName';

این روش به سادگی SID کاربر دیتابیس را با SID لاگین سرور تطبیق می‌دهد، به شرطی که نام لاگین سرور دقیقاً با نام کاربر دیتابیس مطابقت داشته باشد.

2. ایجاد لاگین جدید برای کاربر دیتابیس Orphaned

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

ابتدا SID کاربر Orphaned را از دیتابیس استخراج کنید:

USE YourDatabaseName;
GO

SELECT sid
FROM sys.database_principals
WHERE name = 'OrphanedUserName';

سپس، با استفاده از SID به‌دست‌آمده، لاگین جدید را ایجاد کنید. فرض کنید SID به دست آمده 0x... باشد:

CREATE LOGIN [NewLoginName] 
    WITH PASSWORD = 'YourStrongPassword', 
    SID = 0x..., -- Replace with the actual SID obtained
    CHECK_POLICY = ON;

اگر کاربر SQL Authenticated است، باید رمز عبور را نیز تعیین کنید. برای کاربران Windows Authenticated، دستور FROM WINDOWS استفاده می‌شود:

CREATE LOGIN [NewLoginName] 
    FROM WINDOWS 
    WITH SID = 0x...; -- Replace with the actual SID obtained

پس از ایجاد لاگین با SID صحیح، کاربر دیتابیس دیگر Orphaned نخواهد بود.

3. حذف کاربر دیتابیس Orphaned

در برخی موارد، ممکن است کاربر Orphaned دیگر نیازی به دسترسی به دیتابیس نداشته باشد. در چنین شرایطی، می‌توانید به سادگی کاربر دیتابیس Orphaned را حذف کنید. این کار معمولاً زمانی انجام می‌شود که می‌خواهید دسترسی کاربر را به طور کامل از دیتابیس قطع کنید یا آن را با یک لاگین جدید از ابتدا پیکربندی کنید.

USE YourDatabaseName;
GO

DROP USER [OrphanedUserName];

قبل از حذف هر کاربر، اطمینان حاصل کنید که این کاربر هیچ شیء (مانند VIEW، FUNCTION، PROCEDURE) را در دیتابیس owns نمی‌کند. در غیر این صورت، ابتدا باید مالکیت آن اشیاء را تغییر دهید یا اشیاء را حذف کنید. همچنین، اگر کاربر دارای Schema باشد، باید ابتدا Schema را حذف یا به کاربر دیگری انتقال دهید.

بهترین روش‌ها و ملاحظات امنیتی

• پشتیبان‌گیری منظم: همیشه قبل از انجام هرگونه تغییر در پیکربندی امنیتی، از دیتابیس خود پشتیبان‌گیری کنید.
• تست در محیط توسعه: تغییرات مربوط به کاربران و لاگین‌ها را ابتدا در یک محیط توسعه یا تست پیاده‌سازی و آزمایش کنید.
• اصل کمترین امتیاز (Principle of Least Privilege): اطمینان حاصل کنید که کاربران فقط مجوزهای لازم برای انجام وظایف خود را دارند.
• مستندسازی: تمام تغییرات اعمال شده بر کاربران و لاگین‌ها را مستند کنید تا در آینده برای عیب‌یابی و مراجعات مفید باشد.
• بررسی دوره‌ای: به طور منظم دیتابیس‌های خود را برای شناسایی و رفع به موقع OrphanedUser بررسی کنید.

نتیجه‌گیری

OrphanedUser یک مشکل رایج اما قابل حل در SQL Server هستند که می‌توانند دسترسی کاربران را مختل کرده و چالش‌های امنیتی ایجاد کنند. با درک علل و استفاده از روش‌های صحیح شناسایی و رفع که در این مقاله توضیح داده شد، می‌توانید امنیت و کارایی دیتابیس‌های SQL Server خود را تضمین کنید. انتخاب روش مناسب (نگاشت به لاگین موجود، ایجاد لاگین جدید یا حذف کاربر) بستگی به سناریوی خاص شما دارد. اجرای منظم این فرآیندها به حفظ سلامت و امنیت محیط SQL Server شما کمک شایانی خواهد کرد.