spirng框架之spring security（二）insert 语句补充

百炼成神 LV

已于 2024-01-23 16:20:14 修改

阅读量419

点赞数 6

文章标签：数据库 sql

于 2024-01-08 21:43:49 首次发布

本文链接：https://blog.csdn.net/u011529483/article/details/135467110

版权

本文详细记录了在Spring框架下使用Oracle数据库进行用户表（SYS_USER）、资源权限表（SYS_PERMISSION）、角色表（SYS_ROLE）以及它们之间的关联表（SYS_ROLE_PERMISSION和SYS_USER_ROLE）的创建和插入操作，主要用于个人备忘和快速参考。

摘要由CSDN通过智能技术生成

提示：本文为 spirng框架之spring security（二）文章的补充，主要做记录用，记录了insert 添加数据语句。没有别的东西，方便个人以后拿来即用。oracle数据库

-- 用户表
create sequence SEQ_SYS_USER
minvalue 1
maxvalue 999999999999999
start with 1
increment by 1
nocache
order;
 
create table SYS_USER
(
  ID                    NUMBER(15) PRIMARY KEY, 
  USERNAME              VARCHAR2(50) not null,
  PASSWORD              VARCHAR2(200) not null,
  ADDTIME               DATE not null,
  ACCOUNTNONEXPIRED     NUMBER(1) not null, --账户是否过期
  ACCOUNTNONLOCKED      NUMBER(1) not null, --账户是否锁定
  CREDENTIALSNONEXPIRED NUMBER(1) not null, --密码是否过去
  ENABLED               NUMBER(1) not null  --账户是否可用
)
 
select * from SYS_USER
insert into SYS_USER values(SEQ_SYS_USER.Nextval,'zhangsan','123456',sysdate,1,1,1,1);
insert into SYS_USER values(SEQ_SYS_USER.Nextval,'lisi','666666',sysdate,1,1,1,1);
 
-- 资源权限表
create sequence SEQ_SYS_PERMISSION
minvalue 1
maxvalue 999999999999999
start with 1
increment by 1
nocache
order;
 
create table SYS_PERMISSION
(
  ID                    NUMBER(15) PRIMARY KEY,
  AVAILABLE             NUMBER(1) not null,
  NAME                  VARCHAR2(30) not null, --名称
  PARENT_ID             NUMBER(15) not null, --父ID
  PARENT_IDS            NUMBER(15) not null,
  PERMISSION            VARCHAR2(50) not null,
  RESOURCE_TYPE         VARCHAR2(50) not null, --类型：菜单、请求、按钮
  url                   VARCHAR2(100) not null --请求的URL
)

select * from SYS_PERMISSION
insert into SYS_PERMISSION values(SEQ_SYS_PERMISSION.Nextval,1,'0',0,0,'rbac','req','/test/adduser');
insert into SYS_PERMISSION values(SEQ_SYS_PERMISSION.Nextval,1,'0',0,0,'rbac','req','/test/findAllUser');
insert into SYS_PERMISSION values(SEQ_SYS_PERMISSION.Nextval,1,'0',0,0,'rbac','req','/test/hello');
insert into SYS_PERMISSION values(SEQ_SYS_PERMISSION.Nextval,1,'发送管理',10000,0,'rbac','menu','/');
insert into SYS_PERMISSION values(SEQ_SYS_PERMISSION.Nextval,1,'短信发送',4,0,'rbac','menu','/test/send');
insert into SYS_PERMISSION values(SEQ_SYS_PERMISSION.Nextval,1,'发送查询',4,0,'rbac','menu','/test/sendQuery');
insert into SYS_PERMISSION values(SEQ_SYS_PERMISSION.Nextval,1,'待发查询',4,0,'rbac','menu','/test/tempQuery');
insert into SYS_PERMISSION values(SEQ_SYS_PERMISSION.Nextval,1,'用户管理',10000,0,'rbac','menu','/');
insert into SYS_PERMISSION values(SEQ_SYS_PERMISSION.Nextval,1,'用户查询',8,0,'rbac','menu','/test/userQuery');
insert into SYS_PERMISSION values(SEQ_SYS_PERMISSION.Nextval,1,'用户充值',8,0,'rbac','menu','/test/userCz');
insert into SYS_PERMISSION values(SEQ_SYS_PERMISSION.Nextval,1,'基础数据管理',10000,0,'rbac','menu','/');
insert into SYS_PERMISSION values(SEQ_SYS_PERMISSION.Nextval,1,'黑名单',11,0,'rbac','menu','/test/black');
insert into SYS_PERMISSION values(SEQ_SYS_PERMISSION.Nextval,1,'关键字',11,0,'rbac','menu','/test/wdKey');
insert into SYS_PERMISSION values(SEQ_SYS_PERMISSION.Nextval,1,'0',0,0,'rbac','req','/test/findMenu');
 
-- 角色表
create sequence SEQ_SYS_ROLE
minvalue 1
maxvalue 999999999999999
start with 1
increment by 1
nocache
order;
 
create table SYS_ROLE
(
  ID                    NUMBER(15) PRIMARY KEY,
  AVAILABLE             NUMBER(1) not null,
  DESCRIPTION            VARCHAR2(50) not null,
  ROLE                  VARCHAR2(50) not null
)

select * from SYS_ROLE;
insert into SYS_ROLE values(SEQ_SYS_ROLE.Nextval,1,'管理员','admin')
insert into SYS_ROLE values(SEQ_SYS_ROLE.Nextval,1,'用户','user');
 
 
-- 角色、资源关联表，关联角色和资源权限
create table SYS_ROLE_PERMISSION
(
  ROLE_ID             NUMBER(15) not null,
  PERMISSION_ID       NUMBER(15) not null
)

select * from SYS_ROLE_PERMISSION;
insert into SYS_ROLE_PERMISSION values(1,14);
insert into SYS_ROLE_PERMISSION values(1,4);
insert into SYS_ROLE_PERMISSION values(1,5);
insert into SYS_ROLE_PERMISSION values(1,6);
insert into SYS_ROLE_PERMISSION values(1,7);
insert into SYS_ROLE_PERMISSION values(1,8);
insert into SYS_ROLE_PERMISSION values(1,9);
insert into SYS_ROLE_PERMISSION values(1,10);
insert into SYS_ROLE_PERMISSION values(1,11);
insert into SYS_ROLE_PERMISSION values(1,12);
insert into SYS_ROLE_PERMISSION values(1,13);
insert into SYS_ROLE_PERMISSION values(1,1);
insert into SYS_ROLE_PERMISSION values(1,2);
insert into SYS_ROLE_PERMISSION values(1,3);
insert into SYS_ROLE_PERMISSION values(2,2);
insert into SYS_ROLE_PERMISSION values(2,3);
 
-- 用户、角色关联表，关联用户和角色
create table SYS_USER_ROLE
(
  USER_ID             NUMBER(15) not null,
  ROLE_ID       NUMBER(15) not null
)

select * from SYS_USER_ROLE;
insert into SYS_USER_ROLE values(2,2);
insert into SYS_USER_ROLE values(1,1);