Several problems in the 2.0 version

[cziegenberg]

Hi,

great to see that the development is going one, because I didn't get any feedback on my suggestions regarding the database structure. I just updated my implementation an recognized the following problems:

General:

1. Is it possible to move the PDO apdater into a sepparate project? I use another database layer and I would prefer to download only the neccessary components. Also this would allow to offer packages for other famous database layers like Zend DB.

Database:

2. You renamed some fields in the oauth_client and oauth_scopes tables, which is problematic, because now some field names are reserved keyword in some DMBS (e.g. "key" in SqlServer). That's why I prefixed them...

3. You renamed some id fields in the table, some not - e.g. 'client_id' in 'oauth_clients' to 'id', but 'endpoint_id' in 'oauth_client_endpoints' is still the same. It's okay to name it this way, but you should use the same naming convention for all tables.

4. Renaming some of the proposed tables/fields is was a good idea, but the naming of the indexes and foreign keys doesn't match these new names now.

5. Some fields have an empty default value, which doesn't make sense in most cases and would be treatet as NULL in some DBMS - this would lead to an error there, because NULL is not allowed for these fields.

6. The new field "scope_ids" is missing in your SQL script. This information should be saved in a sepparate table, not in one field.

Session Interface:

7. Why does the new method "removeAuthCode" delete the whole session and not just the auth code. One session can have more than one Auth Code, and all are deleted with this method.

8. Why did you remove the client_id from "validateRefreshToken"? This is a security problem. Following the RFC, "The authorization server MUST maintain the binding between a refresh token and the client to whom it was issued." - this doesn't make sense, if this information is not checked in the validate method.

As soon as the problems are solved, I will try to provide the database structure for other DBMS.

[alexbilbie]

General:

1. I'd prefer not to move it into a separate project so it makes it this project a bit more "get up and go". I've no problem with linking to other DB implementations in the Composer "suggest" parameter.

Database:

2. Just escape the keywords

3. That was a mistake on my part, I somehow missed endpoint_id. I will update it in the next release.

4. I updated all of the indexes so they refer to the correct columns, what isn't correct?

5. I've gone through and checked and I can't see an example of where a field that isn't set with "Allow Null" which would have an empty field. This isn't a problem.

6. I'll update it and release a new version. As for putting it in a new table it potentially saves on N extra queries - in my implementation some access tokens have up to 20 scopes.

Session interface:

7. It doesn't delete the whole session, it just removes the auth code. I can't see in the spec where it says you can have multiple auth codes in one session?

8. That was a mistake on my part. I've added it back in and released a new version.

Thank you very much for your feedback @ziege

[cziegenberg]

To 1) Okay.

To 2) Yes, that's possible, but not the best practice.

To 3) I saw you changed it, but there is another one: session_token_scope_id

To 4) The names were created using the table name and the used column names, and these aren't up to date. That's not important but helps to avoid naming conflicts.

To 5) The fields are: "access_token" in "oauth_session_access_tokens", "auth_code" in "oauth_session_authcodes", "redirect_uri" in "oauth_session_redirects", "refresh_token" and "client_id" in "oauth_session_refresh_tokens". All default to an empty string '', which is equal to NULL in some DBMS, but as they are also NOT NULL, this default value would not be possible. I think all fields should always be filled, so simply removing the default would fix it.

To 6) In Postgres this would not be a problem, because Postgres supports arrays, but MySQL and other DBMS don't. Saving the data as a string is a bit easier, right, but it has many disadvantages:

• You are not able to use foreign keys, to protect from invalid data. If a scope is deleted or changed, foreign key constraints would update the data for the authcode - saved as a string this would probably result in an error.
• You don't know how long the string can be - you have to use field types which allow a big amount of data (about 600.000 bytes with the current structure), and fetching theses data would be more expensive for the database. The current CHAR field is the worst variant - uses a lot of space and result in errors very soon. Saving the data in a sepparate table would save disk space in most cases and avoid errors (about the same disk space if TEXT used, but it would be more efficient).
• You don't have the possibility to extend these data later, because they depend on your own, special format.

Also with 20 scopes it's only one insert, because you can insert multiple entries with one query (http://dev.mysql.com/doc/refman/5.5/en/insert.html) - as far as I know, only SqlServer < 2008 doesn't support this. And you can also get all data with one query.

Suggestion (not tested):
CREATE TABLE `oauth_session_authcode_scopes` (
  `session_id` int(10) UNSIGNED NOT NULL,
  `scope_id` smallint(5) UNSIGNED NOT NULL,
  PRIMARY KEY (`session_id`, `scope_id`),
  CONSTRAINT `f_oaseausc_seid` FOREIGN KEY (`session_id`) REFERENCES `oauth_sessions` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `f_oaseausc_scid` FOREIGN KEY (`scope_id`) REFERENCES `oauth_scopes` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

To 7) You can have multiple auth codes with different scopes - we discussed about this earlier I think (example where you have one client and one auth server, but several resource servers where you want to connect to using different scopes). That's why I split the session and the auth codes in the table structure. The RFC indirectly mentions this, because there it's possible "to obtain additional access tokens with identical or narrower scope" using refresh tokens.

Hope this helps.

[alexbilbie]

2. I can change this in the next major release, I've opened a ticket Rename key column in oauth_scopes table #45

3. Fixed, and I've pushed another release

4. I misunderstood your original point. I hadn't realised they'd slipped in when I copied the create syntax into the script. Now fixed.

5. I've opened a ticket for this Move authcode scopes into a new table #44

6. I'll address this in the next release as some logic needs to be refactored. I've created Allow for multiple auth codes per session #46 and Allow for reduced scopes when issuing new refresh tokens #47