user_ora.sql

公安户口管理系统公安户口管理系统公安户口管理系统公安户口管理系统公安户口管理系统

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 OR REPLACE 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 OR REPLACE 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 OR REPLACE 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 OR REPLACE 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 OR REPLACE 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;


insert into sysinfo_applist (appid,appname,appdes) values ('001','巨龙暂口网络信息系统','zk');


insert into sysinfo_model(appid,bh0000,rightid,des000) values('001','10','10','日常工作');
insert into sysinfo_model(appid,bh0000,rightid,des000) values('001','20','20','办证撤消');
insert into sysinfo_model(appid,bh0000,rightid,des000) values('001','30','30','信息查询');
insert into sysinfo_model(appid,bh0000,rightid,des000) values('001','40','40','系统维护');
insert into sysinfo_model(appid,bh0000,rightid,des000) values('001','50','50','房东信息');
insert into sysinfo_model(appid,bh0000,rightid,des000) values('001','60','60','报表打印');
insert into sysinfo_model(appid,bh0000,rightid,des000) values('001','70','70','系统帮助');

insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('2001','001','新办证修改','wuo_xbzzzxg','u');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('2002','001','撤消延期','wuo_yqzx','u');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('1001','001','新办证','wuo_xbzzz','u');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('1002','001','延期','wuo_zzzyq','u');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('1003','001','迁移','wuo_zzzqy','u');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('2003','001','撤消迁移','wuo_qyzx','u');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('1004','001','注销','wuo_zzxxzx','u');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('1009','001','批量照片','wuo_plzp','u');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('3002','001','携童信息','w_xtxxcx','w');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('1008','001','违法信息','wuo_fzxxwh','u');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('3003','001','延期信息','w_yqxxcx','w');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('3004','001','迁移信息','w_qyxxcx','w');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('5002','001','许可证年审','wuo_xkzns','u');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('3001','001','暂住信息','w_zzxxcx','w');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('3005','001','房东信息','w_fdxxcx','w');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('3006','001','租房信息','w_zfxxcx','w');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('6001','001','暂住人口统计表（年）','wuo_zzrktjb_n','U');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('6002','001','证件报表','wuo_fhddjb','u');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('6003','001','房东及租房信息','wuo_fdczfbb','u');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('6004','001','办证人员统计','wuo_bztj','u');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('6005','001','延期人员统计','wuo_yqtj','u');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('7001','001','关于','wuo_about','u');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('7002','001','帮助','zkhelp.chm','h');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('3007','001','违法信息','w_fzxxcx','w');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('5001','001','房东信息维护','wuo_fdxxwh','u');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('4007','001','数据下载','wuo_sjxz','u');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('4001','001','系统设定','wuo_xtsd','u');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('4002','001','打印边界设定','wuo_dybjtz','u');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('3008','001','许可证信息','w_xkzcx0','w');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('4003','001','服务处所维护','wuo_fwcswh','u');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('3009','001','注销信息','w_zxxxcx','w');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('4005','001','数据上传','wuo_sjsc','u');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('4008','001','用户管理','w_sysuser_main','w');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('6006','001','暂住人口统计表（月）','wuo_zzrktjb_y','u');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('6008','001','有效期统计','wuo_gqryqd','u');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('6007','001','工作清单','wuo_gzqd','u');
insert into sysuser_rightlist(rightid,appid,rightname,uo_name,object_mode) values ('4009','001','表码维护','w_bm_edit','w');

insert into sysuser_userlist(userid,usernm,userpw,is_dba,is_grp,is_role,is_valid) values('system','超级用户','system','t','f','u','t');
                                                                                                                
insert into sysuser_rightassign(rightid,userid) values ('4008','system');

insert into sysuser_dataassign(assignid,appid,colname,codetable,codecol,codename,dwname,whereclause,filterclause,assignmode,usemode) values
('001','001','巨龙暂口网络版','bm_pcs','bh0000','mc0000','dddw_bm_pcs','1','1','S','1');

                                                                                                                
commit;