lundi 29 décembre 2014

Database design of role permission on resource


I have this database design to manage resources, roles and permissions. role_permission.permission contains an integer as combination of many privileges. The value are power of 2. For example: 2 (READ), 4 (UPDATE), 8 (CREATE), ... I have 3 roles: Employee, Human Resource and System Admin.


enter image description here


PostgreSQL scripts.



CREATE TABLE resource (
resource_id SERIAL NOT NULL,
resource_name VARCHAR(50),
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
PRIMARY KEY (resource_id)
);

CREATE TABLE role_permission (
role_id INTEGER NOT NULL,
resource_id INTEGER NOT NULL,
permission INTEGER,
PRIMARY KEY(role_id, resource_id),
FOREIGN KEY (role_id) REFERENCES role(role_id),
FOREIGN KEY (resource_id) REFERENCES resource(resource_id)
);

CREATE TABLE role (
role_id SERIAL NOT NULL,
role_name VARCHAR(50),
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
PRIMARY KEY (role_id)
);

CREATE TABLE employee_role (
employee_id INTEGER NOT NULL,
role_id INTEGER NOT NULL,
FOREIGN KEY (employee_id) REFERENCES employee(employee_id),
FOREIGN KEY (role_id) REFERENCES role(role_id)
);


When Employee logins, he can:



  1. View other Employee's information.

  2. Edit himself information.


This is the query I use to get permissions of currently logged in user on every resources.



SELECT rp.role_id
,rp.resource_id
,rs.resource_name
,rp.permission
FROM employee_role er
INNER JOIN role r ON er.role_id = r.role_id
INNER JOIN role_permission rp ON r.role_id = rp.role_id
INNER JOIN resource rs ON rp.resource_id = rs.resource_id
WHERE er.employee_id = 4


Then I put them in a map with key resourceName and value permission. This is the Java code to check if a specified field name should be displayed in the screen.



public boolean isAuthorized(String checkedResourceName, String permission) {
Integer currentPermission = authorizationMap.get(checkedResourceName);
if(currentPermission != null) {
return isPermissionGranted(currentPermission, Permissions.valueOf(permission.toUpperCase()));
}
return false;
}

private boolean isPermissionGranted(int currentPermission, Permission p) {
return (currentPermission & p.getValue()) == p.getValue();
}


In layout I call this method. It returns a boolean.



#{authorizationCheckBean.isAuthorized('firstName', 'READ')}


================================


The problem is this database design manages only permissions of the person who affects, not the person who gets affected.


How can I differentiate the case "Employee views other Employee" and the case "Employee views himself"?


For example: Employee A views Employee B, the screen shows: field A & field B. Employee A views himself, the screens shows: field B & field C.





Aucun commentaire:

Enregistrer un commentaire