Dmitry Yemanov wrote:

>"Jim Starkey" <jas@...> wrote:
>
>
>
>>First, I'd like to ammend my proposal with the addition of
>>
>> upgrade user <username> ...
>>
>>The "upgrade user" command is syntactically parallel to "create user",
>>and is semantically equivalent to "alter user" if the named user exists
>>and semantically equivalent to "create user" if not. The upgrade form
>>allows a single script to both create and update accounts.
>>
>>
>
>We already have RECREATE and CREATE OR ALTER statements. AFAIU, the latter
>one is exactly your UPGRADE statement, but it doesn't introduce additional
>reserved words. Let's be consistent with the existing DDL.
>

First, upgrade has different semantics than recreate. Following
recreation of a table, the table is empty. After an upgrade of a table,
the data is unchanged.

Upgrade is an enormously useful verb. It allows a single script to be
used to both initially and upgrade an application's meta data.
Netfrastructure uses it extensively:

* Doubling clicking on a table object in the Workbench utility
creates a window containing the table definition expressed as an
"upgrade table" that can be readily tweaked and executed
* A module, when installed in a parent application, does an upgrade
to the parent schema, effectively merging any fields specific to
that module into the parent's table.
* The backup restore function does upgrades, so a single backup file
can be an install, upgrade in place, or refresh operation. If,
for any reason, an application instance has extended an
application table, the extensions are preserved.
* When an application is upgraded, all database objects in all
instances a upgraded, bring all to a common minimum state

Key to making this all work is specific intelligence built into the
upgrade verbs. An upgrade of a table, for example, adds but not drop
fields and increases, never shortens, character field lengths, etc.

Database guys (male, female) tend to consider databases as the center of
the world around which all over computing objects revolve. When you
think of applications as a unit, a database becomes an piece requiring
close integration with the other pieces -- code, data, installation,
management, distribution, upgrades. Database features can and should be
designed to facilitate this, rather than just something that a human DBA
manages.

I would like to see upgrade verb supported for all Firebird database object.

>
>And also getting back to the proposed [SET PASSWORD <pwd>] syntax element.
>Some auth methods (e.g. biometrical) doesn't have a password at all. Some
>other methods may have more complicated private auth data. So it seems that
>SET PASSWORD is not universal. Even if we drop my idea of having FB$USERS
>inside a database, maybe the suggested IDENTIFIED BY <tagged_string> (or
>alike) syntax is more appropriate for any possible auth method?
>

Absolutely. I did mention that create/alter/upgrade user would require
open ended parameters, but that I wanted to duck the question until later.

>>different ideas of what should be stored in a system table. We could
>>define a core system table FB$USERS (folks, we're not rdb anymore) with
>>account name as primary key.
>>
>>
>
>It may also contain at least DEFAULT_ROLE. If the user credentials (not
>directly related to authentication, like last/first names) are not subject
>of auth plugin, then FB$USERS may also contain these columns.
>

Good point, particularly if you think the SQL single role model has
merit. I don't. Netfrastructure associates an arbitrary number of
roles with an account, some initially active, some latent. The
application then turns roles on or off based on what it knows about the
client. It's a pity that the SQL committee is just learning about the
web, but it's actually been up and running for sometime now. If they
ever figure out that a server servers many clients with different rights
and responsibilities, who knows what might happen? They might even
figure out that multi-table search would let a user do to a database
what Google does to the entire friggin' world.

>>Someone raised the question as to why the network connection database
>>parameter provided for a list of addresses. I did this because
>>Interbase/Firebird/Vulcan TCP servers can redirect a connection across
>>the network. I want to security plugin to have this information.
>>
>>
>
>Understood. But did you actually try this? Borland has broken this (known as
>"multi-hop") ability when introducing dialects in IB 6.0 and it didn't work
>since that time. I've fixed it in HEAD a couple of weeks ago, but the
>solution requires some testing.
>
>
>

From my experience, it's always worked. It worked when I sold the
system to Ashton-Tate and it worked as soon as Vulcan came up. I don't
even know how they could have broken it without of the world crashing
down on their heads. But they did manage to break quite a few other
good and useful things.