Using WinMySQL Professional
Manage users privileges
Only accessible when connected to the database «mysql», this «Server administration>Manage users privileges» submenu allows a MySQL® DBA to add or revoke users and to change their password in a first screen, and allows a MySQL® DBA to manage users privileges on a second screen.
The first screen, immediately accessible when this feature is invoked, contains two parts. The «Users list» groupbox on the left part shows in a listbox, the already known users for the active MySQL® server ; the «User» groupbox on the right part allows a single user management.
If the current MySQL® server uses the old privileges system, you will be notified. So, if you don't want to be notified, then uncheck the submenu «Server management>Old privileges system warning».
How to add a user ?
In the «User» groupbox (on the «users management» screen, on the right), enter a new login name in the «username» field. To apply a normal security level, you should enter a password in the «Password» field, but it is not required.
Then, click on the «Add user button», WinMySQL Professional will try to add the current new user but won't refresh the MySQL® server to force to take effect immediately, because this user only has as privilege to exist and to connect to nothing from «localhost».
So, after a successfully user creation, WinMySQL Professional suggests to grant privileges to this user but you can refuse to do it later. If you accept, the second screen appears. See the section «How to grant or revoke privileges ?» below to know how to use this screen. If you have refused to enter this screen and you want this user creation takes effect immediately, use the «Server administration>Refresh privileges» submenu.
How to change a user password ?
In the users list (in the listbox of the first screen left part), click on the wanted user, his name appears into the username field, in the «user» groupbox. Then, enter a new password in the «Password» field and click on the «Change password» button. After having successfully changed the password, WinMySQL Professional will try to refresh privileges to force to take effect immediately.
Very important warning : WinMySQL Professional assumes that a user is the same user on all the machines where he connects from. So, it changes the password for the current user connecting from any machines. If you have a user called «bill» connecting from «msoft.com», he may be not the same as «bill» connecting from «whitehouse.gov» with perhaps a different password. WinMySQL Professional will assume both are the same user, and will change both password.
If «bill@msoft.com» and «bill@whitehouse.gov» are two distinct users, then DO NOT USE this feature to change passwords. In this case, you must manually change password using the SQL pseudo-terminal window (see «Submenu File>New SQL Query» section) with the following query :
update user set password=password('new_password')
where user='user_name'
and host='host_name'
and password=password('old_password')
To force the modification to take effect imediately, use the «Server administration>Refresh privileges» submenu
---------------------ooOoo---------------------
How to grant or revoke privileges ?
First, note there is a MySQL® default special user named «*** Any user ***». This user represents any user. It is a good idea, for security reasons that you may not grant any privilege on it.
To enter into the user privileges form, you may, as seen above, successfully create a user and accept to enter this screen, or you can select an existing user in the users list (in the listbox of the first screen left part) and then, click on the «Select» button.
This feature allows to manage privileges on two ranges, the general range and a specific database range.
The general range: If a privilege is checked, it overrides all specific directives. So, if the selected user is not a DBA, you should switch to «On a specific database» privilege range.
In the general range, select a hostname in the «Privileges available from host» combo box from where you want the privileges will be available for the current user and check or uncheck the privileges, then click on the «Update privileges for the current parameters» button. WinMySQL Professional will try to update the privileges and refresh it to take effect immediately.
If you wish apply privileges on an inexisting host in the combo box list, enter it in the combo box field and strike «Tab» key or exit from the combo box : WinMySQL Professional will ask a confirmation to add it. At the opposite, if you want to erase a hostname, select it and click on the «Del» button.
Note the host named «%» means «any host», «%.winmysql.com» means «any host in the domain winmysql.com» and «194.199.73.%» means «any host of the network 194.199.73.0 mask 255.255.255.0».
Before adding new hosts, you must know how works the MySQL® privileges system :
Let's get an example :
We add «%» in the host combo box field for user «alex» with only the «Select» privilege. This means user «alex» can «Select» on any database (hosted in the current MySQL® server, of course) from any host EXCEPTED from «localhost» (which required a specific privileges definition) and those already inserted in the combo box list. In this case, its respective privileges would apply (it may be more privileges than a «Select» or less ).
If «%» is alone in the combo box list, user «alex» can «Select» on any database from any host excepted from «localhost».
If there is a «%» with the «Select» privilege and a «hacker-center.naughty.org.» with no privilege, the user «alex» can «Select» on any database from any host excepted from «localhost» and from «hacker-center.naughty.org.».
In conclusion, privileges in the general range works with the general case minus the specific cases (except «localhost» ) and the general range overrides the specific database range, so if a user is not a DBA, you shouldn't grant any privileges to him switched on the general range.
The specific database range : It is the safest mode to grant (or revoke) privileges because it restricts users privileges to specific databases.
In this range, select a hostname in the «Privileges available from host» combo box from where you want the privileges will be available for the current user, select a database in the «Privileges on specific database» combo box that you want to restrict the privilege range to and check or uncheck the privileges, then click on the «Update privileges for the current parameters» button. WinMySQL Professional will try to update the privileges and refresh it to take effect immediately.
If you want to apply privileges on a non-existing database in the database combo box list, just enter it in the combo box field et strike the «Tab» key or exit from the combo box. WinMySQL Professional will ask a confirmation to add it. Do the same operations but with the hosts combo box if you want to add an inexisting host in the hosts combo box list. At the opposite, if you want to delete an existing host or an existing database in its respective combo box list, then select the item to erase and click on the «Del» button located on the right of the whole combo box.
Note, if you make an inexisting combination between the user, the hostname, and the database, WinMySQL Professional will ask if you want to add it.
Also note the «%» wild symbol seen above for the hostnames can either be used for the databasenames.
Conclusion : To manage properly MySQL® privileges, you may respect the following tips :
The user that you are managing is a DBA : give him all privileges from «%» and «localhost» switched on the general range and don't touch the database specific privileges range.
The user that you are managing is a normal user : give him no privileges from «%» and «localhost» (WinMySQL Professional does it by default on «localhost») or from «his_machine.his_domain.his_country» and «localhost» and nothing else switched on the general range and give him privileges according his job only on his related databases from «%» and «localhost» or from «his_machine.his_domain.his_country» and «localhost» and nothing else switched on the database specific range.