نحوه ساخت کاربر MySQL و اعطای دسترسی‌ها (گام‌به‌گام)

مقدمه

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

نکات کلیدی

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

پیش‌نیازها

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

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

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

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

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

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

در سیستم‌های اوبونتویی اجراکننده 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 تعیین کنید. قرار دادن نام کاربری و هاست در کوتیشن تکی همیشه لازم نیست؛ اما انجامش می‌تواند به جلوگیری از خطاها کمک کند.

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

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

پلاگین 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 اجرا کنید تا جدول‌های مجوز ریلود شوند و تضمین شود مجوزهای جدید اثر می‌گذارند:

FLUSH PRIVILEGES;

اما طبق مستندات رسمی MySQL، وقتی جدول‌های مجوز را به‌طور غیرمستقیم با دستور مدیریت-حسابی مثل GRANT تغییر می‌دهید، دیتابیس جدول‌های مجوز را فوراً در حافظه ریلود می‌کند؛ یعنی دستور 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 وصل شود. با تأیید اعتبارنامه‌ها و اعطای دسترسی‌های لازم رفعش کنید.

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

اعتبارنامه‌های کاربر را تأیید کنید: مطمئن شوید نام کاربری، رمز عبور و هاست درست‌اند. دوباره چک کنید که نام کاربری و رمز عبور درست spell شده‌اند و هاست روی مقدار درست تنظیم شده (مثلاً ‘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 وقتی رخ می‌دهد که سعی کنید کاربری بسازید که از قبل وجود دارد. چک کنید کاربر موجود است یا نه؛ سپس یا حساب موجود را تغییر دهید یا قبل از ساخت جدیدش حذفش کنید.

برای چک کردن موجود بودن کاربر، از دستور زیر استفاده کنید:

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

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

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';

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

نتیجه‌گیری

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