How to Create User, Database with SQL in MariaDB on Ubuntu 16.04

November 17, 2016 | By in UBUNTU HOWTO
| Reply More

MariaDB is an open-source MySQL project with more improved features. It is highly compatible with MySQL and it can be considered as a drop-in replacement for MySQL.  All client libraries, client-server protocols, SQL dialect, MySQL replication, Master-slave configuration etc are quite similar to MySQL.

In this article, I'll explain some of the basic SQL commands in MariaDB. Let's walk through these commands one by one.

Basic SQL statements

This includes an important list of commands which are used for data manipulation, storing and other transactions.

Commands for Data Manipulation

1) CREATE DATABASE : This creates a database with the given name.  We can use the clause IF EXISTS/IF NOT EXISTS with these command to return warnings with details rather than errors while executing.

MariaDB [(none)]> CREATE DATABASE docker;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS docker;
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [(none)]> show warnings;
+-------+------+-------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------+
| Note | 1007 | Can't create database 'docker'; database exists |
+-------+------+-------------------------------------------------+
1 row in set (0.00 sec)

2) DROP DATABASE : This drops all tables in the database and deletes the database. When a database is dropped, user privileges on the database are not automatically dropped.

MariaDB [(none)]> drop database docker;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> drop database docker;
ERROR 1008 (HY000): Can't drop database 'docker'; database doesn't exist

3) USE :  This is used to select a database for subsequent statements. The database remains the same until a next use statement is executed.

Syntax USE db_name;

MariaDB [(none)]> use docker;
Database changed

4) CREATE TABLE: It is used to create a new table, where you store your actual data inside the database.

MariaDB [docker]> create table t1 (a int check(a>0) ,b int check (b> 0), constraint abc check (a>b));
Query OK, 0 rows affected (0.01 sec)

5) ALTER TABLE : It enables you to change the structure of an existing table. We can modify your existing table using this command. You can add/delete columns, create/destroy indexes, change the type of existing columns, or rename columns or the table itself using this. It can force  MariaDB to re-build the table. You can view this example in which we're altering the table engine from InnoDB to MyISAM

MariaDB [docker]> show table status;
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| t1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2016-10-31 06:49:41 | NULL | NULL | utf8mb4_general_ci | NULL | | |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.01 sec)

MariaDB [docker]> ALTER TABLE t1 ENGINE = MyISAM;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [docker]> show table status;
+------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| t1 | MyISAM | 10 | Fixed | 0 | 0 | 0 | 2533274790395903 | 1024 | 0 | NULL | 2016-10-31 08:23:25 | 2016-10-31 08:23:25 | NULL | utf8mb4_general_ci | NULL | | |
+------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.00 sec)

6) DROP TABLE : removes one or more tables. When a table is dropped, user privileges on the table are not automatically dropped.

Syntax : drop table table_name;

MariaDB [docker]> drop table t2, t3;
Query OK, 0 rows affected (0.02 sec)

7) DESCRIBE : It  provides information about the columns in a table. This displays complete information about a table including  the data entered in each column. It is a shortcut for SHOW COLUMNS FROM.

Syntax:

{DESCRIBE | DESC} tbl_name [col_name | wild]

MariaDB [mydatabase]> desc mytable;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+---

8) INSERT :  This statement inserts new rows into an existing table. Let's create a table named "products" and insert some data into that.

MariaDB [mydatabase]> CREATE TABLE IF NOT EXISTS products (
-> productID INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> productCode CHAR(3) NOT NULL DEFAULT '',
-> name VARCHAR(30) NOT NULL DEFAULT '',
-> quantity INT UNSIGNED NOT NULL DEFAULT 0,
-> price DECIMAL(7,2) NOT NULL DEFAULT 99999.99,
-> PRIMARY KEY (productID)
-> );
Query OK, 0 rows affected (0.01 sec)

First, insert the data into the first row with the column values.

MariaDB [mydatabase]> INSERT INTO products VALUES (1001, 'PEN', 'Pen Red', 5000, 1.23);
Query OK, 1 row affected (0.00 sec)

