DDL to create MySQL tables for PDO storage support of oauth2-server-php library
DDL to create MySQL oauth user and database for PDO storage support of https://github.com/bshaffer/oauth2-server-php.
mysql> source oauth.ddl
If there is an existing oauth
database, a copy will be saved to oauth_backup
prior to dropping and creating a new oauth
database and tables.
mysql> source test-data.sql
Test data is used to perform unit testing of the PDO interface and also to provide an example implementation for the different grant types. This file should not be run for production deployments.
A bug that affects the behavior of scopes was fixed in version 1.8 of the library. If you are on a version prior to 1.8, you may be affected if your application:
A NULL
value in oauth_clients.scope
will cause a token to be granted with ALL default scopes found in oauth_scopes
. This opens up the possibility for a less-permissioned token granted from an unauthenticated Client Credentials request could be used to access resources that otherwise would require a username and password.
To fix this, insert a "dummy" value for oauth_clients.scope
:
mysql> INSERT INTO oauth_clients (client_id, scope) VALUES (YOUR_CLIENT_ID, "dummy-value");
This step is not necessary if you are using version 1.8 or higher of the library.
+--------------+---------------+------+-----+---------+-------+
| 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 | |
+--------------+---------------+------+-----+---------+-------+
+--------------------+---------------+------+-----+---------+-------+
| 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 | |
+--------------------+---------------+------+-----+---------+-------+
+---------------+---------------+------+-----+---------+-------+
| 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 | |
+---------------+---------------+------+-----+---------+-------+
+----------+---------------+------+-----+---------+-------+
| 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 | |
+----------+---------------+------+-----+---------+-------+
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| client_id | varchar(80) | NO | | NULL | |
| subject | varchar(80) | YES | | NULL | |
| public_key | varchar(2000) | NO | | NULL | |
+------------+---------------+------+-----+---------+-------+
+----------------------+---------------+------+-----+---------+-------+
| 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 | |
+----------------------+---------------+------+-----+---------+-------+
+---------------+------------------+------+-----+-------------------+-----------------------------+
| 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 | |
+---------------+------------------+------+-----+-------------------+-----------------------------+
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| scope | varchar(80) | NO | PRI | NULL | |
| is_default | tinyint(1) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
+----------------+------------------+------+-----+---------+----------------+
| 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 | |
+----------------+------------------+------+-----+---------+----------------+