Mysql – Is it possible to create a View with read only option in MySQL

MySQL

I'm currently having the latest version of MySQL(ver 8.0.2) and I'm trying to create a read-only View.

This is how my query looks like:

CREATE VIEW Emp_Salary3 AS
SELECT Empid, Ename, Date_Joined, Salary, Dcode 
FROM Employees
WHERE Salary < 35000
WITH READ ONLY;

But then the response I got was:

Error Code: 1064. You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'READ ONLY' at line 5

Hence I checked the manual as well, it doesn't have a READ-ONLY option whatsoever. Is there a work around for this?

Any help could be appreciated.

Best Answer

MySQL doesn't support WITH READ ONLY for CREATE VIEW.

It is possible to create a read-only view in an indirect way: create an user that has the SELECT privilege on all tables and CREATE VIEW privilege then use this user to create the view and specify SQL SECURITY DEFINER in the CREATE VIEW statement:

CREATE 
    DEFINER = CURRENT_USER
    SQL SECURITY DEFINER
VIEW Emp_Salary3 AS
SELECT Empid, Ename, Date_Joined, Salary, Dcode 
FROM Employees
WHERE Salary < 35000

The line DEFINER = CURRENT_USER is not needed if you use the limited user to create the view.

Or you can use an admin user to create the view and in this case the line DEFINER = must contain the name of the user that will own the view.

The documentation explains:

MySQL checks view privileges like this:

At view definition time, the view creator must have the privileges needed to use the top-level objects accessed by the view. For example, if the view definition refers to table columns, the creator must have some privilege for each column in the select list of the definition, and the SELECT privilege for each column used elsewhere in the definition. If the definition refers to a stored function, only the privileges needed to invoke the function can be checked. The privileges required at function invocation time can be checked only as it executes: For different invocations, different execution paths within the function might be taken.

The user who references a view must have appropriate privileges to access it (SELECT to select from it, INSERT to insert into it, and so forth.)

When a view has been referenced, privileges for objects accessed by the view are checked against the privileges held by the view DEFINER account or invoker, depending on whether the SQL SECURITY characteristic is DEFINER or INVOKER, respectively.

This means the users of the view must have at least SELECT privilege for the view. Then, if the SQL SECURITY is DEFINER then the privileges of the DEFINER user are applied to the tables and views used in the view definition.

Related Topic