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: domain of rick.hillegas@oracle.com
 designates 148.87.113.117 as permitted sender)
Message-ID: <4F9185C6.6020203@oracle.com>
Date: Fri, 20 Apr 2012 08:50:30 -0700
From: Rick Hillegas <rick.hillegas@oracle.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.7; en-US;
 rv:1.9.2.18) Gecko/20110616 Thunderbird/3.1.11
MIME-Version: 1.0
To: derby-user@db.apache.org
Subject: Re: User authorisation
References: 
 <CADuAvzdYCGK=b0WEMSTLkrQad-feKa5fMK2ScA2HXKY2FSKsOQ@mail.gmail.com>
In-Reply-To: 
 <CADuAvzdYCGK=b0WEMSTLkrQad-feKa5fMK2ScA2HXKY2FSKsOQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Hi Trejkaz,

I do not see any responses to your email yet. I did not respond 
immediately because I was not sure what help you wanted from the 
community. Maybe other people were confused too.

It sounds as though you have written your own custom authenticator 
backed by a database which maintains the following information:

a) Credentials for legal users. The credentials are system-wide so that 
a user does not need to remember a different name/password pair for each 
database.

b) A binding of databases to the users who are allowed to connect to 
them. Just because a user can connect to one database does not mean that 
they are allowed to connect to all databases.

I am not sure whether you are the asking the community for the following 
advice:

1) Is there an off-the-shelf, commodity-tested UserAuthenticator which 
does what's described above?

2) ...or does Derby actually provide tools to configure what's described 
above? Derby's security documentation is hard to follow and it's not 
clear whether you can solve this problem with Derby-supplied features.

3) ...or something else?

I can't answer (1) but maybe someone else in the community is aware of 
off-the-shelf solutions in this area.

Concerning (2):

As you know, Derby supplies 3 authentication schemes:

i) Custom-written (that's what you're doing)

ii) Forwarding to an external LDAP service

iii) BUILTIN credentials. This scheme is not safe for production use and 
is only appropriate for testing purposes.

In 10.9, Derby will introduce a fourth authentication scheme:

iv) NATIVE authentication. This provides a package of procedures for 
maintaining user credentials in a Derby database. Think of it as a new 
form of BUILTIN credentials, which is safer, easier to administer, and 
appropriate for production use.

Any of these schemes (except BUILTIN) can be used for purpose (a) above.

Derby also supplies two authorization schemes, each of which can be 
configured per-database:

I) Coarse-grained authorization. This controls who can connect to the 
database and perform read and write operations there. By setting the 
derby.database.defaultConnectionMode property to the value noAccess, you 
can prevent anyone from connecting to a database. Then by setting 
derby.database.fullAccessUsers to a list of valid user names, you can 
relax that restriction, allowing just those users to connect to the 
database. This may achieve purpose (b) above.

II) Fine-grained authorization. This is the SQL Standard GRANT/REVOKE 
style of controlling who can read/write data and execute 
functions/procedures. This does not allow you to control who can connect 
to a database, but it does permit you to control what they can do once 
they have connected. When fine-grained SQL authorization is enabled, a 
user starts out only being able to use tables, views, functions, and 
procedures in their own schema. Other users can GRANT broader access to 
their own SQL objects.

I don't know whether any of this is useful to you, but I wanted to 
summarize some of Derby's security features which might not be easy to 
understand just by reading the user manuals.

Hope this helps,
-Rick


On 4/15/12 7:24 PM, Trejkaz wrote:
> Hi all.
>
> We are using a custom UserAuthenticator to integrate Derby into our
> own user database.
>
> At the moment, this custom UserAuthenticator is also doing the job of
> tracking who can open which database. So of course when we deny
> someone because they don't have access, the error message they get is
> along the lines of "incorrect username or password".
>
> Is there something like a UserAuthoriser which would be more
> appropriate for this task?
>
> I see that there is a lot of documentation about permissions in the
> Derby documentation but all the docs I can find seem to tie into JVM
> security, which we're deliberately not using for performance reasons.
> We only want the coarsest level of security where we can control who
> can connect to which database.
>
> TX
>