You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Francois Orsini (JIRA)" <de...@db.apache.org> on 2006/01/24 19:12:11 UTC
[jira] Created: (DERBY-866) BUILT-IN Derby User Management (DDL)
Enhancements
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
Re: [jira] Created: (DERBY-866) BUILT-IN Derby User Management (DDL)
Enhancements
Posted by "David W. Van Couvering" <Da...@Sun.COM>.
Francois Orsini wrote:
> Hi Satheesh,
>
> Thanks for the initial feedback. Please find some answers enclosed below.
>
[snip]
>
> 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.
+1. I like us allowing LDAP, but I would be very uncomfortable making
it a requirement for user managment.
Re: [jira] Created: (DERBY-866) BUILT-IN Derby User Management (DDL) Enhancements
Posted by Francois Orsini <fr...@gmail.com>.
On 1/25/06, Satheesh Bandaram <sa...@sourcery.org> wrote:
> Thanks for your answers, Francois... Some more below...
>
> On 1/24/06, Satheesh Bandaram <satheesh@sourcery.org > wrote:
> >
> >
> > 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.
>
> Having Derby manage passwords is not very convinient... Users have to
> manage their username/password to their machines and also a different system
> to databases that is not as secure nor has good tools. If you remember,
> Sybase had elaborate stored procedures to add users and to manage their
> passwords initially. I think (if my memory serves right), they removed
> support for all these later and instead tied up with operating system based
> users/passwords. Not sure if Java has a way to link up to a (commonly used)
> domain server or just to host user management or other mechanisms, but that
> would surely be better.
>
No - Sybase still has a built-in authentication mechanism called 'ASE'
(sp_addlogin mylogin, mypassword, @auth_mech = ASE) - they also
support different authentication mechanisms like Derby does. Sybase
can also map an LDAP user to a Sybase ASE user via sp_maplogin
(corresponding to the EXTERNAL attribute mentioned in the
JIRA)...Again, we already have a built-in authentication scheme as
many (if not most) other database systems out there - If people want
to map an external user (login) with a built-in one, we can provide
this as well via the EXTERNAL attribute - Some applications may also
require to ship a database with user credentials already pre-set for a
client application which itself could run disconnected.
>
> 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.
>
> OK. That clarifies what you are proposing. Dan does have a point about how
> new DDL makes it much better than what is already there.
>
I think Dan's point is towards using derby procedures instead of
SQL-like DDL though
>
> >
> > 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
>
> More profile/password management changes to Derby? I am not sure if that
> adds much value, IMHO, since there are many other services that do this much
> better already and are more secure.
>
It is all about offering various choices to end-users, not everyone is
running an LDAP or PAM external authentication server - as far as
password expiration and account locking, most known database systems
out there offer this as part of their built-in user scheme...What you
mentioned here is on phase 2 btw.
Thanks for the additional comments Satheesh.
>
> Satheesh
>
>
> Thanks for all the comments.
>
>
> > Satheesh
> >
> >
> > Francois Orsini (JIRA) wrote:
> >
> > 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.
> >
> >
> >
> >
>
>
Re: [jira] Created: (DERBY-866) BUILT-IN Derby User Management (DDL) Enhancements
Posted by Francois Orsini <fr...@gmail.com>.
Hi Satheesh,
Thanks for the initial feedback. Please find some answers enclosed below.
On 1/24/06, Satheesh Bandaram <sa...@sourcery.org> 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.
Satheesh
>
> Francois Orsini (JIRA) wrote:
>
> 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.
>
>
[jira] Updated: (DERBY-866) BUILT-IN Derby User Management (DDL)
Enhancements
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-866?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dag H. Wanvik updated DERBY-866:
--------------------------------
Component/s: Services
> 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.
-
You can reply to this email to add a comment to the issue online.
Re: [jira] Commented: (DERBY-866) BUILT-IN Derby User Management (DDL) Enhancements
Posted by Francois Orsini <fr...@gmail.com>.
On 1/25/06, Daniel John Debrunner <dj...@apache.org> wrote:
> Francois Orsini (JIRA) wrote:
> > [ http://issues.apache.org/jira/browse/DERBY-866?page=comments#action_12364050 ]
> >
> > Francois Orsini commented on DERBY-866:
> > ---------------------------------------
> >
> >
> >>Daniel John Debrunner commented on DERBY-866:
> >>---------------------------------------------
> >>
> >>What's the purpose of returning the password column in the table/vti SYSUSERS?
> >>
> >
> > Password won't be clearly readable, only the base64 representation of a already hashed password
> > - no risk really
>
> I think it's a huge risk. You are giving crackers information to start
> an attack. Every encryption scheme is breakable, it's just a matter of
> time/cpu usage.
Fair enough - in that case only a user with an 'Admin' role or with
'CREATE USER' privilege would have been able to access the password
base64-single-hashed column, *not* everyone...But I agree it is still
better not to show it at all - what we could do is only show the
content IF the authentication scheme/style is different than built-in-
or like you say we don't display the column at all.
Thanks for additional feedback Dan.
>
> Dan.
>
>
Re: [jira] Commented: (DERBY-866) BUILT-IN Derby User Management
(DDL) Enhancements
Posted by Daniel John Debrunner <dj...@apache.org>.
Francois Orsini (JIRA) wrote:
> [ http://issues.apache.org/jira/browse/DERBY-866?page=comments#action_12364050 ]
>
> Francois Orsini commented on DERBY-866:
> ---------------------------------------
>
>
>>Daniel John Debrunner commented on DERBY-866:
>>---------------------------------------------
>>
>>What's the purpose of returning the password column in the table/vti SYSUSERS?
>>
>
> Password won't be clearly readable, only the base64 representation of a already hashed password
> - no risk really
I think it's a huge risk. You are giving crackers information to start
an attack. Every encryption scheme is breakable, it's just a matter of
time/cpu usage.
Dan.
[jira] Commented: (DERBY-866) BUILT-IN Derby User Management (DDL)
Enhancements
Posted by "Francois Orsini (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-866?page=comments#action_12364050 ]
Francois Orsini commented on DERBY-866:
---------------------------------------
>
> Daniel John Debrunner commented on DERBY-866:
> ---------------------------------------------
>
> What's the purpose of returning the password column in the table/vti SYSUSERS?
>
Password won't be clearly readable, only the base64 representation of a already hashed password - no risk really, just thought it could have some usage and if we use EXTERNAL to specify that this user maps to one defined in some LDAP repository (as an example), then because of EXTERNAL the password would not be encrypted and instead display the User DN ('uid=mary,ou=People,o=FlyTours.com') that maps into LDAP...So in that case it would have a meaning...See my previous comment on the EXTERNAL attribute definition. We could also decide not to display anything for built-in stored passwords (eventhough they cannot be decrypted)...
> 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
Re: [jira] Commented: (DERBY-866) BUILT-IN Derby User Management (DDL) Enhancements
Posted by Francois Orsini <fr...@gmail.com>.
On 1/26/06, Daniel John Debrunner <dj...@apache.org> wrote:
> Francois Orsini (JIRA) wrote:
>
> > [ http://issues.apache.org/jira/browse/DERBY-866?page=comments#action_12364090 ]
> >
> > Francois Orsini commented on DERBY-866:
> > ---------------------------------------
>
> > In this case, the password column can just be renamed to something else
> > (i.e. AUTH_INFO), in order to support *not* just a password value
>
> I'm still missing something, you seem keen on having a column that
> returns the hashed password. Do you have some intended use for this?
>
> Besides it being a security risk, I just can't see any use for it.
>
Nope - like I said in a previous reply:
>> But I agree it is still
>> better not to show it at all - what we could do is only show the
>> content IF the authentication scheme/style is different than built-in-
>> or like you say we don't display the column at all.
I agreed about not showing it at all. I was just making another
suggestion in the just above blurb as well as agreeing to your
comment...
> Dan.
>
>
>
Re: [jira] Commented: (DERBY-866) BUILT-IN Derby User Management
(DDL) Enhancements
Posted by Daniel John Debrunner <dj...@apache.org>.
Francois Orsini (JIRA) wrote:
> [ http://issues.apache.org/jira/browse/DERBY-866?page=comments#action_12364090 ]
>
> Francois Orsini commented on DERBY-866:
> ---------------------------------------
> In this case, the password column can just be renamed to something else
> (i.e. AUTH_INFO), in order to support *not* just a password value
I'm still missing something, you seem keen on having a column that
returns the hashed password. Do you have some intended use for this?
Besides it being a security risk, I just can't see any use for it.
Dan.
[jira] Commented: (DERBY-866) BUILT-IN Derby User Management (DDL)
Enhancements
Posted by "Francois Orsini (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-866?page=comments#action_12364090 ]
Francois Orsini commented on DERBY-866:
---------------------------------------
> Daniel John Debrunner commented on DERBY-866:
> ---------------------------------------------
>
> I see the argument for DDL, maybe the create user could be like
>
> CREATE USER <username> IDENTIFIED BY PASSWORD <password>
>
> basically the addition of the PASSWORD keyword would make future enhancements more in line, e.g.
>
> CREATE USER <username> IDENTIFIED BY LDAP <ldap stuff>
>
> Maybe the SYSUSERS table could have a column indicatting the authentication style, BUILTIN/LDAP/java class name etc.
>
Yes it could - I somewhat tried to avoid this as we have a pluggable authentication scheme so additional custom-created auth providers would not have the proper grammar defined (unless they are part of Derby where the grammar can always be extended to support a new one) - But a new auth provider can just be implemented outside of Derby, hence we would also need to map to the custom auth providers out there - but if we have a column with the authentication provider name name, then we can expect to have the proper user login info as part of the 'IDENTIFIED BY' clause without any extra specific auth type keyword...In this case, the password column can just be renamed to something else (i.e. AUTH_INFO), in order to support *not* just a password value but other type of info (it would be a VARCHAR after all) for the the other providers.
_or_ based on what you're suggesting we could still have:
// Built-In
CREATE USER <username> IDENTIFIED BY PASSWORD <password>
// LDAP (mapping)
CREATE USER <username> IDENTIFIED BY LDAP <ldap stuff>
// PAM (mapping)
CREATE USER <username> IDENTIFIED BY PAM <pam stuff>
// CUSTOME Auth Provider (mapping) - External keyword used for that purpose here
CREATE USER <username> IDENTIFIED BY EXTERNAL <custom stuff>
> 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
[jira] Commented: (DERBY-866) BUILT-IN Derby User Management (DDL)
Enhancements
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-866?page=comments#action_12366665 ]
Daniel John Debrunner commented on DERBY-866:
---------------------------------------------
Thanks for the updated spec, some comments:
- are you expecting this to be ready for the 10.2 release?
- I'm still confused by the phrase "no real ANSI SQL standard for managing users in SQL", the "real" implies there is some sort of ANSI standard in this area, but provisional, or not widely adopted or you imagined it. :-)
- what the defined behaviour for 'CREATE USER dan', ie. no IDENTIFIED BY clause, since it's optional?
- Without a special type token between IDENTIFIED BY and the auto_info I think you require that the authentication provider is defined for the database, otherwise I don't see how the code decides to store a hash of the password or the mapping info.
I was assuming it would be
CREATE USER dan IDENTIFIED BY PASSWORD 'ek992ffdwfe'
or
CREATE USER dan IDENTIFIED BY LDAP 'djd@apache.org'
- Can user_id and auto_info be parameter markers?
- How the mapping works is not defined.
- SYSUSERS example has PASSWORD in it
- Development phasing is a strange section. Really there is only a single phase (as described) for the functionality described by this spec. Phases 2 and 3 are future ideas, so thye are not related to the development of this spec. I would have put them in a separate section like "Future Direction".
- There is a hole in the spec, if this is implemented in 10.2 and DERBY-464 (DML grant revoke) is implemented then the addition of CREATE USER will (could?) allow anyone to create users in the database., which in turn allows anyone to create schemas of any name. You refer to this in your NOTE in the Syntax section, where you say CREATE USER permission will be required, but as far as I know no-one is working on that. Ie. as you have written, what happens when the database is in SQL authorization mode, but system priviledges are *not* available, as I believe will be the case for 10.2?
> 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, 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.
-
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
[jira] Updated: (DERBY-866) BUILT-IN Derby User Management (DDL)
Enhancements
Posted by "Francois Orsini (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-866?page=all ]
Francois Orsini updated DERBY-866:
----------------------------------
Attachment: Derby_User_Enhancement_v1.1.html
Posted new 1.1 spec - Derby_User_Enhancement_v1.1.html - please enclose comments to the JIRA itself.
> 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, 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.
-
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
[jira] Updated: (DERBY-866) BUILT-IN Derby User Management (DDL)
Enhancements
Posted by "Francois Orsini (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-866?page=all ]
Francois Orsini updated DERBY-866:
----------------------------------
Attachment: Derby_User_Enhancement.html
Attached proposal specifications - please post comments in the JIRA itself.
> 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
[jira] Commented: (DERBY-866) BUILT-IN Derby User Management (DDL)
Enhancements
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-866?page=comments#action_12363885 ]
Daniel John Debrunner commented on DERBY-866:
---------------------------------------------
Questions on the spec:
- What do you mean by "familar DDL interface" and "known interface". Are you saying that these are copied from some other database?
- "There is no real ANSI SQL standard ..." - What does this mean, is there a ANSI standard that no-one implements, a "pretend" ANSI standard, something else?
- What's the advantage of implementing these utility operations as DDL? The same functionality can be acheived, probably at a lower footprint by using procedures.
- I think we need to keep a clear separation between the BUILTIN authentication provider and other functionality such as roles, which should be independent of authentication mechanism. Naming becomes important here, SYS_BUILTIN_USERS instead of SYSUSERS, CREATE BUILTIN USER etc. It may be in the future that a Derby database has users which are independent of the authentication mechanism, but there is the ability to provide a mapping between them. E.g. LDAP user fred.jones@acme.com is mapped to user fred in a Derby database.
> 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
[jira] Commented: (DERBY-866) BUILT-IN Derby User Management (DDL)
Enhancements
Posted by "Francois Orsini (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-866?page=comments#action_12364048 ]
Francois Orsini commented on DERBY-866:
---------------------------------------
> Daniel John Debrunner commented on DERBY-866:
> ---------------------------------------------
>
> Questions on the spec:
>
> - What do you mean by "familar DDL interface" and "known interface". Are you saying that these are copied from some other database?
>
I mean that at least 2 known database systems out there are using such DDL interface. The CREATE USER syntax works well in respect with creating a new user object (such as CREATE TABLE for instance) but it not defined in the ANSI SQL Specs (at least I have not found any reference myself...if someone does please let me know). From a SQL standpoint, I find this DDL syntax in-line with other other DDL syntax - That's all.
> - "There is no real ANSI SQL standard ..." - What does this mean, is there a ANSI standard that no-one implements, a "pretend" ANSI standard, something else?
>
I have not found any ANSI SQL defining a standard syntax for SQL USER DDL statements, so that's what I mean by saying there is no "real"ANSI SQL standard - it is all based on preference and what users would be the most familiar with to use as an interface, knowing that 2 other known RDBMS out are using a similar one.
> - What's the advantage of implementing these utility operations as DDL? The same functionality can be acheived, probably at a lower footprint by using procedures.
>
I thought I mentioned it in the proposal but I omitted to add the 'Alternatives' section which would be good to have indeed - I thought it would be more intuitive to use a DDL SQL-like rather than calling a Derby procedure (again for the reasons that this syntax is similar to some used by other RDBMS).
We know tat the current interface (SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY) does NOT scale in the long run - It is based on using a database property to define a user in the database - It is preventing additional semantics (such as password expiration, default Role, etc) to be added to the user (since it is based on a property Name-Value pair definition right now...Sure we could use procedures to manage users but it won't be a structured english-like syntac (ala SQL)...
> - I think we need to keep a clear separation between the BUILTIN authentication provider and other functionality such as roles, which should be independent of authentication mechanism. Naming becomes important here, SYS_BUILTIN_USERS instead of SYSUSERS, CREATE BUILTIN USER etc. It may be in the future that a Derby database has users which are independent of the authentication mechanism, but there is the ability to provide a mapping between them. E.g. LDAP user fred.jones@acme.com is mapped to user fred in a Derby database.
>
I actually thought about the user mapping but did not mention it - a derby user should be generic enough that once authenticated in the system, it is a user, not a built-in user, neither an LDAP user, etc - The CREATE USER DDL command could be expanded with an EXTERNAL attribute to map/refer to a user outside of the Built-In realm and by overloading the IDENTIFIED BY attribute to specify the LDAP user DN (as used when authenticating with LDAP). Roles are independent of Users, and their relationships should be defined in separate system catalogs, a separate user<->role<->role relationship catalog can be defined to store the metadata about user roles or roles assigned to other roles...User mapping is good as there is so little control we can have over users defined in LDAP (in terms of settings additional user semantics in the Derby's realm), hence, we might need to compensate by linking an "external" user defined in Derby (i.e. SYSUSERS) with some user DN (Distinguished Name) defined in an LDAP repository.
Syntax such as:
CREATE USER user_id IDENTIFIED BY 'LDAP User DN' EXTERNAL
for instance: CREATE USER user_id IDENTIFIED BY 'uid=mary,ou=People,o=FlyTours.com' EXTERNAL
The other alternative that you suggest by having a SYS_BUILTIN_USERS is conceivable but I was hoping to use SYSUSERS, as a system catalog not just for Built-In users - for instance if we need to add new user semantics such as user profile, role, etc, then we can map an LDAP authenticated user with a derby defined one with additional derby user properties (and via the EXTERNAL attribute). SYSUSERS can be used for the built-in provider when it is used or/and by other providers to map externally authenticated users whom would need to have additional user properties set in Derby...
--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
[jira] Commented: (DERBY-866) BUILT-IN Derby User Management (DDL)
Enhancements
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-866?page=comments#action_12363958 ]
Daniel John Debrunner commented on DERBY-866:
---------------------------------------------
What's the purpose of returning the password column in the table/vti SYSUSERS?
> 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
[jira] Updated: (DERBY-866) BUILT-IN Derby User Management (DDL)
Enhancements
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-866?page=all ]
Rick Hillegas updated DERBY-866:
--------------------------------
Urgency: Normal
> BUILT-IN Derby User Management (DDL) Enhancements
> -------------------------------------------------
>
> Key: DERBY-866
> URL: http://issues.apache.org/jira/browse/DERBY-866
> Project: Derby
> Issue Type: Improvement
> Components: Security
> Affects Versions: 10.2.0.0
> Reporter: Francois Orsini
> Fix For: 10.2.0.0
>
> 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.
-
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
[jira] Commented: (DERBY-866) BUILT-IN Derby User Management (DDL)
Enhancements
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-866?page=comments#action_12364054 ]
Daniel John Debrunner commented on DERBY-866:
---------------------------------------------
I see the argument for DDL, maybe the create user could be like
CREATE USER <username> IDENTIFIED BY PASSWORD <password>
basically the addition of the PASSWORD keyword would make future enhancements more in line, e.g.
CREATE USER <username> IDENTIFIED BY LDAP <ldap stuff>
Maybe the SYSUSERS table could have a column indicatting the authentication style, BUILTIN/LDAP/java class name etc.
> 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
[jira] Commented: (DERBY-866) BUILT-IN Derby User Management (DDL)
Enhancements
Posted by "David Gradwell (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-866?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12466550 ]
David Gradwell commented on DERBY-866:
--------------------------------------
An SQL based CREATE USER capability is really important for my application.
I fully support the syntax proposed by Francis Orsini and hope that this can be implemented soon.
As an aside, if anyone reading this is a member of the ANSI or ISO SQL Standards committees then please get in touch. I would be willing to draft a change proposal to the SQL Standard for this area. Then the various implementations could provide a common capability.
David Gradwell
> 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: Security
> 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.
-
If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-866) BUILT-IN Derby User Management (DDL)
Enhancements
Posted by "Kathey Marsden (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-866?page=all ]
Kathey Marsden updated DERBY-866:
---------------------------------
Fix Version/s: (was: 10.2.0.0)
removing from 10.2. see:
http://www.nabble.com/10.2-High-Value-Fix-Candidates-and-Fix-Version-Adjustments-tf2007999.html
> BUILT-IN Derby User Management (DDL) Enhancements
> -------------------------------------------------
>
> Key: DERBY-866
> URL: http://issues.apache.org/jira/browse/DERBY-866
> Project: Derby
> Issue Type: Improvement
> Components: Security
> Affects Versions: 10.2.0.0
> 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.
-
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
[jira] Commented: (DERBY-866) BUILT-IN Derby User Management (DDL)
Enhancements
Posted by "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