一、用戶表USER_INFO
1.1、腳本
-- Create table
create table USER_INFO
(id NUMBER(26) not null,--序列號user_id VARCHAR2(50) not null,--登錄賬號password VARCHAR2(50) not null,--密碼msisdn VARCHAR2(100),--移動電話chs_name VARCHAR2(50),--姓名email VARCHAR2(50),--電子郵箱addr VARCHAR2(200),--地址phone VARCHAR2(100),--聯(lián)系電話is_usable NUMBER(8) not null,--是否可用 1-是 0-否memo VARCHAR2(500),--備注version NUMBER(10),--版本city VARCHAR2(50),--城市province VARCHAR2(100),--省country VARCHAR2(100),--國家postal_code VARCHAR2(15),--郵政編碼password_hint VARCHAR2(100),--密碼提示account_enabled CHAR(1),--賬號是否可用 1-是 0-否account_expired CHAR(1),--賬號是否過期 1-是 0-否account_locked CHAR(1),--賬號是否鎖定 1-是 0-否 credentials_expired CHAR(1),--賬號是否鎖定 1-是 0-否create_man NUMBER(26),--創(chuàng)建人site VARCHAR2(100),--所在位置dep_id VARCHAR2(12),--所在部門編號 DEPT_INFO.CODEpassword_error_times VARCHAR2(2) default '0',last_login_time DATE,password_error_lock VARCHAR2(2) default '0',rtx_num VARCHAR2(50)
);
-- Add comments to the columns
comment on column USER_INFO.password_error_timesis '記錄密碼輸錯次數(shù)';
comment on column USER_INFO.last_login_timeis '記錄上一次該賬號進行登錄的時間';
comment on column USER_INFO.password_error_lockis '密碼輸入錯誤鎖，0為正常。1為鎖定';
comment on column USER_INFO.rtx_numis 'RTX號碼';
-- Create/Recreate primary, unique and foreign key constraints
alter table USER_INFOadd constraint PK_USER_INFO primary key (ID)using index;
alter table USER_INFOadd constraint UQ_USER_INFO unique (USER_ID)using index;--序列
create sequence SEQ_USER_INFO
minvalue 1
maxvalue 9999999999999999999999999999
start with 90000
increment by 1
cache 20;
二、存放對用戶的子權(quán)限的分配USER_PERMREG
2.1、腳本
-- Create table
create table USER_PERMREG
(id NUMBER(26) not null,perm_id NUMBER(26) not null,--子權(quán)限ID，PERM_REG.IDuser_ref_id NUMBER(26) not null,--用戶ID。USER_INFO.IDperm_allow NUMBER--是否同意
);
-- Create/Recreate primary, unique and foreign key constraints
alter table USER_PERMREGadd constraint PK_USER_PERMREG primary key (ID)using index;--序列create sequence SEQ_USER_FUNC
minvalue 1
maxvalue 99999999999999999999999999
start with 1
increment by 1
cache 20;
三、存放對特定用戶所分配的權(quán)限USER_RIGHT_LIST
3.1、腳本
-- Create table
create table USER_RIGHT_LIST
(id NUMBER(26) not null,func_id NUMBER(26) not null,--功能號，FUNC_LIST.IDuser_ref_id NUMBER(26) not null,--用戶ID。USER_INFO.IDperm_allow NUMBER
);
-- Create/Recreate primary, unique and foreign key constraints
alter table USER_RIGHT_LISTadd constraint PK_USER_RIGHT_LIST primary key (ID)using index ;
--序列
create sequence SEQ_USER_RIGHT_LIST
minvalue 1
maxvalue 9999999999999999999999999999
start with 20000
increment by 1
cache 20;
四、存放對登錄用戶賦予的角色USER_ROLE_REF
4.1、腳本
-- Create table
create table USER_ROLE_REF
(role_id NUMBER(26) not null,--角色IDuser_id NUMBER(26) not null--用戶ID
);
五、存放系統(tǒng)對用戶角色的定義USER_ROL
5.1、腳本
-- Create table
create table USER_ROL
(id NUMBER(26) not null,name VARCHAR2(100) not null,--角色名稱description VARCHAR2(50)--角色描寫敘述);
-- Create/Recreate primary, unique and foreign key constraints
alter table USER_ROLadd constraint PK_USER_ROL primary key (ID)using index ;
alter table USER_ROLadd constraint UQ_USER_ROL unique (NAME)using index;
--序列
create sequence SEQ_USER_ROL
minvalue 1
maxvalue 9999999999999999999999999999
start with 20000
increment by 1
cache 20;
六、存放對角色的子權(quán)限的分配ROLE_PERMREG
6.1、腳本
-- Create table
create table ROLE_PERMREG
(id NUMBER(26) not null,use_id NUMBER(26),--角色ID，USER_ROL.IDperm_id NUMBER(26),--子權(quán)限ID，PERM_REG.IDperm_allow INTEGER--是否同意
);
-- Create/Recreate primary, unique and foreign key constraints
alter table ROLE_PERMREGadd constraint PK_ROLE_PERMREG primary key (ID)using index;
--序列
create sequence SEQ_ROLE_FUNC
minvalue 1
maxvalue 99999999999999999999999999
start with 1
increment by 1
cache 20;
七、存放對系統(tǒng)角色所分配的權(quán)限ROLE_RIGHT_LIST
7.1、腳本
-- Create table
create table ROLE_RIGHT_LIST
(id NUMBER(26) not null,func_id NUMBER(26) not null,--功能ID，FUNC_LIST.IDrol_ref_id NUMBER(26) not null,--角色ID，USER_ROL.IDperm_allow NUMBER--是否有效 1-是 0-否
);
-- Create/Recreate primary, unique and foreign key constraints
alter table ROLE_RIGHT_LISTadd constraint PK_ROLE_RIGHT_LIST primary key (ID)using index ;
--序列
create sequence SEQ_ROLE_RIGHT_LIST
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 20;
八、存放菜單項與子權(quán)限的關(guān)聯(lián),形成按功能劃分的子權(quán)限,以便為用戶分配更細的權(quán)限PERM_REG
8.1、腳本
-- Create table
create table PERM_REG
(id NUMBER(26) not null,func_id NUMBER(26),--功能ID。FUNC_LIST.IDdef_id NUMBER(26)--子權(quán)限ID。PERM_DEF.ID
);
-- Create/Recreate primary, unique and foreign key constraints
alter table PERM_REGadd constraint PK_PERM_REG primary key (ID)using index;
--序列
create sequence SEQ_PERM_REG
minvalue 1
maxvalue 9999999999999999999999999999
start with 30000
increment by 1
cache 20;
九、系統(tǒng)菜單模塊定義FUNC_LIST
9.1、腳本
-- Create table
create table FUNC_LIST
(id NUMBER(26) not null,level NUMBER,--級別parent_id NUMBER(26),--上級菜單IDname VARCHAR2(50) not null,--顯示名url VARCHAR2(100) not null,--相應(yīng)頁面的地址action VARCHAR2(30) not null,--類型platform/menu/functionis_usable NUMBER(1) not null,--是否啟用sort_order NUMBER(3),--排序memo VARCHAR2(500),--備注icon VARCHAR2(128)--顯示圖片所在路徑
);
-- Create/Recreate primary, unique and foreign key constraints
alter table FUNC_LISTadd constraint PK_FUNC_LIST primary key (ID)using index;
--序列
create sequence SEQ_FUNC_LIST
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 20;
十、部門信息表DEPT_INFO 10.1、腳本
-- Create table
create table DEPT_INFO
(id NUMBER(26) not null,name VARCHAR2(100) not null,--部門名稱parent_id NUMBER(26) not null,--上級部門ID，DEPT_INFO.IDmanager NUMBER(26) not null,--部門經(jīng)理is_dept NUMBER(1) default 0 not null,--是否子公司 1-是 0-否code VARCHAR2(6)--部門代號
);
-- Create/Recreate primary, unique and foreign key constraints
alter table DEPT_INFOadd constraint PK_DEPT_INFO primary key (ID)using index;
alter table DEPT_INFOadd constraint UQ_DEPT_INFO unique (CODE)using index;
--序列
create sequence SEQ_DEPT_INFO
minvalue 1
maxvalue 9999999999999999999999999999
start with 10000
increment by 1
cache 20;
轉(zhuǎn)載于:https://www.cnblogs.com/wzzkaifa/p/7327717.html
總結(jié)
以上是生活随笔為你收集整理的权限管理表结构设计的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。
