[REQ][MySQL] Tables for OAuth2 security schema #3550
Description
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.passwordcolumn limited to 80 characters which can be not enough for future password encryption algorithms. In PHP docspassword_hashrecommended 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.usernameisuser_idmentioned 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.