آموزش ساخت کاربر در MySQL و اعطای دسترسی‌ها (گام‌به‌گام)

مقدمه

MySQL یک سیستم مدیریت پایگاه داده رابطه‌ای متن‌باز است که معمولاً به‌عنوان بخشی از استک LAMP (مخفف لینوکس، ‏Apache، ‏MySQL و PHP) مستقر می‌شود و در زمان نگارش این مقاله، محبوب‌ترین پایگاه داده متن‌باز جهان است.

نکات کلیدی

• کاربران را با CREATE USER بسازید: از CREATE USER 'username'@'host' IDENTIFIED BY 'password' برای افزودن کاربران جدید پایگاه داده استفاده کنید؛ با تعیین نام میزبان (مثل localhost یا % برای هر میزبانی) کنترل کنید کاربران از کجا می‌توانند متصل شوند.
• دسترسی‌ها را با GRANT بدهید: دسترسی‌های مشخص را با GRANT privilege ON database.table TO 'username'@'host' تخصیص بدهید و طبق اصل حداقل دسترسی (Least Privilege)، فقط دسترسی‌هایی را بدهید که هر کاربر لازم دارد.
• پلاگین احراز هویت درست را انتخاب کنید: از caching_sha2_password (پیش‌فرض MySQL 8.0) برای احراز هویت امن با رمز عبور، از mysql_native_password برای سازگاری با PHP، یا از auth_socket برای دسترسی فقط محلی بدون رمز عبور استفاده کنید.
• کاربران را به‌طور ایمن مدیریت کنید: از SHOW GRANTS برای بازبینی دسترسی‌ها، از REVOKE برای حذف دسترسی‌ها و از DROP USER برای حذف حساب‌ها استفاده کنید. در محیط production همیشه قبل از حذف کاربران، دسترسی‌هایشان را REVOKE کنید.
• خطاهای رایج را عیب‌یابی کنید: خطاهای «Access denied» را با تأیید اطلاعات احراز هویت و دسترسی‌ها رفع کنید؛ اتصالات از راه دور را با تنظیم host روی % فعال کنید؛ و خطای 1396 (وجود کاربر) را با بررسی و حذف اولیه حساب‌های موجود رفع نمایید.

پیش‌نیازها

برای دنبال‌کردن این راهنما، به دسترسی به یک پایگاه داده MySQL نیاز دارید. این راهنما فرض می‌کند که این پایگاه داده روی یک سرور خصوصی مجازی (VPS) در حال اجرای اوبونتو نصب شده؛ با این حال، اصولی که تشریح می‌کند فارغ از اینکه چطور به پایگاه داده‌تان دسترسی دارید، قابل اعمال است.

اگر به پایگاه داده MySQL دسترسی ندارید و دوست دارید خودتان یکی راه‌اندازی کنید، می‌توانید یکی از راهنماهای «نصب MySQL» ما را دنبال کنید. باز هم، فارغ از سیستم‌عامل زیربنایی سرورتان، روش‌های ساخت کاربر جدید MySQL و اعطای دسترسی به او عموماً یکسان خواهند بود.

به‌طور جایگزین، می‌توانید یک پایگاه داده MySQL مدیریت‌شده توسط یک ارائه‌دهنده ابری راه‌اندازی کنید. برای جزئیات نحوه راه‌اندازی یک پایگاه داده مدیریت‌شده پارمین کلود، مستندات محصول ما را ببینید.

لطفاً توجه کنید که هر بخشی از دستورات نمونه که لازم است تغییر یا سفارشی کنید، در سراسر این راهنما به‌صورت Placeholder (مثل username و host و password و…) نمایش داده می‌شود.

ساخت یک کاربر جدید

پس از نصب، MySQL یک حساب کاربری root می‌سازد که می‌توانید از آن برای مدیریت پایگاه داده‌تان استفاده کنید. این کاربر دسترسی‌های کامل روی سرور MySQL دارد؛ یعنی کنترل کامل روی هر پایگاه داده، جدول، کاربر و غیره. به همین دلیل، بهتر است از این حساب خارج از وظایف مدیریتی استفاده نشود. این مرحله نحوه استفاده از کاربر root پایگاه MySQL برای ساخت یک حساب کاربری جدید و اعطای دسترسی به آن را تشریح می‌کند.

