Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: local policy)
Date: Fri, 06 Jun 2008 06:20:00 -0700
From: Rick Hillegas <Richard.Hillegas@Sun.COM>
Subject: Re: Database connection not bound to default database schema when
 authenticating through LDAP?
In-reply-to: <4848E214.9010903@vivodinet.gr>
Sender: Richard.Hillegas@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <48493980.8070709@sun.com>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 8BIT
References: <484887F2.1020402@vivodinet.gr> <4848DD6A.7000200@sun.com>
 <4848E214.9010903@vivodinet.gr>
User-Agent: Thunderbird 2.0.0.14 (Macintosh/20080421)

Hi Andreas,

I can see that for many applications it would be useful to have a 
concept of a database-wide default schema, that is, a schema that all 
users start out in when they get a fresh connection. That is not the way 
Derby works. For Derby, the default schema is user-specific, not 
database-wide. This is true even in the degenerate case of an embedded 
application which does not run with authentication turned on: in that 
case the username defaults to APP and the default schema is APP's schema.

The 2003 SQL standard does not offer any guidance on this topic. 
According to part 2, section 4.37.2 (SQL-session identification), the 
default schema of a session is vendor-defined.

I don't remember any deep reason about why Derby has this default 
behavior. It may be nothing more complicated than the fact that the 
original programmers were familiar with this default from previous 
databases which they had worked on.

Some databases let you bind a default schema to a username. See for 
instance http://msdn.microsoft.com/en-us/library/ms190387.aspx. I think 
it would be straightforward to build such a mechanism for 
Derby--although this would take you into session startup logic and the 
devil could be in the details. I think there are plenty of people around 
who understand that logic and who could coach a newcomer through this 
project.

In the meantime, the only workaround I see is for your application to 
force the schema to a default value when your application hands out 
connections.

Please pardon me if I have talked past your real concern.

Hope this helps,
-Rick

Andreas Kyrmegalos wrote:
> Thanks �ystein,
>   I was just checking that now too, with builtin authentication. Same 
> result. My impression was that default schemas are attached to the 
> database not the connecting user. So, that since a user has been 
> authenticated and if there are no authorization locks in place there 
> wouldn't be a need to set the schema each time a user connects. 
> Something like a default behavior. But as you said the default 
> behavior is have the connecting username as the starting schema. 
> Thanks again. What I 'm trying to figure out is the rational behind 
> such an approach. If someone could shed some light on this?
>
> Andreas
>
> �ystein Gr�vlen wrote:
>>
>> The default schema is the same as the username, regardless of whether 
>> it exists or not.  You can use 'set schema' to switch to another schema.
>>
>