Re: creating variable views - Mailing list pgsql-sql

Tom,

> I don't understand the distinction you're trying to make here.  In
> general, a view column that is implemented as a function will give
> the
> optimizer headaches if you refer to it in WHERE --- the simplicity or
> complexity of the function has got nothing to do with that AFAICS.

OK.  I thought that I noticed a difference between simple SQL functions
and PL/pgSQL functions in this respect, but I could easily be mistaken.
> Seems to me that such a change could be trivial, or a serious
> headache,
> with *either* SQL GRANT-based security or application-based security.
> The critical factor is going to be whether you represented the
> "voiding"
> access privilege separately from the other special privileges of the
> sysadmin.  I don't see how one implementation is going to encourage
> you
> to have that foresight better than the other one would.

It's a little difficult to explain wihtout a demo.  It is possible,
using a database with many views and functions which are the primary
methods of DML and query access to develop analogous functionality using
GRANT and REVOKE on the database objects.  In fact, you *have* to do
this if your users will have command-line access to the database.
However, it's not easy.

The interface-based system I use, in its simplest incarnation, takes
into account only a 5-level user access system with no departmental user
groups.  Thus each user is: 0:No Access, 1:Read-only, 2:Data Entry,
3:Full Access, or 5:Admin.  I create a table that lists all of the
interfaces, and the required access level for each interface; if a user
fails the access test for an interface, they are denied access with a
firendly error message ("I'm sorry, you do not have sufficient access
...").

It's very simple to administrate because in order to change the access
to a particular feature on has only to change the access level number.
And the results of the access level test are much easier to trap in your
client-side code than the results of a DENY access error would be.  The
last thing I want for my users is to have to contend with "5301: No
rights on TABLE client_contacts."

-Josh


______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco