Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Fri, 16 Dec 2011 19:34:31 +0000 (UTC)
From: "Rick Hillegas (Updated) (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: 
 <1626701944.20449.1324064071131.JavaMail.tomcat@hel.zones.apache.org>
Subject: [jira] [Updated] (DERBY-866) Derby User Management Enhancements
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


     [ 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-02-ag-createDropUser.diff

Attaching derby-866-02-ag-createDropUser.diff. This patch adds the syscs_create_user() and syscs_drop_user() system procedures. I will run regression tests.

I had originally planned to add just syscs_create_user(). However, I found myself pulling on a ball of yarn because CleanDatabaseSetup needs to return to an initial state as far as possible, and that means dropping users who were added as part of the tests.

I ended up pulling on another ball of yarn, which bloated up the number of files touched by this patch: I added StandardException to the exception signature of a method in the string datatypes. This percolated throughout the SQL interpreter, adding StandardException to the signatures of many methods. I debated swallowing the exception lower down and reporting it in the log. However, after some thought I decided that the changed methods all deserved to throw StandardException and should have done so from the start.

In implementing syscs_create_user() I faced the problem that the implementing method phrases its password arg as a char[] array rather than a String. In order to get around the method resolution problems, I introduced a new, internal SQLPassword type. This type is not available and is mostly invisible to Derby users. SQLPassword has the following behavior:

1) It appears in DatabaseMetaData.getProcedureColumns() as a VARCHAR type.

2) But its corresponding Java type is char[] rather than String.

3) When the SQL interpreter sees a SQLPassword, it treats the contents as a password which needs to be smudged out as soon as possible.

4) I have found one place where knowledge of this internal type leaks out to users: If you select the aliasinfo of the syscs_create_user() procedure and then call the toString() method on that aliasinfo, you will see that the password arg has type PASSWORD rather than VARCHAR.


Touches the following files:

------------

M       java/engine/org/apache/derby/impl/sql/compile/TypeCompilerFactoryImpl.java
M       java/engine/org/apache/derby/impl/sql/compile/CharTypeCompiler.java
M       java/engine/org/apache/derby/impl/sql/compile/SQLToJavaValueNode.java
M       java/engine/org/apache/derby/iapi/services/io/RegisteredFormatIds.java
M       java/engine/org/apache/derby/iapi/services/io/StoredFormatIds.java
M       java/engine/org/apache/derby/iapi/types/TypeId.java
M       java/engine/org/apache/derby/iapi/types/DataValueDescriptor.java
M       java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java
M       java/engine/org/apache/derby/iapi/types/DataType.java
M       java/engine/org/apache/derby/iapi/types/StringDataValue.java
A       java/engine/org/apache/derby/iapi/types/SQLPassword.java
M       java/engine/org/apache/derby/iapi/types/SQLChar.java
M       java/engine/org/apache/derby/iapi/types/SQLVarchar.java
M       java/engine/org/apache/derby/iapi/types/DataValueFactoryImpl.java
M       java/engine/org/apache/derby/iapi/types/DataValueFactory.java
M       java/engine/org/apache/derby/iapi/types/ReaderToUTF8Stream.java
M       java/engine/org/apache/derby/catalog/types/BaseTypeIdImpl.java
M       java/engine/org/apache/derby/catalog/types/TypesImplInstanceGetter.java

Support for new internal SQLPassword datatype.


------------

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/impl/sql/catalog/DD_Version.java
M       java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
M       java/engine/org/apache/derby/catalog/SystemProcedures.java

Support for new system procedures syscs_create_user() and syscs_drop_user().


------------

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

New error message.


------------

M       java/engine/org/apache/derby/impl/sql/GenericParameter.java
M       java/engine/org/apache/derby/impl/sql/execute/ValueRow.java
M       java/engine/org/apache/derby/impl/sql/execute/InsertResultSet.java
M       java/engine/org/apache/derby/impl/sql/execute/IndexValueRow.java
M       java/engine/org/apache/derby/impl/sql/execute/CardinalityCounter.java
M       java/engine/org/apache/derby/impl/sql/execute/RowUtil.java
M       java/engine/org/apache/derby/impl/sql/execute/RIBulkChecker.java
M       java/engine/org/apache/derby/impl/sql/execute/BaseActivation.java
M       java/engine/org/apache/derby/impl/sql/execute/TemporaryRowHolderImpl.java
M       java/engine/org/apache/derby/impl/sql/execute/BasicSortObserver.java
M       java/engine/org/apache/derby/impl/sql/GenericParameterValueSet.java
M       java/engine/org/apache/derby/impl/sql/GenericActivationHolder.java
M       java/engine/org/apache/derby/impl/jdbc/EmbedPreparedStatement.java
M       java/engine/org/apache/derby/impl/services/daemon/IndexStatisticsDaemonImpl.java
M       java/engine/org/apache/derby/iapi/sql/Activation.java
M       java/engine/org/apache/derby/iapi/sql/execute/ExecRow.java
M       java/engine/org/apache/derby/iapi/sql/ParameterValueSet.java
M       java/engine/org/apache/derby/iapi/types/SQLRef.java
M       java/engine/org/apache/derby/iapi/types/XML.java
M       java/testing/org/apache/derbyTesting/unitTests/store/T_AccessRow.java

SQLException percolation across method signatures. Mostly these are trivial changes. The change to EmbedPreparedStatement looks big but it just involves wrapping the existing code in a try/catch block in order to transform the StandardException into a SQLException.


------------

M       java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
A       java/testing/org/apache/derbyTesting/functionTests/tests/lang/NativeAuthProcs.java
M       java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_9.java
M       java/testing/org/apache/derbyTesting/junit/CleanDatabaseTestSetup.java

Regression and upgrade tests for new system procedures.

                
> 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, derby-866-01-aa-sysusers.diff, derby-866-01-ab-sysusers.diff, derby-866-02-ag-createDropUser.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