[ 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
