I understand that this has been discussed before, but I was hoping
that somebody might have some fresh ideas on the problem.
I'm using Postgres for my web app, I users that interface with the
database through the app. All records are classified with an industry
and an occupation. Currently permissions are based around these two
values, i.e. User John Doe can view all records with industries 1-10
and occupations 5-50, user Jane Doe can view all records with ALL
industries and occupations 1-20.
For all the users I am maintaining 2 tables (user_can_see_industry and
user_can_see_occupation), at the application level I am pulling these
values out of the database and constructing my queries. I'm finding
that this approach is not really the best way to manage the
permissions, but at the same time I'm having trouble figuring out a
better way.
Users belong to working groups, and while I'd love to implement a view
managing the permissions for each group this does not really meet the
business needs of the users (more senior users need to be able to see
more records than junior users, group scope is constantly changing,
the scope of each group is not clearly defined (this is probably my
biggest problem, but that's a different story)).
I'm also noticing that with my queries constructed the way they are at
the moment I'm hitting a bit of a performance bottleneck, I'm using a
very long WHERE/OR statement in each query, which will occasionally
slow it down.
What would the benefits be implementing a VIEW for each user, would it
improve query time at all? When updating their permissions I would
obviously have to blow away the old view and create a new one, are
there any negative aspects that approach.
I've thought of using different schema's to limit access, but I'm
having trouble getting my head around that approach. My understanding
is that if User A adds a record to Schema A then User B using Schema B
will not be able to see these records, if this is the case this will
not fit my needs as users should be able to see other users records so
long as they have the correct privileges. The situation can also arise
where User A enters a record but is not allowed to view it after the
fact (the record was given an industry/occupation combination User A
is not allowed to view).
Anyway, I'm leaning heavily towards implementing individual views, but
I was just wondering if anyone has any better ideas, or any reasons to
shoot down the idea of using them.
Cheers,
Conor