/[pdpsoft]/trunk/eu.rcauth.pilot-ica/DS/ansible/roles/delegserver/templates/oa4mp-server-mysql.sql.j2
ViewVC logotype

Contents of /trunk/eu.rcauth.pilot-ica/DS/ansible/roles/delegserver/templates/oa4mp-server-mysql.sql.j2

Parent Directory Parent Directory | Revision Log Revision Log


Revision 3007 - (show annotations) (download)
Tue May 3 17:07:17 2016 UTC (5 years, 8 months ago) by tamasb
File size: 3395 byte(s)
updated to support trace_records 

 - new war file
 - new server config 
 - new shibboleth config mapping more attributes
 - mysql scipt for creating the right DB tables


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 '{{ oa4mp_server_db_user }}'@'localhost' IDENTIFIED BY '{{ oa4mp_server_db_pw }}';
11
12 COMMIT;
13
14 Run the rest of this script. The user must exist before permissions are granted.
15 */
16
17 /* CREATE USER '{{ oa4mp_server_db_user }}'@'localhost' IDENTIFIED BY '{{ oa4mp_server_db_pw }}'; */
18
19 CREATE DATABASE IF NOT EXISTS {{ oa4mp_server_db }}
20 DEFAULT CHARACTER SET utf8;
21 USE {{ oa4mp_server_db }};
22
23 /*
24 Some useful commands. Lst two list users and will show permissions for a single user on a machine.:
25 Show Databases;
26 Show schemas;
27 SELECT User FROM mysql.user;
28 SHOW GRANTS FOR 'user'@'localhost';
29 */
30
31 CREATE TABLE IF NOT EXISTS {{ oa4mp_server_db }}.clients (
32 client_id VARCHAR(255) PRIMARY KEY,
33 public_key TEXT,
34 name TEXT,
35 home_url TEXT,
36 error_url TEXT,
37 email TEXT,
38 proxy_limited BOOLEAN,
39 creation_ts TIMESTAMP,
40 rt_lifetime bigint,
41 callback_uri TEXT
42 );
43
44 CREATE TABLE IF NOT EXISTS {{ oa4mp_server_db }}.client_approvals (
45 client_id VARCHAR(255) PRIMARY KEY,
46 approver TEXT,
47 approved BOOLEAN,
48 approval_ts TIMESTAMP
49 );
50
51 CREATE TABLE IF NOT EXISTS {{ oa4mp_server_db }}.transactions (
52 temp_token VARCHAR(255) PRIMARY KEY,
53 temp_token_valid BOOLEAN,
54 callback_uri TEXT,
55 certreq TEXT,
56 certlifetime BIGINT,
57 client_id TEXT,
58 verifier_token TEXT,
59 access_token TEXT,
60 refresh_token TEXT,
61 refresh_token_valid BOOLEAN,
62 expires_in BIGINT,
63 certificate TEXT,
64 username TEXT,
65 myproxyUsername TEXT,
66 claims TEXT CHARACTER SET 'utf8',
67 user_attributes TEXT CHARACTER SET 'utf8',
68 trace_record TEXT,
69 UNIQUE INDEX verifier (verifier_token(255)),
70 UNIQUE INDEX accessToken (access_token(255)),
71 UNIQUE INDEX refreshToken (refresh_token(255)),
72 UNIQUE INDEX username (username(255),access_token(255))
73 );
74
75
76 CREATE TABLE IF NOT EXISTS {{ oa4mp_server_db }}.trace_records (
77 cn_hash CHAR(44),
78 sequence_nr SMALLINT UNSIGNED,
79 attribute_hash CHAR(44),
80 attribute_salt CHAR(44),
81 attribute_names TEXT,
82 first_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
83 last_seen TIMESTAMP,
84 PRIMARY KEY (cn_hash, sequence_nr)
85 );
86
87 COMMIT;
88
89 /*
90 Now to grant restricted access. The tables have to exist before this step
91 */
92
93 GRANT ALL ON {{ oa4mp_server_db }}.client_approvals
94 TO '{{ oa4mp_server_db_user }}'@'localhost'
95 IDENTIFIED BY '{{ oa4mp_server_db_pw }}';
96
97 GRANT ALL ON {{ oa4mp_server_db }}.clients
98 TO '{{ oa4mp_server_db_user }}'@'localhost'
99 IDENTIFIED BY '{{ oa4mp_server_db_pw }}';
100
101 GRANT ALL ON {{ oa4mp_server_db }}.callbacks
102 TO '{{ oa4mp_server_db_user }}'@'localhost'
103 IDENTIFIED BY '{{ oa4mp_server_db_pw }}';
104
105 GRANT ALL ON {{ oa4mp_server_db }}.transactions
106 TO '{{ oa4mp_server_db_user }}'@'localhost'
107 IDENTIFIED BY '{{ oa4mp_server_db_pw }}';
108
109 GRANT ALL ON {{ oa4mp_server_db }}.trace_records
110 TO '{{ oa4mp_server_db_user }}'@'localhost'
111 IDENTIFIED BY '{{ oa4mp_server_db_pw }}';
112
113 COMMIT;

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