Creating and editing users in MySQL from shell prompt

Creating and editing users in MySQL from shell prompt.

First we need to login into MySQL server as root.

mysql -u root -p

Will be prompted for your MySQL root password (note this is not the same as the server root password).

mysql> create user ‘buddy@localhost’ identified by ‘new-password’;
Query OK, 0 rows affected (0.12 sec)

Next we need to flush the privileges which reloads the ‘user’ table in MySQL - do this each time you add or edit users.

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

To give the user buddy select permission on all the databases, this allows the user to read, but not edit and delete.

mysql> grant select on *.* to ‘buddy’@'localhost’;
Query OK, 0 rows affected (0.00 sec)

The GRANT statement enables system administrators to create MySQL user accounts and to grant rights to accounts. To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting. The REVOKE statement is related and enables administrators to remove account privileges.

Tags: , , , , ,

Trackback URI | Comments RSS

Leave a Reply

You must be logged in to post a comment.