[ 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 [EMAIL PROTECTED] 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
