Haliç University, Institute of Science
Department of Management Information Systems
Advanced Database Systems course: Master’s presentation report
Prepared by: Volkan Avşar · Supervisor: Asst. Prof. Dr. Yüksel Bal · Istanbul, 2017

Preface

Views are an essential part of database systems. Instead of working directly on large tables and getting lost in the data, views let us create smaller virtual tables from a large table and run queries, inserts and deletes on them. They are extremely useful virtual tables.

In this study, we look at how these virtual tables are used in a MySQL database, with sample applications and their results. Each topic is reinforced with an example at the end, and we tested these examples so that the subject could be fully understood.

I prepared this study between 2015 and 2017 with the support of the Management Information Systems department of Haliç University. I would like to thank my advisor, Asst. Prof. Dr. Yüksel Bal, for his contributions and support throughout my studies and thesis work, and my fellow master’s student Giray Bal for his help during this work. And of course, my endless thanks go to my family, who have always supported me, watched over me and worked so hard to help me get where I am today.

Abstract

Views are an essential part of database systems: very useful virtual tables that let you build smaller tables from large ones and run queries, inserts and deletes on them, instead of getting lost in the data of a large table.

In this work, we discuss how these virtual tables are used in a MySQL database, with sample applications and their results. Each topic is reinforced with an example, and we tested these examples to make sure the subject is well understood.

We used HeidiSQL as our database editing tool. You can download it from the internet and install it easily. In the presentation, we also cover its setup and view features, showing how to create, insert into and delete from views not only with code but also through shortcuts in the tool.

Keywords: Database, View, MySQL, HeidiSQL, Tables

1. Introduction

1.1. What is a view?

The main purpose of a view is to retrieve a set of data from tables and let us query the result again as if it were a virtual table. In short, in MySQL a view lets us display our data on screen based on the fields and tables we choose. Displaying or reporting data according to what the user needs is easy in MySQL thanks to views.

The database does not store the data produced by a view. Every time a MySQL statement that uses a view runs, the view rebuilds the virtual table. Changes made through a view affect the underlying source tables as well, and the same changes are reflected there. One important reason to use views is the security they provide: for example, when you don’t want all of a table to be visible, you can use a virtual table to hide the rest. Views are also used to simplify complex queries, shorten query times, reduce network traffic, organize access permissions and compare similar data across different servers.

1.2. Creating a view

A view can be created in two ways:

  1. From the view window in HeidiSQL (since we use HeidiSQL)
  2. By writing code in the query window

In HeidiSQL, when we click the view tab, we can write our query in the editor area. The results of the query appear on the Data tab, and the views we create are listed on the left side.

1.3. Creating a view with code

You can also create a view by writing a CREATE VIEW statement.

General syntax:

CREATE VIEW view_name AS
SELECT * FROM table_name;

The query we used in our example:

CREATE VIEW sonu18denbuyuk AS
SELECT * FROM viewornekleri
WHERE tckno > 12345678918;

When the query runs, it returns all rows in our table whose national ID number ends in a value greater than 18.

2. View capabilities

2.1. WITH CHECK OPTION

In some cases, when defining a view in MySQL Server, we may have specified a criterion in its body with the WHERE keyword. While using such a view, we can also insert rows into our tables through it. In practice, branches are often given a view to restrict which columns of certain tables they can see in the database, and they are asked to perform all their operations through that view. If a view is created with a WHERE condition, rows will be inserted into tables through that view, and those inserts must only be allowed if they satisfy the WHERE condition, then you need to add the WITH CHECK OPTION clause after the WHERE condition when defining the view. It is very simple to use: just write WITH CHECK OPTION after the WHERE criterion.

For example, if we add WITH CHECK OPTION to the end of the code that returns rows whose ID number ends in a value greater than 18, we can perform INSERT, DELETE and UPDATE operations through this view.

In the next example, we delete the row named “esile” from the “sonu18denbuyuk” view. Alternatively, using INSERT, we can add a row whose name is “hale”. As a result, the new row named “hale” is added both to the view and to the database.

We can also run an UPDATE. The following command changes rows whose name is “Ahmet” to “ahmetdegisti” in the “sonu18denbuyuk” view.

To delete a view, use the DROP statement.

A query like SELECT * FROM view_name returns all the data in the view, so any information we don’t want listed stays hidden. In the next example, we list the records we created earlier whose hometown is Ordu.

When creating a view from a base table, you don’t have to use the same column (field) names as the base table. For example, when creating the view named “farkli isimde view”, we can use “ad” instead of “adi”, “soyad” instead of “soyadi” and “yasi” instead of “yas” from the main table.

In complex queries, when the results of some SELECT statements are used by other SELECT statements, the risk of making mistakes when structuring the query increases. Complex queries can be simplified with views. The basic idea is this: since a view is a virtual table obtained by naming the result of a query, instead of nesting another SELECT inside a complex SELECT, you can name that result as a view and use the view’s name. In some cases, it is also possible to store complex questions that are asked very frequently in a company’s database application as views, and then use those views with simpler statements for the same type of query later.

For example, if we write a view in our “viewornekleri” table made of nested SELECTs and conditions such as:

  • tckno greater than 12345678915
  • name starts with “b”
  • surname starts with “s”
  • hometown is Ordu

then the next time, instead of writing these nested SELECT statements again, simply calling the view is enough.

For queries that require a two-step process to obtain information from a table, creating a view in the first step and running the actual query with the help of that view in the second step is often unavoidable. For example, in our “viewornekleri” table we created a separate view for people older than 40, then used this view in another query to calculate the average, giving us the average age of those over 40. Of course, this is a simple example, but there are many more possibilities.

