MySQL – Managing Users from Console

MySQL is packaged with a powerful security model, capable of controlling practically every conceivable user action, ranging from which commands he can execute to how many queries he can execute in an hour.

This works in a two-steps:

1) Authentication :- The user’s host, username, and password are examined. If a match is made within MySQL’s privilege tables, the user is authorized. Otherwise, the user’s connection attempt is denied.
2) Authorization :- Once authenticated, the user’s submitted command is examined and compared against the user’s defined privileges, also found in MySQL’s privilege tables. If the user has sufficient privileges, the command is executed, otherwise it is denied.

Creating a New User Account

To create a new user in server, “CREATE USER” command is used. Please note that if you create the user with this command, no privileges are assigned to him.

Sample commands:

prompt> CREATE USER 'demouser'@'localhost';
prompt> CREATE USER 'demouser'@'localhost' IDENTIFIED BY 'password';
MySQL create new user
MySQL create new user

Changing a Password

To change the password for a user, use “SET PASSWORD” command.

Sample command:

prompt> SET PASSWORD FOR 'demouser'@'localhost' = PASSWORD('newpassword');

Renaming Users

To rename a user, use “RENAME USER” command.

Sample command:

prompt> RENAME USER 'demouser'@'localhost' TO 'superdemouser'@'localhost';

Deleting a User Account

To delete a user, use “DROP USER” command.

Sample command:

prompt> DROP USER 'superdemouser'@'localhost';
MySQL User set password + rename + drop
MySQL User set password + rename + drop

Granting Privileges

For granting privileges to a user, use “GRANT” command.

Sample commands:

prompt> GRANT SELECT ON test.* TO 'demouser'@'localhost';
prompt> GRANT SELECT, UPDATE ON test.* TO 'demouser'@'localhost';
prompt> GRANT SELECT, DELETE ON test.* TO 'demouser'@'localhost';
MySQL Grant Privileges example
[]MySQL Grant Privileges example
C:Usersuser>mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.1.41-community MySQL Community Server (GPL)

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> CREATE USER 'demouser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON test.* TO 'demouser'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> Ctrl-C -- exit!
Bye

C:Usersuser>mysql -u demouser -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 8
Server version: 5.1.41-community MySQL Community Server (GPL)

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> SELECT * FROM EMPLOYEE;
ERROR 1046 (3D000): No database selected
mysql> USE TEST;
Database changed
mysql> SELECT * FROM EMPLOYEE;
+----+-----------+----------+-----------+-------+---------------------+
| ID | FIRSTNAME | LASTNAME | TELEPHONE | EMAIL | CREATED             |
+----+-----------+----------+-----------+-------+---------------------+
|  1 | lokesh    | gupta    | 1234567890| a@b.co| 2013-09-24 22:57:40 |
+----+-----------+----------+-----------+-------+---------------------+
1 row in set (0.00 sec)

mysql> DELETE FROM EMPLOYEE;
ERROR 1142 (42000): DELETE command denied to user 'demouser'@'localhost' for table 'employee'
mysql>

Granting Table- and Column-specific Privileges

For granting privileges on a particular tablee to a user, use “GRANT” command with table name.

Sample command:

prompt> GRANT INSERT ON test.EMPLOYEE TO 'demouser'@'localhost';
prompt> GRANT INSERT, UPDATE ON test.EMPLOYEE TO 'demouser'@'localhost';

For granting privileges on a particular column(s) of table to a user, use “GRANT” command with table name and column name.

Sample command:

prompt> GRANT INSERT (FIRSTNAME), SELECT (FIRSTNAME) ON test.EMPLOYEE TO 'demouser'@'localhost';

Revoking Privileges

For revoking access to a user, use “REVOKE” command with table name.

Sample command:

prompt> REVOKE INSERT ON test.EMPLOYEE TO 'demouser'@'localhost';

Happy Learning !!

Was this post helpful?

Join 7000+ Fellow Programmers

Subscribe to get new post notifications, industry updates, best practices, and much more. Directly into your inbox, for free.

Leave a Comment

HowToDoInJava

A blog about Java and its related technologies, the best practices, algorithms, interview questions, scripting languages, and Python.