db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (Updated) (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-866) Derby User Management Enhancements
Date Thu, 12 Jan 2012 18:41:40 GMT

     [ https://issues.apache.org/jira/browse/DERBY-866?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Rick Hillegas updated DERBY-866:
--------------------------------

    Attachment: derby-866-09-ad-nativeAuthenticationService.diff

Attaching derby-866-09-ad-nativeAuthenticationService.diff. This is the first rev of a patch
which adds a NATIVE authentication service to Derby. Regression tests passed cleanly for me.
The patch also passes a battery of ad-hoc tests, but I have not yet collected them into a
formal regression test for this new functionality. So the patch is not ready for commit. I
am posting the patch now in order to get feedback on whether this approach seems reasonable.

The new authentication service follows the pattern of the existing BASIC authentication service
(BasicAuthenticationServiceImpl). I did not add support for network password substitution
but it may be possible to add that in a later patch.

I have taken the approach of normalizing the user name as a SQL identifier when storing credentials
at database-creation time. This causes the values in SYSSCHEMAS.AUTHORIZATIONID and SYSUSERS.USERNAME
to agree, making it possible to join on those columns. The user name is also normalized at
connection-authorization time. I haven't thought through the implications of whether syscs_create_user,
syscs_reset_password, and syscs_drop_user should also normalize the username. Your opinions
are welcome. Perhaps a clear preference will emerge as I write more tests.

Ad-hoc tests have verified the following behavior (at least in simple configurations):

1) Creating a credentials DB. The initial credentials are automatically stored in SYSUSERS
and the following properties are persisted:

    derby.database.sqlAuthorization=true
    derby.authentication.provider=NATIVE::LOCAL

2) Creating a non-credentials DB, using the credentials DB to authorize the operation.

3) Shutting down a credentials DB and restarting it.

4) Trying to create a database with credentials which are not stored in the credentials DB.
This operation fails as expected.

5) Trying to connect to an existing database with credentials which are not stored in the
credentials DB. This operation fails as expected.

6) Verifying that the following system property setting is sufficient to turn on authentication
and SQL authorization:

    derby.authentication.provider=NATIVE:$credentialsDB:LOCAL

    or

    derby.authentication.provider=NATIVE:$credentialsDB

That is, there is no need to also set the following properties:

   derby.connection.requireAuthentication=true
   derby.database.sqlAuthorization=true

7) Verifying that a credentials DB remembers that it is a credentials DB. When you reboot
it, the connection is authenticated using credentials stored in the local SYSUSERS table.

Tests have been run on disk-based and in-memory databases and on desktop and JSR169 platforms.

I will start assembling a regression test out of the above cases. Even if my approach needs
to be reworked, these tests are a low-bar for any implementation.

I know that code has not yet been written to support the following features:

i) Prevent NATIVE authentication from being enabled in a pre-existing database which lacks
credentials for the DBO.

ii) Enable password substitution over the network.

Many more tests need to be written. In addition, more code may need to be written in order
to implement the full functional spec. At a minimum, the following behaviors need to be verified
and perhaps coded:

A) Once a credentials database is created, NATIVE authentication cannot be disabled for that
database.

B) Similarly, SQL authorization cannot be disabled for a credentials DB.

C) Upgrade paths have not been tested.

D) No testing has been done with databases which are accessed via jar files, the classpath,
or http/https URLs.

E) No testing has been done over the network.


Touches the following files:

----------

M       java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
M       java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
M       java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java

Add a method to retrieve a user's stored credentials. Also added db creation logic to turn
on SQL authorization when NATIVE authentication is enabled.

----------

M       java/engine/org/apache/derby/impl/services/monitor/BaseMonitor.java
M       java/engine/org/apache/derby/iapi/services/monitor/ModuleFactory.java

Added a new method which makes it possible for code outside the Store to calculate the canonicalized
form of a database name. This is necessary in order to figure out whether the local database
is the credentials DB identified by the derby.authentication.provider property.

----------

M       java/engine/org/apache/derby/iapi/services/property/PropertyUtil.java
M       java/engine/org/apache/derby/iapi/reference/Property.java

Added code so that logic outside the Authentication module can figure out whether NATIVE and
NATIVE::LOCAL authentication are enabled.

----------

M       java/engine/org/apache/derby/impl/jdbc/authentication/SpecificAuthenticationServiceImpl.java
A       java/engine/org/apache/derby/impl/jdbc/authentication/NativeAuthenticationServiceImpl.java
M       java/engine/org/apache/derby/impl/jdbc/authentication/AuthenticationServiceBase.java
M       java/engine/org/apache/derby/iapi/jdbc/AuthenticationService.java
M       java/engine/org/apache/derby/modules.properties

The NATIVE authentication service.

----------

M       java/engine/org/apache/derby/catalog/SystemProcedures.java

Refactored some code so that the DBO's credentials can be stored at database creation time.

----------

M       java/engine/org/apache/derby/impl/jdbc/EmbedConnection.java

Added bootstrapping logic to skip NATIVE authentication when the system-wide credentials DB
is being created.

----------

M       java/engine/org/apache/derby/loc/messages.xml
M       java/shared/org/apache/derby/shared/common/reference/SQLState.java

New error messages.

                
> Derby User Management Enhancements
> ----------------------------------
>
>                 Key: DERBY-866
>                 URL: https://issues.apache.org/jira/browse/DERBY-866
>             Project: Derby
>          Issue Type: Improvement
>          Components: Services
>    Affects Versions: 10.2.1.6
>            Reporter: Francois Orsini
>            Assignee: Rick Hillegas
>         Attachments: Derby_User_Enhancement.html, Derby_User_Enhancement_v1.1.html, DummyAuthenticator.java,
UserManagement.html, UserManagement.html, UserManagement.html, UserManagement.html, derby-866-01-aa-sysusers.diff,
derby-866-01-ab-sysusers.diff, derby-866-02-ag-createDropUser.diff, derby-866-03-aa-resetModifyPassword.diff,
derby-866-03-ab-resetModifyPassword.diff, derby-866-04-aa-fixRolesTest.diff, derby-866-05-aa-grantRevoke.diff,
derby-866-06-aa-upgradeFrom10.1.diff, derby-866-07-aa-removeSQLPassword.diff, derby-866-08-aa-passwordHasher.diff,
derby-866-08-ab-passwordHasher.diff, derby-866-08-ad-passwordHasher.diff, derby-866-09-ad-nativeAuthenticationService.diff,
dummyCredentials.properties
>
>
> Proposal to enhance Derby's Built-In DDL User Management. (See proposal spec attached
to the JIRA).
> Abstract:
> This feature aims at improving the way BUILT-IN users are managed in Derby by providing
a more intuitive and familiar DDL interface. Currently (in 10.1.2.1), Built-In users can be
defined at the system and/or database level. Users created at the system level can be defined
via JVM or/and Derby system properties in the derby.properties file. Built-in users created
at the database level are defined via a call to a Derby system procedure (SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY)
which sets a database property.
> Defining a user at the system level is very convenient and practical during the development
phase (EOD) of an application - However, the user's password is not encrypted and consequently
appears in clear in the derby.properties file. Hence, for an application going into production,
whether it is embedded or not, it is preferable to create users at the database level where
the password is encrypted.
> There is no real ANSI SQL standard for managing users in SQL but by providing a more
intuitive and known interface, it will ease Built-In User management at the database level
as well as Derby's adoption.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message