MySQL is one of the most commonly used relational database management systems that enable us to store, manipulate, and retrieve data. It has several options to grant specific users access to databases and tables. Also, it provides options to revoke those privileges from them whenever required.
This is a vast area to understand and providing users with appropriate access requires sound SQL queries. This article will explain different MySQL commands and management methods, such as creating users, granting and revoking user permissions, and using the centralized interface to provide user access. Namely, this guide will show you “MySQL create user and grant” functions.
Here, we consider that the user has root-level access to self-manage the MySQL environment and run different commands. Ensure you have the proper privilege to run the commands.
Prerequisites
For this article, we assume that the user has a MySQL or MariaDB server on their system. To access the MySQL shell, enter the following command and use the root username and password whenever asked. The minimum privileges for creating the account for the user must be CREATE USER and GRANT.
mysql -u root -pMySQL Create Database and User
Let’s start with the commands for MySQL create DB and user afterward. After installing the MySQL server on your system, you should first create a database before creating user accounts.
To create a database, you need to log into the MySQL instance with root access.
In the example below, we are creating the "data" database. Run the following command to do this.
mysql> CREATE DATABASE data;If the given database name already exists in the MySQL instance, you will get an error displayed on the shell. To eliminate that error, you need to use “if not exists” in the CREATE DATABASE command. You can try the following command:
mysql> CREATE DATABASE IF NOT EXISTS data;How to Delete the MySQL Database
If you want to proceed with deleting the existing database, use the following command:
mysql> DROP DATABASE data;Dropping the database will remove the database permanently, along with all the data stored in it. This command will not ask for confirmation to proceed with the deletion and will delete it directly. Therefore, use this drop command carefully.
MySQL Create User: How to Create a New MySQL User Account
Now, it’s time to learn the function for “MySQL create new user.”
Every user in MySQL comes with a username and hostname to access the MySQL instance. To create a new user, you need to enter your username and hostname.
Adding users in MySQL is quite simple — just follow this command to start:
mysql> CREATE USER 'user'@'local_host' IDENTIFIED BY 'password';The user will get the username “user” for accessing the MySQL instance from its local_host, preventing access to the server directly from any device. To open the range of the host, you can specify the wildcard character while granting access to the MySQL instance of the user with this command:
mysql> CREATE USER 'subnet_user'@'10.0.%' IDENTIFIED BY 'password';Here, the “10.0.%” specifies that the user identified with the IP address “10.0.” can access the MySQL instance.
If you want to view the details of all the users in your MySQL instance, you can use the SELECT command, as shown below:
mysql> SELECT * FROM mysql.user;
Create MySQL User With Password
Now, let’s learn the function to create a new MySQL user named “bob” using the following command.
mysql> create user bob@local_host identified by 'SData1pass!';If you again check the list of the available users in your MySQL instance, you will see the new entry.
mysql> SELECT * FROM mysql.user;
After creating a new user successfully, we will now see what databases can be accessed by “bob”. To run the commands as a user “bob”, you need to log in and enter the provided password.
The following command results in a database that “bob” can access.
Mysql> show databases;
MySQL User Account Management
Suppose you have created a new user without any privileges. In such a circumstance, that user can only connect to the database but does not have the required authority to access any data or perform any actions.
As discussed easier, MySQL provides various privileges to users. We will discuss them one by one, as each privilege provides a different level of access to the user.
Common Privileges in MySQL
A privilege is an action that a user can perform on a database. So, the privileges must be given with care by checking what type of action needs to be performed. These privileges are organized into various levels depending on their scope.
- Global and Administrative Privileges: Applicable for the entire databases on the server. Administrative privileges come into the global privileges category, as they allow users to manage operations of the MySQL server without being limited to a particular database.
- Database Privileges: Only applicable to specific databases available in your MySQL instance, including all objects within those databases. If you want, you can provide the database privileges globally as per the requirement.
- Proxy Privileges: Allow users to have all the privileges that another user has. It's like mirroring the privileges of one user to another.
- Database Object Privileges: Some examples include accessing tables, columns, etc. If you want, you can grant database object privileges globally as per your requirement.
All the privileges-related access is stored within a table named “grant” in the “MySQL” database. Some of the standard privileges that are granted to all the users in MySQL are, as follows:
- ALL PRIVILEGES: Provides all privileges except GRANT OPTION and PROXY.
- ALTER: This allows the user to modify the structure of a table or database.
- CREATE: This allows the user to create new databases and tables.
- DELETE: This allows the user to delete entire rows from a table.
- INSERT: This allows the user to add rows to a table.
- SELECT: This allows the user to run several queries to display the content from a table.
- UPDATE: This allows the user to add some rows to a table.
Grant Permissions to a MySQL User Account
You can go with the following syntax to grant specific user account privileges.
GRANT perm1, perm2 ON db_name.table_name TO 'db_user'@'local_host';Here are some examples of the GRANT privilege for better understanding:
- Grant all privileges to a user account for a specific database.
GRANT ALL PRIVILEGES ON db_name.* TO 'db_user'@'local_host';- Grant all the privileges to a user on all the databases.
GRANT ALL PRIVILEGES ON *.* TO 'db_user'@'local_host';- Grant specific privileges to a user account for a specific database.
GRANT SELECT, INSERT, DELETE ON db_name.* TO db_user@'local_host';Displaying MySQL User Account Privileges
To confirm a user account's privileges, you can use the following command:
SHOW GRANTS FOR 'db_user'@'local_host';
Revoking Permissions From a MySQL User Account
MySQL offers the revoke command to delete privileges from a user account. It has a similar syntax to the GRANT command. Instead of using the “GRANT”, you need to mention “REVOKE” along with the specific privileges, as shown below:
mysql> REVOKE INSERT ON data.* FROM 'user'@'local_host';If the mentioned privilege is not in the user account list, then this command will have no on the user account.
How to Remove a User on MySQL
Like you can grant and revoke privileges from a specific user in MySQL, you can remove that user as well. To do so, you need to use the DROP command.
If you want to remove a specific user from MySQL, you can use the following command:
mysql> DROP USER 'user'@'local_host';Changing a Password for MySQL User Account
A different version of MySQL comes with a different syntax for changing the user password. First, confirm which version of MySQL is on your system by executing the following command.
mysql> SELECT version();If you are willing to change the MySQL version 5.76 or higher password, you can use the following command.
mysql> ALTER USER 'user'@'local_host' IDENTIFIED BY 'new_password';But, in case you are using the older MySQL version, you can execute the following command.
mysql>SET PASSWORD FOR 'user'@'local_host' = PASSWORD('new_password');Why It’s Important to Manage MySQL Databases and User Accounts
Managing several users, databases, and privileges is a crucial task. The user must have top-level access to the database for managing everything. As the infrastructure grows, the management becomes more complex. The admin makes sure that the right users have appropriate access without sacrificing security.Why It’s Important to Manage MySQL Databases and User Accounts
With a control plane, you can seamlessly manage database access and users. The following are some ways to achieve that:
- Implement a Centralized Authentication System: A centralized system removes the manual effort required to manage individual users. You can use any tool that will directly integrate with your identity provider and handle the entire authentication.
- Enable Permission Management: Have a centralized interface allowing the user to view and modify access and permissions. Choose the tool offering a role-based system for managing the permissions.
- Regularly Check User Access: For quality control and security, test your database access and ensure your tool has recording capabilities to maintain records of actions, queries, change requests, and permission changes.
Conclusion
MySQL is a relational database management system with several commands for granting and revoking privileges for user accounts. The syntax of these commands may vary from version to version. So, it is essential to check the version of MySQL first and then start executing commands. You’ll be able to maximize your efficiency now that you understand the MySQL create user functions.
This article has explored various commands for MySQL add user, create a new user account with a password, grant different access to user accounts, and revoke user accounts privileges.
Ready to expand your knowledge on MySQL? Check out our roundup of the best MySQL tutorials.
The Complete SQL Bootcamp 2024: Go from Zero to Hero
Frequently Asked Questions
1. How Do I Create a New User in MySQL?
If you want to create a new user within the MySQL database, you need to enter details, such as user_name host_name, that the user can use to access the database using a password.
Run the following command:
mysql> CREATE USER 'db_user'@'local_host' IDENTIFIED BY 'pass';2. How Do I Create a User and Give Privileges in MySQL?
Run the following commands:
mysql> CREATE USER 'user'@'local_host' IDENTIFIED BY 'password'; GRANT perm2 ON db_name.table_name TO 'db_user'@'local_host';3. How Do I Grant Privileges to a User in MySQL Workbench?
Follow the below steps to grant access to users using MySQL Workbench.
Users and Privileges -> Add Account -> provide details (login name, local_host, password) -> apply
Schema Privileges -> Click the user account -> Add Entry
Selected Schema -> choose your database schema.
Select the specific privileges from the below check box accordingly.
4. How Do I Find Users in MySQL Workbench?
In SQL Workbench, it’s quite easy to find all the users. In the Navigator section, select Users and Privileges. You will get the list of all users there.
People are also reading:
