In this process, we will learn how to create a new database and database user for the MariaDB server. MariaDB can be used in conjunction with a wide variety of graphical tools (for example, the free MySQL Workbench), but in situations where you simply need to create a database, provide an associated user, and assign the correct permissions, it is often useful to perform this task from the command line. Known as the MariaDB shell, this simple interactive and text based-command line facility supports the full range of SQL commands and affords both local and remote access to your database server. The shell provides you with complete control over your database server, and for this reason, it represents the perfect tool for you to start your MariaDB work.
To Start With: What Do You Need?
To complete this process, you will require a working installation of the CentOS 7 operating system. It is expected that a MariaDB server is already installed and running on your server.
The Process
The MariaDB command-line tool supports executing commands in both the batch mode (reading from a file or standard input) and interactively (typing in statements and waiting for the results). We will use the latter in this process.
- To begin, log in on your CentOS 7 server with any system user you like and type the following command in order to access the MariaDB server using the MariaDB shell with the main MariaDB administration user called root (use the password created in the previous process):
mysql -u root -p - On successful login, you will be greeted with the MariaDB command-line interface. This feature is signified by the MariaDB shell prompt:
MariaDB [(none)]> - In this first step, we will create a new database. To do this, simply customize the following command by substituting an appropriate value for the new <databasename> value using:
CREATE DATABASE <database-name> CHARACTER SET utf8 COLLATE utf8_general_ci;Note
If this is your first introduction to the MariaDB shell, remember to end each line with a semi-colon (;) and press the Enter key after typing each command. - Having created our database, we will now create a MariaDB user. Each user will consist of a username and a password that is completely independent of the operating system’s user. For reasons of security, we will ensure that access to the database is restricted to localhost only. To proceed, simply customize the following command by changing the values <username>, <password>, and <database-name> to reflect your needs:
GRANT ALL ON <database-name>.* TO '<username>'@'localhost' IDENTIFIED BY '<password>' WITH GRANT OPTION; - Next, make the MariaDB DBMS aware of your new user:
FLUSH PRIVILEGES; - Now simply type the following command to exit the MariaDB shell:
EXIT; - Finally, you can test the accessibility of your new <username> by accessing the MariaDB shell from the command-line in the following way:
mysql -u <username> -p - Now back at the MariaDB shell (MariaDB [(none)]>), type the following commands:
SHOW DATABASES;
EXIT;
How Does It Work?
During the course of this process, you were shown not only how to create a database, but also how to create a database user.
So what did we learn from this experience?
We started the process by accessing the MariaDB shell as the root user with the mysql command. By doing this, we were then able to create a database with a simple SQL function called CREATE DATABASE, providing a custom name for the <database-name> field. We also specified utf8 as the character set of our new database together with a utf8_general_ci collation. A character set is how the characters are encoded in the database and a collation is a set of rules for comparing the characters in a character set. For historical reasons and to keep MariaDB backward-compatible with the older server versions, the default character set is latin1 and latin1_swedish_ci, but for any modern databases, you should always prefer to use utf-8 instead as it is the most standard and compatible encoding for international character sets (non-English alphabets). However, this command can be modified to invoke the need to check if a database name is already in use by using: CREATE DATABASE IF NOT EXISTS <database-name>. In this way, you can then drop or remove a database by using the following command:DROP DATABASE IF EXISTS <database-name>;
Having done this, it is simply a matter of adding a new database user with the appropriate permissions by running our GRANT ALL command. Here we provided <username> with full privileges via a defined <password> for localhost. As a specific <database-name> was elected, then this level of permission will be restricted to that particular database and using <database-name>.* allows us to specify these rules to all the tables (using the asterisks symbol) in this database. The general syntax in order to provide a chosen user with specific permission is:GRANT [type of permission] ON <database name>.<table name> TO '<username>'@'<hostname>';
For security reasons, here in this process, we limit <hostname> to localhost but if you want to grant permissions to remote users you will need to change this value (see later). In our example, we set [type of permission] to ALL but you can always decide to minimize the privileges by providing a single or a comma-separated list of privilege-types offered in the following way:GRANT SELECT, INSERT, DELETE ON <database name>.* TO '<username>'@'localhost';
Using the previous technique, here is a summary of the permissions that can be employed:
- ALL: Allows the <username> value with all available privilege-types
- CREATE: Allows the <username> value to create new tables or databases
- DROP: Allows the <username> value to delete tables or databases
- DELETE: Allows the <username> value to delete rows from tables
- INSERT: Allows the <username> value to insert rows into tables
- SELECT: Allows the <username> value to read from tables
- UPDATE: Allows the <username> value to update table rows
However, once the privileges were granted, the process then showed you that we must FLUSH the system in order to make our new settings available to the system itself. It is important to note that all commands within the MariaDB shell should end in a semicolon (;). Having completed our task, we simply exit the console using the EXIT; statement.
MariaDB is an excellent database system but like all services, it can be abused. So remain vigilant at all times, and by considering the previous advice, you can be confident that your MariaDB installation will remain safe and secure.
There's more…
Creating a restricted user is one way of providing database access but if you have a team of developers who require constant access to a development server, you may wish to consider providing a universal user who maintains superuser privilege. To do this, simply login to the MariaDB shell with your administrator user root, then create a new user in the following way:GRANT ALL ON *.* TO '<username>'@'localhost' IDENTIFIED BY '<password>' WITH GRANT OPTION;
By doing this, you will enable <username> to add, delete, and manage databases across your entire MariaDB server (the asterisks in *.* tell MariaDB to apply the privileges to all the databases and all their associated tables found on the database server), but given the range of administrative features, this new user account will restrict all activities to localhost only. So in simple terms, if you want to provide <username> with access to any database or to any table, always use an asterisk (*) in place of the database name or table name. Finally, every time you update or change a user permission, always be sure to use the FLUSH PRIVILEGES command before exiting the MariaDB shell with the EXIT; command.
Reviewing and revoking permissions or dropping a user on CentOS
It is never a good idea to keep user accounts active unless they are used, so your first consideration within the MariaDB shell (login with your administrator user root) will be to review their current status by typing:SELECT HOST,USER FROM mysql.user WHERE USER='<username>';
Having done this, if you intend to REVOKE permission(s) or remove a user listed here, you can do this with the DROP command. First of all, you should review what privileges the user of interest has by running:SHOW GRANTS FOR '<username>'@'localhost';
You now have two options, starting with the ability to revoke the user’s privileges as follows:REVOKE ALL PRIVILEGES, GRANT OPTION FROM '<username>'@'localhost';
Then you may either reallocate the privilege using the formula provided in the main process or alternatively, you can decide to remove the user by typing:DROP USER '<username>'@'localhost';
Finally, update all your privileges the usual way using FLUSH PRIVILEGES; before exiting the shell EXIT; command.