We can insert multiple row values in one statement as below.

MariaDB [mydatabase]> INSERT INTO products VALUES
-> (NULL, 'PEN', 'Pen Blue', 8000, 1.25),
-> (NULL, 'PEN', 'Pen Black', 2000, 1.25);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

Adding Null values to an auto_increment column will result in a max value of +1. We can even insert values to a specific column using this. A missing entry for an auto_increment column will automatically add a +1 value to the count.

MariaDB [mydatabase]> INSERT INTO products (productCode, name, quantity, price) VALUES
-> ('PEC', 'Pencil 2B', 10000, 0.48),
-> ('PEC', 'Pencil 2H', 8000, 0.49);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

While inserting values to a specified column, missing entries will be filled with its default value. Check out the entry for the last product.

MariaDB [mydatabase]> INSERT INTO products (productCode, name) VALUES ('PEC', 'Pencil HB');
Query OK, 1 row affected (0.00 sec)

We can view our table contents below:

MariaDB [mydatabase]> SELECT * FROM products;
+-----------+-------------+-----------+----------+----------+
| productID | productCode | name | quantity | price |
+-----------+-------------+-----------+----------+----------+
| 1001 | PEN | Pen Red | 5000 | 1.23 |
| 1002 | PEN | Pen Blue | 8000 | 1.25 |
| 1003 | PEN | Pen Black | 2000 | 1.25 |
| 1004 | PEC | Pencil 2B | 10000 | 0.48 |
| 1005 | PEC | Pencil 2H | 8000 | 0.49 |
| 1006 | PEC | Pencil HB | 0 | 99999.99 |
+-----------+-------------+-----------+----------+----------+
6 rows in set (0.00 sec)

MariaDB [mydatabase]>

We can even remove an unwanted  row content  with this statement below:

MariaDB [mydatabase]> delete from products where productID = 1006;
Query OK, 1 row affected (0.01 sec)

MariaDB [mydatabase]> SELECT * FROM products;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name | quantity | price |
+-----------+-------------+-----------+----------+-------+
| 1001 | PEN | Pen Red | 5000 | 1.23 |
| 1002 | PEN | Pen Blue | 8000 | 1.25 |
| 1003 | PEN | Pen Black | 2000 | 1.25 |
| 1004 | PEC | Pencil 2B | 10000 | 0.48 |
| 1005 | PEC | Pencil 2H | 8000 | 0.49 |
+-----------+-------------+-----------+----------+-------+
5 rows in set (0.00 sec)

Help Command

The "HELP" command can be used in any MariaDB client to get basic syntax help and a short description for most commands and functions. We can use help contents to see the list of help categories.

MariaDB [mydatabase]> help contents;
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
Account Management
Administration
Compound Statements
Data Definition
Data Manipulation
Data Types
Functions
........

MariaDB [mydatabase]> help Account Management;
You asked for help about help category: "Account Management"
For more information, type 'help <item>', where <item> is one of the following
topics:
CREATE USER
DROP USER
GRANT
RENAME USER
REVOKE
SET PASSWORD

You can even seek help searching for a particular string like create or grant. It will provide a complete guide on that SQL command usage.

MariaDB [mydatabase]> help create;
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
CREATE DATABASE
CREATE EVENT
CREATE FUNCTION
CREATE FUNCTION UDF
CREATE INDEX
CREATE PROCEDURE
CREATE SERVER
CREATE TABLE
.........

MariaDB [mydatabase]> help CREATE DATABASE;
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...

 

 

The GRANT statement grants privileges to MySQL user accounts. GRANT also serves to specify other account characteristics such as the use of secure connections and limits on access to server resources. To use
GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting.

Normally, a database administrator first uses CREATE USER to create an account, then GRANT to define its privileges and characteristics. For example:

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

However, if an account named in a GRANT statement does not already exist, GRANT may create it under the conditions described later in the discussion of the NO_AUTO_CREATE_USER SQL mode.

The REVOKE statement is related to GRANT and enables administrators to remove account privileges.

