| 1 |
/* |
| 2 |
This script will create the tables for a basic oa4mp install. Since MySQL has *no* varaible |
| 3 |
support, everything is hard-coded. if you want something other than the default names and |
| 4 |
then edit the file. |
| 5 |
*/ |
| 6 |
|
| 7 |
/* |
| 8 |
Usage: Log in as an administrator (such as root) that can create the user, if need be. |
| 9 |
|
| 10 |
CREATE USER '{{ mp_server_db_user }}'@'localhost' IDENTIFIED BY '{{ mp_server_db_pw }}'; |
| 11 |
|
| 12 |
Run the rest of this script. The user must exist before permissions are granted. |
| 13 |
*/ |
| 14 |
|
| 15 |
/* User is created by the ansibel script */ |
| 16 |
/* CREATE USER '{{ mp_server_db_user }}'@'localhost' IDENTIFIED BY '{{ mp_server_db_pw }}'; */ |
| 17 |
|
| 18 |
CREATE DATABASE IF NOT EXISTS {{ mp_server_db }} |
| 19 |
DEFAULT CHARACTER SET utf8; |
| 20 |
USE {{ mp_server_db }}; |
| 21 |
|
| 22 |
/* |
| 23 |
Some useful commands. Lst two list users and will show permissions for a single user on a machine.: |
| 24 |
Show Databases; |
| 25 |
Show schemas; |
| 26 |
SELECT User FROM mysql.user; |
| 27 |
SHOW GRANTS FOR 'user'@'localhost'; |
| 28 |
*/ |
| 29 |
|
| 30 |
CREATE TABLE {{ mp_server_db }}.clients ( |
| 31 |
client_id VARCHAR(255) PRIMARY KEY, |
| 32 |
public_key TEXT, |
| 33 |
name TEXT, |
| 34 |
home_url TEXT, |
| 35 |
error_url TEXT, |
| 36 |
email TEXT, |
| 37 |
proxy_limited BOOLEAN, |
| 38 |
creation_ts TIMESTAMP, |
| 39 |
rt_lifetime bigint, |
| 40 |
callback_uri TEXT |
| 41 |
); |
| 42 |
|
| 43 |
CREATE TABLE {{ mp_server_db }}.client_approvals ( |
| 44 |
client_id VARCHAR(255) PRIMARY KEY, |
| 45 |
approver TEXT, |
| 46 |
approved BOOLEAN, |
| 47 |
approval_ts TIMESTAMP |
| 48 |
); |
| 49 |
|
| 50 |
CREATE TABLE {{ mp_server_db }}.transactions ( |
| 51 |
temp_token VARCHAR(255) PRIMARY KEY, |
| 52 |
temp_token_valid BOOLEAN, |
| 53 |
callback_uri TEXT, |
| 54 |
certreq TEXT, |
| 55 |
certlifetime BIGINT, |
| 56 |
client_id TEXT, |
| 57 |
verifier_token TEXT, |
| 58 |
access_token TEXT, |
| 59 |
refresh_token TEXT, |
| 60 |
refresh_token_valid BOOLEAN, |
| 61 |
expires_in BIGINT, |
| 62 |
certificate TEXT, |
| 63 |
username TEXT, |
| 64 |
myproxyUsername TEXT, |
| 65 |
claims TEXT CHARACTER SET 'utf8', |
| 66 |
mp_client_session_identifier TEXT, |
| 67 |
UNIQUE INDEX verifier (verifier_token(255)), |
| 68 |
UNIQUE INDEX accessToken (access_token(255)), |
| 69 |
UNIQUE INDEX refreshToken (refresh_token(255)), |
| 70 |
UNIQUE INDEX username (username(255),access_token(255)) |
| 71 |
); |
| 72 |
|
| 73 |
COMMIT; |
| 74 |
/* Now to grant restricted access. The tables have to exist before this step */ |
| 75 |
|
| 76 |
GRANT ALL ON {{ mp_server_db }}.client_approvals |
| 77 |
TO '{{ mp_server_db_user }}'@'localhost' |
| 78 |
IDENTIFIED BY '{{ mp_server_db_pw }}'; |
| 79 |
|
| 80 |
GRANT ALL ON {{ mp_server_db }}.clients |
| 81 |
TO '{{ mp_server_db_user }}'@'localhost' |
| 82 |
IDENTIFIED BY '{{ mp_server_db_pw }}'; |
| 83 |
|
| 84 |
GRANT ALL ON {{ mp_server_db }}.callbacks |
| 85 |
TO '{{ mp_server_db_user }}'@'localhost' |
| 86 |
IDENTIFIED BY '{{ mp_server_db_pw }}'; |
| 87 |
|
| 88 |
GRANT ALL ON {{ mp_server_db }}.transactions |
| 89 |
TO '{{ mp_server_db_user }}'@'localhost' |
| 90 |
IDENTIFIED BY '{{ mp_server_db_pw }}'; |
| 91 |
|
| 92 |
commit; |
Parent Directory
| 
Revision Log