در سیستم‌های اوبونتویی که MySQL نسخه 5.7 (و نسخه‌های جدیدتر) را اجرا می‌کنند، کاربر root پایگاه MySQL به‌صورت پیش‌فرض برای احراز هویت با پلاگین auth_socket تنظیم شده؛ نه با رمز عبور. این پلاگین الزام می‌کند که نام کاربر سیستم‌عاملیِ فراخوان‌کننده کلاینت MySQL با نام کاربر MySQLِ تعیین‌شده در دستور مطابقت داشته باشد. این یعنی باید دستور mysql را با sudo اجرا کنید تا با دسترسی‌های کاربر root اوبونتو فراخوانی شده و به کاربر root پایگاه MySQL دسترسی پیدا کنید:

sudo mysql

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

mysql -u root -p

وقتی به خط فرمان MySQL دسترسی پیدا کردید، می‌توانید با دستور CREATE USER کاربر جدیدی بسازید. سینتکس عمومی این است:

CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';

بعد از CREATE USER، یک نام کاربری تعیین می‌کنید. بلافاصله بعد از آن، یک علامت @ و سپس نام میزبانی که این کاربر از آن متصل خواهد شد قرار می‌گیرد. اگر فقط قصد دارید از این کاربر به‌صورت محلی از سرور اوبونتوی خود استفاده کنید، می‌توانید localhost را تعیین کنید. قرار دادن نام کاربری و host در گیومه‌های تکی همیشه لازم نیست؛ اما انجام این کار می‌تواند به جلوگیری از خطاها کمک کند.

برای انتخاب پلاگین احراز هویت کاربرتان، چند گزینه دارید. جدول زیر روش‌های اصلی احراز هویت را مقایسه می‌کند:

پلاگین احراز هویت	امنیت	دسترسی از راه دور	مورد استفاده	سازگاری
caching_sha2_password	قوی (‏SHA-256)	بله	پیش‌فرض MySQL 8.0 به بالا؛ پیشنهادی برای اپلیکیشن‌های جدید	بیشتر کلاینت‌های مدرن
mysql_native_password	خوب (‏SHA-1)	بله	سازگاری با سیستم‌های قدیمی، اپلیکیشن‌های PHP (‏phpMyAdmin)	کلاینت‌های قدیمی‌تر، ‏PHP < 7.4
auth_socket	قوی (مبتنی بر سیستم‌عامل)	خیر	دسترسی فقط محلی، بدون نیاز به رمز عبور	فقط سیستم‌های اوبونتو/دبیان

پلاگین auth_socketای که قبلاً اشاره شد می‌تواند راحت باشد؛ چون امنیت قوی بدون نیاز به وارد کردن رمز عبور برای دسترسی کاربران معتبر به پایگاه داده فراهم می‌کند. اما همچنین از اتصالات از راه دور جلوگیری می‌کند؛ که وقتی برنامه‌های خارجی نیاز به تعامل با MySQL داشته باشند، کارها را پیچیده می‌کند.

به‌عنوان جایگزین، می‌توانید بخش WITH authentication_plugin از سینتکس را کاملاً حذف کنید تا کاربر با پلاگین پیش‌فرض MySQL یعنی caching_sha2_password احراز هویت شود. مستندات MySQL این پلاگین را برای کاربرانی که می‌خواهند با رمز عبور وارد شوند — به دلیل قابلیت‌های امنیتی قوی‌اش — توصیه می‌کند.

دستور زیر را برای ساخت کاربری که با caching_sha2_password احراز هویت می‌شود اجرا کنید. حتماً sammy را به نام کاربری دلخواه و password را به یک رمز عبور قوی به انتخاب خودتان تغییر دهید:

CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';

