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. UseCREATE OR REPLACE
to avoid a view’s “downtime”. - It is possible to run
INSERT
,UPDATE
orDELETE
on the view.WITH CHECK OPTION
means that and must fail with an error if the resulting row doesn’t match theWHERE
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. INSERT
s 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.
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
0 Comments