Re: [gnome-db] Oracle, Table Names and Progress

[Steve Fosdick <gnomedb pelvoux demon co uk>]

In Oracle the each aspect of the data dictionary is presented via three
views, a DBA view, an ALL view and a USER view.

The DBA views list all objects in the database, regardless of owner and
whether or not the current user has any privilege on them.  These views
would be unsuitable for use by a tool like mergeant unless it insisted
to log in to the database as 'SYSTEM', otherwise we would just get many
'permission denied' type errors.

The ALL views list all objects in the database for which the current
user has some privilege, so this will include all tables etc. that the
user has created as well as tables owned by other users who have granted
the current user access to them, and some system tables and views.  The
ALL views are what the gda-oracle-provider curently uses but then we run
into problems with table names as I have previously decribed.

The USER views list only those objects that belong to the current user. 
These views are the easiest to work with and avoid trouble with table
names, but some user's may be frustrated by not seeing all the tables
they know they should have access to.

So, is response to Santi Camps Taltavull" <santi camps terrassa org> who
wrote:

> 	Yes, I think so.  Also it could be usefull filter tables on
> 	which the connection user hasn't any privilege.   I don't know
> 	if this could be done using system views or if should be done
> 	using OCI, but the software I use to work with Oracle (SQL
> 	Navigator) does it.

using the ALL views rather than the DBA view alreay filters for access
privilege.

Daniel Morgan" <danmorg sc rr com> wrote:

> I think we should still use ALL_TABLES, but filter based on the schema
> the user has chosen.  In mergeant, there could be a combo box that
> allowed a user to select the schema they want to use.
> This is the way it was for software I've used.

This sounds like a good idea - the provider can keep a record of the
current schema name and therefore generate unambigious table names when
talking to Oracle, but the user still sees a simple and natural table
list.  The default schema name would be the same as the username
provided when the connection was openned.

The only question this raises is the interface to the provider used to
set the schema name.  Rodgrigo Moya mentioned bug 104137 which says that
mergeant should make use of the provider schema category
SCHEMA_DATABASES and present an option menu based on what it returns.

Is this what the SCHEMA_DATABASES category is supposed to do - offer
different sets of tables/views which are available without changing the
connection to the server (including who we are logged in as)?  If this
is the case then we have a nice solution to the business of table names,
but it does leave the question as to what the create_database and
drop_database methods are supposed to do in the case of Oracle as, for
what we are proposing, these would amount to creating and dropping
users.

Comments please,
Steve.