Re: creating variable views

Josh Berkus wrote:

> 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.

Hmm... I don't recall any reference to that. Thanks for the clarification.

> 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.

Formatting functions? As in formatted output? Could you give an example?

> > 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.

Ok. Looks like I agree with you in every aspect.
Better yet, it's good ammunition for me to explain here why I'm gonna do they
way you suggested.

> 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)

I'm sorry but I have no idea what you're talking about here.
What is this problem? What is the Void function?

> and remains secure because
> the *user* does not know the application password, and without it has no
> access to the database at all.

It definitely seems to be a much better approach to access level management.
One thing that still isn't clear for me is how to implement access level
control on a per row basis.
Perhaps by implementing a group permissions scheme, where I could combine
groups that describe a job title (and its granted permissions) with groups
that describe projects (and its required permissions).
But still, I'm not sure if I should implement that on the interface or use
views that select rows pertaining only to the user's projects.

Any pointer on that would be immensely appreciated.
But the info you already gave is invaluable.
Thanks a lot.

> -Josh Berkus
>
> P.S. Keep up the fun cartoons!

That's what I really do.
I got pulled out to create this DB just because I worked with DB's before.
10 years ago :(

--
Dado Feigenblatt                                 Wild Brain, Inc.
Technical Director                               (415) 553-8000 x???
dado@wildbrain.com                               San Francisco, CA.