Manage Privileges

Creating a user does not create any privileges for the user. The user will be able to connect to the server, but almost only that. He cannot read data from or write to the database and hardly see any details at all. To empower the user privileges must be assigned to him.

The MySQL server handles privileges of a user on both the global level and almost any object level. Some privileges can be assigned on more levels optionally - other privileges are only possible on the global or a specific object-level (and some details in this respect also depend on the server version). SQLyog user mangement is context aware. SQLyog will list the possible privileges in the right area of the User Management dialog depending on server version and what object is currently selected in the left area. You will see the content on the right area change if you select the global level, a database, a table or a stored routine.

Once you have decided on what level the user should have specific privileges, select that level in the left area of the Usermanagment dialog and check the privileges that user should have on this level. Next click 'Save Changes'.

One important detail with MySQL user management is that it is possible to define redundant privileges. For instance it is possible to define database-level or table-level SELECT privilege to a user that already has SELECT privilege on the global level (and in that case all SELECT privileges that are not global are redundant). But privileges on each level are recorded and stored independently by the server and - what is particularly important - in the described situation revoking ('removing') the global SELECT privilege from the user does not automatically remove SELECT privilege on lower levels if such exist. So the user will still be able to SELECT from the database(s) or table(s) where he has object-level SELECT privilege. This has fooled quite a lot of people over time, some people's data have been compromised and you should be aware of that. But this is how the server-side implementation is with MySQL.

Global Privileges:

In order to assign Global Privileges to any user, select an existing user in the User drop-down, select the Global Privileges from the left pane. Check/Uncheck the required privileges and click on ‘Save Changes※.

Object Privileges:

In order to give object level privileges, select an existing user in the User drop-down, choose the object to which you want to grant/revoke privileges. Check/Uncheck the required privileges and click on ‘Save Changes※.

NOTE: To view objects on which the user has privileges, check Show only privileged objects at the bottom left corner.