All,
I work at a university and having discussions with our third party ERP vendor, we found out that the vendor's table for biographical information stores all the data from different constituents. The same table stores information about candidates, students, faculty, staff, alumni, etc.
This presents a challenge to us since we will have to create views to achieve row-level security for our end users and delegate access to the department based on their need. For instance, employees in the admissions office should not be able to query the biographical information of other employees, etc
Even in the vendor's application, users in the admissions office are able to access other employee's biographical information since the software runs queries on the same table. That seems to be a huge security issues in my view. The software has been created more than 10 years ago and little progress has been made towards fixing that.
As a general rule of thumb, what are the best practices for designing a biographical table? Should there be different tables for each constituent(candidate, employee, faculty) or one table for all of them?
Thank you.
Best Answer
The "best practice" would be to use an RDBMS that supports row-level security so that you only need the one table.
Example: Oracle Enterprise Edition users would use the Virtual Private Database [VPD] feature to enforce row-level security.
In Oracle Standard Edition, you could simulate VPD by using a VIEW with a WHERE clause that references a database session variable that is retrieved via SYS_CONTEXT() function. But, it won't be as secure as VPD.
I don't know how to do it in other RDBMS systems.