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)); 