When successfully executed from the mysql program, GRANT responds with Query OK, 0 rows affected. To determine what privileges result from the operation, use SHOW GRANTS.

Commands used for Account Management

Here are some of the command which is used for managing the MariaDB accounts.

9) CREATE USER:  This statement creates new MariaDB accounts. In order to use this command, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database. For each account, CREATE USER creates a new row in the mysql.user table that has no privileges.

The account can be given a password with the optional IDENTIFIED BY clause. To specify the password in plain text, you can omit the PASSWORD keyword. To specify the password as the hashed value as returned by the PASSWORD function, include the PASSWORD keyword.

If you do not specify a password with the IDENTIFIED BY clause, the user will be able to connect without a password. A blank password is not a wildcard to match any password. The user must connect without providing a password if no password is set.

Syntax :

CREATE [OR REPLACE] USER [IF NOT EXISTS]
user_specification [,user_specification] ...
[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
[WITH resource_option [resource_option] ...]

user_specification:
user
[
IDENTIFIED BY [PASSWORD] 'password'
| IDENTIFIED WITH auth_plugin [AS 'auth_string']
]

ssl_option:
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'

with_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count

The GRANT statement grants privileges to MySQL user accounts. GRANT also serves to specify other account characteristics such as use of secure connections and limits on access to server resources. To use
GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting. Let's see how I added a user with a plain password and assigned some resource limit to that.

MariaDB [(none)]> create user saheadmin identified by 'password';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> GRANT USAGE ON *.* TO 'saheadmin'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
Query OK, 0 rows affected (0.00 sec)

10) DROP USER :  This statement removes one or more MariaDB accounts. It removes privilege rows for the account from all grant tables. To use this statement, you must have the global CREATE USER privilege or the DELETE privilege for the mysql database. Each account is named using the same format as for the CREATE USER statement.

MariaDB [mysql]> drop user saheadmin;
Query OK, 0 rows affected (0.00 sec)

11) GRANT : This statement allows you to create MariaDB user accounts and to grant privileges (assign roles) to accounts. To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting.

There are different privilege levels which can be set using the GRANT option. Please see the details below:

Privileges can be set globally, for an entire database, for a table, or for individual columns in a table. Certain privileges can only be set at certain levels.

Global privileges are granted using *.* for priv_level. Global privileges include privileges to administer the database and manage user accounts, as well as privileges for all tables, functions, and procedures. Global privileges are stored in the mysql.user table.

Database privileges are granted using db_name.* for priv_level, or using just * to use default database. Database privileges include privileges to create tables and functions, as well as privileges for all tables, functions, and procedures in the database. Database privileges are stored in the mysql.db table.

Table privileges are granted using db_name.tbl_name for priv_level or using just tbl_name to specify a table in the default database. The TABLE keyword is optional. Table privileges include the ability to select and change data in the table. Certain table privileges can be granted for individual columns.

Column privileges are granted by specifying a table for priv_level and providing a column list after the privilege type. They allow you to control exactly which columns in table users can select and change.

Function privileges are granted using FUNCTION db_name.routine_name for priv_level, or using just FUNCTION routine_name to specify a function in the default database.

Procedure privileges are granted using PROCEDURE db_name.routine_name for priv_level, or using just PROCEDURE routine_name to specify a procedure in the default database.

You can refer this Manual for more options with examples.

12) RENAME USER : This can be used to rename a MariaDB account.

Syntax :

RENAME USER old_user TO new_user
[, old_user TO new_user] ...

I created two users namely Vicky a& Sara and renamed  it to Vik and Sarah. Once modified, we can confirm its status in the MySQL user tables.

MariaDB [mysql]> rename user 'vicky' to 'vik'@'localhost', 'sara' to 'sarah'@'localhost';
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> use mysql; select * from user;

13) SET PASSWORD :  This statement assigns a password to an existing MariaDB user account. Password can be specified using the PASSWORD() or OLD_PASSWORD() function. We can give the literal text of the password as such when using these functions. If the password is specified without using either function, the password should be the already-encrypted password value as returned by PASSWORD(). OLD_PASSWORD() should only be used for very old MySQL/MariaDB versions(< 4.0.0).

