📄 user_loc.sql
字号:
IS_VALID
FROM SYSUSER_USERLIST
WHERE IS_ROLE = 'r';
--用户通过直接授权所获得的权限
CREATE VIEW vw_userright
( userid,
usernm,
rightid,
appid,
rightname,
ctrlpre,
ctrlname,
ctrlstatue,
ctrl001,
ctrl002,
ctrl003,
uo_name,
object_mode )
AS SELECT
SYSUSER_USERLIST.USERID ,
SYSUSER_USERLIST.USERNM ,
SYSUSER_RIGHTLIST.RIGHTID ,
SYSUSER_RIGHTLIST.APPID ,
SYSUSER_RIGHTLIST.RIGHTNAME ,
SYSUSER_RIGHTLIST.CTRLPRE ,
SYSUSER_RIGHTLIST.CTRLNAME ,
SYSUSER_RIGHTLIST.CTRLSTATUE ,
SYSUSER_RIGHTLIST.CTRL001 ,
SYSUSER_RIGHTLIST.CTRL002 ,
SYSUSER_RIGHTLIST.CTRL003,
SYSUSER_RIGHTLIST.uo_name,
SYSUSER_RIGHTLIST.object_mode
FROM SYSUSER_RIGHTASSIGN ,
SYSUSER_RIGHTLIST ,
SYSUSER_USERLIST
WHERE ( SYSUSER_USERLIST.USERID = SYSUSER_RIGHTASSIGN.USERID )
AND ( SYSUSER_RIGHTASSIGN.RIGHTID = SYSUSER_RIGHTLIST.RIGHTID );
--角色拥有的权限
CREATE VIEW vw_roleright
( roleid,
rolenm,
rightid,
appid,
rightname,
ctrlpre,
ctrlname,
ctrlstatue,
ctrl001,
ctrl002,
ctrl003,
uo_name,
object_mode )
AS SELECT VW_ROLELIST.ROLEID ,
VW_ROLELIST.ROLENM ,
SYSUSER_RIGHTLIST.RIGHTID ,
SYSUSER_RIGHTLIST.APPID ,
SYSUSER_RIGHTLIST.RIGHTNAME ,
SYSUSER_RIGHTLIST.CTRLPRE ,
SYSUSER_RIGHTLIST.CTRLNAME ,
SYSUSER_RIGHTLIST.CTRLSTATUE ,
SYSUSER_RIGHTLIST.CTRL001 ,
SYSUSER_RIGHTLIST.CTRL002 ,
SYSUSER_RIGHTLIST.CTRL003,
SYSUSER_RIGHTLIST.uo_name,
SYSUSER_RIGHTLIST.object_mode
FROM SYSUSER_RIGHTASSIGN ,
SYSUSER_RIGHTLIST ,
VW_ROLELIST
WHERE ( VW_ROLELIST.ROLEID = SYSUSER_RIGHTASSIGN.USERID )
AND ( SYSUSER_RIGHTASSIGN.RIGHTID = SYSUSER_RIGHTLIST.RIGHTID );
--用户所拥有的角色
CREATE VIEW vw_userroles
( userid,
usernm,
roleid,
rolenm,
roleds )
AS SELECT VW_USERLIST.USERID,
VW_USERLIST.USERNM,
VW_ROLELIST.ROLEID,
VW_ROLELIST.ROLENM,
VW_ROLELIST.ROLEDS
FROM VW_USERLIST,
VW_ROLELIST,
SYSUSER_ROLEASSIGN
WHERE VW_USERLIST.USERID = SYSUSER_ROLEASSIGN.USERID AND
VW_ROLELIST.ROLEID = SYSUSER_ROLEASSIGN.ROLEID;
--用户通过所拥有的角色所获得的权限
CREATE VIEW vw_role_userright
( userid,
usernm,
rightid,
appid,
rightname,
ctrlpre,
ctrlname,
ctrlstatue,
ctrl001,
ctrl002,
ctrl003,
uo_name,
object_mode )
AS SELECT
VW_USERLIST.USERID ,
VW_USERLIST.USERNM ,
SYSUSER_RIGHTLIST.RIGHTID ,
SYSUSER_RIGHTLIST.APPID ,
SYSUSER_RIGHTLIST.RIGHTNAME ,
SYSUSER_RIGHTLIST.CTRLPRE ,
SYSUSER_RIGHTLIST.CTRLNAME ,
SYSUSER_RIGHTLIST.CTRLSTATUE ,
SYSUSER_RIGHTLIST.CTRL001 ,
SYSUSER_RIGHTLIST.CTRL002 ,
SYSUSER_RIGHTLIST.CTRL003,
SYSUSER_RIGHTLIST.uo_name,
SYSUSER_RIGHTLIST.object_mode
FROM SYSUSER_RIGHTLIST ,
SYSUSER_RIGHTASSIGN ,
SYSUSER_ROLEASSIGN ,
VW_USERLIST
WHERE (VW_USERLIST.USERID = SYSUSER_ROLEASSIGN.USERID AND SYSUSER_RIGHTASSIGN.USERID = SYSUSER_ROLEASSIGN.ROLEID)
AND (SYSUSER_RIGHTLIST.RIGHTID = SYSUSER_RIGHTASSIGN.RIGHTID );
--组拥有的权限视图
CREATE VIEW vw_group_right
( groupid,
groupnm,
rightid,
appid,
rightname,
ctrlpre,
ctrlname,
ctrlstatue,
ctrl001,
ctrl002,
ctrl003,
uo_name,
object_mode )
AS select vw_userright.userid,
vw_userright.usernm,
vw_userright.rightid,
vw_userright.APPID ,
vw_userright.RIGHTNAME ,
vw_userright.CTRLPRE ,
vw_userright.CTRLNAME ,
vw_userright.CTRLSTATUE ,
vw_userright.CTRL001 ,
vw_userright.CTRL002 ,
vw_userright.CTRL003,
vw_userright.uo_name,
vw_userright.object_mode
FROM vw_grouplist,vw_userright
where vw_grouplist.groupid = vw_userright.userid
UNION SELECT VW_ROLE_USERRIGHT.USERID,
VW_ROLE_USERRIGHT.USERNM,
VW_ROLE_USERRIGHT.RIGHTID ,
VW_ROLE_USERRIGHT.APPID ,
VW_ROLE_USERRIGHT.RIGHTNAME ,
VW_ROLE_USERRIGHT.CTRLPRE ,
VW_ROLE_USERRIGHT.CTRLNAME ,
VW_ROLE_USERRIGHT.CTRLSTATUE ,
VW_ROLE_USERRIGHT.CTRL001 ,
VW_ROLE_USERRIGHT.CTRL002 ,
VW_ROLE_USERRIGHT.CTRL003,
VW_ROLE_USERRIGHT.uo_name,
VW_ROLE_USERRIGHT.object_mode
FROM vw_grouplist,VW_ROLE_USERRIGHT
where vw_grouplist.groupid=VW_ROLE_USERRIGHT.userid;
--用户从组获得的权限
CREATE VIEW vw_userright_fromgroup
( userid,
usernm,
rightid,
appid,
rightname,
ctrlpre,
ctrlname,
ctrlstatue,
ctrl001,
ctrl002,
ctrl003,
uo_name,
object_mode )
AS SELECT
vw_userlist.userid,
vw_userlist.usernm,
vw_group_right.rightid,
vw_group_right.appid,
vw_group_right.rightname,
vw_group_right.ctrlpre,
vw_group_right.ctrlname,
vw_group_right.ctrlstatue,
vw_group_right.ctrl001,
vw_group_right.ctrl002,
vw_group_right.ctrl003,
vw_group_right.uo_name,
vw_group_right.object_mode
from vw_userlist,vw_group_right
where vw_userlist.usergr=vw_group_right.groupid;
--用户拥有的全部权限视图
CREATE VIEW vw_all_userright
( userid,
usernm,
rightid,
appid,
rightname,
ctrlpre,
ctrlname,
ctrlstatue,
ctrl001,
ctrl002,
ctrl003,
uo_name,
object_mode )
AS SELECT
VW_USERRIGHT.USERID,
VW_USERRIGHT.USERNM,
VW_USERRIGHT.RIGHTID ,
VW_USERRIGHT.APPID ,
VW_USERRIGHT.RIGHTNAME ,
VW_USERRIGHT.CTRLPRE ,
VW_USERRIGHT.CTRLNAME ,
VW_USERRIGHT.CTRLSTATUE ,
VW_USERRIGHT.CTRL001 ,
VW_USERRIGHT.CTRL002 ,
VW_USERRIGHT.CTRL003,
VW_USERRIGHT.uo_name,
VW_USERRIGHT.object_mode
FROM VW_USERRIGHT
UNION SELECT
VW_ROLE_USERRIGHT.USERID,
VW_ROLE_USERRIGHT.USERNM,
VW_ROLE_USERRIGHT.RIGHTID ,
VW_ROLE_USERRIGHT.APPID ,
VW_ROLE_USERRIGHT.RIGHTNAME ,
VW_ROLE_USERRIGHT.CTRLPRE ,
VW_ROLE_USERRIGHT.CTRLNAME ,
VW_ROLE_USERRIGHT.CTRLSTATUE ,
VW_ROLE_USERRIGHT.CTRL001 ,
VW_ROLE_USERRIGHT.CTRL002 ,
VW_ROLE_USERRIGHT.CTRL003,
VW_ROLE_USERRIGHT.uo_name,
VW_ROLE_USERRIGHT.object_mode
FROM VW_ROLE_USERRIGHT
UNION SELECT
vw_userright_fromgroup.userid,
vw_userright_fromgroup.usernm,
vw_userright_fromgroup.rightid,
vw_userright_fromgroup.appid,
vw_userright_fromgroup.rightname,
vw_userright_fromgroup.ctrlpre,
vw_userright_fromgroup.ctrlname,
vw_userright_fromgroup.ctrlstatue,
vw_userright_fromgroup.ctrl001,
vw_userright_fromgroup.ctrl002,
vw_userright_fromgroup.ctrl003,
vw_userright_fromgroup.uo_name,
vw_userright_fromgroup.object_mode
FROM vw_userright_fromgroup;
CREATE VIEW vw_userassign_fromgroup
( userid,
assignid,
appid,
colname,
assignvalue,
assignname,
whereclause,
filterclause,
usemode,
isdef0 )
AS select
vw_userlist.userid,
SYSUSER_USERASSIGN.ASSIGNID ,
SYSUSER_USERASSIGN.APPID ,
SYSUSER_USERASSIGN.COLNAME ,
SYSUSER_USERASSIGN.ASSIGNVALUE ,
SYSUSER_USERASSIGN.ASSIGNNAME ,
SYSUSER_USERASSIGN.WHERECLAUSE ,
SYSUSER_USERASSIGN.FILTERCLAUSE,
SYSUSER_USERASSIGN.usemode,
SYSUSER_USERASSIGN.isdef0
from vw_userlist,SYSUSER_USERASSIGN
where vw_userlist.usergr=SYSUSER_USERASSIGN.userid;
CREATE VIEW vw_allassign
( userid,
assignid,
appid,
colname,
assignvalue,
assignname,
whereclause,
filterclause,
usemode,
isdef0 )
AS select
vw_userassign_fromgroup.USERID ,
vw_userassign_fromgroup.ASSIGNID ,
vw_userassign_fromgroup.APPID ,
vw_userassign_fromgroup.COLNAME ,
vw_userassign_fromgroup.ASSIGNVALUE ,
vw_userassign_fromgroup.ASSIGNNAME ,
vw_userassign_fromgroup.WHERECLAUSE ,
vw_userassign_fromgroup.FILTERCLAUSE,
vw_userassign_fromgroup.USEMODE,
vw_userassign_fromgroup.ISDEF0
from vw_userassign_fromgroup
UNION SELECT
SYSUSER_USERASSIGN.USERID,
SYSUSER_USERASSIGN.ASSIGNID ,
SYSUSER_USERASSIGN.APPID ,
SYSUSER_USERASSIGN.COLNAME ,
SYSUSER_USERASSIGN.ASSIGNVALUE ,
SYSUSER_USERASSIGN.ASSIGNNAME ,
SYSUSER_USERASSIGN.WHERECLAUSE ,
SYSUSER_USERASSIGN.FILTERCLAUSE,
SYSUSER_USERASSIGN.usemode,
SYSUSER_USERASSIGN.isdef0
FROM SYSUSER_USERASSIGN;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -