Mariadb: how view permissions use roles

Situation

A coworker created a view on two joined tables. The view was created without error but when selected, no records were displayed. However, when the query that the view is based on is run, records are displayed.

Analysis

My coworker’s sql user was granted/attached to the admin role for that database. This user created the view.

About 3 to 5 years ago, Mariadb introduced roles - https://mariadb.com/kb/en/create-role/. They are access control groups at the sql user level. Once created, you grant new sql users to them to use the access privileges of the role. The user has minimal privileges, itself.

I checked:

  • the role had full privileges

  • the sql user was correctly attached to the role.

Everything seemed fine.

Fortunately, I noticed that another system user had no problems using views. That system user was an admin sql user rather than a role. Also, when I logged into the mariadb server as a rootish user, the view worked. These observations pointed me to the view’s definition of privileges. I tried several privilege declarations and by explicitly stating the role of the view, it worked.

Demo

You can use any database for the demo.

Ref - https://mariadb.com/kb/en/create-view/.

1) Define the view

DROP VIEW IF EXISTS test_view;
CREATE VIEW IF NOT EXISTS test_view
AS
SELECT * FROM data_table
;

The view definition is:

CREATE
    ALGORITHM=UNDEFINED
    DEFINER=my_sql_user@db_server_name
    SQL SECURITY DEFINER
    VIEW test_adds
AS
SELECT
    database_name.data_table.id AS `nexus_id`
    , database_name.data_table.adds AS `adds`
    , database_name.data_table.eff_from_dt AS `eff_from_dt`
    , database_name.data_table.notes AS `notes`
FROM data_table
;

Implicitly, my sql user was selected as the definer. However, my sql user has very limited privileges. It is the role that gives me my 'powers.'

2) Now, explicitly define the privileges user.

DROP VIEW IF EXISTS test_view2;
CREATE
    DEFINER=admin_role_name
    VIEW IF NOT EXISTS test_view2
AS
SELECT * FROM data_table
;

In General

In my experience, views have stricter security and hard coded paths on them. If you want to dump their definition and load it into another database, you will run into errors.

Also, they can be slower because you can’t put indices on them. Therefore, I prefer not to use them and use tables instead.

Buyer beware.