Re: schemas to limit data access

On 8/31/07, Brennan, Sean (IMS) <BrennanS(at)imsweb(dot)com> wrote:
> Hi,
> I was looking for opinions on performance for a design involving schemas. We have a 3-tier system with a lot of hand-written SQL in our Java-based server, but we want to start limiting the data that different users can access based on certain user properties. Rather than update hundreds of queries throughout our server code based on these user properties we were thinking that instead we would do the following:

> 1. Build a schema for each user.
> 2. Reset the users search path for each database connection so it accesses their schema first, then the public schema
> 3. Inside that users schema create about 5 views to "replace" tables in the public schema with the same name. Each of these views would provide only a subset of the data for each corresponding table in the public schema based on the users properties.
> 4. Provide rules for each of these views so they would act as insertable/updateable/deleteable views.
>
> Does anyone have any thoughts on how this may perform over the long-haul? Database cleanup or maintenance problems?

This will work relatively ok if the main tables in the public schema
do not change very much...otherwise you have to drop all the views,
change tables, and re-make. Even still, that's a lot of rules flying
around, and excessive use of rules is asking for trouble.

You may want to explore trying to do it using a single view for each
underlying table, and drop the schemas approach (which I would be
looking at for separate physical tables, not views). A very simple
way to do this that might work for you is:

create view foo_view select * from foo where owner_col = current_user;
plus update, delete rules, etc.

you can then rename the tables in place for seamless app integration.
You could replace the current_user item with an expression but the
performance issues could be large...owner_col could be an array though
as long as it's relatively small (you may want to look at array
indexing techniques if you go the array route).

merlin