权限管理表结构设计

一、用户表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),--联系电话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),--创建人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 '记录密码输错次数'; 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; 二、存放对用户的子权限的分配USER_PERMREG

2.1、脚本

-- Create table create table USER_PERMREG (id NUMBER(26) not null,perm_id NUMBER(26) not null,--子权限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; 三、存放对特定用户所分配的权限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 );五、存放系统对用户角色的定义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; 六、存放对角色的子权限的分配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),--子权限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;
七、存放对系统角色所分配的权限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; 八、存放菜单项与子权限的关联,形成按功能划分的子权限,以便为用户分配更细的权限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)--子权限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; 九、系统菜单模块定义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,--相应页面的地址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,--部门经理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;

转载于:https://www.cnblogs.com/wzzkaifa/p/7327717.html

总结

以上是生活随笔为你收集整理的权限管理表结构设计的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。