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.
Thanks,
-Rick
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
SYSCS_UTIL.SYCS_CREATE_USER.
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
SYS.SYSUSERS table.
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<[email protected]> 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.
