- Trending Categories
- Data Structure
- Operating System
- C Programming
- 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?
152 Lectures 16 hours
87 Lectures 5.5 hours
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 −
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.
- How can we set up a MySQL User account by using SQL GRANT statement?
- How can we change MySQL user password by using the SET PASSWORD statement?
- How can we change MySQL user password by using the ALTER USER statement?
- How can we create a MySQL user account by omitting the hostname?
- How can we change MySQL user password by using UPDATE statement?
- How can we insert data into a MySQL table?
- How can we use a MySQL subquery with INSERT statement?
- How can we insert data into an existing MySQL table by using PHP script?
- How can we use SET statement to assign a SELECT result to a MySQL user variable?
- How can we insert a new row into a MySQL table?
- How can we specify default values in MySQL INSERT statement?
- How can we divide the result set returned by MySQL into groups?
- How can we upload data into MySQL tables by using mysqlimport?
- How to set the local user account settings using PowerShell?
- How can we use WHERE clause with MySQL INSERT INTO command?