Zero logo

MySQL Edit Restricted User

There are three methods for editing a restricted user; you can use UniController, phpMyAdmin or MySQL Prompt. UniController provides a convenient menu option described bellow. To use this option ensure the MySQL server is running otherwise a warning message is produced.

Edit Restricted MySQL User using UniController

MySQL > Edit restricted MySQL User

Edit Restricted User

  • 1) Select the user to edit from the list. This populates the form.
  • 3) Enter the current or a new password for the user
  • 5) Select or deselect user privileges as required
  • 6) Click "Update User" button

Delete Restricted User

  • 1) Select the user to be deleted from list.
  • 7) Click "Delete" button


Note 1: Cancel (8) clears only the edit fields.
Note 2: Cancel (9) clears only the selected user.

 Edit Restricted MySQL User

Edit Restricted MySQL User using phpMyAdmin

Start Controller and start both servers, then click phpMyAmin button. To edit a restricted user, proceed as follows:

Note: Assume the user fred has already been created.

When first started the phpMyAdmin home page is displayed; you can
always return to this page by clicking the home icon (1)

  • 1) If not at the home page, click home icon
  • 2) From the top menu bar, select Privileges
  • 3) Under "Action", Click Edit Privileges
The Edit Privileges: User 'fred'@'127.0.0.1' page is displayed.
From this page you can change the user privileges, the database(s)
the user is assigned to and privileges, password and login information. 		Edit restricted user using phpMyAdmin

Edit Restricted MySQL User using MySQL Prompt

Editing an existing user with restricted privileges can be performed using the MySQL Client.

You can use REVOKE to remove some or all privileges or alternatively use GRANT to add additional privileges. This example assumes a user fred has been created with the privileges GRANT SELECT, INSERT, UPDATE, DELETE assigned on database wordpress.

You can revoke (remove) privileges; for example, the following command removes INSERT, UPDATE and DELETE.
  • REVOKE INSERT, UPDATE, DELETE ON wordpress.* FROM 'fred'@'127.0.0.1' ;
You can grant (add) privileges; for example, the following command adds INSERT and UPDATE.
  • GRANT INSERT, UPDATE ON wordpress.* TO 'fred'@'127.0.0.1' ;
You can display the grants assigned with the following command:
  • SHOW GRANTS FOR 'fred'@'127.0.0.1';

Example for the above commands. Open MySQL prompt window click MySQL Console button. Then enter the following commands:

  • SHOW GRANTS FOR 'fred'@'127.0.0.1';
  • REVOKE INSERT, UPDATE, DELETE ON wordpress.* FROM 'fred'@'127.0.0.1' ;
  • SHOW GRANTS FOR 'fred'@'127.0.0.1';
  • GRANT INSERT, UPDATE ON wordpress.* TO 'fred'@'127.0.0.1' ;
  • SHOW GRANTS FOR 'fred'@'127.0.0.1';
  • exit
The results are shown below. 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.5.33 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

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 GRANTS FOR 'fred'@'127.0.0.1';
+-------------------------------------------------------------------------------------------------------------+
| Grants for [email protected]                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'fred'@'127.0.0.1' IDENTIFIED BY PASSWORD '*F5F0B28BD93FCF0C77FD96BB97BBC745ED8EA6BC' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `wordpress`.* TO 'fred'@'127.0.0.1'                                 |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> REVOKE INSERT, UPDATE, DELETE ON wordpress.* FROM 'fred'@'127.0.0.1' ;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'fred'@'127.0.0.1';
+-------------------------------------------------------------------------------------------------------------+
| Grants for [email protected]                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'fred'@'127.0.0.1' IDENTIFIED BY PASSWORD '*F5F0B28BD93FCF0C77FD96BB97BBC745ED8EA6BC' |
| GRANT SELECT ON `wordpress`.* TO 'fred'@'127.0.0.1'                                                         |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> GRANT INSERT, UPDATE ON wordpress.* TO 'fred'@'127.0.0.1' ;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'fred'@'127.0.0.1';
+-------------------------------------------------------------------------------------------------------------+
| Grants for [email protected]                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'fred'@'127.0.0.1' IDENTIFIED BY PASSWORD '*F5F0B28BD93FCF0C77FD96BB97BBC745ED8EA6BC' |
| GRANT SELECT, INSERT, UPDATE ON `wordpress`.* TO 'fred'@'127.0.0.1'                                         |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> exit
Bye

C:\UniServerZ\core\mysql\bin>

Related topics

MySQL Prompt