📄 user_ora.sql
字号:
drop table SYSUSER_USERASSIGN cascade constraints
/
drop table SYSUSER_ROLEASSIGN cascade constraints
/
drop table SYSUSER_RIGHTASSIGN cascade constraints
/
drop table SYSUSER_USERLIST cascade constraints
/
drop table SYSUSER_RIGHTLIST cascade constraints
/
drop table SYSUSER_DATAASSIGN cascade constraints
/
drop table SYSINFO_MODEL cascade constraints
/
drop table SYSINFO_BASE cascade constraints
/
drop table SYSINFO_APPLIST cascade constraints
/
-- ============================================================
-- Table: SYSINFO_APPLIST
-- ============================================================
create table SYSINFO_APPLIST
(
APPID CHAR(3) not null,
APPNAME CHAR(40) null ,
APPDES CHAR(255) null
)
/
create unique index SYSINFO_APPLIST_X on SYSINFO_APPLIST (APPID asc)
/
-- ============================================================
-- Table: SYSINFO_BASE
-- ============================================================
create table SYSINFO_BASE
(
SYSID0 CHAR(10) null ,
SYSMC0 CHAR(30) null ,
SYSVER CHAR(10) null ,
PCS000 CHAR(24) null
)
/
-- ============================================================
-- Table: SYSINFO_MODEL
-- ============================================================
create table SYSINFO_MODEL
(
APPID CHAR(3) not null,
BH0000 CHAR(3) not null,
RIGHTID CHAR(2) not null,
DES000 CHAR(20) null ,
WINDOW CHAR(20) null ,
WINTYPE CHAR(20) null ,
SUB_MENU_NUM LONG null ,
constraint SYS_C0025904 primary key (APPID, BH0000)
)
/
-- ============================================================
-- Table: SYSUSER_DATAASSIGN
-- ============================================================
create table SYSUSER_DATAASSIGN
(
ASSIGNID CHAR(4) not null,
APPID CHAR(3) not null,
COLNAME CHAR(30) not null,
CODETABLE CHAR(16) null ,
CODECOL CHAR(12) null ,
CODENAME CHAR(12) null ,
DWNAME CHAR(30) null ,
WHERECLAUSE CHAR(100) null ,
FILTERCLAUSE CHAR(100) null ,
ASSIGNMODE CHAR(3) null ,
USEMODE CHAR(1) null ,
constraint SYS_C0025878 primary key (ASSIGNID)
)
/
-- ============================================================
-- Table: SYSUSER_RIGHTLIST
-- ============================================================
create table SYSUSER_RIGHTLIST
(
RIGHTID CHAR(10) not null,
APPID CHAR(3) null ,
RIGHTNAME CHAR(40) null ,
RIGHTDES CHAR(40) null ,
CTRLPRE CHAR(1) null ,
CTRLNAME CHAR(100) null ,
CTRLSTATUE CHAR(10) null ,
CTRL001 CHAR(100) null ,
CTRL002 CHAR(100) null ,
CTRL003 CHAR(100) null ,
UO_NAME CHAR(20) null ,
OBJECT_MODE CHAR(1) null ,
constraint SYS_C0025886 primary key (RIGHTID)
)
/
-- ============================================================
-- Table: SYSUSER_USERLIST
-- ============================================================
create table SYSUSER_USERLIST
(
USERID CHAR(16) not null,
USERNM CHAR(16) null ,
USERDS CHAR(40) null ,
USERPW CHAR(20) null ,
USERWD CHAR(11) null ,
USERGR CHAR(16) null ,
IS_DBA CHAR(1) null ,
IS_GRP CHAR(1) null ,
IS_ROLE CHAR(1) null ,
IS_VALID CHAR(1) null ,
constraint SYS_C0025891 primary key (USERID)
)
/
-- ============================================================
-- Table: SYSUSER_RIGHTASSIGN
-- ============================================================
create table SYSUSER_RIGHTASSIGN
(
RIGHTID CHAR(10) not null,
USERID CHAR(16) not null,
constraint SYS_C0025889 primary key (RIGHTID, USERID)
)
/
-- ============================================================
-- Table: SYSUSER_ROLEASSIGN
-- ============================================================
create table SYSUSER_ROLEASSIGN
(
USERID CHAR(16) not null,
ROLEID CHAR(16) not null,
constraint SYS_C0025894 primary key (USERID, ROLEID)
)
/
-- ============================================================
-- Table: SYSUSER_USERASSIGN
-- ============================================================
create table SYSUSER_USERASSIGN
(
USERID CHAR(16) not null,
ASSIGNID CHAR(4) not null,
APPID CHAR(3) not null,
COLNAME CHAR(30) not null,
ASSIGNVALUE CHAR(30) not null,
ASSIGNNAME CHAR(60) null ,
WHERECLAUSE CHAR(120) null ,
FILTERCLAUSE CHAR(120) null ,
USEMODE CHAR(1) null ,
ISDEF0 CHAR(1) null ,
constraint SYS_C0025884 primary key (USERID, ASSIGNID, ASSIGNVALUE)
)
/
alter table SYSUSER_RIGHTASSIGN
add constraint SYSUSER_RIGHTASSIGN_FK1 foreign key (RIGHTID)
references SYSUSER_RIGHTLIST (RIGHTID) on delete cascade
/
alter table SYSUSER_RIGHTASSIGN
add constraint SYSUSER_RIGHTASSIGN_FK2 foreign key (USERID)
references SYSUSER_USERLIST (USERID) on delete cascade
/
alter table SYSUSER_ROLEASSIGN
add constraint SYSUSER_ROLEASSIGN_FK1 foreign key (USERID)
references SYSUSER_USERLIST (USERID) on delete cascade
/
alter table SYSUSER_ROLEASSIGN
add constraint SYSUSER_ROLEASSIGN_FK2 foreign key (ROLEID)
references SYSUSER_USERLIST (USERID) on delete cascade
/
alter table SYSUSER_USERASSIGN
add constraint SYSUSER_USERASSIGN_FK1 foreign key (ASSIGNID)
references SYSUSER_DATAASSIGN (ASSIGNID) on delete cascade
/
alter table SYSUSER_USERASSIGN
add constraint SYSUSER_USERASSIGN_FK2 foreign key (USERID)
references SYSUSER_USERLIST (USERID) on delete cascade
/
-------------VIEW-------------
drop view VW_ALL_USERRIGHT
/
drop view VW_ALLASSIGN
/
drop view VW_GROUP_RIGHT
/
drop view VW_GROUPLIST
/
drop view VW_ROLE_USERRIGHT
/
drop view VW_ROLELIST
/
drop view VW_ROLERIGHT
/
drop view VW_USERASSIGN_FROMGROUP
/
drop view VW_USERLIST
/
drop view VW_USERRIGHT
/
drop view VW_USERRIGHT_FROMGROUP
/
drop view VW_USERROLES
/
--用户列表
CREATE OR REPLACE VIEW vw_userlist
( userid,
usernm,
userds,
userpw,
userwd,
usergr,
is_dba,
is_grp,
is_valid )
AS SELECT
USERID,
USERNM,
USERDS,
USERPW,
USERWD,
USERGR,
IS_DBA,
IS_GRP,
IS_VALID
FROM SYSUSER_USERLIST
WHERE IS_ROLE = 'u';
--组列表视图
CREATE OR REPLACE VIEW vw_grouplist
( groupid,
groupnm,
groupds,
is_valid )
AS SELECT
USERID,
USERNM,
USERDS,
IS_VALID
FROM SYSUSER_USERLIST
WHERE is_grp = 't';
--角色列表
CREATE OR REPLACE VIEW vw_rolelist
( roleid,
rolenm,
roleds,
rolepw,
is_valid )
AS SELECT
USERID,
USERNM,
USERDS,
USERPW,
IS_VALID
FROM SYSUSER_USERLIST
WHERE IS_ROLE = 'r';
--用户通过直接授权所获得的权限
CREATE OR REPLACE 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 OR REPLACE VIEW vw_roleright
( roleid,
rolenm,
rightid,
appid,
rightname,
ctrlpre,
ctrlname,
ctrlstatue,
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -