Step-by-Step Guide to Create MySQL User

After installing MySQL, the root users can access the full database installation. Due to security reasons, the administrator must develop particular user accounts to help non-root users manage specific data sets. This article helps MySQL database permissions. It also highlights the creation of new user and grant or revoke permissions.

Steps to create a new MySQL User

1. Log into the MySQL console:

• Open your terminal or command prompt.
• Type mysql and press Enter.
• You may be prompted for a password. Enter the password for the root user.

2. Create the user:

• Use the following command, replacing username with your desired username and password with a strong password:

SQL:

CREATE USER ‘username’@’localhost’ IDENTIFIED BY ‘password’;

• The @’localhost’ part specifies that the user can only connect from the local machine. If you want to allow connections from remote hosts, replace ‘localhost’ with the IP address or hostname of the remote host.

3. Grant privileges (optional):

•  In case of giving special privileges to the user, opt for GRANT command. For example, allowing all privileges on a database is called mydatabase.

SQL:

GRANT ALL PRIVILEGES ON mydatabase. * TO ‘username’@’localhost’;

• To grant specific privileges, replace ALL with the desired privileges (e.g., SELECT, INSERT, UPDATE, DELETE, etc.).

4. Flush privileges:

• To get changes done on the spot execute the below command:

SQL:

FLUSH PRIVILEGES;

Example:

To create a user named myuser with the password mypassword and grant them all privileges on the mydatabase database, you would use the following commands:

SQL:

CREATE USER ‘myuser’@’localhost’ IDENTIFIED BY ‘mypassword’;

GRANT ALL PRIVILEGES ON mydatabase.* TO ‘myuser’@’localhost’;

FLUSH PRIVILEGES;

Steps to Grant Permissions in MySQL

Granting Permissions in MySQL

You’ll use the GRANT command to grant permissions to a MySQL user. Here’s the basic syntax:

SQL:

GRANT privilege_list ON database_name.table_name TO ‘username’@’host’;

Breakdown:

• privilege_list: A comma-separated list of privileges to grant. Common privileges include:
  • SELECT: Read data from the table.
  • INSERT: Insert new rows into the table.
  • UPDATE: Modify existing rows in the table.
  • DELETE: Delete rows from the table.
  • CREATE: Create new tables and databases.
  • DROP: Delete tables and databases.
  • ALL: Grant all privileges.
• database_name.table_name: Specifies the database and table to which the privileges apply. In case of giving privileges to all tables in a database, use database_name.*.
• ‘username’@’host’: Specifies the user and host from which the user can connect.

Steps to Grant All Privileges

To give all privileges to all users on a particular database, use below mentioned SQL command:

SQL:

GRANT ALL PRIVILEGES ON database_name.* TO ‘username’@’host’;

Breakdown:

• GRANT ALL PRIVILEGES: This allows all possible permissions to the user.
• ON database_name.*: This tells database about which privileges user has to use.  The * wildcard means all tables within that database.
• TO ‘username’@’host’: This identifies the user and the host from which they can connect.

Example:

To grant all privileges on the my_database database to a user named my_user who can connect from localhost, you would use:

SQL:

GRANT ALL PRIVILEGES ON my_database.* TO ‘my_user’@’localhost’;

Grant Delete Privileges in MySQL

For deleting the privileges to a MySQL user on a particular database or table, you can opt for below mentioned SQL command

SQL:

GRANT DELETE ON database_name.table_name TO ‘username’@’host’;

Breakdown:

• GRANT DELETE: This command allows the delete privilege.
• ON database_name.table_name: This specifies the database and table to which the privilege applies.
  • You are allowed to use database_name.* to approve the privilege to all tables within a specific database.
• TO ‘username’@’host’: This identifies the user and the host from which they can connect.

Steps to Revoke Privileges in MySQL

You can revoke privileges from a MySQL user with the help of the REVOKE command. Here’s the basic syntax:

SQL: REVOKE privilege_list ON database_name.table_name FROM ‘username’@’host’;

Breakdown:

• REVOKE: This keyword initiates the privilege revocation.
• privilege_list: You get a comma-separated list of privileges to revoke. Common privileges include:
  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • CREATE
  • DROP
  • ALL (to revoke all privileges)

• database_name.table_name: Particular database and table from which privileges are being revoked. Users can use database_name. * to revoke privileges on all tables within a database.
• ‘username’@’host’: Identifies the user and host from which the user can connect.

Final Verdict

I hope this article has guided you through the proper solutions and steps for creating users and assigning various types of permissions in a MySQL database.