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 Re: Casing of USERNAME arguments in the new NATIVE procedures
Date Fri, 11 May 2012 18:24:12 GMT
Thanks for the experiments and discussion, Knut and Dag. I have attached 
a patch to DERBY-5762 which may give you the casing behavior you expect. 
I would appreciate your feedback on the patch. The patch makes the 
NATIVE procedures normalize the USERNAME argument before using it to key 
into SYSUSERS. The patch has the following behavior:

1) It preserves the existing NATIVE behavior that allows only one set of 
credentials for a given authorization id. As Knut notes, this diverges 
from the behavior of the other authentication schemes, which allow you 
to declare many sets of credentials for a given authorization id. I 
regard that as a serious security defect in those other schemes.

2) The patch lets you use the same user name string on your connection 
attempts and on your calls to the NATIVE procedures.


On 5/10/12 7:54 AM, Dag Wanvik wrote:
> Thanks for investigating this, Knut.
> As you say, chanding this to behave like BUILTIN in the URL does clear 
> up the confusing user experience when in relation to case-sensitive 
> user name parameters in the SYSCS_UTIL procedures.
> +1 to your suggestion that we forbid N->1 mapping of user names to 
> user authorizationId. We should forbid that when calling 
> At that point we should also check against the roles table, and vice 
> versa when creating roles. The latter operation does try to detect if 
> the new role would conflict with existing non-role authentication 
> identifiers, i.e. user names, but does not yet check againt the new 
> Thanks,
> Dag
> This solves the issue
> Den 10.05.12 16:43, skrev Knut Anders Hatlen:
>> Thanks for writing this up, Rick. Some comments below.
>> Rick Hillegas<rick.hillegas@oracle.com>  writes:
>>> 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';
>> This behaviour is actually different from what you get in BUILTIN. There
>> you would define the two users this way...
>> call syscs_util.syscs_set_database_property('derby.user.FRED', 
>> 'fredPassword');
>> call syscs_util.syscs_set_database_property('derby.user.fREd', 
>> 'fREdPassword');
>> ...FRED can log in like this...
>>      connect 'jdbc:derby:memory:db;user=FRED;password=fredPassword';
>> ...but not like this...
>>      connect 'jdbc:derby:memory:db;user=fREd;password=fredPassword';
>> ...and you'll be refused access if you double-quote the user name...
>>      ij>  connect 
>> 'jdbc:derby:memory:db;user="fREd";password=fREdPassword';
>>      ERROR 08004: Connection authentication failure occurred.  Reason:
>>      Invalid authentication..
>> ...because fREd needs to log in this way...
>>      connect 'jdbc:derby:memory:db;user=fREd;password=fREdPassword';
>> For both FRED and fREd, VALUES CURRENT_USER will return FRED. It looks
>> like the user names are not normalized until they are mapped to their
>> respective authorization identifiers. And when they differ only in
>> casing, they both map to the same identifier, even though they are two
>> different users with different passwords.
>> If NATIVE had done the same thing, having case-sensitive user name
>> parameters in the SYSCS_UTIL procedures would be less confusing, I
>> think, as it would be consistent with how user names are handled in the
>> URL and in the methods
>>    DriverManager.getConnection(url, user, pass)
>>    DataSource.getConnection(user, pass)
>>    ConnectionPoolDataSource.getPooledConnection(user, pass)
>>    XADataSource.getXAConnection(user, pass)
>> I'm not sure how much sense it makes to support user names that only
>> differ in casing, like BUILTIN does, so I wouldn't object to NATIVE
>> refusing to add both FRED and fREd in the same database. Mapping
>> multiple users to the same authorization identifier definitely is
>> confusing, and I doubt that anyone is (intentionally) depending on that
>> feature.

View raw message