Create and set a MySQL user through the command line in Linux

Create and set a MySQL user through the command line in Linux

Q: I want to create a new user account on the MySQL server and grant it appropriate permissions and resource restrictions. How can I create and set a MySQL user through command line?

To access a MySQL server, you must use a user account to log on to it. Each MySQL user account has many related attributes, such as the user name, password, permissions, and resource restrictions. "Permission" defines what a specific user can do on the MySQL server, and "Resource Restriction" sets a series of server resource usage licenses for the user. Creating or updating a user involves managing all attributes of the user account.

The following shows how to create and set a MySQL user in Linux.

First, log on to the MySQL server as the root user.

1. $ Mysql-u root-p

When the verification prompt appears, enter the password of the MySQL root Account.

Create a MySQL user

Run the following command to create a user with the username and password "myuser" and "mypassword" respectively.

1. Mysql> create user 'myuser' @ 'localhost' identified by 'mypassword ';

Once a user is created, all account details, including encrypted passwords, permissions, and resource restrictions, will be stored in a table named "user, this table exists in the special mysql database.

Run the following command to verify that the account is successfully created

1. Mysql> SELECT host, user, password FROM mysql. user WHERE user = 'myuser ';

Grant MySQL user permissions

A new MySQL user does not have any access permissions, which means that you cannot perform any operations in the MySQL database. You must grant necessary permissions to the user. The following are some available permissions:

• ALL: ALL available Permissions
• CREATE: Creates databases, tables, and indexes.
• LOCK_TABLES: locking a table
• ALTER: modify a table
• DELETE: DELETE a table
• INSERT: INSERT table or column
• SELECT: Retrieves table or column data.
• CREATE_VIEW: Create a view
• SHOW_DATABASES: list databases
• DROP: deletes databases, tables, and views.

Run the following command to grant specific permissions to the "myuser" user.

1. Mysql> GRANT <privileges> ON <database>. <table> TO 'myuser' @ 'localhost ';

In the preceding command, <privileges> indicates a list of permissions separated by commas. If you want to grant permissions to any database (or table), use the asterisk (*) instead of the database (or table) Name.

For example, grant CREATE and INSERT permissions to all databases/tables:

1. Mysql> grant create, insert on *. * TO 'myuser' @ 'localhost ';

Verify the full permissions granted to the user:

1. Mysql> show grants for 'myuser' @ 'localhost ';

Grant all permissions to all databases/tables:

1. Mysql> grant all on *. * TO 'myuser' @ 'localhost ';

You can also delete your existing permissions. Run the following command to revoke the existing permissions of the "myuser" account:

1. Mysql> REVOKE <privileges> ON <database>. <table> FROM 'myuser' @ 'localhost ';

Add resource limits for users

In MySQL, you can set MySQL resource usage restrictions for individual users. The following restrictions apply to available resources:

• MAX_QUERIES_PER_HOUR: Maximum number of requests per hour
• MAX_UPDATES_PER_HOUR: Maximum number of updates per hour allowed
• MAX_CONNECTIONS_PER_HOUR: Maximum number of connections allowed per hour (LCTT and MySQL global variable: max_user_connections jointly determine the number of simultaneous connections from the user to the database)
• MAX_USER_CONNECTIONS: number of simultaneous connections to the server

Run the following command to add a resource limit for the "myuser" account:

1. Mysql> grant usage on <database>. <table> TO 'myuser' @ 'localhost' WITH <resource-limits>;

In <resource-limits>, you can specify multiple resource limits separated by spaces.

For example, add MAXQUERIESPERHOUR and MAXCONNECTIONSPERHOUR resource restrictions:

1. Mysql> grant usage on *. * TO 'myuser' @ 'localhost' WITH MAX_QUERIES_PER_HOUR 30 MAX_CONNECTIONS_PER_HOUR 6;

Verify the user's resource restrictions:

1. Mysql> show grants for 'myuser' @ 'localhost;

The last important step to create and set a MySQL user is:

1. Mysql> flush privileges;

The change takes effect. Now the MySQL user account can be used.