/[pdpsoft]/trunk/aarc.master-portal/ansible/roles/masterportal/templates/mp-oa2-server-mysql.sql.j2
ViewVC logotype

Contents of /trunk/aarc.master-portal/ansible/roles/masterportal/templates/mp-oa2-server-mysql.sql.j2

Parent Directory Parent Directory | Revision Log Revision Log


Revision 3091 - (show annotations) (download)
Thu Aug 18 13:39:16 2016 UTC (5 years, 5 months ago) by tamasb
File size: 3016 byte(s)
added some missing fields 

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 access_token_valid TINYINT(1) DEFAULT NULL,
66 auth_time TIMESTEMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
67 nonce TEXT,
68 scopes TEXT,
69 claims TEXT CHARACTER SET 'utf8',
70 mp_client_session_identifier TEXT,
71 UNIQUE INDEX verifier (verifier_token(255)),
72 UNIQUE INDEX accessToken (access_token(255)),
73 UNIQUE INDEX refreshToken (refresh_token(255)),
74 UNIQUE INDEX username (username(255),access_token(255))
75 );
76
77 COMMIT;
78 /* Now to grant restricted access. The tables have to exist before this step */
79
80 GRANT ALL ON {{ mp_server_db }}.client_approvals
81 TO '{{ mp_server_db_user }}'@'localhost'
82 IDENTIFIED BY '{{ mp_server_db_pw }}';
83
84 GRANT ALL ON {{ mp_server_db }}.clients
85 TO '{{ mp_server_db_user }}'@'localhost'
86 IDENTIFIED BY '{{ mp_server_db_pw }}';
87
88 GRANT ALL ON {{ mp_server_db }}.callbacks
89 TO '{{ mp_server_db_user }}'@'localhost'
90 IDENTIFIED BY '{{ mp_server_db_pw }}';
91
92 GRANT ALL ON {{ mp_server_db }}.transactions
93 TO '{{ mp_server_db_user }}'@'localhost'
94 IDENTIFIED BY '{{ mp_server_db_pw }}';
95
96 commit;

grid.support@nikhef.nl
ViewVC Help
Powered by ViewVC 1.1.28