MySQL 8 Cookbook

How to do it...

Suppose you want to give access only to the emp_no and salary columns of the salaries table, and from_date is after 2002-01-01. For this, you can create a view with the SQL that gives the required result.

mysql> CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW salary_view AS SELECT emp_no, salary FROM salaries WHERE from_date > '2002-01-01';

Now the salary_view view is created and you can query it just like any other table:

mysql> SELECT emp_no, AVG(salary) as avg FROM salary_view GROUP BY emp_no ORDER BY avg DESC LIMIT 5;

You can see that the view has access to particular rows (that is, from_date > '2002-01-01') and not all of the rows. You can use the view to restrict user access to particular ...