I have a database table that holds records. The table column are company id, department id, section id ,document title and another table that hold the access roles to these records based employee assigned roles.
For eg. DOCUMENT_MANAGEMENT TABLE>>>
1,1,1,1,'Procedure for HR Hiring','some data'
2,1,1,2,'Resumes Submissions','some data'
3,1,2,1,'Study of Engineering Designs','some data'
4,1,2,2,'Engineering Architecture','some data'
The table rows need to be displayed based on a specific role.
And hence the query results needs to be tied to Employee Roles.
If you noticed the above table, the employee with EMPLOYEE_ID (1), should have access to the Department ID (1 & 2) but the Section ID is limited to section 1 for department 1 and all section ID (denoted by -1 value) for department 2.
In short, the Employee 1 can access all the Sections of department 2 but is restricted to only section 1 of department 1.
Likewise, the Employee 2 can access all the Sections of department 1 & 2.
My idea was to restrict the access by the following query and ensuring that the user role table is linked to the primary query.
SELECT DOCUMENT_TITLE FROM DOCUMENT_MANAGEMENT
(DEPARTMENT_ID=-1 OR -1=-1 ) AND
(SECTION_ID=-1 OR -1=-1 ) AND
(UPPER(DOCUMENT_TITLE ) Like '%' +@SEARCH_STR + '%' OR UPPER(DOCUMENT_DESCRIPTION ) Like '%' +@SEARCH_STR + '%' ) AND
(DEPARTMENT_ID IN (SELECT DISTINCT DEPARTMENT_ID FROM EMPLOYEE_ROLE_TABLE WHERE USER_ID=1) OR DEPARTMENT_ID=-1) AND
(SECTION_ID IN (SELECT DISTINCT SUB_DEPARTMENT_ID FROM EMPLOYEE_ROLE_TABLE WHERE USER_ID=1) OR SECTION_ID=-1)
The issue I am having is for The record #2, is not returned by this query for employee id 1 who has the permission to access all the sections. As the value of in the employee role table is -1 (which indicates that whatever value is stored in the section_Id column of table DOCUMENT_MANAGEMENT should be visible to this employee.
What is wrong with the query I had written.