[REQ][MySQL] Tables for OAuth2 security schema

[ybelenko]

Is your feature request related to a problem? Please describe.

While thinking about OAuth2 library for PHP Slim generator in #3549 I've found OAuth database schema in source code. Here and here.

Describe the solution you'd like

It would be great to add these tables to MySQL schema generator. Database schema copied from oauth2-server-php-mysql repo described below.

Tables

oauth_access_tokens

+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| access_token | varchar(40)   | NO   | PRI | NULL    |       |
| client_id    | varchar(80)   | YES  |     | NULL    |       |
| user_id      | varchar(80)   | YES  |     | NULL    |       |
| expires      | timestamp     | NO   |     | NULL    |       |
| scope        | varchar(4000) | YES  |     | NULL    |       |
+--------------+---------------+------+-----+---------+-------+

oauth_authorization_codes

+--------------------+---------------+------+-----+---------+-------+
| Field              | Type          | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| authorization_code | varchar(40)   | NO   | PRI | NULL    |       |
| client_id          | varchar(80)   | YES  |     | NULL    |       |
| user_id            | varchar(80)   | YES  |     | NULL    |       |
| redirect_uri       | varchar(2000) | NO   |     | NULL    |       |
| expires            | timestamp     | NO   |     | NULL    |       |
| scope              | varchar(4000) | YES  |     | NULL    |       |
| id_token           | varchar(1000) | YES  |     | NULL    |       |
+--------------------+---------------+------+-----+---------+-------+

oauth_clients

+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| client_id     | varchar(80)   | NO   | PRI | NULL    |       |
| client_secret | varchar(80)   | YES  |     | NULL    |       |
| redirect_uri  | varchar(2000) | YES  |     | NULL    |       |
| grant_types   | varchar(80)   | YES  |     | NULL    |       |
| scope         | varchar(4000) | YES  |     | NULL    |       |
| user_id       | varchar(80)   | YES  |     | NULL    |       |
+---------------+---------------+------+-----+---------+-------+

oauth_jti

+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| issuer   | varchar(80)   | NO   |     | NULL    |       |
| subject  | varchar(80)   | YES  |     | NULL    |       |
| audience | varchar(80)   | YES  |     | NULL    |       |
| expires  | timestamp     | NO   |     | NULL    |       |
| jti      | varchar(2000) | NO   |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+

oauth_jwt

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| client_id  | varchar(80)   | NO   |     | NULL    |       |
| subject    | varchar(80)   | YES  |     | NULL    |       |
| public_key | varchar(2000) | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

oauth_public_keys

+----------------------+---------------+------+-----+---------+-------+
| Field                | Type          | Null | Key | Default | Extra |
+----------------------+---------------+------+-----+---------+-------+
| client_id            | varchar(80)   | YES  |     | NULL    |       |
| public_key           | varchar(2000) | YES  |     | NULL    |       |
| private_key          | varchar(2000) | YES  |     | NULL    |       |
| encryption_algorithm | varchar(100)  | YES  |     | RS256   |       |
+----------------------+---------------+------+-----+---------+-------+

oauth_refresh_tokens

+---------------+------------------+------+-----+-------------------+-----------------------------+
| Field         | Type             | Null | Key | Default           | Extra                       |
+---------------+------------------+------+-----+-------------------+-----------------------------+
| refresh_token | varchar(40)      | NO   | PRI | NULL              |                             |
| client_id     | varchar(80)      | YES  |     | NULL              |                             |
| user_id       | varchar(80)      | YES  |     | NULL              |                             |
| expires       | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| scope         | varchar(4000)    | YES  |     | NULL              |                             |
+---------------+------------------+------+-----+-------------------+-----------------------------+

oauth_scopes

+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| scope      | varchar(80) | NO   | PRI | NULL    |       |
| is_default | tinyint(1)  | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

oauth_users

+----------------+------------------+------+-----+---------+----------------+
| Field          | Type             | Null | Key | Default | Extra          |
+----------------+------------------+------+-----+---------+----------------+
| username       | varchar(80)      | YES  |     | NULL    |                |
| password       | varchar(80)      | YES  |     | NULL    |                |
| first_name     | varchar(80)      | YES  |     | NULL    |                |
| last_name      | varchar(80)      | YES  |     | NULL    |                |
| email          | varchar(2000)    | YES  |     | NULL    |                |
| email_verified | tinyint(1)       | YES  |     | NULL    |                |
| scope          | varchar(4000)    | YES  |     | NULL    |                |
+----------------+------------------+------+-----+---------+----------------+

Describe alternatives you've considered

I can't say that I'm fully satisfied with mentioned schema. My complaints are:

• oauth_users.password column limited to 80 characters which can be not enough for future password encryption algorithms. In PHP docs password_hash recommended length is 255.
• Schema doesn't set column collations. It's important for case-sensitive tokens(base64 e.g.). More info in submitted issue
• It's not obvious that oauth_users.username is user_id mentioned in other tables. Maybe column comment can make it more clear.

If there are database architects in our community, they can significantly improve this schema.