MySQL is a handy, open-source database management software that you can use to store, organize, and retrieve server data. It’s one of the most common tools used in pricey & cheap VPS hosting alike as it makes running websites an easy & simple experience and is compatible with both Windows and Linux OS. When you first open it after installation, you are given a username, password, and root access to the databases and tables. It’s fine if you’re the only one working on the server, but sometimes extra people might need to access it, too. And then you’ll have to create new accounts with limited privileges so that they’re not able to delete or modify anything important. Here’s how to do just that.
How to create a MySQL user
In general, it’s good practice not to give full control of the application to non-root users, even if they’re the most trusted people. The smallest human error might have some severe consequences if they accidentally change something that must strictly stay the same.
Follow these steps to create another MySQL account on your cheap VPS hosting server:
1. Open up the command line and access your MySQL server.
2. Once that is done, execute this command: CREATE USER ‘new_user’@’localhost’ IDENTIFIED BY ‘password’;3. “new_user” is the account name, and “password” is, well, the password for it. Enter whatever you like in these two fields. Take note that “localhost” means “this computer” and nor the server’s IP address.
At this point, the new account has no permissions and can’t even log into the database. Grant them the privilege to access the information they need with this command:
mysql> GRANT ALL PRIVILEGES ON * . * TO ‘newuser’@’localhost’;
The asterisks refer to the MySQL database and table. Now the new user can read, execute, edit, and perform all tasks on the application. Take note that this command grants them full root access, which is highly impractical in most cases and can put your cheap VPS hosting server security at risk. We will be looking into how to grant select permissions shortly.
Meanwhile, after you finalize all the permissions, always reload the privileges with this command for them to take immediate effect:
mysql> FLUSH PRIVILEGES;
How to grant permissions
If there is no need for another MySQL account with full root access to the system, you can grant them separate privileges with this command:
mysql> GRANT type_of_permission ON database_name.table_name TO ‘username’@’localhost’;
If you want the account to be able to access any database or any table, just put an asterisk (*) instead of the database or table name in the command. If you want to grant them specific permissions, just replace “type_of_permission” with the appropriate name. Some of the most common privileges are:
· CREATE – allows users to create new databases and tables
· DROP – allows users to delete databases and tables
· DELETE – allows users to delete rows from tables
· INSERT – allows users to insert rows into tables
· SELECT – allows users to use the SELECT command to read databases
· UPDATE – allows users to update table rows
· GRANT OPTION – allows users to grant or remove other accounts’ permissions
There might be a time when you might have to revoke the given privileges. Either they made a crucial mistake, or the account will be used by someone new on your cheap VPS hosting, and you just don’t see the need to create a new one. Enter the following command to do so:
REVOKE PERMISSION_TYPE ON database_name.table_name FROM ‘user_name’@‘localhost’;
If you want to revoke all privileges, enter this command:
REVOKE ALL PRIVILEGES ON * . * FROM ‘user_name’@’localhost’;
To delete an existing MySQL account, use this command:
DROP USER ‘user_name’@‘localhost’;
To check what kind of privileges an account already has, type in this command:
SHOW GRANTS FOR ‘user_name’@’localhost’;
Keep in mind that you have to have full root access to run any of the above-listed commands. Always remember to execute the FLUSH PRIVILEGES command for any of the changes to take effect.
As you can see, creating a new MySQL account and granting it privileges isn’t so hard. Use this knowledge wisely to create non-root users of the better safety of your cheap VPS hosting server. And if you don’t have MySQL yet, try it out. It’s fairly easy to use and very reliable, making database management efficient.