Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How can we set up a MySQL User account by using INSERT INTO statement?
For adding a new user to MySQL, we just need to add a new entry to the user table in the database mysql. To illustrate it we are using the following example −
Example
The following program is an example of adding a new user guest with SELECT, INSERT and UPDATE privileges with the password guest123; the SQL query is −
root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed
mysql> INSERT INTO user (host, user, password,select_priv, insert_priv,
update_priv) VALUES ('localhost', 'guest',PASSWORD('guest123'),'Y','Y’,
'Y');
Query OK, 1 row affected (0.20 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)
mysql> SELECT host, user, password FROM user WHERE user = 'guest';
+-----------+---------+------------------+
| host | user | password |
+-----------+---------+------------------+
| localhost | guest | 6f8c114b58f2ce9e |
+-----------+---------+------------------+
1 row in set (0.00 sec)When adding a new user, remember to encrypt the new password using PASSWORD() function provided by MySQL. As you can see in the above example, the password bypass is encrypted to 6f8c114b58f2ce9e.
The FLUSH PRIVILEGES statement tells the server to reload the grant tables. If you don't use it, then you won't be able to connect to MySQL using the new user account at least until the server is rebooted.
Updated on: 20-Jun-2020
2K+ Views
Advertisements