Mysql Create Database and User With All Privileges

MySQL is one of the well-nigh popular and open-source relational database management systems around the earth. It provides a lot of direction options similar creating and managing a user with specific permissions to databases and tables.

how to create a new user and grant permissions in mysql

When you lot hire a new programmer to manage MySQL databases then you may need to grant specific permission to manage those databases such as deleting or modifying the information. In that case, information technology is essential for your to know how to grant specific privileges to the MySQL user business relationship.

In this mail service, we volition show you how to create a MySQL user and grant specific permissions

Prerequisites

A Linux VPS with MySQL server installed.
Access to the root user account (or admission to an admin account with root privileges)

Log in to the Server & Update the Server Os Packages

First, log in to your Debian ten server via SSH every bit the root user:

ssh          [email protected]_Address -p Port_number

Yous will need to replace 'IP_Address' and 'Port_number' with your server's respective IP address and SSH port number. Additionally, supersede 'root' with the username of the admin business relationship if necessary.

Before starting, you have to make sure that all Bone packages installed on the server are up to date. Yous can do this by running the post-obit commands:

apt-get update -y apt-go upgrade -y

Create a New MySQL User

First, you will need to connect to the MySQL beat using the MySQL root user. You can connect it using the post-obit command:

mysql -u root -p

You volition be asked to provide your MySQL root password. In one case you are connected to the MySQL shell, yous should run across the post-obit output:

Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connexion id is viii Server version: eight.0.25-0ubuntu0.20.04.1 (Ubuntu)  Copyright (c) 2000, 2021, Oracle and/or its affiliates.  Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their corresponding owners.  Type 'aid;' or '\h' for help. Blazon '\c' to clear the current input statement.  mysql>

Now, create a new MySQL user with the following command:

mysql> CREATE USER 'username'@'localhost' IDENTIFIED By 'userpassword';

Where:

username is the name of the MySQL user you desire to create.
userpassword is the password of the MySQL user.
localhost is a host from where you want to connect to MySQL.

You will need to replace the localhost with the remote server IP accost if you lot want to connect the MySQL from the remote server. In that case, yous can create a new MySQL user with the following command:

mysql> CREATE USER 'username'@'192.168.0.100' IDENTIFIED BY 'userpassword';

192.168.0.100 is the IP accost of the remote server.

Grant Privileges to a MySQL User Account

There are multiple types of permissions available in MySQL that you can provide to the MySQL user account. Some of the virtually unremarkably used permissions are shown beneath:

ALL PRIVILEGES: – This will allow MySQL users to run any query on the specified database.
CREATE: – This will allow MySQL users to create databases and tables.
DELETE: – This will let MySQL users to delete rows from the table.
Drop: – This volition allow MySQL users to drop databases and tables.
INSERT: – This will allow MySQL users to insert rows to a specific table.
SELECT: – This volition allow MySQL users to read a database.
UPDATE: – This will allow MySQL users to update tabular array rows.
GRANT Selection: – This will allow MySQL users to grant or remove other users' privileges.

To grant all privileges to the MySQL user account on a specific database, run the following command:

mysql> GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost';

To grant all privileges to the MySQL user on a specific table from a database, run the following command:

mysql> GRANT ALL PRIVILEGES ON dbname.tablename TO 'username'@'localhost';

To grant multiple privileges similar, SELECT, INSERT, DELETE to the MySQL user on a specific database, run the post-obit command:

mysql> GRANT SELECT, INSERT, DELETE ON dbname.* TO          [electronic mail protected]'localhost';

You volition need to run the flush privileges command for the changes to take effect.

mysql> FLUSH PRIVILEGES;

View MySQL User Business relationship Privileges

If y'all want to view the privileges assigned to the MySQL user business relationship, run the following command:

mysql> SHOW GRANTS FOR 'username'@'localhost';

You should see the following output:

+--------------------------------------------------------------+ | Grants for          [e-mail protected]          | +--------------------------------------------------------------+ | GRANT USAGE ON *.* TO `username`@`localhost`                 | | GRANT ALL PRIVILEGES ON `dbname`.* TO `username`@`localhost` | +--------------------------------------------------------------+ 2 rows in prepare (0.00 sec)

Remove MySQL User Account Privileges

You lot can use the REVOKE command to remove the permission of the MySQL user from the specific database or table.

For example, to remove ALL PRIVILEGES from the database, run the following command:

mysql> REVOKE ALL ON *.* FROM 'username'@'localhost';

Next, you will need to run the affluent privileges command for the changes to take event.

mysql> FLUSH PRIVILEGES;

Of course, yous don't have to manage MySQL server if you use one of our Managed Hosting services, in which instance you can simply ask our adept Linux admins to manage the MySQL server for you. They are available 24/7 and will take intendance of your request immediately.

how to create a new user and grant permissions in mysql

If you liked this post on How to Create a New User and Grant Privileges in MySQL, please share information technology with your friends on the social networks using the buttons on the left or but get out a respond below. Thanks.