Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6159Qa38245 for ; Sun, 1 Jul 2001 01:09:26 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 79545 for ; Sat, 30 Jun 2001 22:09:24 -0700 From: "Josh Berkus" Subject: RE:creating variable views To: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Sat, 30 Jun 2001 22:09:24 -0700 Message-ID: MIME-Version: 1.0 Content-Type: message/rfc822 X-Archive-Number: 200107/1 X-Sequence-Number: 3782 From: "Josh Berkus" Subject: Re: [SQL] creating variable views To: dado@wildbrain.com X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Sat, 30 Jun 2001 22:04:01 -0700 Message-ID: In-Reply-To: <004801c101c1$6e6c9410$b3f22ccf@dado.org> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="_===79542====davinci.ethosmedia.com===_" This is a multi-part MIME message --_===79542====davinci.ethosmedia.com===_ Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit 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 --_===79542====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===79542====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===79542====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===79542====davinci.ethosmedia.com===_--