DbSchema Database Designer

DbSchema | Create New User in MySQL & Grant Access

Publish on DbSchema Blog >>>

Creating a new user in MySQL and granting privileges is very easy. First, you have to open the MySQL command line.

1.Open MySQL

You can do this by reaching the bin directory from Program Files.

cd\Program Files\MySQL\MySQL Server 8.0\bin

This is the usual path for the MySQL bin folder. After you reach the bin folder, execute the next command:

mysql -uroot -p

This will require the password set at the installation.

2.What are MySQL users?

In MySQL, users are a combination of __username`` and __hostname identified by a __`password. The __hostname`` can be replaced with __%`` to allow connections from any host.

3.Create a user for ‘localhost’

Now, from inside the MySQL shell, let’s create a new user for __`localhost`` by executing the next command:

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

To grant access to the user, execute:

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

Finally, we will reload the grant table by executing:


Now let’s break down the commands to understand them better:

  • __CREATE USER ‘user‘@’localhost’ - pretty straight forward, this command will tell MySQL to create a new user. Replace __user with
    the name you want to attribute. __@’localhost’ specifies that the user can connect only from __localhost``;

  • __IDENTIFIED BY ‘password’` - here we specify the password attributed to that user;

  • __GRANT ALL PRIVILEGES - this will give the user rights to modify the tables, columns, etc. The __ALL PRIVILEGES part specifies
    that the user will have standard privileges;

  • __ON .- this part specifies the databases on which the user will have access. The first * means that the user will have access to **all databases**. If you want to grant access on a specific database, replace it with the name. For example,ON dbtest.*`;

  • __TO ‘user‘@’localhost’` - identifies the user;

  • __FLUSH PRIVILEGES` - this will tell the server to refresh the grant tables.

4.Create a user that can connect from any host

To give a user the right to connect from any host, we have to replace the __localhostmentions from the previous commands with%`. They will look like this:

CREATE USER 'user'@'%' IDENTIFIED BY 'password';



5.Delete a user from database

To delete a user from the database, we will execute the next command:

DROP USER 'user'@'localhost'

Now you are ready to manage your users in MySQL.

Visually Manage Databases using DbSchema

DbSchema is a databases client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:

DbSchema Designer alt >

Interactive Diagrams

Design tables, column and foreign keys directly in diagrams, by double-clicking them. Changes will be saved to the design model and, if DbSchema is connected to the database also into the database. More.

Connection Dialog alt >

Simple Connection Dialog

Choose the database location, the user and password, and simply get connected. Choose 'Edit Manually' into the JDBC URL combo to enter a custom URL. More.

Relational Data Explorer alt >

Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys. Double-click cells to edit the data. More.

Query Builder alt >

Query Builder

Create SQL Queries featuring JOINS, GROUP BY, ORDER BY just using the mouse. More.

SQL Query Editor alt >

SQL Query Editor

Edit and execute SQL Queries. The editor is autocompletion-enabled. More.

Schema Synchronization alt >

Design Schema in Team & Schema Deployment

DbSchema is using the design model, a copy of the schema structure, independent of the database.
The design model can be saved to file and shared in a team.
Connecting to another database you may compare the design model with the database, commit the differences or merge them in the design model. More.

Dark Theme alt >

Dark Theme

Configurable styles & dark theme. More.

Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.

DbSchema can be downloaded for free. No registration is required.