MySQL Create User

The MySQL Create User statement allows us to create a new user account in the database server. It provides authentication, SSL/TLS, resource-limit, role, and password management properties for the new accounts. It also enables us to control the accounts that should be initially locked or unlocked.

Syntax:-

CREATE USER [IF NOT EXISTS] account_name IDENTIFIED BY 'password';

Example:-

create user mktutorial@localhost identified by 'mk123456';

To show the list of user:-

select user from mysql.user;

Grant Privileges to the MySQL New User

  • ALL PRIVILEGES: It permits all privileges to a new user account.
  • CREATE: It enables the user account to create databases and tables.
  • DROP: It enables the user account to drop databases and tables.
  • DELETE: It enables the user account to delete rows from a specific table.
  • INSERT: It enables the user account to insert rows into a specific table.
  • SELECT: It enables the user account to read a database.
  • UPDATE: It enables the user account to update table rows.

Example:-

GRANT ALL PRIVILEGES ON * . * TO mktutorial@localhost;

GRANT CREATE, SELECT, INSERT ON * . * TO mktutorial@localhost;

FLUSH PRIVILEGES;

SHOW GRANTS for username;

MySQL Drop User

The MySQL Drop User statement allows us to remove one or more user accounts and their privileges from the database server. If the account does not exist in the database server, it gives an error.

Syntax:-

DROP USER 'account_name';

Example:-

DROP USER mktutorial@localhost;

Change MySQL User Password

MySQL user is a record that contains the login information, account privileges, and the host information for MySQL account to access and manage the database. The login information includes the user name and password. In some cases, there is a need to change the user password in the MySQL database.

MySQL allows us to change the user account password in three different ways, which are given below:

  • UPDATE Statement
  • SET PASSWORD Statement
  • ALTER USER Statement

Change user account password using the UPDATE statement

This statement is the first way to change the user password for updating the user table of the MySQL database. Here, you have to use the FLUSH PRIVILEGE statement after executing an UPDATE statement for reloading privileges from the grant table of the MySQL database.

Example:-

UPDATE user SET password = PASSWORD('mk12345') WHERE user = 'mktutorial' AND host = 'localhost';

Change user account password using SET PASSWORD statement

The SET PASSWORD statement is the second way to change the user password in the MySQL database. If you want to change the other account password, you must have the UPDATE privilege. The SET PASSWORD statement uses the user account in the username@localhost format.

Example:-

SET PASSWORD FOR 'mktutorial'@'localhost' = PASSWORD('mk12345');

Change user account password using ALTER USER statement

The ALTER USER statement is the third way to change the user password in the MySQL database. MySQL uses ALTER USER statement with the IDENTIFIED BY clause for changing the password of a user account

Example:-

ALTER USER mktutorial@localhost IDENTIFIED BY 'mk123';

Setting up Resource Limits on Users in MySQL

Often while managing and creating new users, we use all the default options and tend not to use extra features provided by MySQL. These extra options could prevent a user from using all the resources and degrading the performance of MySQLwe will discuss a few such features that will put resource restrictions on users.

max_user_connections

Sometimes, due to unprecedented growth or huge transactions, a single user makes too many connections, and the MySQL server gets starved of free connectionsThis blocks the DBA from logging into MySQL to fix it. To fix it in MySQL 5.7 and below, we have to do a restart. For MySQL 8, it can be done without a restart, but we have to configure the admin interface beforehand. You can read more about it here in Dealing With “Too Many Connections” Error in MySQL 8.

Syntax:-

CREATE USER 'monitor_user'@'%' IDENTIFIED BY 'YourPasswordHere' WITH MAX_USER_CONNECTIONS 100;

CREATE USER mktutorial@localhost IDENTIFIED BY 123456 WITH MAX_USER_CONNECTIONS 100;

max_connections_per_hour

Instead of setting up max_user_connections for users, we can also restrict the limit to per hour. This will add a time-based restriction to the user, only allowing a specific number of connections per hour.

Syntax:-

ALTER USER 'monitor_user'@'%' WITH MAX_CONNECTIONS_PER_HOUR 10;

ALTER USER mktutorial@localhost WITH MAX_CONNECTIONS_PER_HOUR 10;

max_queries_per_hour

This option will set a limit for any kind of queries that a user can run per hour and will only allow the user to run a pre-defined number of queries per hour.

Syntax:-

ALTER USER 'monitor_user'@'%' WITH MAX_QUERIES_PER_HOUR 10;

ALTER USER mktutorial@localhost WITH MAX_QUERIES_PER_HOUR 10;

max_updates_per_hour

If the user tries to run more than the defined updates per hour, the following error message will appear.

Syntax:-

UPDATE some_table SET col1='Value d' WHERE id=2;