Create New User Account in MySQL
Table of Contents
___________________________________________________________________________________________________
1. Login to MySQL
2. How to List all existing users?
3. How to Create new user?
4. How to Connect to New User Account using Password?
5. How To Change the Password for Your Own User Account?
6. How To Change the Password of Another User Account?
7. How to Lock user account?
8. How to Unlock user account?
9. How To Rename an Existing User Account Name?
10. How to Drop user?
___________________________________________________________________________________________________
[root@rac1 mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.23 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
2. How to List all existing users?
mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> mysql> select database; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 mysql> select database (); +-------------+ | database () | +-------------+ | mysql | +-------------+ 1 row in set (0.00 sec) mysql> mysql> show tables; +------------------------------------------------------+ | Tables_in_mysql | +------------------------------------------------------+ | columns_priv | | component | | db | | default_roles | | engine_cost | | func | | general_log | | global_grants | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | password_history | | plugin | | procs_priv | | proxies_priv | | replication_asynchronous_connection_failover | | replication_asynchronous_connection_failover_managed | | role_edges | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +------------------------------------------------------+ 35 rows in set (0.00 sec) mysql> mysql> desc user; +--------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Host | char(255) | NO | PRI | | | | User | char(32) | NO | PRI | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Reload_priv | enum('N','Y') | NO | | N | | | Shutdown_priv | enum('N','Y') | NO | | N | | | Process_priv | enum('N','Y') | NO | | N | | | File_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Show_db_priv | enum('N','Y') | NO | | N | | | Super_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Repl_slave_priv | enum('N','Y') | NO | | N | | | Repl_client_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Create_user_priv | enum('N','Y') | NO | | N | | | Event_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | | Create_tablespace_priv | enum('N','Y') | NO | | N | | | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | | | ssl_cipher | blob | NO | | NULL | | | x509_issuer | blob | NO | | NULL | | | x509_subject | blob | NO | | NULL | | | max_questions | int unsigned | NO | | 0 | | | max_updates | int unsigned | NO | | 0 | | | max_connections | int unsigned | NO | | 0 | | | max_user_connections | int unsigned | NO | | 0 | | | plugin | char(64) | NO | | caching_sha2_password | | | authentication_string | text | YES | | NULL | | | password_expired | enum('N','Y') | NO | | N | | | password_last_changed | timestamp | YES | | NULL | | | password_lifetime | smallint unsigned | YES | | NULL | | | account_locked | enum('N','Y') | NO | | N | | | Create_role_priv | enum('N','Y') | NO | | N | | | Drop_role_priv | enum('N','Y') | NO | | N | | | Password_reuse_history | smallint unsigned | YES | | NULL | | | Password_reuse_time | smallint unsigned | YES | | NULL | | | Password_require_current | enum('N','Y') | YES | | NULL | | | User_attributes | json | YES | | NULL | | +--------------------------+-----------------------------------+------+-----+-----------------------+-------+ 51 rows in set (0.00 sec) mysql> select host,User from user; +-----------+------------------+ | host | User | +-----------+------------------+ | localhost | mysql.infoschema | | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+------------------+ 4 rows in set (0.00 sec) mysql> ---- OR ----------- mysql> use dell; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> mysql> select host,user from mysql.user; +-----------+------------------+ | host | user | +-----------+------------------+ | localhost | mysql.infoschema | | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+------------------+ 4 rows in set (0.00 sec) mysql> ---- OR ------------ select * from INFORMATION_SCHEMA.USER_ATTRIBUTES;
mysql> select database(); +------------+ | database() | +------------+ | dell | +------------+ 1 row in set (0.00 sec) mysql> -- CREATE NEW USER, raj=username, localhost=from where user raj can connect to mysql db mysql> create user 'raj'@'localhost' identified by 'Raj_123%'; Query OK, 0 rows affected (0.01 sec) mysql> -- srinu@% -- srinu user can connect from any host mysql> create user 'srinu'@'%' identified by 'Srinu_123%'; Query OK, 0 rows affected (0.01 sec) mysql> -- sugi@192.168.2.% -- sugi user can connect from any host using 192.168.2.0 subnet. mysql> create user 'sugi'@'192.168.2.%' identified by 'Sugi_123%'; Query OK, 0 rows affected (0.01 sec) mysql> -- teja@192.168.2.102 -- teja user allowed only from host ip 192.168.2.102 mysql> create user 'teja'@'192.168.2.102' identified by 'Teja_123%'; Query OK, 0 rows affected (0.01 sec) mysql> -- create user with comment mysql> create user sunil@localhost identified by 'Sunil_123%' COMMENT 'Sunil - Local User'; Query OK, 0 rows affected (0.01 sec) mysql> mysql> select * from INFORMATION_SCHEMA.USER_ATTRIBUTES where user = 'sunil' and host = 'localhost'; +-------+-----------+-----------------------------------+ | USER | HOST | ATTRIBUTE | +-------+-----------+-----------------------------------+ | sunil | localhost | {"comment": "Sunil - Local User"} | +-------+-----------+-----------------------------------+ 1 row in set (0.00 sec) mysql> -- create user with attribute mysql> create user 'mike'@'localhost' identified by 'Mike_123%' ATTRIBUTE '{"Name":"Mike","Role":"DBA"}'; Query OK, 0 rows affected (0.01 sec) mysql> mysql> select * from INFORMATION_SCHEMA.USER_ATTRIBUTES where user = 'mike' and host = 'localhost'; +------+-----------+---------------------------------+ | USER | HOST | ATTRIBUTE | +------+-----------+---------------------------------+ | mike | localhost | {"Name": "Mike", "Role": "DBA"} | +------+-----------+---------------------------------+ 1 row in set (0.00 sec) mysql> -- creating a user that can connect from multiple hosts mysql> create user 'scott'@'localhost' identified by 'Scott_1%'; Query OK, 0 rows affected (0.01 sec) mysql> create user 'scott'@'192.168.%.%' identified by 'Scott_12%'; Query OK, 0 rows affected (0.01 sec) mysql> create user 'scott'@'192.168.2.%' identified by 'Scott_123%'; Query OK, 0 rows affected (0.01 sec) mysql> create user 'scott'@'192.168.2.102' identified by 'Scott_1234%'; Query OK, 0 rows affected (0.00 sec) mysql> mysql> select user,host from INFORMATION_SCHEMA.USER_ATTRIBUTES where user = 'scott'; +-------+---------------+ | USER | HOST | +-------+---------------+ | scott | 192.168.%.% | | scott | 192.168.2.% | | scott | 192.168.2.102 | | scott | localhost | +-------+---------------+ 4 rows in set (0.00 sec) mysql> mysql> select host,user from mysql.user order by user; +---------------+------------------+ | host | user | +---------------+------------------+ | localhost | mike | | localhost | mysql.infoschema | | localhost | mysql.session | | localhost | mysql.sys | | localhost | raj | | localhost | root | | 192.168.%.% | scott | | 192.168.2.% | scott | | 192.168.2.102 | scott | | localhost | scott | | % | srinu | | 192.168.2.% | sugi | | localhost | sunil | | 192.168.2.102 | teja | +---------------+------------------+ 14 rows in set (0.00 sec) mysql> mysql> -- create user with password expire mysql> create user 'hr'@'localhost' identified by 'Hr_1234%' password expire; Query OK, 0 rows affected (0.01 sec) mysql> [root@rac1 ~]# mysql -u hr -pHr_1234% mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 8.0.23 Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql> mysql> SET PASSWORD = 'Hr_123%_'; Query OK, 0 rows affected (0.01 sec) mysql> mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec) mysql> mysql> mysql>
4. How to Connect to New User Account using Password?
[root@rac1 ~]# mysql -u scott -pScott_1% mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 8.0.23 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> SELECT USER(); +-----------------+ | USER() | +-----------------+ | scott@localhost | +-----------------+ 1 row in set (0.00 sec) mysql> mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec) mysql>
5. How To Change the Password for Your Own User Account?
[root@rac1 ~]# mysql -u raj -pRaj_123% mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 8.0.23 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT USER(); +---------------+ | USER() | +---------------+ | raj@localhost | +---------------+ 1 row in set (0.00 sec) mysql> SET PASSWORD = 'Raj_1234%'; Query OK, 0 rows affected (0.01 sec) mysql> mysql> exit Bye [root@rac1 ~]# mysql -u raj -pRaj_1234% mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15 Server version: 8.0.23 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT USER(); +---------------+ | USER() | +---------------+ | raj@localhost | +---------------+ 1 row in set (0.00 sec) mysql> ---------------- OR ----------------- [root@rac1 ~]# mysql -u raj -pRaj_1234% mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 23 Server version: 8.0.23 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> ALTER USER USER() IDENTIFIED BY 'Raj_12345%'; Query OK, 0 rows affected (0.01 sec) mysql>
6. How To Change the Password of Another User Account?
mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> mysql> SET PASSWORD FOR 'raj'@'localhost' = 'Raj_12345%'; Query OK, 0 rows affected (0.01 sec) mysql> [root@rac1 ~]# mysql -u raj -h localhost -pRaj_12345% mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16 Server version: 8.0.23 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select user(); +---------------+ | user() | +---------------+ | raj@localhost | +---------------+ 1 row in set (0.00 sec) mysql> -------- OR -------- mysql> use mysql; Database changed mysql> ALTER USER 'raj'@'localhost' IDENTIFIED BY 'Amudala%12'; Query OK, 0 rows affected (0.01 sec) mysql> [root@rac1 ~]# mysql -u raj -h localhost -pAmudala%12 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 18 Server version: 8.0.23 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select user(); +---------------+ | user() | +---------------+ | raj@localhost | +---------------+ 1 row in set (0.00 sec) mysql>
[root@rac1 mysql]# mysql -u root -h localhost -pMysql@123 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 Server version: 8.0.23 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> ALTER USER 'raj'@'localhost' ACCOUNT LOCK; Query OK, 0 rows affected (0.01 sec) mysql> [root@rac1 ~]# mysql -u raj -h localhost -pAmudala%12 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 3118 (HY000): Access denied for user 'raj'@'localhost'. Account is locked. [root@rac1 ~]# mysql> select database (); +-------------+ | database () | +-------------+ | mysql | +-------------+ 1 row in set (0.00 sec) mysql> mysql> select user,host,account_locked from user where user='raj' and host='localhost'; +------+-----------+----------------+ | user | host | account_locked | +------+-----------+----------------+ | raj | localhost | Y | +------+-----------+----------------+ 1 row in set (0.00 sec) mysql>
8. How to Unlock user account?
mysql> select database (); +-------------+ | database () | +-------------+ | mysql | +-------------+ 1 row in set (0.00 sec) mysql> mysql> select user,host,account_locked from user where user='raj' and host='localhost'; +------+-----------+----------------+ | user | host | account_locked | +------+-----------+----------------+ | raj | localhost | Y | +------+-----------+----------------+ 1 row in set (0.00 sec) mysql> mysql> ALTER USER 'raj'@'localhost' ACCOUNT UNLOCK; Query OK, 0 rows affected (0.00 sec) mysql> select user,host,account_locked from user where user='raj' and host='localhost'; +------+-----------+----------------+ | user | host | account_locked | +------+-----------+----------------+ | raj | localhost | N | +------+-----------+----------------+ 1 row in set (0.01 sec) mysql>
9. How To Rename an Existing User Account Name?
mysql> select user,host,account_locked from mysql.user where user='hr'; +------+-----------+----------------+ | user | host | account_locked | +------+-----------+----------------+ | hr | localhost | N | +------+-----------+----------------+ 1 row in set (0.00 sec) mysql> RENAME USER 'hr'@'localhost' TO 'hr1'@'localhost'; Query OK, 0 rows affected (0.01 sec) mysql> select user,host,account_locked from mysql.user where user='hr'; Empty set (0.00 sec) mysql> select user,host,account_locked from mysql.user where user='hr1'; +------+-----------+----------------+ | user | host | account_locked | +------+-----------+----------------+ | hr1 | localhost | N | +------+-----------+----------------+ 1 row in set (0.00 sec) mysql>
mysql> select user,host,account_locked from mysql.user where user='hr1'; +------+-----------+----------------+ | user | host | account_locked | +------+-----------+----------------+ | hr1 | localhost | N | +------+-----------+----------------+ 1 row in set (0.00 sec) mysql> mysql> DROP USER 'hr1'@'localhost'; Query OK, 0 rows affected (0.01 sec) mysql> select user,host,account_locked from mysql.user where user='hr1'; Empty set (0.00 sec) <---- User hr1 not found. mysql>
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/
Reference:
https://dev.mysql.com/doc/refman/8.0/en/create-user.html