How to Create And Manage Users In MySQL?

To create and manage users in MySQL, you need to have administrative privileges or the "CREATE USER" privilege.

To create a new user, you can use the "CREATE USER" statement followed by the username and password. Here is an example:

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

You can replace 'username' with the desired username for the user and 'password' with the desired password.

To assign specific privileges to the user, you can use the "GRANT" statement. For example, to grant all privileges on a specific database to the user, you can use the following command:

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

Replace 'database_name' with the actual name of the database you want to grant access to. The keyword "ALL PRIVILEGES" grants all available privileges, but you can also specify specific privileges such as "SELECT," "UPDATE," or "DELETE."

If you want to remove privileges from a user, you can use the "REVOKE" statement. For example:

REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'localhost';

To delete a user completely, you can use the "DROP USER" statement followed by the username:

DROP USER 'username'@'localhost';

Remember to substitute 'username' with the actual username you wish to delete.

Lastly, you can view all existing users within MySQL using the following command:

SELECT User, Host FROM mysql.user;

This will show you a list of active users and their corresponding hostnames.

Best MySQL Managed Hosting Providers in 2024

1

Rating is 5 out of 5

Vultr

• Ultra-fast Intel Core
• High Performance and Cheap Cloud Dedicated Servers
• 1 click install Wordpress
• Low Price and High Quality

Try It Now

2

Rating is 4.9 out of 5

Digital Ocean

• Active Digital Community
• Simple Control Panel
• Starting from 5$ per month

Try It Now

How to revoke privileges from a user in MySQL?

To revoke privileges from a user in MySQL, you can use the REVOKE statement. Here is the syntax to revoke privileges from a user:

1
2
3

REVOKE privilege_type [, privilege_type]...
ON database_name.table_name
FROM user_name [IDENTIFIED BY 'password']

Here's what each component means:

• privilege_type: The privilege or privileges you want to revoke. Some common privileges include SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, and GRANT OPTION.
• database_name.table_name: The specific database and table you want to revoke privileges from. If you want to revoke privileges from all databases, you can use *.*.
• user_name: The name of the user you want to revoke privileges from.
• IDENTIFIED BY 'password': Optional. If the user has a specific password set, you may need to include this clause and specify the password.

Example:

Let's say you want to revoke the SELECT and INSERT privileges from the user "testuser" on the "mydatabase" database. The syntax would be:

1

REVOKE SELECT, INSERT ON mydatabase.* FROM testuser;

This will revoke the SELECT and INSERT privileges from the specified user on the specified database.

What is the command to display the current user in MySQL?

The command to display the current user in MySQL is:

1

SELECT USER();

How to show the current user in MySQL?

To show the current user in MySQL, you can use the following SQL command:

1

SELECT USER();

This will retrieve the currently logged-in user in the current MySQL session.