During buddy-testing, some confusion arose around how to case the
USERNAME arguments of NATIVE procedures. Derby user names (specified on
connection urls) are case-insensitive, but the USERNAME arguments to the
NATIVE procedures are case-sensitive. This means that you create a user
this way...
-- create two separate users
call syscs_util.syscs_create_user( 'FRED', 'fredPassword' );
call syscs_util.syscs_create_user( 'fREd', 'fREdPassword' );
...FRED can log in like this...
connect 'jdbc:derby:db;user=fred;password=fredPassword';
or
connect 'jdbc:derby:db;user=fREd;password=fredPassword';
...while fREd has to double-quote his user name in order to log in:
connect 'jdbc:derby:memory:db;user="fREd";password=fredPassword';
The confusion arises because of the following:
1) Derby user names are SQL identifiers - That means that if you don't
double-quote them, Derby will uppercase them internally. That is, the
user name attribute is case-insensitive although the password attribute
is case-sensitive. This is simply an odd fact about how Derby operates.
It stems from the tight association among Derby user names, schema
names, and authorization ids. The Derby wiki describes this situation
(http://wiki.apache.org/db-derby/UserIdentifiers), although I think that
the explanation is hard to follow.
2) The NATIVE procedures follow the pattern of the other SYSCS_UTIL
procedures: arguments which represent SQL identifiers (like schema and
table names) must be cased correctly. Unless the SQL object was declared
with double-quotes, it is internally represented as uppercase; when you
call a system procedure on that SQL object, you need to uppercase the
name. This is the pattern across all of the system procedures in
SYSCS_UTIL. Note that a different pattern applies to the jar file
procedures in SQLJ. There Derby follows the rules laid out by the SQLJ
committee fifteen years ago. The SQLJ arguments munge schema and object
names into a single string, forcing you to put double-quotes around the
schema name and the object name. This also is how you have to treat JDBC
metadata methods which collapse 2-part SQL Standard names into a single
string.
Here are some proposals for how to address this confusion:
A) Document the confusion. The Reference material on the NATIVE
procedures should note that the USERNAME arguments are SQL identifiers
like the schema and object names in other SYSCS_UTIL procedures. Usually
you want to uppercase them.
+ Simplest solution.
- Does nothing to clean up the confusion about username casing in Derby.
B) Make the USERNAME arguments of the NATIVE procedures require double
quotes like the object name arguments of the SQLJ procedures.
+ Might be less confusing to some end-users. Whenever you use
double-quotes for a user name on a connection url, you would also use
double quotes when managing that user's credentials.
- The NATIVE procedures would not handle SQL identifiers the way that
other SYSCS_UTIL procedures do.
- Involves more changes to the code and docs late in the release cycle.
C) Change the behavior of user names on connection requests when NATIVE
authentication is on. That is, in this situation make user name
attributes case-sensitive like password attributes are.
+ Would be the least confusing solution.
- Lots of edge-cases here.
- Would be a hassle for legacy applications migrating off of BUILTIN
onto NATIVE authentication.
D) Replace the NATIVE procedures with new Derby-specific SQL statements.
The statements would need to be designed in such a way that we would be
confident that the SQL committee would not create a conflicting standard
in this area. We would also have to create special GRANT/REVOKE syntax
for managing permissions for the new commands.
+ This would make it clear that NATIVE user names are SQL identifiers,
like the user names on connection urls.
- This is a fair amount of effort late in the release.
I am inclined to pursue option (A) unless there is a strong consensus to
pursue one of the other options. If you have opinions about this topic,
please share them by the end of this week. The other options have the
potential of delaying the release of 10.9.
Thanks,
-Rick