توجه: یک مشکل شناخته‌شده با برخی نسخه‌های PHP وجود دارد که با caching_sha2_password مشکل ایجاد می‌کند. اگر قصد دارید از این پایگاه داده با یک اپلیکیشن PHP استفاده کنید — مثلاً phpMyAdmin — ممکن است بخواهید در عوض کاربری بسازید که با پلاگین قدیمی‌تر اما همچنان امنِ mysql_native_password احراز هویت شود:

CREATE USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

اگر مطمئن نیستید، همیشه می‌توانید کاربری بسازید که با caching_sha2_password احراز هویت شود و بعداً با این دستور آن را ALTER کنید:

ALTER USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

پس از ساخت کاربر جدید خود، می‌توانید دسترسی‌های مناسب را به او بدهید.

اعطای دسترسی‌ها به کاربر

سینتکس عمومی برای اعطای دسترسی‌های کاربر به این شکل است:

GRANT PRIVILEGE ON database.table TO 'username'@'host';

مقدار PRIVILEGE در این سینتکسِ نمونه، تعیین می‌کند کاربر مجاز است چه اقداماتی روی پایگاه داده و جدول مشخص‌شده انجام بدهد. می‌توانید چند دسترسی را با جدا کردن هر کدام با کاما، در یک دستور به همان کاربر بدهید. همچنین می‌توانید با وارد کردن ستاره‌ها (*) به‌جای نام پایگاه داده و جدول، به‌طور سراسری به کاربر دسترسی بدهید. در SQL، ستاره‌ها کاراکترهای خاصی هستند که برای نمایش «همه» پایگاه‌های داده یا جدول‌ها استفاده می‌شوند.

برای توضیح، دستور زیر به کاربری دسترسی‌های سراسری برای CREATE و ALTER و DROP کردنِ پایگاه‌های داده، جدول‌ها و کاربران — و همچنین توانایی INSERT و UPDATE و DELETE کردن داده از هر جدولی روی سرور — می‌دهد. این دستور همچنین به کاربر توانایی کوئری گرفتن داده با SELECT، ساخت کلیدهای خارجی با کلیدواژه REFERENCES و انجام عملیات‌های FLUSH با دسترسی RELOAD را هم می‌دهد. با این حال، باید فقط دسترسی‌هایی را به کاربران بدهید که لازم دارند؛ پس دسترسی‌های کاربر خودتان را آزادانه در صورت نیاز تنظیم کنید.

فهرست کامل دسترسی‌های موجود را می‌توانید در مستندات رسمی MySQL پیدا کنید.

این دستور GRANT را — با جایگزینی sammy با نام کاربر MySQL خودتان — اجرا کنید تا این دسترسی‌ها را به کاربرتان بدهید:

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

توجه کنید که این دستور همچنین شامل WITH GRANT OPTION است. این گزینه به کاربر MySQL شما اجازه می‌دهد هر دسترسی‌ای که دارد را به کاربران دیگر روی سیستم بدهد.

هشدار: برخی کاربران ممکن است بخواهند به کاربر MySQL خودشان دسترسی ALL PRIVILEGES را بدهند؛ که دسترسی‌های گسترده Superuserای مشابه دسترسی‌های کاربر root فراهم می‌کند؛ به این شکل:

GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

چنین دسترسی‌های گسترده‌ای نباید به‌سادگی اعطا شوند؛ چون هر کسی که به این کاربر MySQL دسترسی داشته باشد، کنترل کامل روی هر پایگاه داده‌ای روی سرور خواهد داشت.

بسیاری از راهنماها اجرای دستور FLUSH PRIVILEGES را بلافاصله بعد از یک دستور CREATE USER یا GRANT — برای Reload کردن جدول‌های Grant و اطمینان از اعمال شدنِ دسترسی‌های جدید — پیشنهاد می‌کنند:

FLUSH PRIVILEGES;

با این حال، طبق مستندات رسمی MySQL، وقتی جدول‌های Grant را به‌طور غیرمستقیم با یک دستور مدیریت حساب مانند GRANT تغییر می‌دهید، پایگاه داده جدول‌های Grant را فوراً در حافظه Reload می‌کند؛ یعنی دستور FLUSH PRIVILEGES در مورد ما لازم نیست. از طرف دیگر، اجرای آن هیچ اثر منفی‌ای روی سیستم نخواهد داشت.

