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

[Satheesh Bandaram]

Hi Francois, Some questions on the proposal. 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? 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. 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? Why not make SYSUSERS a system table now, instead of a VTI? Making it a system catalog has benifits like dictionary management. 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.