Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (athena.apache.org: domain of knut.hatlen@oracle.com
 designates 141.146.126.227 as permitted sender)
From: Knut Anders Hatlen <knut.hatlen@oracle.com>
To: <derby-dev@db.apache.org>
Subject: Re: Casing of USERNAME arguments in the new NATIVE procedures
References: <4FA936F0.90706@oracle.com>
Date: Thu, 10 May 2012 16:43:59 +0200
In-Reply-To: <4FA936F0.90706@oracle.com> (Rick Hillegas's message of "Tue, 08
	May 2012 08:08:32 -0700")
Message-ID: <x6sjf8nljk.fsf@oracle.com>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/24.1.50 (usg-unix-v)
MIME-Version: 1.0
Content-Type: text/plain

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.

-- 
Knut Anders