In this recipe, 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 complete this recipe, 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 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 recipe.
root
(use the password created in the previous recipe):mysql -u root -p
MariaDB [(none)]>
<database-name>
value using:CREATE DATABASE <database-name> CHARACTER SET utf8 COLLATE utf8_general_ci;
<username>
, <password>
, and <database-name>
to reflect your needs:GRANT ALL ON <database-name>.* TO '<username>'@'localhost' IDENTIFIED BY '<password>' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EXIT;
<username>
by accessing the MariaDB shell from the command-line in the following way:mysql -u <username> -p
MariaDB [(none)]>
), type the following commands:SHOW DATABASES; EXIT;
During the course of this recipe 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 recipe 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 historic 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 recipe 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-typesCREATE
: Allows the <username>
value to create new tables or databasesDROP
: Allows the <username>
value to delete tables or databasesDELETE
: Allows the <username>
value to delete rows from tablesINSERT
: Allows the <username>
value to insert rows into tablesSELECT
: Allows the <username>
value to read from tablesUPDATE
: Allows the <username>
value to update table rowsHowever, once the privileges were granted, the recipe 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 advices, you can be confident that your MariaDB installation will remain safe and secure.
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.
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 recipe 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.