{"id":3549,"date":"2019-10-25T03:15:10","date_gmt":"2019-10-25T03:15:10","guid":{"rendered":"http:\/\/blog.hostinger.io\/hostinger-tutorials\/?p=3549"},"modified":"2025-12-02T15:34:43","modified_gmt":"2025-12-02T15:34:43","slug":"how-create-mysql-user-and-grant-permissions","status":"publish","type":"post","link":"\/ca\/tutorials\/how-create-mysql-user-and-grant-permissions","title":{"rendered":"MySQL add user: how to create and grant privileges"},"content":{"rendered":"

Do you want to understand the basics of MySQL operations? Read on, as this article will show you how to create a user in MySQL. You’ll also learn about several commands to grant privileges, revoke privileges, and delete existing users.<\/p>

\"\"<\/a><\/figure>

\n\n\n\n\n<\/p>

What Is a MySQL Database<\/h2>

In order to understand MySQL, you’ll need to know what a database is. It’s a virtual storage where you can save necessary data for building websites and web applications.<\/p>

MySQL database can store user account details, such as usernames, passwords, email addresses, and any type of information that you want to keep for later use.<\/p>

However, the stored data should be in some kind of order. That’s why we have database management systems. These tools are used to communicate with the database and allow developers to structure, store, dump, and modify the data.<\/p>

Prerequisites for Creating a MySQL User<\/h2>

Before we continue to the next section of the tutorial, make sure that you have installed MySQL. If you haven’t, we have great tutorials on how to install it on both Ubuntu<\/a> and CentOS<\/a>.<\/p>

\n

Pro Tip<\/h4>\n

We will use the command line to access our Linux VPS as root. You can either use PuTTY<\/a> (Windows) or your terminal (macOS, Linux) and log in using your SSH root login information provided by your hosting provider.<\/p>\n <\/div>\n\n\n\n<\/p>

How to Create a MySQL User Account and Grant All Privileges<\/h2>

Just as you start using MySQL, you’ll be given a username and a password. These initial credentials will grant you root<\/strong> access or full control of all your databases and tables.<\/p>

However, there are times when you’ll need to give the database access to someone else without granting them full control.<\/p>

For instance, you hire developers to maintain your databases, but you don’t want to provide them with the ability to delete or modify any sensitive information.<\/p>

In that case, you should give them the credentials of a non-root user. This way, you can keep track of what the developers can and cannot do with your data.<\/p>

In this part, we will explain how to create a user account in MySQL with all privileges to your database. In a practical sense, it’s not wise to give full control to a non-root user. However, it’s still a good entry-point to learn about user privileges.<\/p>

To create a new user account in MySQL, follow these steps:<\/p>

    \n
  1. Access command line and enter MySQL server:\n
    mysql<\/pre>\n<\/li>\n\n\n\n
  2. The script will return this result, which verifies that you are accessing a MySQL server.\n
    mysql><\/pre>\n<\/li>\n\n\n\n
  3. Then, execute the following command:\n
    CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';<\/pre>\n<\/li>\n<\/ol>
      \n
    1. new_user<\/strong> is the name we’ve given to our new user account and the IDENTIFIED BY ‘password’<\/strong> section sets a passcode for this user. You can replace these values with your own, inside the quotation marks.<\/li>\n\n\n\n
    2. In order to grant all privileges of the database for a newly created user, execute the following command:\n
      GRANT ALL PRIVILEGES ON * . * TO 'new_user'@'localhost';<\/pre>\n<\/li>\n\n\n\n
    3. For changes to take effect immediately flush these privileges by typing in the command:\n
      FLUSH PRIVILEGES;<\/pre>\n<\/li>\n<\/ol>
      Once that is done, your new user account has the same access to the database as the root user.<\/p>
      How to Grant Privileges Separately for a MySQL User<\/h2>
      In this part, we will explain how to grant privileges separately for a user account in MySQL.<\/p>
      When specifying the database name and table name, separate them with a . (period) and no spaces. This will give the root user fine-grain control over certain data.<\/p>
      Also, replace the PERMISSION_TYPE<\/strong> value with the kind of access you want to grant to your new user account.<\/p>
      Here are the most used commands in MySQL:<\/p>