--用户所拥有的菜单权限是三部分的并集:
--1,默认对"全部用户"开放的菜单
--2,对特定角色开放的菜单,用户所属角色有(员工/客户),角色是用户的属性,但不是必须属性,可以没有
--3,对指定群组开放的菜单(这些群组可以新增或删除,如"Administrators","资讯中心","人力资源部","客服组")
--对菜单权限进行修改的时候要查询出所有菜单,对一些特殊的菜单(如修改菜单的开放权限,新增菜单,群组新增,群组成员管理)
--授权的时候要注意只开放给特定的群组或角色。
SELECT DISTINCT
m3.menuxxid as m3_menuxxid,m3.menutext as m3_menutext,m3.suprxxid as m3_suprxxid,m3.signflag as m3_signflag,m3.sequence,'分隔列',
m2.menuxxid as m2_menuxxid,m2.menutext as m2_menutext,m2.suprxxid as m2_suprxxid,m2.signflag as m2_signflag,m2.sequence,'分隔列',
m1.menuxxid as m1_menuxxid,m1.menutext as m1_menutext,m1.suprxxid as m1_suprxxid,m1.signflag as m1_signflag,m1.sequence
FROM sysmenu m1
INNER JOIN sysmenu m2
ON m2.suprxxid = m1.menuxxid and m1.suprxxid = 100 --顶级菜单ID(100)
LEFT OUTER JOIN sysmenu m3
ON m3.suprxxid = m2.menuxxid
order by m1.sequence,m2.suprxxid,m2.sequence,m3.suprxxid,m3.sequence
--当上级菜单(一级,二级)定义为不显示,或只开放给特定群组,或只开放给特定角色,这种限制会作用到它下面的子菜单
SELECT DISTINCT
m1.menuxxid,m1.menutext,m1.suprxxid,m1.newindow,m1.hyperlink,m1.sequence,
m2.menuxxid,m2.menutext,m2.suprxxid,m2.newindow,m2.hyperlink,m2.sequence,
m3.menuxxid,m3.menutext,m3.suprxxid,m3.newindow,m3.hyperlink,m3.sequence
FROM
sysmenu m1
INNER JOIN sysmenu m2
ON m2.suprxxid = m1.menuxxid and m1.suprxxid = 100
LEFT OUTER JOIN sysmenu m3
ON m3.suprxxid = m2.menuxxid
,profile p LEFT OUTER JOIN grupuser u INNER JOIN grupinfo i INNER JOIN grupmenu g
ON i.grupxxid = g.grupxxid
ON u.grupxxid = i.grupxxid
ON p.loginxid = u.loginid
WHERE
p.loginxid = 'andy liu'
AND
(
m1.pagecntl = 'Y'
AND
m1.signflag='80'
OR
(m1.signflag='81' AND p.usertype='10')
OR
(m1.signflag='82' AND p.usertype='11')
OR
(
m1.signflag='83' AND g.menuxxid = m1.menuxxid
)
)
AND
(
m2.pagecntl = 'Y'
AND
m2.signflag='80'
OR
(m2.signflag='81' AND p.usertype='10')
OR
(m2.signflag='82' AND p.usertype='11')
OR
(
m2.signflag='83' AND g.menuxxid = m2.menuxxid
)
)
AND
(
m3.menuxxid IS NULL
OR
(
m3.pagecntl = 'Y'
AND
m3.signflag='80'
OR
(m3.signflag='81' AND p.usertype='10')
OR
(m3.signflag='82' AND p.usertype='11')
OR
(
m3.signflag='83' AND g.menuxxid = m3.menuxxid
)
)
)
order by 6,9,12,15,18
上述SQL中的g.menuxxid = m3.menuxxid部分改为IN语句;另外此SQL查出的记录为分层结构;要将其转化成平铺结构(不体现sysmenu的层次结构)可使用Union将一级,二级,三级菜单串起来。
***权限菜单所使用的数据库可以跟存放生产数据的数据库不同。