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.
