We have a database that will eventually become the system of record for the whole (extremely large) organization.
There is customer data, sales data, product data, etc. that certain web applications need access to, and certain departments need access to. Some users need access to the entire table but usually they only need access to a subset
The current solution is to create a schema for each specific web application and creating a view in that schema that accesses the information they need. Which is insecure because they also have select privileges to the underlying tables right now.
My question: Should we create a schema that contains all of the views that each department/web app would need access to, then give select access to those specific views to the specific schemas?
Is there a better option out there?
