[
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
