From derby-dev-return-90361-apmail-db-derby-dev-archive=db.apache.org@db.apache.org Fri Sep 2 19:23:35 2011 Return-Path: X-Original-To: apmail-db-derby-dev-archive@www.apache.org Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 4126175A3 for ; Fri, 2 Sep 2011 19:23:35 +0000 (UTC) Received: (qmail 25225 invoked by uid 500); 2 Sep 2011 19:23:35 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 25172 invoked by uid 500); 2 Sep 2011 19:23:34 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 25164 invoked by uid 99); 2 Sep 2011 19:23:34 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 02 Sep 2011 19:23:34 +0000 X-ASF-Spam-Status: No, hits=-2000.5 required=5.0 tests=ALL_TRUSTED,RP_MATCHES_RCVD X-Spam-Check-By: apache.org Received: from [140.211.11.116] (HELO hel.zones.apache.org) (140.211.11.116) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 02 Sep 2011 19:23:31 +0000 Received: from hel.zones.apache.org (hel.zones.apache.org [140.211.11.116]) by hel.zones.apache.org (Postfix) with ESMTP id EFC9546CCC for ; Fri, 2 Sep 2011 19:23:09 +0000 (UTC) Date: Fri, 2 Sep 2011 19:23:09 +0000 (UTC) From: "Rick Hillegas (JIRA)" To: derby-dev@db.apache.org Message-ID: <1873950791.11801.1314991389979.JavaMail.tomcat@hel.zones.apache.org> Subject: [jira] [Commented] (DERBY-866) BUILT-IN Derby User Management (DDL) Enhancements MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 X-Virus-Checked: Checked by ClamAV on apache.org [ https://issues.apache.org/jira/browse/DERBY-866?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13096224#comment-13096224 ] Rick Hillegas commented on DERBY-866: ------------------------------------- Derby has changed a bit since Francois posted his original spec on this issue. I would like to revive the discussion of this feature. I agree that Derby needs a simple scheme for managing user credentials. This seems to me to be a pre-requisite to making Derby secure-by-default. I agree with the previous discussion on this issue that the current BUILTIN mechanism is too limited to support production-quality user management. In particular, the BUILTIN mechanism does not support password expiration limits. The BUILTIN mechanism is a development/debugging aid, not a production-quality tool. Since the SQL Standard does not address user management, I am reluctant to introduce new DDL to support this feature. Here are the outlines of a scheme which I think would be fairly easy to implement in the 10.9 timeframe: 1) Introduce a new value for derby.authentication.provider. This would tell Derby to use this new authentication scheme. 2) Introduce a new database property which defines how long passwords are usable: derby.authentication.password.expiration. This can be set to the number of milliseconds which a password remains valid after being updated. If set to a value <= 0, then newly updated passwords don't expire. 3) Introduce a new system table which only the DBO can view. The DBO can grant SELECT on this table to other users/roles: create table sys.sysusers ( userName varchar( 128 ), hashingScheme varchar( 32672 ), password varchar( 32672 ), lastModified timestamp ); The second column of this table (hashingScheme) contains something like the decoding instructions which are prepended to current BUILTIN passwords when they are persisted. A user can not log in after derby.authentication.password.expiration milliseconds have elapsed since lastModified. Trying to log in with an expired password will raise an exception. 4) Introduce the following new system procedures which only the DBO can run. The DBO can grant EXECUTE privilege on these procedures to other users/roles: create procedure syscs_util.createUser ( in userName varchar( 128 ), in password varchar( 32672 ) ) ... create procedure syscs_util.modifyUser ( in userName varchar( 128 ), in password varchar( 32672 ) ) ... create procedure syscs_util.dropUser ( in userName varchar( 128 ) ) ... 5) Introduce the following new system procedure which any user can run to change their own password: create procedure syscs_util.modifyPassword ( in password varchar( 32672 ) ) ... What are your thoughts? Thanks, -Rick > BUILT-IN Derby User Management (DDL) 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 > Attachments: Derby_User_Enhancement.html, Derby_User_Enhancement_v1.1.html > > > 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. For more information on JIRA, see: http://www.atlassian.com/software/jira