oracle数据库权限

1、权限5张表

drop table t_user;
drop table t_role;
drop table t_menu;
drop table t_user_role;
drop table t_role_menu;
drop sequence seq_user_role;
drop sequence seq_role_menu;
purge recyclebin;

/用户表/
– 主键100+
create table t_user
(
u_id int primary key,
u_account varchar2(12),
u_password varchar2(12)
);
/角色表/
– 主键200+
create table t_role
(
r_id int primary key,
r_name varchar2(12),
r_desc varchar2(100)
);

/权限清单表/
– 主键300+
create table t_menu
(
m_id int primary key,
m_name varchar2(12),
m_desc varchar2(100)
);

/用户角色关系/
create table t_user_role
(
id int primary key,
u_id int,
r_id int,
describ varchar2(100) default null
);

create sequence seq_user_role
start with 1
increment by 1
nocache
nocycle;

/角色权限关系表/
create table t_role_menu
(
id int primary key,
m_id int,
r_id int,
describ varchar2(100) default null
);
create sequence seq_role_menu
start with 1
increment by 1
nocache
nocycle;

/添加测试数据/
/用户测试数据/
insert into t_user values (101,‘xiaohei’,‘123456’);
insert into t_user values (102,‘xiaohong’,‘123456’);
insert into t_user values (103,‘xiaohua’,‘123456’);
insert into t_user values (104,‘xiaoming’,‘123456’);
insert into t_user values (105,‘xiaoqiang’,‘123456’);
/角色测试数据/
insert into t_role values (201,‘connect’,‘会话、查询’);
insert into t_role values (202,‘resource’,‘会话、查询、DDL、DML、事务’);
insert into t_role values (203,‘dba’,‘all’);
/权限清单测试数据/
insert into t_menu values (301,‘session’,‘会话’);
insert into t_menu values (302,‘query’,‘查询’);
insert into t_menu values (303,‘ddl’,‘数据定义语言’);
insert into t_menu values (304,‘dml’,‘数据操作语言’);
insert into t_menu values (305,‘dcl’,‘数据控制语言’);
insert into t_menu values (306,‘transication’,‘事务’);
/角色清单关系/
insert into t_role_menu values (seq_role_menu.nextval,301,201,default);
insert into t_role_menu values (seq_role_menu.nextval,302,201,default);
insert into t_role_menu values (seq_role_menu.nextval,301,202,default);
insert into t_role_menu values (seq_role_menu.nextval,302,202,default);
insert into t_role_menu values (seq_role_menu.nextval,303,202,default);
insert into t_role_menu values (seq_role_menu.nextval,304,202,default);
insert into t_role_menu values (seq_role_menu.nextval,306,202,default);
insert into t_role_menu values (seq_role_menu.nextval,301,203,default);
insert into t_role_menu values (seq_role_menu.nextval,302,203,default);
insert into t_role_menu values (seq_role_menu.nextval,303,203,default);
insert into t_role_menu values (seq_role_menu.nextval,304,203,default);
insert into t_role_menu values (seq_role_menu.nextval,305,203,default);
insert into t_role_menu values (seq_role_menu.nextval,306,203,default);

commit;

–将小黑提升为resource角色
insert into t_user_role values (seq_user_role.nextval,101,202,‘经理’);
– 查看小黑的权限
–1、根据用户找到角色
–2、根据角色找到权限
select u.u_account,ur.r_id,rm.m_id,m.m_name,m.m_desc
from t_user u
left join t_user_role ur on u.u_id=ur.u_id
left join t_role_menu rm on ur.r_id=rm.r_id
left join t_menu m on rm.m_id=m.m_id
where u.u_id=‘101’