I did think about using views, but that would only work for SELECT queries since views in H2 are not currently updatable (and we're not open to changing to another database that does, eg HSQLDB). I also tried using views and INSTEAD OF triggers for Insert/Update/Delete, but that got complex quite quickly.
I have since come up with a new (simpler) design of that part of the system so that no queries need to be rewritten. Thankyou for your help and suggestions though On Mon, Jun 28, 2010 at 5:01 PM, Thomas Mueller <[email protected]> wrote: > Hi, > > Why don't you use triggers and views? That way you can make it work > for other databases, and you don't need your own SQL parser / > generator. > >> The use case is to allow 3rd parties access to the same database, but >> to isolate the records that belong to each party automatically in each >> table (without their knowledge). > > I suggest to use views to limit what users can see. This is possible > using the function SESSION_ID within the function. See also > SQLInjection.limitRowAccess(). > >> When they create a table, extra >> fields are added that identifies the party and a few other things. >> When they execute an INSERT query, these fields need to be added to >> whatever query they supplied (otherwise the query will fail). > > You can use default values, computed columns, and / or triggers for that. > >> When >> they execute a SELECT query, these fields need to be added to the >> query (so the party can only see the data that belongs to them) but >> not be a part of the ResultSet so the party doesnt know of the fields >> existance. > > You can use views for that. > >> it was decided that the data stored would have metadata attached >> (depending on which website the application was invoked from). > > Here again you can use SESSION_ID(), USER(), triggers and views. > > Regards, > Thomas > > -- > You received this message because you are subscribed to the Google Groups "H2 > Database" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
