Re: Easy power-user ad-hoc tool

That is a very good point.

We have a line in PRODUCT_USER_PROFILE to disable HOST for SQL*Plus and so it's just ignored.

Plus we log all input so we know who tried to do something that they shouldn't have.

Bob

TurkBear wrote:

> Unless you parse the input VERY carefully, a malicious user could truly trash your server...
> Any Sql with a HOST command in it could access the OS and do anything....
>
> Robert Malos <bob.malos_at_csu.mnscu.edu> wrote:
>
> >If you're just looking for a query tool:
> >
> >I created a web-based ad-hoc query tool back in 1995 that has been in use
> >at the University of Minnesota (basically unchanged) ever since. It has
> >enabled many hundreds of users to build and run hundreds of thousands of
> >queries. I'm not trying to sell anything (and I don't own it anyway) but
> >it would be very easy to duplicate.
> >
> >The key is that all it does is generate SQL (or let a user just type some
> >in) that simply gets executed as a batch via that database's command line
> >tool (isql for MS or SQL*Plus for Oracle). That means that it can accept
> >absolutely anything the the command-line tool accepts - anything. And the
> >results are returned to the web server as a simple text file (and all the
> >security is handled by the database). Then I found a third-party tool
> >called DBMS/Copy that lets you convert text files to many other formats
> >"on the fly" (with no need to create a "conversion" beforehand). But both
> >isql and SQL*Plus let you return basic comma-separated files.
> >
> >A very basic web form that just presents a text box and sends that text
> >through the command-line tool is very easy to do. For the
> >Query-By-Example I just wrote a series of scripts that present first a
> >list of tables then a list of columns and then a form to generate
> >conditions. It can easily generate Joins and Group-By's by reading the
> >database schema and acting accordingly.
> >
> >So this tool has been used by everyone from true beginners who don't know
> >anything about SQL to expert SQL writers to DBAs (it will also accept
> >administrative commands and stored procedures and PL-SQL, etc --
> >*anything* that isql or PL*SQL will accept).
> >
> >True - this is *not* a report writer. But over the years I (and others)
> >have tried to replace it with something much fancier but our users
> >continue to prefer this "primitive" tool (it was finally changed from CGI
> >scripts to Active Server Pages but it still works the same way). I have
> >experience with both Cognos and Brio (and have looked at a lot of other
> >tools). If a tool is truly "thin client" then all the work is done at the
> >web server (or application server) level and that's fine if the result
> >sets will always be small. But our users sometimes brought back thousands
> >(or every tens of thousands) of rows and then that server's memory got
> >used up very quickly. If a tool is "fat client" then you have a plug-in
> >or Java applet or something else which has to be downloaded to each
> >end-user and performance depends on the power of that user's desktop.
> >
> >And - best of all - if you make your own simple query tool then you can
> >add some little custom features that no off-the-shelf tool would have
> >(over the years we've done things like 'saved queries' for each user, a
> >query log of every bit of SQL that has ever been run, row and time
> >limits, etc.).
> >
> >The best off-the-shelf tool I've seen lately is Hotquery -
> >www.hotquery.com (written with Cold Fusion). The only problem I had with
> >it is that it *is* truly thin-client so my web-server's memory got
> >swamped pretty quickly (but they may have some way to deal with that
> >now).
> >
> >Bob Malos
> >
> >
> >Jared wrote:
> >
> >> Hi, all -
> >>
> >> I am looking to replace Access within my user base. With one
> >> exception there is no VBA to worry about (and I will take care of the
> >> exception). Basic requirements are: ability to access Oracle and SQL
> >> Server (2000), output Excel files. I am trying to avoid the tools
> >> that require catalogs in teh middle (we have Cognos Query in house if
> >> I want to go that route).
> >>
> >> I have tried Discoverer and consider it slow, btw, but in terms of
> >> user interface it is OK.
> >>
> >> Without igniting a flame war, I would appreciate some feedback fro
> >> people who have implemented such a tool within their user base with
> >> good results.
> >>
> >> Thank you!
> >>
> >> Regards,
> >> jh
Received on Wed Dec 18 2002 - 15:14:03 CET