Dado,
Glad to have you with us. Incidentally, in answer to two of your
remarks:
1. "current_user", like "current_timestamp" is a built-in SQL92
function, as opposed to a PostgreSQL function. As such, it does not
require () and takes no parameters.
2. You can compile any user-defined function into a view, wherever you
want. Be warned, though, that views with lots of user-defined functions
suffer from a certain lack of optimization as compared with pure SQL
views. As such, I try to stick with strictly formatting functions in
views.
> Lastly, any pointer on how people go about managing that kind of
> access? I
> mean, gazzillions of tables with related data spread all over, and
> many
> different groups of people with different access levels to certain
> rows on
> certain tables?
Actually, in every project I've undertaken, I avoid using the built-in
DB security and create my own security interfaces. This is because, for
an end-user program, you are concered with the user's access to
*interfaces*, not their access to *tables*. In only two occasions can I
imagine DB security making any sense for a user application:
1. The users are SQL experts and want to run their own queires, and have
to be kept away from specific sensitive data.
2. The users have access to certain 3rd-party tools that need to bypass
the regular interface (e.g. IQ Reports, FRx) and the database contains
specific tables of sensitive data.
Additionally, it must be noted that trying to combine a Web interface
with specfic user DB logins eliminates all ability to pool DB
connections and similar web efficiency, as well as forcing you to use
SSL for any extranet app.
Otherwise, one takes these steps:
1. Create a table of users, passwords, and access levels for your app.
2. Create a second table of interfaces and special functions and the
access levels required to reach them.
3. Build your interface so that it connects to the database using a
single super-user login which is kept encrypted and hidden from the
user.
4. In the interface, before letting the user open each screen or run
each function, check their user access against the tables in 1. and 2.
This works quite well for me. It's a *lot* easier to adjust than DB
level security ("all of the accountants need access to the Void
function" can be fixed with a single UPDATE) and remains secure because
the *user* does not know the application password, and without it has no
access to the database at all.
-Josh Berkus
P.S. Keep up the fun cartoons!
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