MariaDB [(none)]> set password for 'saheadmin'@'localhost' = Password('sahe123');
Query OK, 0 rows affected (0.00 sec)

The PASSWORD() function returns a 41 string hash.

14) CREATE ROLE :  This statement is used to create one or more MariaDB roles. You need to have the global create user and insert privilege for this purpose.  On adding a role, a new column is added to the MySQL.user table with no privilege, and with the corresponding is_role field set to Y. We need to login to a privileged user and run this command to create your preferred role.

Syntax :

CREATE [OR REPLACE] ROLE [IF NOT EXISTS] role
[WITH ADMIN
{CURRENT_USER | CURRENT_ROLE | user | role}]

MariaDB [(none)]> create role admin;
Query OK, 0 rows affected (0.00 sec)

With Admin option:

The optional WITH ADMIN clause determines whether the current user/role or another user/role has use of the newly created role. By default, it takes WITH ADMIN CURRENT_USER , which means that the current user will be able to GRANT this role to users.

MariaDB [(none)]> create role sysadmin with admin saheadmin;
Query OK, 0 rows affected (0.01 sec)

15) DROP ROLE : This statement is used to remove one or more MariaDB roles from the server. It won't remove or disable a previously assigned role for a user with SET ROLE usage. Therefore,  if the role is subsequently recreated and granted, it will again be used as user's default. We need to completely remove the record of the default role from the mysql user.table using SET ROLE NONE.

MariaDB [(none)]> drop role supportadmin;
Query OK, 0 rows affected (0.01 sec)

16) SET ROLE :  This statement enables a role, along with all of its  permissions, for the current session. I created a role for a one of my user 'saheadmin' and grant privileges for that user to manage the role.

root@linoxide-mariadb:~# mysql -u saheadmin -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 76
Server version: 10.0.27-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> set role sysadmin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select current_role;
+--------------+
| current_role |
+--------------+
| sysadmin |
+--------------+
1 row in set (0.00 sec)

To remove a particular role from the server as a whole, we need to use drop role 'rolename' alongside with set role 'none'. This will explicitly remove a role from the server.

MariaDB [(none)]> set role none;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select current_role;
+--------------+
| current_role |
+--------------+
| NULL |
+--------------+

17) SHOW GRANTS : This statement list out the GRANT statements or privileges added to the users/roles.

MariaDB [(none)]> show grants for 'saheadmin'@'localhost';
+-----------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for saheadmin@localhost |
+-----------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'saheadmin'@'localhost' IDENTIFIED BY PASSWORD '*2DDFCC8B407A5C3798347782FC76BF0367586AD5' WITH MAX_QUERIES_PER_HOUR 90 |
+-----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> show grants for saheadmin;
+-----------------------------------------------------+
| Grants for saheadmin@% |
+-----------------------------------------------------+
| GRANT sysadmin TO 'saheadmin'@'%' WITH ADMIN OPTION |
| GRANT USAGE ON *.* TO 'saheadmin'@'%' |
+-----------------------------------------------------+
2 rows in set (0.00 sec)

It can also be used to view the privileges granted to a role. This is how we can view the grant privileges for a role.

MariaDB [(none)]> show grants for sysadmin;
+----------------------------------+
| Grants for sysadmin |
+----------------------------------+
| GRANT USAGE ON *.* TO 'sysadmin' |
+----------------------------------+
1 row in set (0.00 sec)

Conclusion

MariaDB is becoming a lot more popular than MySQL and  even non-Oracle fans are replacing MySQL as fast as they can. This article will give you a quick start with MariaDB and make you comfortable with MariaDB commands .  You can visit MariaDB official page to learn more options and usages about these commands. I hope you enjoyed reading this article. Please post your valuable comments and suggestions on this.

Filed Under : OPEN SOURCE TOOLS, UBUNTU HOWTO

Tagged With : ,

Free Linux Ebook to Download

Leave a Reply

Commenting Policy:
Promotion of your products ? Comment gets deleted.
All comments are subject to moderation.