MySQL Edit Restricted User

There are three methods for editing a restricted user; you can use a command window, phpMyAdmin or UniController. 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

Server Configuration > MySQL > Edit Restricted MySQL User

Edit Restricted User

  • 1) Select user to edit from the list, which populates the upper form
  • 3) Enter the old 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 user to be deleted from the list.
  • 7) Click "Delete" button

Note 1: Cancel (8) and (9) clear both the edit fields and the user selection.

Edit Restricted MySQL User

Edit Restricted MySQL User using phpMyAdmin

Start UniController 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 command window

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. Click MySQL console, which opens a command window. Then we run the MySQL Client with the following commands:

  • mysql -h127.0.0.1 -uroot -proot
  • 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.
C:\UniServer\usr\local\mysql\bin>mysql -h127.0.0.1 -uroot -proot

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 151
Server version: 5.5.20 MySQL Community Server (GPL)
Copyright (c) 2000, 2011, 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 fred@127.0.0.1                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| 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 fred@127.0.0.1                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| 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 fred@127.0.0.1                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| 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:\UniServer\usr\local\mysql\bin>

Related topics

MySQL Console