Introduction on user privileges

Information about user privileges is stored in the user, db, host, tables_priv, and columns_priv tables in the mysql database (that is, in the database named mysql). The MySQL server reads the contents of these tables when it starts up.

MySQL access control would involve two stages:

Stage 1: The server checks whether your desktop (host address or IP address) is allowed to connect.
Stage 2: Assuming you can connect, the server checks each request you issue to see whether you have sufficient privileges to perform it. For e.g, Create table privilege, Drop table privilege or Alter table privilege.

MySQL server uses the User, Db, and Host tables in the Mysql database at both stages of access control.

You can use Navicat's Manage Users Window to set up the security of your MySQL server. The Manage Users window contains two tabs; User Privileges and Host Privileges. On the User Privileges tab one can create and remove users from the MySQL security tables as well as setting their privileges on any level (global, database, table and column) of the MySQL security scheme.

User Privileges

The tree view on the left-hand side displays all the user/host combinations that exist in the user table. The tree view provides a drill down into the db, tables_priv and columns_priv tables (see setting a user's privileges below).

Create a new user

1. Click the Add User button on the toolbar
   
2. Enter the user name
   
3. Enter the user host
   
4. Enter the user's password and click OK

Remove an existing user

1. Select the user in the tree view
   
2. Click on the Delete User button.
   
3. Navicat will confirm if you would like to remove the user@host from the other security tables (db, tables_priv and columns_priv) as well. Select Yes if you want to delete the user from the other security tables.

Setting a user's privileges

1. Select the user whose privileges must be changed. The user's Global Privileges will be displayed on the right hand side of the window
   
2. Set the user's global privileges as required
   
3. If the user's database privileges must be changed, Double Click on the user. A list of databases on the server will be displayed below the user. Select the database on which the user's privileges must be changed and click the Set Privileges button on the toolbar
   
4. Set the user's database privileges as required
   
5. Follow the same procedure for setting a table and column privileges.

Host Privileges

This tab shows the host table. The tree view on the left-hand side displays all the host/database combinations that exist in the host table.

Create a new host

1. Click the Add Host button on the toolbar
   
2. Enter the Host and press TAB
   
3. Enter the Database and click OK
   
   

Remove an existing host

1. Select the host in the tree view
   
2. Click on the Delete Host button

Setting a host's privileges

1. Select the host whose privileges must be changed. The host privileges will be displayed on the right hand side of the window
   
2. Set the host privileges as required