DB/Misc: Add new View for RBAC

(cherry picked from commit 46bab72af54c57cc654ebc0af685eafa3011c5dc)

1 files changed, 13 insertions, 0 deletions

diff --git a/sql/updates/auth/master/2021_06_20_00_auth.sql b/sql/updates/auth/master/2021_06_20_00_auth.sql
new file mode 100644
index 00000000000..4b8209a2123
--- /dev/null
+++ b/sql/updates/auth/master/2021_06_20_00_auth.sql
@@ -0,0 +1,13 @@
+--
+DROP VIEW IF EXISTS `vw_rbac`;
+
+CREATE SQL SECURITY INVOKER VIEW `vw_rbac` AS
+(
+    SELECT `t1`.`linkedId` AS `Permission ID`,
+           `t1`.`id` AS `Permission Group`,
+           IFNULL(`t2`.`secId`, 'linked') AS `Security Level`,
+       `t3`.`name` AS `Permission`
+    FROM `rbac_linked_permissions` `t1`
+    LEFT JOIN `rbac_default_permissions` `t2` ON `t1`.`id` = `t2`.`permissionId`
+    LEFT JOIN `rbac_permissions` `t3` ON `t1`.`linkedId` = `t3`.`id`
+);