[
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
