db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Casing of USERNAME arguments in the new NATIVE procedures
Date Tue, 08 May 2012 15:08:32 GMT
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';


     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 

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.


View raw message