[jira] Commented: (DERBY-866) BUILT-IN Derby User Management (DDL) Enhancements

["Francois Orsini (JIRA)" <de...@db.apache.org>]

    [ http://issues.apache.org/jira/browse/DERBY-866?page=comments#action_12363881 ] 

Francois Orsini commented on DERBY-866:
---------------------------------------

Hi Satheesh,

Thanks for the initial feedback. Please find some answers enclosed below.

On 1/24/06, Satheesh Bandaram  wrote:
>
> Hi Francois,
>
> Some questions on the proposal.
>
>    1. I am not sure if database is the right place to do
>    authentication. For example, your proposal still calls for sysadmin to know
>    user passwords (at least initially). Who has the authorization to run ALTER
>    USER? Database keeping user passwords may not be a good idea for many
>    reasons. How to ensure no one is snooping on the protocol to read clear text
>    password that is embedded as part of SQL statement? Can a sysadmin turn on
>    tracing in network server or in the engine to see what password users are
>    picking for themselfs?
>
>
This proposal does not discuss how a sysadmin (dba) type of user is defined,
as obviously we don't have System privileges support as well as roles (yet)
- specs for these are incoming .

Here it how it would work in the meantime:

- For a database to be created and assuming 'sqlStandard' authorization mode
is enabled and we have support for certain system privileges such as the
CREATE DATABASE one, a grantee, would be allowed to create a database and
assign a 'dba' (or 'admin') role to a new user in the just created database.
Again, this is assuming there is support for Roles and System privileges but
features can be done in phases as you know. Derby supports authentication at
the System and Database level - Derby databases can defined their own
security restrictions/settings independently of the system level - as you
know, derby databases can be moved around independently of the derby system
realm  - there is no 'master' system tables (i.e. mo master DB) in Derby and
it was done so that databases could be detached, accessed without having to
be defined in some sort of master database containing metadata to access
user databases - this is a very powerful and flexible model but it has a
cost...

An application should be able to have a database which itself is either
being shipped to a user or distributed with user credentials and
authorization (privileges) metadata being defined as part of the database.
In order to run ALTER USER, the individual would need to have the CREATE
USER privilege (we don't have that right now). Derby has a Built-In
authentication provider (scheme) which allows Derby to run its own user
authentication realm - You can't always assume LDAP, many applications don't
use or have access to LDAP and are not always connected, yet they still want
to be able to protect access to a DB.

As far as snooping the clear text password on the network, we do have the
same issue with the current scheme of calling
SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY across a networked JDBC connectoin  -
in fact, there is a similar issue with LDAP authentication, some people use
SSL in order to solve this but in our case, DRDA also has mechanisms for
resetting a user password over a network and in a secure way - hence we
could either piggyback on existing and similar DRDA mechanisms or have the
admin use an SSL-enabled (or IPSEC) derby connection for such admin
commands...


>    1. Why not sure LDAP or some other standard authentication models?
>    It may be good to strengthen Derby authentication, but not necessarily by
>    making Derby manage passwords.
>
>
Derby Built-In authentication is important - again, not everyone is using
LDAP, especially with small departmental level type of databases as well as
embedded / disconnected ones. Derby already manages user passwords at the
database level when a user is defined in a database. Derby stores user
password in a secure fashion (they cannot be decrypted easily and if at all
- they are single-hashed) - the transport of passwords across a network is
defined and handled in the DRDA specifications which themselves are part of
a standard - encrypting a password in a DB is not related to how a password
is transported across the network - they are both important to address and
we have the means.


>    1. You said current user management would work as is in addition to
>    the new proposal. How do you propose to keep these two in sync? Could a user
>    be present in both ways and what happens if their passwords are different?
>
>
No - this phase I proposal is to enhance the DDL support for managing
Built-In Derby users in a database. We would still be using database
properties to store the actual user/password combination as presently. You
cannot have 2 users of the same name in a derby database - the only way to
achieve something close would be to define a user at the system level and
the same at the database level, and this last one would take precedence -
also, in derby it is possible to set a a database property to only consider
database properties and not the system ones, meaning that even if some users
would be defined at the system level, they would not be granted access to
the database which has authentication turned ON.


>    1. Why not make SYSUSERS a system table now, instead of a VTI?
>    Making it a system catalog has benifits like dictionary management.
>
>
Agreed - just a question of phasing something in different stages - Also, we
would not have to do any upgrade changes with this first phase since it
would still be going after database properties underneath. I agree that the
upgrade issue would still have to be done if sysusers is added - at the same
time, it is very likely that it will be required if additional user
semantics are added (i.e. profiles, pwd expiration, roles (hence UID
required instead of username) - so Yes, this is a valid point

Thanks for all the comments.

--francois

> BUILT-IN Derby User Management (DDL) Enhancements
> -------------------------------------------------
>
>          Key: DERBY-866
>          URL: http://issues.apache.org/jira/browse/DERBY-866
>      Project: Derby
>         Type: Improvement
>   Components: Security
>     Versions: 10.2.0.0
>     Reporter: Francois Orsini
>      Fix For: 10.2.0.0
>  Attachments: Derby_User_Enhancement.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.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira