📄 user_loc.sql
字号:
%% ============================================================
%% Database name: MODEL_1
%% DBMS name: Sybase SQL Anywhere
%% Created on: 2000-11-01 10:35
%% ============================================================
if exists(select 1 from sys.systable where table_name='SYSUSER_ROLEASSIGN' and table_type='BASE') then
drop table SYSUSER_ROLEASSIGN
end if;
if exists(select 1 from sys.systable where table_name='SYSUSER_USERLIST' and table_type='BASE') then
drop table SYSUSER_USERLIST
end if;
if exists(select 1 from sys.systable where table_name='SYSUSER_RIGHTASSIGN' and table_type='BASE') then
drop table SYSUSER_RIGHTASSIGN
end if;
if exists(select 1 from sys.systable where table_name='SYSUSER_RIGHTLIST' and table_type='BASE') then
drop table SYSUSER_RIGHTLIST
end if;
if exists(select 1 from sys.systable where table_name='SYSUSER_USERASSIGN' and table_type='BASE') then
drop table SYSUSER_USERASSIGN
end if;
if exists(select 1 from sys.systable where table_name='SYSUSER_DATAASSIGN' and table_type='BASE') then
drop table SYSUSER_DATAASSIGN
end if;
if exists(select 1 from sys.systable where table_name='sysinfo_model' and table_type='BASE') then
drop table sysinfo_model
end if;
if exists(select 1 from sys.systable where table_name='sysinfo_base' and table_type='BASE') then
drop table sysinfo_base
end if;
if exists(select 1 from sys.systable where table_name='SYSINFO_APPLIST' and table_type='BASE') then
drop table SYSINFO_APPLIST
end if;
%% ============================================================
%% Table: SYSINFO_APPLIST
%% ============================================================
create table SYSINFO_APPLIST
(
APPID char(3) not null,
APPNAME char(40) ,
APPDES char(255)
);
comment on table SYSINFO_APPLIST is 'SYSINFO_APPLIST';
%% ============================================================
%% Index: SYSINFO_APPLIST_X
%% ============================================================
create unique index SYSINFO_APPLIST_X on SYSINFO_APPLIST (APPID asc);
%% ============================================================
%% Table: sysinfo_base
%% ============================================================
create table sysinfo_base
(
sysid0 char(10) ,
sysmc0 char(30) ,
sysver char(10) ,
pcs000 char(24)
);
comment on table sysinfo_base is 'sysinfo_base';
%% ============================================================
%% 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) ,
window char(20) ,
wintype char(20) ,
sub_menu_num double ,
primary key (appid, bh0000)
);
comment on table sysinfo_model is 'sysinfo_model';
%% ============================================================
%% 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) ,
CODECOL char(12) ,
CODENAME char(12) ,
DWNAME char(30) ,
WHERECLAUSE char(100) ,
FILTERCLAUSE char(100) ,
ASSIGNMODE char(3) ,
USEMODE char(1) ,
primary key (ASSIGNID)
);
comment on table SYSUSER_DATAASSIGN is 'SYSUSER_DATAASSIGN';
%% ============================================================
%% 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) ,
WHERECLAUSE char(120) ,
FILTERCLAUSE char(120) ,
USEMODE char(1) ,
ISDEF0 char(1) ,
primary key (USERID, ASSIGNID, ASSIGNVALUE)
);
comment on table SYSUSER_USERASSIGN is 'SYSUSER_USERASSIGN';
%% ============================================================
%% Table: SYSUSER_RIGHTLIST
%% ============================================================
create table SYSUSER_RIGHTLIST
(
RIGHTID char(10) not null,
APPID char(3) ,
RIGHTNAME char(40) ,
RIGHTDES char(40) ,
CTRLPRE char(1) ,
CTRLNAME char(100) ,
CTRLSTATUE char(10) ,
CTRL001 char(100) ,
CTRL002 char(100) ,
CTRL003 char(100) ,
UO_NAME char(20) ,
OBJECT_MODE char(1) ,
primary key (RIGHTID)
);
comment on table SYSUSER_RIGHTLIST is 'SYSUSER_RIGHTLIST';
%% ============================================================
%% Table: SYSUSER_RIGHTASSIGN
%% ============================================================
create table SYSUSER_RIGHTASSIGN
(
RIGHTID char(10) not null,
USERID char(16) not null,
primary key (RIGHTID, USERID)
);
comment on table SYSUSER_RIGHTASSIGN is 'SYSUSER_RIGHTASSIGN';
%% ============================================================
%% Table: SYSUSER_USERLIST
%% ============================================================
create table SYSUSER_USERLIST
(
USERID char(16) not null,
USERNM char(16) ,
USERDS char(40) ,
USERPW char(20) ,
USERWD char(11) ,
USERGR char(16) ,
IS_DBA char(1) ,
IS_GRP char(1) ,
IS_ROLE char(1) ,
IS_VALID char(1) ,
primary key (USERID)
);
comment on table SYSUSER_USERLIST is 'SYSUSER_USERLIST';
%% ============================================================
%% Table: SYSUSER_ROLEASSIGN
%% ============================================================
create table SYSUSER_ROLEASSIGN
(
USERID char(16) not null,
ROLEID char(16) not null,
primary key (USERID, ROLEID)
);
comment on table SYSUSER_ROLEASSIGN is 'SYSUSER_ROLEASSIGN';
alter table SYSUSER_USERASSIGN
add foreign key SYSUSER_USERASSIGN_FK1 (ASSIGNID)
references SYSUSER_DATAASSIGN (ASSIGNID) on update restrict on delete cascade;
alter table SYSUSER_USERASSIGN
add foreign key SYSUSER_USERASSIGN_FK2 (USERID)
references SYSUSER_USERLIST (USERID) on update restrict on delete cascade;
alter table SYSUSER_RIGHTASSIGN
add foreign key SYSUSER_RIGHTASSIGN_FK1 (RIGHTID)
references SYSUSER_RIGHTLIST (RIGHTID) on update restrict on delete cascade;
alter table SYSUSER_RIGHTASSIGN
add foreign key SYSUSER_RIGHTASSIGN_FK2 (USERID)
references SYSUSER_USERLIST (USERID) on update restrict on delete cascade;
alter table SYSUSER_ROLEASSIGN
add foreign key SYSUSER_ROLEASSIGN_FK1 (USERID)
references SYSUSER_USERLIST (USERID) on update restrict on delete cascade;
alter table SYSUSER_ROLEASSIGN
add foreign key SYSUSER_ROLEASSIGN_FK2 (ROLEID)
references SYSUSER_USERLIST (USERID) on update restrict on delete cascade;
%% ============================================================
%% Database name: MODEL_1
%% DBMS name: Sybase SQL Anywhere
%% Created on: 2000-11-01 10:52
%% ============================================================
if exists(select 1 from sys.systable where table_name='vw_all_userright' and table_type='VIEW') then
drop view vw_all_userright
end if;
if exists(select 1 from sys.systable where table_name='vw_allassign' and table_type='VIEW') then
drop view vw_allassign
end if;
if exists(select 1 from sys.systable where table_name='vw_group_right' and table_type='VIEW') then
drop view vw_group_right
end if;
if exists(select 1 from sys.systable where table_name='vw_grouplist' and table_type='VIEW') then
drop view vw_grouplist
end if;
if exists(select 1 from sys.systable where table_name='vw_role_userright' and table_type='VIEW') then
drop view vw_role_userright
end if;
if exists(select 1 from sys.systable where table_name='vw_rolelist' and table_type='VIEW') then
drop view vw_rolelist
end if;
if exists(select 1 from sys.systable where table_name='vw_roleright' and table_type='VIEW') then
drop view vw_roleright
end if;
if exists(select 1 from sys.systable where table_name='vw_sys_userright' and table_type='VIEW') then
drop view vw_sys_userright
end if;
if exists(select 1 from sys.systable where table_name='vw_userassign_fromgroup' and table_type='VIEW') then
drop view vw_userassign_fromgroup
end if;
if exists(select 1 from sys.systable where table_name='vw_userlist' and table_type='VIEW') then
drop view vw_userlist
end if;
if exists(select 1 from sys.systable where table_name='vw_userright' and table_type='VIEW') then
drop view vw_userright
end if;
if exists(select 1 from sys.systable where table_name='vw_userright_fromgroup' and table_type='VIEW') then
drop view vw_userright_fromgroup
end if;
if exists(select 1 from sys.systable where table_name='vw_userroles' and table_type='VIEW') then
drop view vw_userroles
end if;
--用户列表
CREATE 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 VIEW vw_grouplist
( groupid,
groupnm,
groupds,
is_valid )
AS SELECT
USERID,
USERNM,
USERDS,
IS_VALID
FROM SYSUSER_USERLIST
WHERE is_grp = 't';
--角色列表
CREATE VIEW vw_rolelist
( roleid,
rolenm,
roleds,
rolepw,
is_valid )
AS SELECT
USERID,
USERNM,
USERDS,
USERPW,
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -