Оффлайн
- Регистрация
- 1 Мар 2015
- Сообщения
- 1,481
- Баллы
- 155
When working with MySQL, it's good practice to avoid using the root account for applications or development work. Instead, create a dedicated user with the necessary permissions. This improves security, allows better access control, and keeps your setup clean.
In this article, you'll learn how to:
Open your terminal and log into MySQL as the root user:
mysql -u root -p
You’ll be prompted to enter the root password.
Step 2: Create a New User
Run the following command inside the MySQL prompt:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'StrongPassword123';
To allow the new user to perform any action on all databases:
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';
If you want this user to be able to grant permissions to others, include:
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost' WITH GRANT OPTION;
Step 4: Apply the Privileges
Always run this after changing privileges to ensure they're applied:
FLUSH PRIVILEGES;
Step 5: Test the New User
Exit MySQL:
exit
Then test the new user login:
mysql -u newuser -p
If successful, you've set up the user correctly.
Understanding WITH GRANT OPTION
The WITH GRANT OPTION clause allows the user to grant their own privileges to other users. It's useful for admin-level users but should be used with caution.
Example:
If you give alice select access to a database with grant option, she can also grant that select access to others.
GRANT SELECT ON mydb.* TO 'alice'@'localhost' WITH GRANT OPTION;
Then alice can do:
GRANT SELECT ON mydb.* TO 'bob'@'localhost';
Note: For typical application users or developers, you don’t need WITH GRANT OPTION.
Conclusion
Creating and managing MySQL users properly is crucial for maintaining secure and organized systems. By following the steps above, you can create a new user, grant the necessary permissions, and optionally enable that user to manage others.
In this article, you'll learn how to:
- Create a new MySQL user
- Grant all privileges to the user
- Understand what WITH GRANT OPTION means
- Test the new user login
Open your terminal and log into MySQL as the root user:
mysql -u root -p
You’ll be prompted to enter the root password.
Step 2: Create a New User
Run the following command inside the MySQL prompt:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'StrongPassword123';
- Replace newuser with your desired username.
- Replace 'localhost' with '%' if you want the user to connect remotely.
- Replace 'StrongPassword123' with a strong password.
To allow the new user to perform any action on all databases:
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';
If you want this user to be able to grant permissions to others, include:
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost' WITH GRANT OPTION;
Step 4: Apply the Privileges
Always run this after changing privileges to ensure they're applied:
FLUSH PRIVILEGES;
Step 5: Test the New User
Exit MySQL:
exit
Then test the new user login:
mysql -u newuser -p
If successful, you've set up the user correctly.
Understanding WITH GRANT OPTION
The WITH GRANT OPTION clause allows the user to grant their own privileges to other users. It's useful for admin-level users but should be used with caution.
Example:
If you give alice select access to a database with grant option, she can also grant that select access to others.
GRANT SELECT ON mydb.* TO 'alice'@'localhost' WITH GRANT OPTION;
Then alice can do:
GRANT SELECT ON mydb.* TO 'bob'@'localhost';
Note: For typical application users or developers, you don’t need WITH GRANT OPTION.
Conclusion
Creating and managing MySQL users properly is crucial for maintaining secure and organized systems. By following the steps above, you can create a new user, grant the necessary permissions, and optionally enable that user to manage others.