MySQL Create Users

Example of MySQL Create User Statement

Given below is the example mentioned:

Steps to create a user in MySQL server:

Step 1: Login into the MySQL server from the command line as the root user.

mysql -u root –p

‘ u’ is the root username, and ‘p’ is the root password.

Step 2: Check for all existing users in the present working server.

SELECT user from mysql.user;

MySQL is the database’s name, and the user is the table’s name.

We can also write the above statement as:

Step 1:

use mysql;

Step 2:

select user from user;

The output of a list of users:

Step 3: Create a new user.

CREATE USER abc@localhost IDENTIFIED BY 'abc123';

Step 4: We will use the IF NOT EXISTS clause with the CREATE USER statement.

CREATE USER IF NOT EXISTS abc@localhost IDENTIFIED BY 'abc123';

List of Privileges provided by Server

Given below is the list of privileges provided by the server:

• ALL PRIVILEGES: It gives all privileges to a new user.
• CREATE: It gives the user privilege to create databases and tables.
• DROP: It gives the user privilege to drop databases and tables.
• DELETE: It gives the user privilege to delete rows from a specific table.
• INSERT: It allows the user to insert rows into a table.
• SELECT: It provides the user with the privilege to read a database.
• UPDATE: It gives the user the right to update table rows.

Syntax of Grant Privileges to the User

Given below is the syntax for granting privileges to the user:

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

• Below is the query if we want to share all privileges with a user.

Code:

GRANT ALL PRIVILEGES ON * . * TO abc@localhost;

‘*’ allows the user to read, edit, execute, and perform all tasks in all the databases and tables.

• If you want to give specific privileges to a newly created user, execute the following command.

Code:

GRANT CREATE, SELECT, INSERT ON * . * TO abc@localhost;

• Given below is the query to flush all the privileges of a user.

Code:

FLUSH PRIVILEGES;

• If we want to see the existing privileges for the user, execute the following command.

Code:

SHOW GRANTS for username;

• Given below is the query to list fields of the user table.

Code:

desc mysql.user;

Output:

Code:

select user,host,account_locked from mysql.user;

Output:

• Given below is the query to see the current user.

Code:

SELECT user();

Output:

Or

Code:

SELECT current_user();

Output:

Code:

SELECT User, host FROM mysql.user;

Output:

• If you want to display unique usernames.

Code:

SELECT DISTINCT User FROM mysql.user;

Output:

• Given below is the query to Delete a user.

Code:

Drop user 'user'@'localhost';

• Given below is the query to Revoke privileges from the user.

Code:

Revoke all privileges on database_name from 'user'@'localhost';

• Given below is the query to view a list of all users.

Code:

select user from mysql.user GROUP BY user;

Output:

• Given below is the query to delete a specific user.

Code:

Delete from mysql.user where user ='username';

Where username is the name of the user which we want to delete.

Output:

So, after the deletion of the root user, we are left with only three users.

Conclusion

In this article, we saw how to create and delete new users. We have also seen how to grant privileges to users and revoke privileges and different types of privileges granted to the users in a MySQL database. We have also seen how to list users in the present server.

Recommended Articles

We hope that this EDUCBA information on “MySQL Users” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. Unique Key in MySQL
2. MySQL Timestamp
3. Examples of MySQL Constraints
4. MySQL Outer Join