[DERBY-866] Derby User Management Enhancements

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.2.1.6
• Fix Version/s: 10.9.1.0
• Component/s: Services
• Urgency: Normal
• Issue & fix info: Patch Available
• Bug behavior facts: Security
• Assignee: Richard N. Hillegas
• Reporter: Francois Orsini
• Created: 25/Jan/06 03:10
• Updated: 03/Sep/14 08:31
• Resolved: 15/May/12 17:36

Description

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.

Issue Links

• incorporates: DERBY-5607 Deadlock in Java 5 VM when using NATIVE authentication with a client running in the same VM as the server
• incorporates: DERBY-5522 Document the NATIVE authentication scheme.
• is related to: DERBY-2470 No authentication required to restore a backup
• is related to: DERBY-5651 Protocol error when connecting to db with NATIVE authentication using strong password substitution
• is related to: DERBY-5615 NPE in Store when running SELECT in a read-only database accessed via the classpath subprotocol when authentication, authorization, and Java security are turned on
• is related to: DERBY-5618 On Windows, orderly engine shutdown does not release the file handle on a jar file containing a database which was booted using the classpath subprotocol
• is related to: DERBY-5744 Native user authentication: syscs_util.syscs_create_user allows too long user names
• is related to: DERBY-5747 Native user authentication: Docs do not describe what happens to schema and its SQL objects on SYSCS_UTIL.SYSCS_DROP_USER call
• is related to: DERBY-6221 Can't select from SYS.SYSUSERS if you use a WHERE clause in the query
• is related to: DERBY-5647 NATIVE warns about password expiry for DBO
• is related to: DERBY-5548 Implement a GRANT/REVOKE scheme for authorizing system-wide operations
• is related to: DERBY-3282 Add a mechanism for managing users in Derby
• is related to: DERBY-5762 Consider storing a normalized authorization id in SYS.SYSUSERS in order to make NATIVE procedures follow the same casing conventions for usernames which we use on connection urls
• is related to: DERBY-5763 Reference Guide should note the new casing rules for the USERNAME arguments to the NATIVE procedures
• is related to: DERBY-5768 Remove references to NATIVE::LOCAL from Derby error messages.
• is related to: DERBY-5648 Unclear password expiry warning when using separate credentials db
• is related to: DERBY-5657 Message XCY05 for NATIVE authentication is (too?) complex
• is related to: DERBY-5658 Regularize capitalization in error messages for NATIVE authentication
• is related to: DERBY-5741 Native user authentication: improve checking of syntax
• relates to: DERBY-5580 NativeAuthenticationServiceTest getting multiple errors trying to delete files/directories during teardown
• relates to: DERBY-5652 Assert failure when bootstrapping NATIVE with invalid provider string
• relates to: DERBY-5748 Native user authentication: SYSCS_UTIL.SYSCS_MODIFY_PASSWORD accepts old password
• relates to: DERBY-5742 Native user authentication: improve wording of error message
• relates to: DERBY-5644 Typo in error message for NATIVE authentication