db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-866) BUILT-IN Derby User Management (DDL) Enhancements
Date Fri, 02 Sep 2011 19:23:09 GMT

    [ 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

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

create procedure syscs_util.modifyPassword
    in password varchar( 32672 )

What are your thoughts?


> 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:
>            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, 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


View raw message