Aaron
Thanks for this one - I had actually wondered about doing that but the
trouble is that they say that they need up to the minute reports not
"as of last night". Indeed, I do have another app where I
do just that because I find that reports indexes/requirements are very
different to transactional type requirements. However, you have
made me make up my mind to see if I can persuade them to work on data
that is a day old.
What we really need is a good graphical (Windows based) query/report tool
that allows us to configure the tables to be viewed etc etc and, most
importantly, is license free. There's fame for someone
there....
Hilary
At 10:07 21/07/2006 -0500, Aaron Bono wrote:
On 7/21/06, Hilary Forbes
<hforbes@dmr.co.uk> wrote:
Dear All
Next question on privileges! Can I safely remove all privileges from the system catalog tables for a user and still enable them to select from the public schema? I guess the real question is what access rights does an ordinary user have to have to the system catalog tables in order for postgres to work properly given I only ever want the user to be able to SELECT from views.
This is all brought about by a user who wants to use MS Access Query for adhoc queries to a (small) database via ODBC. (the database itself drives a major web application.) I can't find an easy way of preventing them seeing that tables exist but I don't want them trying to manually update any tables of mine or postgres's thank you very much! (Don't shoot the messenger - there's no accounting for user's tastes!)
This doesn't address the permissions issue but is a suggestion regarding your approach on granting access to an untrusted user for reporting purposes...
Whenever I have a user that needs to do reporting from any production database, I set up a separate reporting database. If possible, this is placed on a completely different machine and the data is fed from production to the reporting server nightly. Tech savy business users (the ones who typically need this kind of access) are notorious for writing bad queries and causing performance problems. If you isolate their activity, you will eliminate lots of headache. If they cause a problem on the reporting server, you don't have to drop everything to get the problem fixed like you would if they caused problems on the live database.
An argument that the users who run the reports often make is that they need the most current data. Most of the time this is not the case. My recommendation is to let the users create the queries they need to run for realtime data on the reporting database, then pass them by an expert for review before putting them into an IT controlled reporting application.
Bottom line, be careful about giving non-experts too much access to your live production data.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
Hilary Forbes
DMR Limited (UK registration 01134804)
A DMR Information and Technology Group company (www.dmr.co.uk)
Direct tel 01689 889950 Fax 01689 860330
DMR is a UK registered trade mark of DMR Limited
**********************************************************