Re: Oracle XE - GUI end-user reporting tool?

"Cris Carampa" <cris119_at_operamail.com> wrote in message news:43abc6c9$0$338$5fc30a8_at_news.tiscali.it...
> Jeremy wrote:
>
>>>HTMLDB?
>>
>>
>> I had thought about that but is that too "developer-focused" or can an
>> end user literally just log in and develop their own enquiries?
>
> Never tested the product myself. According to marketing stuff yes. Perhaps
> it depends on how smart the user is :)
>
> A question for the people who tested the product: is it possible to build
> queries based on a remote database, just providing the database connection
> string?
>
> Kind regards,
>
You can sorta user HTML DB for end-user reporting, but it's security scheme makes it extremely difficult, and somewhat dangerous.

here's what you have to do to set up a reporting schema/user in HTML DB (any clarifications or corrections by the Oracle HTML DB team are welcome)

[ ] create a database account for reporting -- you can do this via HTML DB when creating the workspace or outside of HTML DB
---- if creating the db account outside of HTML DB, grant it at least 2m quota on its default tablespace for objects that HTML DB creates (the quota and default objects can be dropped later) ---- you don't need to grant any privileges -- HTML DB does that for you (in a fairly large way)
-- you will need to DIRECTLY grant select privileges on other schema's objects to this schema
---- HTML DB does not use role privileges since all SQL is processed via DBMS_SYS_SQL
---- this means only objects owned by the schema, objects with public privileges, and objects that the schema has direct privileges on can be accessed via HTML DB
---- any objects for which the schema inherits privileges via roles are NOT accessible via HTML DB
[ ] in HTML DB create an HTML DB workspace -- have the workspace use the newly created reporting schema, if you created it outside of HTML DB, or have HTML DB create the schema for you [ ] in HTML DB, DO NOT create any 'Schema to Workspace Mapping' to attempt to map the source schemas to the workspace -- this will give HTML DB users __FULL__ DML and DDL privileges on the mapped schemas ... this again is due to HTML DB processing all SQL via DBMS_SYS_SQL (this is what I've observed in 1.6, I have not verified if 2.0 has improved on this) [ ] in SQL*Plus or another appropriate tool, revoke the quota from the reporting schema, and revoke the CONNECT role and all privileges which HTML DB granted to the reporting schema, except for CREATE SESSION, and drop all the default objects that HTML DB created in the schema (these are for the HTML DB sample application)

the newly created HTML DB workspace is created with an ADMIN user that can create other HTML DB users within the same workspace. These HTML DB users can be created as workspace administrators, developers, or end-user workspace users.

End-user workspace users only have privilege on HTML DB pages for which HTML DB workspace developers allow permission (via HTML DB's Authorization Schemes, which must be explicitly created for each HTML DB application), they cannot access the SQL Workshop and query-builder functionality (which does not work very well for objects in non-mapped schemas, anyway). Additionally the HTML DB developer(s) must provide the URLs needed for accessing the HTML pages, typically creating a navigation page as an entry point

Workspace developers are able to create private synonyms for any accessible table, but will not be able to access the tables or their synonyms via any of the wizards, including the nifty object browser and query builder that were added in version 2.0 (well, you can get to the table via the ynonym -- a link to the underlying table is provided on the synonym definition screen in the object browser, but the table is not listed in the browser, and JavaScript errors occur when the table's definition is first displayed; likely because the table is not in the object browser list)

So, to use HTML DB as an end-user reporting tool, you have to set it up carefully, forget about using database roles, and require end-users to know SQL (since they will have to use HTML DB as developer-type users, and can only create objects based on SQL statements, not based on wizards or the query builder).

Regarding developing queries on a remote database, I would assume that's possible but only if you write the sql and include the DB link in the sql -- wizards only work on schemas mapped to the workspace

Contact me offline if you want to discuss some reporting options based on mod_plsql that i've been working on.

++ mcs Received on Fri Dec 23 2005 - 12:52:40 CET