MySQL User Creation and Authorization Guide

One of the most important rules of database security is to create new users without unnecessary privileges instead of the root user for each transaction. In this guide, you will learn the step-by-step process of creating a secure user on MySQL and granting authorization to these users.

What Will You Learn in This Guide?

Create secure users instead of MySQL "root" account.
Choosing different authentication methods (plugin).
Database and table based authorization.

1. Step 1: Introduction to MySQL Command Line

To log in to MySQL, the auth_socket plugin is usually used on Ubuntu systems. This matches the name of the system user with the name of the database user. You can use the following command to log in:

sudo mysql

If you have assigned a password to your root account, you can access it with the following command:

mysql -u root -p

This command will ask you to enter your password.

2. Step: Creating a New User

When creating a new user, it is necessary to specify from which machine it will be connected. If access will only be provided from within the server, localhost can be used. An example user creation command:

CREATE USER 'genix_user'@'localhost' IDENTIFIED BY 'guclu_sifre_buraya';

Note: If you want to provide a compatible connection with applications such as PHP, you can use the mysql_native_password authentication method:

CREATE USER 'genix_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'guclu_sifre_buraya';

3. Step: Authorize the User

We use the GRANT command to grant certain privileges to the newly created user. The following command authorizes genix_user to perform only certain actions:

GRANT SELECT, INSERT, UPDATE ON veritabani_adi.* TO 'genix_user'@'localhost';

If you want to give full permissions to the user, you can use this command (But this is not recommended):

GRANT ALL PRIVILEGES ON *.* TO 'genix_user'@'localhost' WITH GRANT OPTION;

This command gives the user full permissions on all databases and tables.

4. Step 1: Check and Revoke Privileges

To see the permissions a user has, you can use the following command:

SHOW GRANTS FOR 'genix_user'@'localhost';

If you want to revoke a privilege, you can use the REVOKE command:

REVOKE INSERT ON veritabani_adi.* FROM 'genix_user'@'localhost';

To delete a user completely:

DROP USER 'genix_user'@'localhost';

Frequently Asked Questions (FAQ)

1. I get the "Access denied for user" error, what should I do?

Check username, password or host information. % is different with localhost; Also make sure the correct database access rights are granted.

2. How to grant remote access to a user?

When creating the user, use the % sign instead of localhost and open port 3306 through the server's firewall (UFW).

3. When is the FLUSH PRIVILEGES command required?

Authorizations are automatically updated after GRANT or REVOKE commands. However, if you are making manual changes, you may need to run the FLUSH PRIVILEGES command.

4. How do I change user password in MySQL?

To change the password, you can use the following command:

ALTER USER 'kullanici_adi'@'localhost' IDENTIFIED BY 'yeni_sifre';

5. How can I remove user privileges?

You can use the following command to remove all permissions defined to the user:

REVOKE ALL PRIVILEGES ON *.* FROM 'kullanici_adi'@'localhost';

Result

A practical, step-by-step guide in Turkish that includes the steps of creating a new user on MySQL, assigning a password and managing database privileges.

To try database management on a professional infrastructure, you can immediately try your projects on the GenixNode platform.