MySQL User Management: A Complete Guide

With the databases on your 20i Hosting packages, there will be a Default Database User that can be used in the majority of use cases. However, there are certain cases where you may look to create and use your own MySQL User. 

For example, the default database users do not provide full MySQL permissions, specifically, it lacks the permissions for creating and managing Events, the use of References or the setting of Triggers. The use of MySQL Users will allow you to enable these permission grants should they be required on your site. Usually, a lack of MySQL Permissions can be diagnosed when trying to execute MySQL queries as an error will be thrown such as: 

TRIGGER command denied to user 'ExampleUser'@'xxx.xxx.xxx.xxx' for table 'ExampleDatabase'.'ExampleTable'

REFERENCES command denied to user 'ExampleUser'@'xxx.xxx.xxx.xxx' for table 'ExampleDatabase'.'ExampleTable'

If you see these errors, or similar, then the use of a MySQL User with elevated permissions should resolve the issues you are facing.

On the other hand, MySQL Users can also allow you to limit the permissions to the database, so should you have a user or a piece of software that you only want to grant specific permissions to, then this can be achieved using a MySQL User. Overall MySQL Users are a good addition to your packages and can provide an extra level of security and accountability on your databases.

Creating MySQL Users

To create a MySQL User on your package you will first need to head to the Web Tools section of your package, from there you will want to navigate to MySQL Databases. Here you will be presented with two sections, the first being Managed MySQL Databases and the second being Add MySQL User, this is where you will be able to create your MySQL Users.

You will first need to set a Username for your MySQL User, this can be anything you would like. The next section, Database, will allow you to select which Database this MySQL User has access to. Finally is the Password section which will allow you to set the password your MySQL User will use. If you then press Create MySQL User your user will be created and you should see it appear under the Managed MySQL Users Section.

Managing MySQL Users

From within the Manage MySQL Users section, you can update the password of your user as well as manage the grants and permissions assigned to them. To manage the grants assigned to the user you will need to click on Options and then Managed Grants. You will then need to select which database you want to manage the grants for. This should then bring up the current grants for the user.

From here you will be able to remove any grants that are not wanted, or alternatively add those which the user may be missing. For example, by default, the Event, References and Trigger permissions are all disabled. If you click on Select All this will apply the maximum amount of grants applicable to your MySQL User. Alternatively, you can select certain grants to disable to restrict the access the user has to the database.  Once you have made the desired changes, you will then need to click on Save Privileges and then your MySQL User is ready to be used.