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.
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:
- View other Employee's information.
- 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