Just wanted to see if there was a much better way of doing this. We have a web application where user data needs to be separated as much as possible and where access needs to be controlled and restricted on several levels. Most data coming into the system is from anonymous sources so the application itself needs restricted insert/update rights, and our clients need pretty much full access to the data but we still want to funnel all access through predefined views and functions.
The best thing I have come up with so far is to create a user and schema for each client. Client's accessing the database provide thier username and password, in addition to our own pre authentication and our code does all database access through views and functions. Now for new transactions that come in from the anonymous sources, I would create a secondary user for every schema that has limited permissions for inserting new data.
We also have a Kerberos infrastructure and could use that for database authentication.
The number of schema's will be fairly large (in the thousands depending on how many users per database). This plan I think offers the best separation of data and permissions without using completely separate databases. Any thoughts?
Chris