mysql - 递归mysql查询检查用户权限

Question

我有 3 张 table :

1. 角色

   id | name   | parent_id
   -----------------------
   0  | user   | null
   1  | admin  | 0
   2  | manager| 0
   3  | ceo    | 1
   

其中 role.parent_id 引用 role.id(例如:admin 继承自 user)

1. 行动

   id | name   |
   -------------
   0  | create |
   1  | delete |
   2  | update |
   3  | reset  |
   

2. 角色操作

   role_id | action_id |
   ---------------------
   0       | 0         |   (user can create)
   1       | 2         |   (admin can update)
   2       | 1         |   (manager can delete)
   3       | 3         |   (ceo can reset)
   

因为ceo继承自admin，而admin继承自user，所以ceo应该能够创建、删除和重置。

如何编写递归查询来列出首席执行官(或管理员等)可以执行的所有操作？

如果是首席执行官，示例输出将是:

    ceo  | create |
    ceo  | delete |
    ceo  | reset  |

Answer 1

尝试这样

select 'ceo' as name,t1.name as dd from role_action t2 
inner join 
(
select id,name,@pv:=pid as 'pid' from
(select id, name,pid from role
order by case when name='ceo' then '0' else 1 end,
 pid desc) 
as t
join
(select @pv:=3)tmp
where id=@pv) as t on 
 t2.id=t.id
inner join action t1 on t1.id=t2.aid

Sql Fiddle