-- Create table
create table RBAC_GRANT
(
GRANT_ID INTEGER not null,
PERMISSION_ID INTEGER,
ROLE_ID INTEGER,
ACTION VARCHAR2(500)
)
-- Add comments to the columns
comment on column RBAC_GRANT.GRANT_ID
is '编号';
comment on column RBAC_GRANT.PERMISSION_ID
is '权限编号';
comment on column RBAC_GRANT.ROLE_ID
is '角色编号';
comment on column RBAC_GRANT.ACTION
is '拥护的操作';
-- Create/Recreate primary, unique and foreign key constraints
alter table RBAC_GRANT
add constraint PK_RBAC_GRANT primary key (GRANT_ID);
--Create sequences
Create Sequence SEQ_RBAC_GRANT;
---- Create table
create table RBAC_PERMISSION
(
PERMISSION_ID INTEGER not null,
RESOURCE_ID INTEGER
);
-- Add comments to the columns
comment on column RBAC_PERMISSION.PERMISSION_ID
is '权限编号';
comment on column RBAC_PERMISSION.RESOURCE_ID
is '资源编号';
-- Create/Recreate primary, unique and foreign key constraints
alter table RBAC_PERMISSION
add constraint PK_RBAC_PERMISSION primary key (PERMISSION_ID);
--Create Sequences
Create Sequence SEQ_RBAC_PERMISSION;
-- Create table
create table RBAC_RESOURCE
(
RESOURCE_ID INTEGER not null,
PARENT_ID INTEGER default 0 not null,
RESOURCE_NAME VARCHAR2(50) not null,
RESOURCE_KEY VARCHAR2(50) not null,
CREATE_TIME DATE default sysdate,
UPDATE_TIME DATE default sysdate,
INFO VARCHAR2(500),
HAS_LEAF INTEGER,
TYPE INTEGER,
SORT INTEGER,
ACTION VARCHAR2(500),
HAS_MULTI INTEGER,
IS_DELETE INTEGER default 1 not null,
IS_FUNCTION INTEGER default 1 not null
);
-- Add comments to the columns
comment on column RBAC_RESOURCE.PARENT_ID
is '父资源ID（0 表示根）';
comment on column RBAC_RESOURCE.RESOURCE_NAME
is '资源名称';
comment on column RBAC_RESOURCE.RESOURCE_KEY
is '键';
comment on column RBAC_RESOURCE.CREATE_TIME
is '创建时间';
comment on column RBAC_RESOURCE.UPDATE_TIME
is '修改时间';
comment on column RBAC_RESOURCE.INFO
is '资源信息';
comment on column RBAC_RESOURCE.HAS_LEAF
is '是否还有子节点（0没有1有）';
comment on column RBAC_RESOURCE.TYPE
is '资源类型(600运维类型601运营类型)';
comment on column RBAC_RESOURCE.SORT
is '资源排序';
comment on column RBAC_RESOURCE.ACTION
is '资源操作';
comment on column RBAC_RESOURCE.HAS_MULTI
is '是否有多级子节点（0没有1有）';
comment on column RBAC_RESOURCE.IS_DELETE
is '是否删除';
comment on column RBAC_RESOURCE.IS_FUNCTION
is '是否存在功能';
-- Create/Recreate primary, unique and foreign key constraints
alter table RBAC_RESOURCE
add constraint PK_RBAC_RESOURCE primary key (RESOURCE_ID);
--Create Sequences
Create sequence SEQ_RBAC_RESOURCE;
-- Create table
create table RBAC_ROLE
(
ROLE_ID INTEGER not null,
ROLE_NAME VARCHAR2(50) not null,
DESCRIPTION VARCHAR2(1000),
CREATE_TIME DATE default sysdate,
UPDATE_TIME DATE default sysdate,
ROLE_TYPE INTEGER,
IS_DELETE INTEGER default 1
);
-- Add comments to the table
comment on table RBAC_ROLE
is '角色';
-- Add comments to the columns
comment on column RBAC_ROLE.ROLE_NAME
is '角色名称';
comment on column RBAC_ROLE.DESCRIPTION
is '角色描述';
comment on column RBAC_ROLE.CREATE_TIME
is '创建时间';
comment on column RBAC_ROLE.UPDATE_TIME
is '修改时间';
comment on column RBAC_ROLE.ROLE_TYPE
is '角色类型。305-代理商,306-高级用户,307-访客用户,308-超级管理员,309-运维管理员,310-运营管理员';
comment on column RBAC_ROLE.IS_DELETE
is '是否删除, 0删除 1未删除';
-- Create/Recreate primary, unique and foreign key constraints
alter table RBAC_ROLE
add constraint PK_RBAC_ROLE primary key (ROLE_ID);
--Create Sequences
Create Sequence SEQ_RBAC_GRANT;
-- Create table
create table RBAC_ROLE_USER
(
ROLE_USER_ID INTEGER not null,
ROLE_ID INTEGER,
USER_ID INTEGER
);
-- Add comments to the columns
comment on column RBAC_ROLE_USER.ROLE_USER_ID
is '编号';
comment on column RBAC_ROLE_USER.ROLE_ID
is '角色';
comment on column RBAC_ROLE_USER.USER_ID
is '用户';
-- Create/Recreate primary, unique and foreign key constraints
alter table RBAC_ROLE_USER
add constraint PK_RBAC_ROLE_USER primary key (ROLE_USER_ID);
--Create Sequences
Create Sequence SEQ_R_ROLE_USER;
-- Create table
create table USER_INFO
(
USER_ID INTEGER not null,
USER_NAME VARCHAR2(50) not null,
PASSWORD VARCHAR2(50) not null,
EMAIL VARCHAR2(50),
QQ VARCHAR2(20),
IS_DELETE INTEGER default 0 not null,
HASHCODE VARCHAR2(50),
ADDRESS VARCHAR2(100),
CREATE_TIME DATE default sysdate not null,
UPDATE_TIME DATE default sysdate not null,
PHONE VARCHAR2(100),
BIRTHDAY DATE default sysdate not null,
REALLY_NAME VARCHAR2(100),
DEPART_ID INTEGER,
SEX VARCHAR2(5),
STATE INTEGER,
HEIGHT INTEGER,
WEIGHT INTEGER,
EDUCATION INTEGER default -1,
HOBBY VARCHAR2(100),
INDUSTRY_ID INTEGER default 0 not null,
POI_ID INTEGER default 0 not null,
HEAD_FACE VARCHAR2(20)
);
-- Add comments to the columns
comment on column USER_INFO.USER_ID
is '编号';
comment on column USER_INFO.USER_NAME
is '名称';
comment on column USER_INFO.PASSWORD
is '密码';
comment on column USER_INFO.EMAIL
is '电子邮件';
comment on column USER_INFO.QQ
is 'qq号码';
comment on column USER_INFO.IS_DELETE
is '删除状态 1未删除 0删除';
comment on column USER_INFO.HASHCODE
is 'hash代码';
comment on column USER_INFO.ADDRESS
is '住址';
comment on column USER_INFO.CREATE_TIME
is '建立时间';
comment on column USER_INFO.UPDATE_TIME
is '修改时间';
comment on column USER_INFO.PHONE
is '电话号码';
comment on column USER_INFO.BIRTHDAY
is '生日';
comment on column USER_INFO.REALLY_NAME
is '真实名称';
comment on column USER_INFO.DEPART_ID
is '部门编号';
comment on column USER_INFO.SEX
is '性别';
comment on column USER_INFO.STATE
is '其他状态';
comment on column USER_INFO.HEIGHT
is ' 身高';
comment on column USER_INFO.WEIGHT
is '体重';
comment on column USER_INFO.EDUCATION
is '学历';
comment on column USER_INFO.HOBBY
is '爱好';
comment on column USER_INFO.INDUSTRY_ID
is '用户所属行业ID';
comment on column USER_INFO.HEAD_FACE
is '用户头像';
-- Create/Recreate primary, unique and foreign key constraints
alter table USER_INFO
add constraint PK_USER primary key (USER_ID);
--Create Sequences
Create Sequence SEQ_USER_INFO;
--权限视图
create or replace view v_permission as
select
res.RESOURCE_ID,
res.PARENT_ID,
res.RESOURCE_NAME,
res.RESOURCE_KEY,
res.CREATE_TIME,
res.UPDATE_TIME,
res.INFO,
res.has_leaf,
res.is_function,
res.type,
res.sort,
res.has_multi,
res.is_delete,
rt.action,
rt.role_id
from rbac_grant rt
inner join rbac_permission per
on rt.permission_id=per.permission_id
inner join rbac_resource res
on res.resource_id=per.resource_id
create or replace view v_permission_tree as
select
res.RESOURCE_ID,
res.PARENT_ID,
res.RESOURCE_NAME,
res.RESOURCE_KEY,
res.CREATE_TIME,
res.UPDATE_TIME,
res.INFO,
res.has_leaf,
res.type,
res.is_function,
res.sort,
res.action,
res.is_delete,
per.permission_id
from rbac_resource res
inner join rbac_permission per
on res.resource_id=per.resource_id
