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

[Satheesh Bandaram]

Thanks for your answers, Francois... Some more below... Francois Orsini wrote: Hi Satheesh, Thanks for the initial feedback. Please find some answers enclosed below. On 1/24/06, Satheesh Bandaram <[EMAIL PROTECTED] > 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 - 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. 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. 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.