اگر لازم بود دسترسی‌ای را REVOKE کنید، ساختار تقریباً یکسان با اعطای آن است:

REVOKE type_of_permission ON database_name.table_name FROM 'username'@'host';

توجه کنید که هنگام REVOKE کردن دسترسی‌ها، سینتکس ایجاب می‌کند از FROM استفاده کنید؛ به‌جای TO که هنگام اعطای دسترسی‌ها استفاده کردید.

می‌توانید دسترسی‌های فعلی یک کاربر را با اجرای دستور SHOW GRANTS بازبینی کنید:

SHOW GRANTS FOR 'username'@'host';

همان‌طور که می‌توانید پایگاه‌های داده را با DROP حذف کنید، می‌توانید از DROP برای حذف کاربر هم استفاده کنید:

DROP USER 'username'@'localhost';

پس از ساخت کاربر MySQL و اعطای دسترسی‌ها به او، می‌توانید از کلاینت MySQL خارج شوید:

exit

در آینده، برای ورود به‌عنوان کاربر جدید MySQL خود، از دستوری مثل زیر استفاده می‌کنید:

mysql -u sammy -p

فلگ p- باعث می‌شود کلاینت MySQL از شما رمز عبور کاربر MySQL را برای احراز هویت بخواهد.

حذف یک کاربر MySQL

برای حذف یک کاربر MySQL، می‌توانید از دستور DROP USER استفاده کنید. سینتکس این دستور به این شکل است:

DROP USER 'username'@'host';

به‌جای username، نام کاربری واقعی‌ای که می‌خواهید حذف کنید را قرار دهید؛ و به‌جای host، نام میزبان یا آدرس IPای که کاربر می‌تواند از آن متصل شود. مثلاً، برای حذف کاربری با نام sammy که می‌تواند از localhost متصل شود، از این دستور استفاده می‌کنید:

DROP USER 'sammy'@'localhost';

پس از اجرای این دستور، کاربر مشخص‌شده از سرور MySQL حذف خواهد شد. توجه کنید که این اقدام برگشت‌ناپذیر است؛ پس حتماً با احتیاط و فقط وقتی از حذف کاربر مطمئن هستید استفاده کنید.

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

خطاهای رایج و عیب‌یابی

۱. خطای Access denied for user

خطای Access denied for user وقتی رخ می‌دهد که کاربری با اطلاعات احراز هویت نادرست یا دسترسی‌های ناکافی سعی در اتصال به MySQL داشته باشد. آن را با تأیید اطلاعات احراز هویت و اعطای دسترسی‌های لازم رفع کنید.

برای رفع این خطا، این مراحل را دنبال کنید:

• تأیید اطلاعات احراز هویت کاربر: مطمئن شوید نام کاربری، رمز عبور و host درست هستند. دوباره بررسی کنید که نام کاربری و رمز عبور به‌درستی تایپ شده‌اند و host روی مقدار درست تنظیم شده (مثلاً localhost یا % یا یک آدرس IP مشخص).
• بررسی دسترسی‌ها: مطمئن شوید به کاربر دسترسی‌های لازم برای دسترسی به پایگاه داده اعطا شده است. می‌توانید این کار را با اجرای دستور SHOW GRANTS برای بازبینی دسترسی‌های فعلی کاربر انجام دهید:

SHOW GRANTS FOR 'username'@'localhost';

این دستور دسترسی‌های فعلیِ اعطاشده به کاربر را نمایش می‌دهد. اگر کاربر دسترسی‌های لازم را ندارد، می‌توانید با دستور GRANT به او بدهید.

• دسترسی به پایگاه داده یا جدول مشخص: اگر کاربر در حال تلاش برای دسترسی به یک پایگاه داده یا جدول مشخص است، مطمئن شوید به کاربر روی همان پایگاه داده یا جدول خاص دسترسی اعطا شده. مثلاً، برای اعطای دسترسی روی یک پایگاه داده مشخص:

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

یا برای اعطای دسترسی روی یک جدول مشخص:

GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.table_name TO 'username'@'localhost';

