ts of privileges. But a view (or a stored routine) can have only
one definer. So, when a view (or a stored routine) is created with the SQL
SECURITY DEFINER, one can specify whether the definer should be CURRENT_USER
(and the view will have none of the privileges of the user's role) or
CURRENT_ROLE (in this case, the view will use role's privileges, but none of
the user's privileges). As a result, sometimes one can create a view that is
impossible to use.

CREATE ROLE r1;

GRANT ALL ON db1.* TO r1;

GRANT r1 TO foo@localhost;

GRANT ALL ON db.* TO foo@localhost;

SELECT CURRENT_USER
+---------------+
| current_user  |
+---------------+
| foo@localhost |
+---------------+

SET ROLE r1;

CREATE TABLE db1.t1 (i int);

CREATE VIEW db.v1 AS SELECT * FROM db1.t1;

SHOW CREATE VIEW db.v1;
+------+-----------------------------------------------------------------------
------------------------------------------------------------------+------------
---------+----------------------+
| View | Create View                                                          
                                 |
character_set_client | collation_connection |
+------+-----------------------------------------------------------------------
------------------------------------------------------------------+------------
---------+----------------------+
| v1   | CREATE ALGORITHM=UNDEFINED DEFINER=`foo`@`localhost` SQL SECURITY
DEFINER VIEW `db`.`v1` AS SELECT `db1`.`t1`.`i` AS `i` from `db1`.`t1` | utf8 
       | utf8_general_ci      |
+------+-----------------------------------------------------------------------
------------------------------------------------------------------+------------
---------+----------------------+

CREATE DEFINER=CURRENT_ROLE VIEW db.v2 AS SELECT * FROM db1.t1;

SHOW CREATE VIEW db.b2;
+------+-----------------------------------------------------------------------
-----------------------------------------------------+----------------------+--
-------------------+
| View | Create View                                                          
                           | character_set_client |
collation_connection |
+------+-----------------------------------------------------------------------
-----------------------------------------------------+----------------------+--
-------------------+
| v2   | CREATE ALGORITHM=UNDEFINED DEFINER=`r1` SQL SECURITY DEFINER VIEW
`db`.`v2` AS select `db1`.`t1`.`a` AS `a` from `db1`.`t1` | utf8              
 | utf8_general_ci      |
+------+-----------------------------------------------------------------------
-----------------------------------------------------+----------------------+--
-------------------+

Other Resources
---------------

* Roles Review by Peter Gulutzan

URL: https://mariadb.com/kb/en/roles_overview/https://mariadb.com/kb/en/roles_overview/