[
https://issues.apache.org/jira/browse/DERBY-866?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13166546#comment-13166546
]
Rick Hillegas commented on DERBY-866:
-------------------------------------
Hi again Mike,
I would like to address some of your other concerns.
Let me start out by noting that no-one is forcing an application to use NATIVE
authentication. The application has to opt into this scheme. If the application
doesn't need authentication, then this feature will not affect it at all. If
the application uses LDAP or a custom authenticator, then this feature has no
effect either.
Like you, I have worked on systems which had a master database in addition to
the application-specific databases. I share your anxiety about the complexity
of those systems. Because of those anxieties, I resisted this approach for a
long time. However, I have settled on this approach because it is nonetheless
more appealing than the other alternatives which I listed above in my
2011-11-22 comment. Option (I) is impossible to implement securely. Option
(III) is not a solution at all, it just punts the problem back to the user. So
I have settled on option (II).
This proposal tries not to tightly couple the databases. What is being
discussed here is not a master database in the sense of the old systems which
we used to work on. This is just a credentials database. In most systems which
opt into NATIVE authentication, I suspect that there will only be one database.
That is, for them the application database will do double duty as the
credentials database too.
Of course, once we introduce the possibility of a separate credentials
database, then developers may think up other uses for it and try to use it to
perform some of the other functions of a master database. We will have to be
vigilant about that feature-creep.
Let me address some of your other concerns.
o You observe that "a single database problem could prevent access to all
databases". True enough. But this is just a special case of the fact that the
authenticator, whatever it is, is a bottleneck and single-point-of-failure.
LDAP could go down and so could a user-supplied custom authenticator.
o You observe that "now backup of a database becomes more complicated as it can
be out of sync with the master db". Again, I do not see a new danger being
introduced here. The linkage to the credentials database is no stronger than
the linkage to LDAP or to a user-supplied custom authenticator.
o You ask, "are databases now not portable across systems, or only if combined
with master db?" Again, the same response. No new danger is introduced other
than the danger already experienced with LDAP and user-supplied custom
authenticators. If you move your database to another system, then you must make
sure that the authentication service is available there too. I would even go so
far as to argue that NATIVE authentication is more portable than LDAP for the
usual case of an application with a single database which serves as the
credentials database. The credentials are in the database. Nothing could be
more portable than that.
o You ask, "how does this affect replication?" I do not see any replication
issues for a system which has only one database and which uses it as the
credentials database. In systems which manage multiple databases in the same
JVM, I do not think that replication failover has really been thought through.
In those systems the slave database will be on a separate machine and will
somehow have to be copied back to the system hosting the common JVM. After the
slave database becomes usable by the common JVM, it will have access to the
credentials database managed by that JVM. However, thank you for raising this
topic. This is an area we will want to test.
o You ask, "can the master db be encrypted, and if so how do you get multiple
keys from single connection?" Yes, it can be encrypted. As with any encrypted
database, it will have to be booted by a super-user before anyone else can use
it. Once the encrypted database has been booted, no-one else needs to supply
the encryption key. For the common case of a system with one database which
also serves as the credentials database, no additional hurdles are introduced
by NATIVE authentication. For systems which manage multiple databases, the
credentials database will have to be booted with an encryption key before
anyone can do any work. But there is nothing special about this situation
either. LDAP has to be booted and so does a user-supplied custom authenticator.
o You ask, "does collation of the 2 dbs have an effect, now do all dbs in the
system need same collation?" I don't see why. In Derby, user names are stored
in SYSSCHEMAS.AUTHORIZATIONID. The string columns of Derby's catalogs are not
governed by the collation used for user tables. Even if you enable a collation
for user tables, Derby's catalogs continue to obey the comparison/sorting
semantics of databases which don't use collations.
o You observe, "Having a separate db to me seems like even more administration
for derby." I think that this is again a general comment about any
authentication scheme. Yes, someone has to administer LDAP and the
user-supplied custom authenticator.
o You observe, "Also for derby a database is a pretty heavy weight object in
the system. A good Derby application is one that is embedded and uses a single
database. This is the best case usage of Derby. Of course multiple databases
are allowed and work but often lead to unexpected problems." Agreed. I expect
that the usual use case will be an application with a single database which
also serves as the credentials database.
o You continue, "Adding a 2nd db in a normal case may double the memory
requirements of derby given the 1 cache per database model currently
implemented, n background threads per active db, ..." Here we are talking about
a system which has a separate credentials database. That would be a system
which is already managing multiple Derby databases.
Hope that this helps allay your concerns.
Thanks,
-Rick
> Derby User Management Enhancements
> ----------------------------------
>
> Key: DERBY-866
> URL: https://issues.apache.org/jira/browse/DERBY-866
> Project: Derby
> Issue Type: Improvement
> Components: Services
> Affects Versions: 10.2.1.6
> Reporter: Francois Orsini
> Assignee: Rick Hillegas
> Attachments: Derby_User_Enhancement.html,
> Derby_User_Enhancement_v1.1.html, DummyAuthenticator.java,
> UserManagement.html, UserManagement.html, UserManagement.html,
> derby-866-01-aa-sysusers.diff, derby-866-01-ab-sysusers.diff,
> dummyCredentials.properties
>
>
> Proposal to enhance Derby's Built-In DDL User Management. (See proposal spec
> attached to the JIRA).
> Abstract:
> This feature aims at improving the way BUILT-IN users are managed in Derby by
> providing a more intuitive and familiar DDL interface. Currently (in
> 10.1.2.1), Built-In users can be defined at the system and/or database level.
> Users created at the system level can be defined via JVM or/and Derby system
> properties in the derby.properties file. Built-in users created at the
> database level are defined via a call to a Derby system procedure
> (SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY) which sets a database property.
> Defining a user at the system level is very convenient and practical during
> the development phase (EOD) of an application - However, the user's password
> is not encrypted and consequently appears in clear in the derby.properties
> file. Hence, for an application going into production, whether it is embedded
> or not, it is preferable to create users at the database level where the
> password is encrypted.
> There is no real ANSI SQL standard for managing users in SQL but by providing
> a more intuitive and known interface, it will ease Built-In User management
> at the database level as well as Derby's adoption.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
