[DERBY-5762] Consider storing a normalized authorization id in SYS.SYSUSERS in order to make NATIVE procedures follow the same casing conventions for usernames which we use on connection urls

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.9.1.0
• Issue & fix info: Patch Available
• Assignee: Unassigned
• Reporter: Richard N. Hillegas
• Created: 11/May/12 18:01
• Updated: 14/Jun/13 17:14
• Resolved: 17/May/12 13:07

Description

Right now if you want to connect with a lowercase authorization id, you need to double-quote it:

connect 'jdbc:derby:db;user="dbo";password=dbo_password';

But you don't use double-quotes when creating NATIVE credentials for that user:

call syscs_util.syscs_create_user( 'dbo', 'dbo_password' );

I will attach a proof-of-concept patch which causes the NATIVE procedures to normalize USERNAME arguments before using them to key into SYS.SYSUSERS. This preserves the following feature of the current implementation:

1) Only one set of NATIVE credentials can be stored for a given authorization id. Note that this differs from the behavior of other authentication schemes. The other authentication schemes let you store a set of credentials for every upper/lower-case permutation of the authorization id. To me , this seems like a big security hole in those other authentication schemes.

In addition, the proof-of-concept patch has the following behavior:

2) You connect with the same username string which you use when calling syscs_util.syscs_create_user.

If this seems like the right casing behavior, I will write some tests and check this in.

Issue Links

• is related to: DERBY-5763 Reference Guide should note the new casing rules for the USERNAME arguments to the NATIVE procedures
• relates to: DERBY-866 Derby User Management Enhancements