این مراحل خطای Access denied for user را رفع کرده و تضمین می‌کنند کاربر دسترسی لازم به پایگاه داده MySQL را داشته باشد.

۲. ناتوانی کاربر در اتصال از راه دور

اتصالات از راه دور را با اعطای دسترسی‌ها همراه با نام میزبانِ تنظیم‌شده روی % (هر میزبانی) یا یک آدرس IP مشخص فعال کنید. مثلاً:

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%';

این دستور همه دسترسی‌ها را به کاربر username از هر میزبانی (‏%) می‌دهد.

۳. خطای 1396: شکست عملیات CREATE USER

خطای 1396 وقتی رخ می‌دهد که سعی کنید کاربری بسازید که از قبل وجود دارد. بررسی کنید کاربر وجود دارد یا نه؛ سپس یا حساب موجود را تغییر بدهید یا قبل از ساخت حساب جدید آن را Drop کنید.

برای بررسی اینکه آیا کاربری از قبل وجود دارد، از این دستور استفاده کنید:

SELECT * FROM mysql.user WHERE User = 'newuser';

اگر کاربر وجود دارد، حساب کاربری موجود را Drop کنید:

DROP USER 'newuser'@'%';

سپس حساب کاربری جدید را بسازید:

CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';

سوالات متداول

۱. چطور یک کاربر MySQL با دسترسی‌های محدود بسازم؟

یک کاربر MySQL با دسترسی‌های محدود، با تعیین فقط دسترسی‌های موردنیاز بسازید. مثلاً، برای اعطای دسترسی‌های SELECT و INSERT و UPDATE و DELETE روی یک پایگاه داده مشخص:

GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'username'@'localhost';

این رویکرد تضمین می‌کند کاربر فقط بتواند اقدامات تعیین‌شده را روی پایگاه داده مشخص انجام بدهد و دسترسی‌هایش محدود بماند.

۲. چطور دسترسی‌های کاربر MySQL را بررسی کنم؟

دسترسی‌های کاربر MySQL را با دستور SHOW GRANTS بررسی کنید. سینتکس آن این است:

SHOW GRANTS FOR 'username'@'localhost';

این دستور همه دسترسی‌های اعطاشده به کاربر مشخص‌شده را نمایش می‌دهد.

۳. تفاوت GRANT ALL PRIVILEGES و دسترسی‌های مشخص چیست؟

دستور GRANT ALL PRIVILEGES به کاربر، همه دسترسی‌های موجود روی یک پایگاه داده یا جدول را می‌دهد؛ درحالی‌که دسترسی‌های مشخص، دسترسی کاربر را فقط به اقدامات تعیین‌شده محدود می‌کنند. اعطای همه دسترسی‌ها می‌تواند یک ریسک امنیتی باشد؛ چون کنترل کامل روی پایگاه داده یا جدول را به کاربر می‌دهد. از طرف دیگر، اعطای دسترسی‌های مشخص تضمین می‌کند کاربر فقط اقداماتِ ضروری برای نقشش را بتواند انجام بدهد و ریسک دسترسی یا تغییرات غیرمجاز را کاهش می‌دهد.

۴. چطور دسترسی از راه دور را برای یک کاربر MySQL مجاز کنم؟

دسترسی از راه دور را با اعطای دسترسی‌ها همراه با نام میزبانِ تنظیم‌شده روی % (هر میزبانی) مجاز کنید. مثلاً:

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%';

این دستور همه دسترسی‌ها را به کاربر از هر میزبانی می‌دهد و دسترسی از راه دور را ممکن می‌سازد.

۵. چطور یک کاربر MySQL را به‌طور ایمن حذف کنم؟

یک کاربر MySQL را با REVOKE کردن اولِ همه دسترسی‌ها و سپس Drop کردن حساب کاربری، به‌طور ایمن حذف کنید:

REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'localhost';
DROP USER 'username'@'localhost';

این روی_approach تضمین می‌کند حساب کاربری بدون به جا گذاشتن هیچ دسترسی باقی‌مانده‌ای که قابل سوءاستفاده باشد، به‌طور ایمن حذف شود.

جمع‌بندی

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