了解基于角色的访问控制(RBAC)

了解基于角色的访问控制

1.用户表

CREATE TABLE t_user(
  user_id NUMBER() PRIMARY KEY,
  user_name VARCHAR2(),
  gender NUMBER(),
  birthday DATE,
  create_time DATE DEFAULT SYSDATE
);
COMMENT ON TABLE t_user IS '用户表';
COMMENT ON COLUMN t_user.user_id IS '用户ID';
COMMENT ON COLUMN t_user.user_name IS '用户姓名';
COMMENT ON COLUMN t_user.gender IS '性别｛1男/0女｝';
COMMENT ON COLUMN t_user.birthday IS '出生日期';
COMMENT ON COLUMN t_user.create_time '创建时间';
           

2.角色表

CREATE TABLE t_role(
  role_id NUMBER() PRIMARY KEY,
  role_name VARCHAR2(),
  create_time DATE DEFAULT SYSDATE
);
COMMENT ON TABLE t_role IS '角色表';
COMMENT ON COLUMN t_role.role_id IS '角色ID';
COMMENT ON COLUMN t_role.role_name IS '角色名称';
COMMENT ON COLUMN t_role.create_time '创建时间';
           

3.权限表

CREATE TABLE t_power(
  power_id NUMBER() PRIMARY KEY,
  power_name VARCHAR2(),
  create_time DATE DEFAULT SYSDATE
);
COMMENT ON TABLE t_power IS '权限表';
COMMENT ON COLUMN t_power.power_id IS '权限ID';
COMMENT ON COLUMN t_power.power_name IS '权限名称';
COMMENT ON COLUMN t_power.create_time '创建时间';
           

4.用户角色（关系）表

CREATE TABLE t_user_role(
  user_id NUMBER() NOT NULL,
  role_id NUMBER() NOT NULL ,
  create_time DATE DEFAULT SYSDATE
);
COMMENT ON TABLE t_user_role IS '用户角色（关系）表';
COMMENT ON COLUMN t_user_role.user_id IS '用户ID';
COMMENT ON COLUMN t_user_role.role_id IS '角色ID';
COMMENT ON COLUMN t_user_role.create_time '创建时间';
           

5.角色权限（关系）表

CREATE TABLE t_role_power(
  role_id NUMBER() NOT NULL,
  power_id NUMBER() NOT NULL
);
COMMENT ON TABLE t_role_power IS '角色权限（关系）表';
COMMENT ON COLUMN t_role_power.role_id IS '角色ID';
COMMENT ON COLUMN t_role_power.power_id IS '权限ID';
COMMENT ON COLUMN t_role_power.create_time '创建时间';