User Permisssions White List vs Black List 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));
You would need two different user roles: one with the permission granted for most users, and one without the permission for User B.
In the world of security, grants or permissions should always be assigned according to the principle of least privilege (only what the user absolutely needs to do their job). As such, privileges should always be denied by default and assigned explicitly, as with a whitelist. They should never be assigned by default and blocked individually (blacklist). Implicit privileges, especially when combined with explicit ones, are more difficult to audit and troubleshoot. Keep it simple.