3. Creating users and granting privileges

MySQL commands for creating, authorizing, deleting and listing users, and for viewing privileges, are as follows.

Creating a new user in MySQL:

CREATE USER 'korsan';

If you create a user without a password as above, anyone can access MySQL through the “korsan” user without a password.

This user has not yet been granted privileges on any database, so even if it connects to MySQL, it cannot work on any database. But if you forget to set a password and then grant it privileges on a database, the consequences can be bad.

You can also specify a host parameter for the users you create in MySQL. This setting answers the question “from where (which host) can the user connect?”

  • localhost: access only from the machine where MySQL is installed
  • %: access from any host
  • IP: access only from the IP addresses you specify

Let’s create a user that can only log in to MySQL from the machine MySQL runs on:

CREATE USER 'korsan'@'localhost' IDENTIFIED BY 'powerful_password';

If you create the user like this, you cannot access MySQL remotely with tools such as Navicat or Workbench; you can only access it from that machine.

Now let’s create a user that can access MySQL from outside:

CREATE USER 'korsan'@'%' IDENTIFIED BY 'another_password';

As you can see, we created two different users with the same username but different passwords: one can connect only locally with “powerful_password”, the other from outside with “another_password”.

You can list the users you have created with this query:

SELECT host, user FROM mysql.user;

There are two ways to delete the users you have created:

DROP USER korsan;

If you drop the user this way, you only delete the user created with “%”. Specifying the host, as below, is the safer approach:

DROP USER 'korsan'@'localhost';
DROP USER 'korsan'@'%';

You can also use DELETE instead of DROP, but this is not recommended. In fact, if you DELETE a user and then try to create it again with the same name, you get an error.

DELETE FROM mysql.user WHERE user='korsan';
-- or
DELETE FROM mysql.user WHERE user='korsan' AND host='localhost';
DELETE FROM mysql.user WHERE user='korsan' AND host='%';

After creating a user, we need to decide which privileges each user will have on which databases and tables. As with user creation, privileges can be granted per host.

GRANT ALL PRIVILEGES ON korsanDB.* TO 'korsan'@'localhost';

With the syntax above, the “korsan” user, connecting from the local machine, can perform all operations on all tables of the “korsanDB” database (expressed as korsanDB.*).

GRANT SELECT, INSERT, UPDATE, DELETE ON korsanDB.users TO 'korsan'@'%';

With this syntax, the “korsan” user, connecting from an external machine, can only perform SELECT, INSERT, UPDATE and DELETE on the users table of the “korsanDB” database (korsanDB.users). If the user has no password yet, you can set one while granting privileges:

GRANT ALL PRIVILEGES ON korsanDB.* TO 'korsan'@'localhost' IDENTIFIED BY 'password';

To view all privileges assigned to a user, you can use the following syntax:

SHOW GRANTS FOR 'korsan'@'localhost';
SHOW GRANTS FOR 'korsan'@'%';

Don’t forget to set a password when creating or authorizing a new user in MySQL. Use different passwords and different privileges per host. It may seem like a small detail, but it can deter malicious people trying to access your database. Finally, the following syntax can help you create a user quickly and authorize it properly:

CREATE USER 'korsan'@'%';
GRANT ALL PRIVILEGES ON korsanDB.* TO 'korsan'@'%' IDENTIFIED BY 'password';
GRANT UPDATE ON DATABASE1.memleketordu TO 'korsan2'@'localhost';

With the last command, we gave the user permission to update the related view.

REVOKE UPDATE ON database1.memleketordu FROM 'korsan2'@'localhost';

With this command, we remove the update permission.

SHOW GRANTS FOR 'korsan2'@'localhost';

And with this command, we can view the user’s privileges on the database.

Note: This report was written in 2017. In MySQL 8.0 and later, the IDENTIFIED BY clause can no longer be used with GRANT; create the user with a password first (CREATE USER … IDENTIFIED BY …) and then grant privileges.

4. Conclusions

Because working directly on large tables and bulk records in MySQL is demanding in terms of processing load, MySQL allows operations to be carried out through views. Views are used not only in MySQL but also in MS SQL Server and other databases.

Our study showed that views are very easy to use yet have very powerful functions. We supported each topic with examples and aimed to make it easy to understand.

We concluded that views have a wide and frequent range of uses, that anyone working with databases, whether an administrator or a developer, should definitely know them, and that managing and operating databases without views would be difficult.

5. References

  • Cem Demir (2016). MySQL için kullanıcı oluşturma ve yetkilendirme. cemdemir.net
  • Limon Host (2016). MySQL’de yeni kullanıcı oluşturma ve yetki verme. limonhost.net
  • Adveyer.com (2016). MySQL kullanıcı ekleme ve yetki verme. adveyer.com
  • Sqlogren.com (2016). MySQL kullanıcı yetkilendirilmesi. sqlogren.com

About the author

Volkan Avşar was born in 1987 and completed his primary and secondary education in Istanbul. He graduated from the Computer Software department of Kadırga Technical High School, and then from Anadolu University (Public Administration) and Istanbul University (Persian Language and Literature). He has worked as an IT manager, IT specialist and consultant at various companies, contributing to server virtualization, help desk operations and processes. He speaks English and Persian. At the time of writing, he was pursuing a master’s degree in Management Information Systems at Haliç University.

databasemysqluzerindeviewyapisi