Databases

Overview

The MG panel gives direct access to Maria DB. The interface allows you to manage databases of Internet sites. It is possible to create databases, delete them, add users with privileges, just to give an idea of the possibilities available to you.

In the following, we will limit ourselves to information related to the use for beginners. 

You may refer to the article PhpMyAdmin if deemed necessary.

Note : The PlanetHoster Glossary contains explanations on multiple topics and can be consulted to clarify certain terms.

Prerequisite

Access to https://mg.n0c.com/en/.

Concepts to be Known

Details on Users Privileges

Throughout this article, some English terms related to user privileges will come up often. We invite you to refer to the table below to understand their meaning.

PrivilegeDefinition
ALTERModify properties (ALTER AGGREGATE, ALTER CONVERSION, etc.).
ALTER ROUTINEModify the definition of a routine, that can be an aggregate function, a normal function or a procedure. Routines are SQL objects that allow defining a logic to apply to data.
CREATEDefine a table (CREATE TABLE), a new database (CREATE DATABASE), etc.
CREATE ROUTINEGroup stocked functions and procedures. 
CREATE TEMPORARY TABLESCreate a temporary table. A temporary table exists only in the session that created it. 
CREATE VIEWDefine a view. A view is a virtual table; in other words, it is a table whose data are not stored in the database, and containing informations from many tables.
DELETEDelete lines in a table. By using commands associated to WHERE, it is possible to select the lines that will be deleted.
DROPPermanently delete a table from a database (DROP TABLE), a role (DROP ROLE), etc.
EVENTAllow to obtain data to monitor or solve problems in SQL.
EXECUTEExecute a prepared instruction.
INDEXPerform commands associated to indexes such as CREATE INDEX. With an index placed on one or many columns, a database system can first search data on the index and, if what is searched is found, it knows more rapidly where are the records.
INSERTInsert data in a table.
LOCK TABLESLock a table.
REFERENCESThe reference permission on a table allows to create a foreign key that refers to this table; this way, administrators can perform required modifications without having to take into account the needs of the persons to whom the table has been referenced.
SELECTReturn the records in a result table. This command can select one or many columns in a table.
SHOW VIEWDisplay the value of a executions parameter.
TRIGGERExecute a set of SQL instructions right after an event.
UPDATEPerform modifications on existing lines. Often, this command is used with WHERE to specify on which lines the modification is applicable.
Privileges

Icons for Managing Passwords

When you have to create or change passwords, there are some icons that will be very useful. The table below explains their meaning.

IconDescription
1Hide (or display) password
2Password complexity (a red icon shows that the password is not secure enough).
3Generate a password.
4Copy the password.
Icons for managing passwords

SQL Databases Management

Access to the Interface

Click on Databases -> SQL Databases:

Please note that the CURRENT DATABASES are displayed at the top of the window and the CURRENT USERS are listed at the bottom.

Create an SQL Database with a New User

  1. Click on the Create button:
  1. Enter the name of the DATABASE:
  1. It is recommended to create a database user at this point. Check Create Database User checkbox.
  2. Provide the USERNAME:
  1. Provide the PASSWORD.
  2. Set the user’s privileges by checking or unchecking the appropriate boxes.
  3. Click on the CREATE button.

Create a Database User

  1. In the CURRENT USERS window, click on the Create button:
  1. Enter the USERNAME:
  1. Provide a PASSWORD.
  2. Choose the ASSOCIATED DATABASE.
  3. Define the privileges:
  1. Click on the CREATE button.
  2. Confirmation messages are displayed and the user is added to the list of CURRENT USERS.

Modify Usernames and/or Passwords

  1. Locate the database and click the Rename icon next to the user whose name or password you want to change:
  1. Enter the new USERNAME and/or PASSWORD in the appropriate field.
  1. Click on the SAVE button.
  2. Confirmation messages are displayed.

Add an User to a Database

Typically, several users will need to connect to the database. It is necessary to add them one by one.

  1. Locate the database and click on the Add another user icon:
  1. Choose the USER:
  1. Click on the ADD button.
  2. A confirmation message is displayed.
  3. The user is displayed alongside the database:

Manage Database User Privileges  

Anytime, it is possible to modify the privileges of a database user.

  1. Locate the database and click on the Modify User Privileges icon:
  1. Check or uncheck the appropriate boxes: 
  1. Click on the SAVE button.
  2. A confirmation message is displayed.

Delete Users 

It is advisable to delete users who no longer use your databases.

  1. Identify the user you wish to remove and click on the Delete icon:
  1. Approve the deletion at the command prompt.
  2. A confirmation message is displayed.

Remove a User’s Access to a Database

Il est essentiel de maintenir à jour l’accès des utilisateurs à vos bases de données.

  1. Locate the database and the user to which you want to remove all privileges and click on the Delete icon:
  1. Approve the deletion at the command prompt.
  2. A confirmation message is displayed.

Delete an SQL Database

It is recommended to delete your databases if they are no longer used.

  1. Identify the database and click on the Delete icon:

References

https://docs.planethoster.com/guide/astuces-techniques/importer-une-base-de-donnees-de-plus-de-50-mo-sur-un-compte-world-en-ssh (in French).

https://kb.n0c.com/knowledge-base/les-importations-exportations-des-bases-de-donnees-en-ligne-de-commande-mode-user/ (in French).

https ://docs.planethoster.com/guide/astuces-techniques/se-connecter-en-ftp-avec-le-logiciel-filezilla (in French).

https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver15 (in English).

Updated on March 30, 2022

Related Articles