MariaDB/MySQL: Using views to grant or deny row-level privileges

Relational DBMSs allow to grant users permissions on certain tables or columns. Privileges can also determine what a user can do – typical privileges are SELECT, INSERT,DELETE, UPDATE. Privileges are assigned or revoked using the GRANT and REVOKE statements.

But how to grant or revoke privileges on a certain set of rows? While there is no built-in SQL statement for this, we can do so by creating a view on the set of rows that a user is able to work with.

Let’s see how to do it with an example. This article uses MySQL/MariaDB syntax, but the SQL statements can easily be adapted to PostgreSQL or other technologies.

Introducing an example

The example we’re going to use is simple enough to explain the idea clearly, but realistic. Suppose we have an employee table. We have an hypothetical application that should only read and modify the rows representing employees who work in the sales department. employee has a foreign key referencing the department table, which we can use to identify the persons who work in sales.

Here are the tables definitions.

CREATE TABLE department (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE unq_name (name)
);
INSERT INTO department (name) VALUES ('Sales');
SET @sales_id := LAST_INSERT_ID();
INSERT INTO department (name) VALUES ('IT');
SET @it_id := LAST_INSERT_ID();
CREATE TABLE employee (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    department_id INT UNSIGNED NOT NULL,
    email VARCHAR(100),
    PRIMARY KEY (id),
    UNIQUE unq_email (email),
    FOREIGN KEY fk_employee_department
        (department_id) REFERENCES department (id)
);
INSERT INTO employee (department_id, email) VALUES
    (@sales_id,  '
        
            mi*****@ou*********.com
            
                
                
                
            
            
                
                
                
            
        
'),
    (@sales_id,  '
        
            ed***@ou*********.com
            
                
                
                
            
            
                
                
                
            
        
'),
    (@it_id,     '
        
            mo************@ou*********.com
            
                
                
                
            
            
                
                
                
            
        
'),
    (@it_id,     '
        
            to******@ou*********.com
            
                
                
                
            
            
                
                
                
            
        
'),
    (@it_id,     '
        
            la********@ou*********.com
            
                
                
                
            
            
                
                
                
            
        
');

If you don’t understand the use of LAST_INSERT_ID(), you may want to read my article MySQL/MariaDB: use SQL properly to run less queries.

Creating a view to grant permissions

Creating the view is quite simple:

CREATE VIEW vw_employee_sales AS
    SELECT *
        FROM employee
        WHERE department_id = (SELECT id FROM department WHERE name = 'Sales')
    WITH CHECK OPTION;
CREATE USER 'sales'@'%' IDENTIFIED BY 'secret';
GRANT
    SELECT, INSERT, UPDATE, DELETE
    ON test.vw_employee_sales
    TO 'sales'@'%';

We created a view and granted permissions on it to the sales user. The user cannot access the employee table directly. It can only access the view.

The user can query the view, and will only see the rows concerning sales:

MariaDB [test]> SELECT * FROM vw_employee_sales;
+----+---------------+-------------------------+
| id | department_id | email                   |
+----+---------------+-------------------------+
|  1 |             1 | 
        
            mi*****@ou*********.com
            
                
                
                
            
            
                
                
                
            
        
 |
|  2 |             1 | 
        
            ed***@ou*********.com
            
                
                
                
            
            
                
                
                
            
        
   |
+----+---------------+-------------------------+

Some notes on the view:

• Hopefully you’ve been told that you should rewrite subqueries as joins for performance reasons. However, that is not always the case. When the subquery returns a small amount of rows (or one row, like in our example), a subquery is usually faster than a join. I cannot give you a number of rows not to exceed, it’s better to test it from case to case. But for example, Amazon RedShift documentation suggests to only prefer subqueries if they return less than 200 rows. (a very generic advice, given that they don’t even mention the DISTSTYLE…)
• In general, using SELECT * is a bad habit. But in a view definition it is fine, if we actually need to include all columns. If a column is added later, it will not be included in the view. To include it, recreate the view. Use CREATE OR REPLACE to avoid a view’s “downtime”.
• It is possible to run INSERT, UPDATE or DELETE on the view. WITH CHECK OPTION means that and must fail with an error if the resulting row doesn’t match the WHERE clause. Unfortunately, this is not the default behavior.

Notes on the permissions:

• The new user can connect from any host. This is a bad practice. If we cannot specify a single hostname or IP, hopefully we can specify a pattern. For example, all application servers that run the sales application could have hostnames containing sales-app.
• This technique is often used to grant permissions to users who access the database directly: typically data scientists or analysts. It would be better to use a role for these persons, instead of assigning permissions individually.

Index usage

A common concern is that adding a view could prevent the DBMS from choosing a proper query plan, even for simple views (single table, no GROUP BY). This can occasionally be the case, at least with MySQL and MariaDB (check your favorite DBMS to find out how it behaves):

MariaDB [test]> EXPLAIN SELECT * FROM vw_employee_sales WHERE email LIKE 'M%' G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: employee
         type: ALL
possible_keys: unq_email,fk_employee_department
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using where
*************************** 2. row ***************************
           id: 3
  select_type: SUBQUERY
        table: department
         type: const
possible_keys: unq_name
          key: unq_name
      key_len: 52
          ref: const
         rows: 1
        Extra: Using index

Use FORCE INDEX in the view definition to fix the problem:

CREATE OR REPLACE VIEW vw_employee_sales AS
    SELECT *
        FROM employee FORCE INDEX (unq_email)
        WHERE department_id = (SELECT id FROM department WHERE name = 'Sales')
    WITH CHECK OPTION
;
MariaDB [test]> EXPLAIN SELECT * FROM vw_employee_sales WHERE email LIKE 'm%' G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: employee
         type: range
possible_keys: unq_email
          key: unq_email
      key_len: 103
          ref: NULL
         rows: 3
        Extra: Using index condition; Using where
*************************** 2. row ***************************
           id: 3
  select_type: SUBQUERY
        table: department
         type: const
possible_keys: unq_name
          key: unq_name
      key_len: 52
          ref: const
         rows: 1
        Extra: Using index

Inserting data into the view

The user has the INSERT privilege, so it is able to insert new rows into the view that it is allowed to see:

INSERT INTO vw_employee_sales (department_id, email) VALUES
    (1,  '
        
            mi******@ou*********.com
            
                
                
                
            
            
                
                
                
            
        
');
MariaDB [test]> SELECT * FROM vw_employee_sales;
+----+---------------+--------------------------+
| id | department_id | email                    |
+----+---------------+--------------------------+
|  1 |             1 | 
        
            mi*****@ou*********.com
            
                
                
                
            
            
                
                
                
            
        
  |
|  2 |             1 | 
        
            ed***@ou*********.com
            
                
                
                
            
            
                
                
                
            
        
    |
|  6 |             1 | 
        
            mi******@ou*********.com
            
                
                
                
            
            
                
                
                
            
        
 |
+----+---------------+--------------------------+

Adding and dropping columns

Adding a new column to the base table is fully supported. As mentioned before, even if we used SELECT *, new columns will not be included in the view. INSERTs into the view will not fail, provided that the new columns have a DEFAULT value.

Dropping columns that are included in the view will make the view invalid. Any SQL statement that mentions the view will fail with an error. To fix the problem, we can simply recreate the view.

Conclusions

We discussed how to use views to assign or deny permissions on specific sets of rows. We saw it from a practical DBA standpoint. We discussed how to avoid bad consequences for query optimisation and how the view is affected by changes on the base table.

Did you notice any mistake? Do you have different opinions? Do you have ideas to share? Please comment!

As usual, I’ll be happy to fix errors and discuss your ideas. I want to thank all the persons who contributed this website with their comments, creating a valuable shared knowledgebase.

Federico Razzoli

Did you like this article?

Photo credit