I have Applications, Roles, Users, Permissions, Application_Permissions, User_Permissions, Role_Permissions, Application_Roles and Role_Permissions tables. So, permission can be defined at application level, role level and user level. Let’s say User A
belongs to Application X
and Application X
can have XX Permission
. So, User A
will get XX Permission
. Now, I want to give all users of Application B
with XX Permission
but except User B
. How can I fit this with my current design?
CREATE TABLE APPLICATION_PERMISIONS (APPLICATION_PERMISION_ID number(15) GENERATED AS IDENTITY, APPLICATION_ID number(15), PERMISION_DEFINITION_ID number(15)CONSTRAINT PK_SEC_APPLICATION_PERMISIONS PRIMARY KEY (APPLICATION_PERMISION_ID)); CREATE TABLE APPLICATION_ROLES (APPLICATION_ROLE_ID number(15) GENERATED AS IDENTITY, APPLICATION_ID number(15), ROLE_ID number(15)CONSTRAINT PK_SEC_APPLICATION_ROLES PRIMARY KEY (APPLICATION_ROLE_ID)); CREATE TABLE APPLICATIONS (APPLICATION_ID number(15) GENERATED AS IDENTITY, CODE varchar2(30), NAME_AR varchar2(255), NAME_EN varchar2(255), START_DATE date, END_DATE date CONSTRAINT PK_SEC_APPLICATIONS PRIMARY KEY (APPLICATION_ID)); CREATE TABLE PERMISION_DEFINITIONS (PERMISION_DEFINITION_ID number(15) GENERATED AS IDENTITY, CODE varchar2(30), NAME_AR varchar2(255), NAME_EN varchar2(255), START_DATE date, END_DATE date CONSTRAINT PK_SEC_PERMISION_DEFITIONS PRIMARY KEY (PERMISION_DEFINITION_ID)); CREATE TABLE ROLE_PERMISSIONS (ROLE_PERMISSION_ID number(15) GENERATED AS IDENTITY, ROLE_ID number(15), PERMISION_DEFINITION_ID number(15), START_DATE date, END_DATE date CONSTRAINT PK_SEC_ROLE_PERMISSIONS PRIMARY KEY (ROLE_PERMISSION_ID)); CREATE TABLE ROLES (ROLE_ID number(15) GENERATED AS IDENTITY, CODE varchar2(30) NOT NULL, NAME_AR varchar2(255), NAME_EN varchar2(255), START_DATE date, END_DATE date CONSTRAINT PK_SEC_ROLES PRIMARY KEY (ROLE_ID)); CREATE TABLE USER_PERMISSIONS (USER_PERMISSION_ID number(15) GENERATED AS IDENTITY, USER_ID number(15), PERMISION_DEFINITION_ID number(15)CONSTRAINT PK_SEC_USER_PERMISSIONS PRIMARY KEY (USER_PERMISSION_ID)); CREATE TABLE USER_ROLES (USER_ROLE_ID number(15) GENERATED AS IDENTITY, USER_ID number(15), ROLE_ID number(15)CONSTRAINT PK_SEC_USER_ROLES PRIMARY KEY (USER_ROLE_ID));