Ways to create a new user and assign permissions in MySQL

Tram Ho

Introduce

MySQL is an open source database management system that helps users store, organize, and then retrieve data. It has a variety of options available to the user to manipulate tables and databases. This article guides user creation and authorization in MySQL.

As soon as you start using the MySQL database, you will be provided with a username and password. These login credentials will give you “root access”. A root user has full access to the databases and tables in the databases. But over time or manipulating database production, for example, you will need to grant the database access to a user without giving them full control.

How to create a new user in MySQL?

To create a new user, follow the steps below:

  1. Access to mysql
  2. Execute the command to create the user:

newuser: the user name

password: the user’s password

At this point, the new user does not have permission to do anything with the database. In fact, even if the new user tries to login, they will not be able to access the MySQL shell.

Therefore, the first thing to do is to give the user access to the information they need

  1. Assign permissions to users

The * above corresponds to the database and table that the user can access – in particular, this command allows the user to add, edit, and delete work on all tables in the database.

  1. For changes to be made immediately, use the following command:

Thus, with the steps above, your newly created user has full authority as the root user

Several permissions you can assign to users in MySQL

Below is a list of commonly used commands to assign authority to a user:

  • ALL PRIVILEGES – Allows MySQL user to have full authority on databases (or some set up db)
  • CREATE – Allows a user to create new tables or databases
  • DROP – Allows you to delete tables or databases
  • DELETE – Allows deletion of data records in the tables table
  • INSERT – Allows adding new records to the database table
  • SELECT – Allows you to use the Select command to search for data
  • UPDATE – Allows updating csdl
  • GRANT OPTION – Allows you to assign or delete the rights of other users.

MySQL allows to assign permissions to users with a simple command line:

If you want to allow users to access all databases or all tables, use an * instead of the database name or table.

Every time you update or change permissions, use the Flush Privileges command to ensure the changes take effect.

To apply multiple types of permissions, separate them with commas. For example, you can assign CREATE and SELECT permissions to a user:

If you need to revoke user rights, use the command REVOKE:

For example, to revoke all permissions for a user:

Or you can also delete the user:

You can check that the newly created user is successful and able to log in, first perform a logout by:

and login using the command

NOTE: Remember, to exercise these permissions you need root privileges. Also, be sure to execute FLUSH PRIVILEGES after making any permission changes.

How to display permissions of a user in MySQL?

To know what permissions you have to assign to a MySQL, you can use the SHOW GRANTS command:

Refer

https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql

Share the news now

Source : Viblo