db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dag Wanvik <dag.wan...@oracle.com>
Subject Re: Casing of USERNAME arguments in the new NATIVE procedures
Date Thu, 10 May 2012 14:54:49 GMT
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 


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