How To Create a New User and Grant Permissions in MySQL

Have you seen our latest unmetered dedicated servers offer?

Check it now: https://zetservers.com/streaming-servers.php


How to Create a New User

Let’s make a new user with the MySQL shell:

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

 

The newly created user doesn’t have any rights. In order to provide the user with access they will need.

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

Please note that in this example we are granting newuser full root access to everything in our database. While this is helpful for explaining some MySQL concepts, it may be impractical for most use cases and could put your database’s security at high risk.

Reload privileges:

FLUSH PRIVILEGES;

Your changes will now be applied.

How To Grant Different User Permissions

Here is a short list of other common possible permissions that users can enjoy.

  • ALL PRIVILEGES- as we saw previously, this would allow a MySQL user full access to a designated database (or if no database is selected, global access across the system)
  • CREATE- allows them to create new tables or databases
  • DROP- allows them to them to delete tables or databases
  • DELETE- allows them to delete rows from tables
  • INSERT- allows them to insert rows into tables
  • SELECT- allows them to use the SELECT command to read through databases
  • UPDATE- allow them to update table rows
  • GRANT OPTION- allows them to grant or remove other users’ privileges

To provide a specific user with a permission, you can use:

GRANT type_of_permission ON database_name.table_name TO 'username'@'localhost';

If you want to give them access to any database or to any table, make sure to put an asterisk (*) in the place of the database name or table name.

Each time you update or change a permission use the Flush Privileges command to apply changes.

If you need to revoke a permission, the structure is almost identical to granting it:

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

Let’s try to login with the new user:

mysql -u [username] -p

Leave a Reply

Your email address will not be published. Required fields are marked *