· 4 years ago · Mar 02, 2021, 03:06 PM
1/****************************************************
2 STEP 01 - Create Admin database
3*****************************************************/
4use role cpdw_sbx_accountadmin;
5drop database if exists cpadmin_sbx;
6create or replace database cpadmin_sbx
7 comment = 'The admin database contains schemas that support the platform administration and automation in the account, e.g. RBAC access role automation';
8create or replace schema secadmin with managed access
9 comment = 'The secadmin schema contains objects that support SecOps in the account, e.g. RBAC access role automation';
10grant usage on database cpadmin_sbx to role cpdw_sbx_securityadmin;
11grant ownership on schema secadmin to role cpdw_sbx_securityadmin ;
12
13/****************************************************
14 STEP 02 - Create metamodel for storing the grants required for schemas, warehouses
15*****************************************************/
16
17use role cpdw_sbx_whadmin;
18grant usage on warehouse CPDW_SBX_GENERIC_WH to role cpdw_sbx_securityadmin;
19use role cpdw_sbx_securityadmin;
20use schema cpadmin_sbx.secadmin;
21use warehouse CPDW_SBX_GENERIC_WH;
22
23create or replace table rbac_grant(
24 id varchar(40) default UUID_STRING(),
25 future_grant boolean not null,
26 privilege varchar(255) not null,
27 object_level varchar(255) not null,
28 role_level varchar(255) not null,
29 ordinal integer not null,
30 created_role varchar(255) default current_role(),
31 created_by varchar(100) default current_user(),
32 created_on timestamp default current_timestamp(),
33 updated_role varchar(255) default current_role(),
34 updated_by varchar(100) default current_user(),
35 updated_on timestamp default current_timestamp(),
36 CONSTRAINT rbac_grant_pk PRIMARY KEY (id)
37 )
38 comment = 'Contains the grants required by RBAC access role best practices';
39
40insert into rbac_grant
41(privilege, object_level, role_level, future_grant, ordinal)
42values
43 ('select on all tables', 'schema', 'SR', true, 1)
44,('select on all views ', 'schema', 'SR', true, 2)
45,('usage, read on all stages', 'schema', 'SR', true, 3)
46,('usage on all file formats', 'schema', 'SR', true, 4)
47,('select on all streams', 'schema', 'SR', true, 5)
48,('usage on all functions', 'schema', 'SR', true, 6)
49,('insert, update, delete, references on all tables', 'schema', 'SRW', true, 7)
50,('read, write on all stages', 'schema', 'SRW', true, 9)
51,('usage on all sequences', 'schema', 'SRW', true, 10)
52,('usage on all procedures', 'schema', 'SRW', true, 11)
53,('monitor, operate on all tasks', 'schema', 'SRW', true, 12)
54,('ownership on all tables', 'schema', 'SFULL', true, 13)
55,('ownership on all external tables', 'schema', 'SFULL', true, 13)
56,('ownership on all views ', 'schema', 'SFULL', true, 14)
57,('ownership on all materialized views', 'schema', 'SFULL', true, 14)
58,('ownership on all stages', 'schema', 'SFULL', true, 15)
59,('ownership on all file formats', 'schema', 'SFULL', true, 16)
60,('ownership on all streams', 'schema', 'SFULL', true, 17)
61,('ownership on all procedures', 'schema', 'SFULL', true, 18)
62,('ownership on all functions', 'schema', 'SFULL', true, 19)
63,('ownership on all sequences ', 'schema', 'SFULL', true, 20)
64,('ownership on all tasks', 'schema', 'SFULL', true, 21)
65,('usage', 'warehouse', 'WU', true, 1)
66,('operate, monitor', 'warehouse', 'WUO', true, 2)
67,('modify', 'warehouse', 'WFULL', true, 3);
68
69/****************************************************
70 STEP 03 - Create the Stored Proc to generate the RBAC access roles
71*****************************************************/
72/****************************************************************************************\
73 DESC: Create the baseline access and technical roles for schemas in database(s) and Warehouse.
74 Sproc is Owned Pseudo-admin
75
76 Follows best practices provided by Snowflake:
77
78 Best Practices for Access roles:
79 Fully qualified name to desired granularity
80 Include privilege level in role name
81
82 Example Format:
83 <database_name>_<schema_name>_<access level>
84
85
86 Stored proce sample usage:
87
88 call mydb.utl.SP_RBAC_CREATE_ACCESS_ROLE(to_array('MYDB'), ,
89 ARRAY_APPEND(TO_ARRAY('MYWH_XS'), 'MYWH_XL'),
90 false
91 );
92
93 YYYY-MM-DD WHO CHANGE DESCRIPTION
94 ---------- ------------ ----------------------------------------------------------------
95 2020-10-13 dsandler Initial Version - PrPr
96\****************************************************************************************/
97
98use role cpdw_sbx_securityadmin;
99
100CREATE OR REPLACE PROCEDURE SP_RBAC_CREATE_ACCESS_ROLE
101 (DATABASE_ARR ARRAY, -- pass the list of databases for the schema-level access roles
102 WAREHOUSE_ARR ARRAY, -- pass the list of warehouses for the warehouse-level access roles
103 EXECUTE_GRANTS boolean
104 )
105RETURNS VARIANT NOT NULL -- will return a JSON string containg the DDL to create roles and grants,
106 -- will also contain a log of any grants if in_EXECUTE_GRANTS specified
107LANGUAGE JAVASCRIPT
108EXECUTE AS CALLER
109AS
110$$
111/****************************************************************************************\
112 Assign variables
113\****************************************************************************************/
114// This variable will hold a JSON data structure that holds ONE row.
115var json_row_ddl = {};
116// This array will contain all the rows.
117var array_of_ddl_rows = [];
118// This variable will hold a JSON data structure that we can return as
119// a VARIANT.
120// This will contain ALL the rows in a single "value".
121var json_tbl_ddl = {};
122
123var exec_grants_bool = false;
124var create_tech_role_bool = false;
125
126if (EXECUTE_GRANTS != undefined) {
127 exec_grants_bool = EXECUTE_GRANTS;
128}
129
130/****************************************************************************************\
131 Access roles - Schema level
132\****************************************************************************************/
133
134var base_access_role_ddl = [
135"USE ROLE <database_name>_roleadmin;",
136"CREATE or REPLACE ROLE <database_name>_<schema_name>_sr;",
137"CREATE or REPLACE ROLE <database_name>_<schema_name>_srw;",
138"CREATE or REPLACE ROLE <database_name>_<schema_name>_sfull;",
139"GRANT ROLE <database_name>_<schema_name>_sr TO ROLE <database_name>_<schema_name>_srw;",
140"GRANT ROLE <database_name>_<schema_name>_srw TO ROLE <database_name>_<schema_name>_sfull;",
141"GRANT ROLE <database_name>_<schema_name>_sfull TO ROLE <database_name>_dbadmin;",
142"USE ROLE <database_name>_dbadmin;",
143"GRANT USAGE ON DATABASE <database_name> TO ROLE <database_name>_<schema_name>_sr;",
144"GRANT USAGE ON SCHEMA <database_name>.<schema_name> TO ROLE <database_name>_<schema_name>_sr;"
145];
146
147// Get schema grants
148select_sql = `select privilege, object_level, role_level, future_grant
149 from rbac_grant
150 where object_level = 'schema'
151 order by ordinal`;
152select_exec = {sqlText: select_sql};
153stmt = snowflake.createStatement(select_exec);
154rs = stmt.execute();
155
156// Add schema-level grants
157while (rs.next()) {
158 var privilege = rs.getColumnValue('PRIVILEGE');
159 var future_grant = rs.getColumnValue('FUTURE_GRANT');
160 var object_level = rs.getColumnValue('OBJECT_LEVEL');
161 var role_level = rs.getColumnValue('ROLE_LEVEL').toLowerCase();
162 var base_grant = `GRANT ${privilege} in ${object_level} <database_name>.<schema_name> to ROLE <database_name>_<schema_name>_${role_level};`;
163 var base_future_grant = base_grant.replace(/ all /g, " future " );
164 base_access_role_ddl.push(`${base_grant}`);
165 if (future_grant) {
166 base_access_role_ddl.push(`${base_future_grant}`);
167 }
168}
169
170// Step 1 - loop through databases, get schemas
171
172for (var database_cnt = 0; database_cnt < DATABASE_ARR.length; database_cnt++) {
173 var database_name = DATABASE_ARR[database_cnt];
174 var show_schema_sql = "show schemas in database " + database_name;
175 var select_schema_sql = "select schema_name from " + database_name + ".information_schema.schemata where schema_name not in ('PUBLIC', 'INFORMATION_SCHEMA')";
176
177
178 var select_schema_exec = {sqlText: select_schema_sql};
179 var stmt = snowflake.createStatement(select_schema_exec);
180 var rs = stmt.execute();
181
182 // Read each row and add it to the array we will return.
183 while (rs.next()) {
184
185 for (var ddl_stmt_cnt = 0; ddl_stmt_cnt < base_access_role_ddl.length; ddl_stmt_cnt++) {
186 json_row_ddl = {};
187 var schema_name = rs.getColumnValue(1).toLowerCase();
188 json_row_ddl["database"] = database_name;
189 json_row_ddl["schema"] = schema_name;
190
191 var ddl = base_access_role_ddl[ddl_stmt_cnt];
192 json_row_ddl["ddl_ordinal"] = ddl_stmt_cnt + 1;
193 var create_access_ddl = ddl.replace(/<database_name>/g, database_name.toLowerCase()).replace(/<schema_name>/g, schema_name);
194 json_row_ddl["create_access_ddl"] = create_access_ddl;
195 json_row_ddl["ddl_executed"] = exec_grants_bool;
196 var execution_result = "";
197 var error_message = "";
198 if (exec_grants_bool) {
199 var ddl_rbac = {sqlText: create_access_ddl};
200 try {
201 var ddl_stmt = snowflake.createStatement(ddl_rbac);
202 //var ddl_rs = snowflake.execute( {sqlText: "use role sysadmin;"} );
203 var ddl_rs = ddl_stmt.execute();
204 execution_result = "Success";
205 } catch (err) {
206 execution_result = "Failure";
207 error_message = err;
208 }
209 }
210 json_row_ddl["ddl_execution_status"] = execution_result;
211 json_row_ddl["ddl_execution_message"] = error_message;
212 array_of_ddl_rows.push(json_row_ddl);
213 }
214 }
215}
216
217
218/****************************************************************************************\
219 Access roles - Warehouse level
220\****************************************************************************************/
221
222
223var base_access_role_wh_ddl = [
224"USE ROLE <database_name>_roleadmin;",
225"CREATE or REPLACE ROLE <warehouse_name>_wu;",
226"CREATE or REPLACE ROLE <warehouse_name>_wuo;",
227"CREATE or REPLACE ROLE <warehouse_name>_wfull;",
228"GRANT ROLE <warehouse_name>_wu TO ROLE <warehouse_name>_wuo;",
229"GRANT ROLE <warehouse_name>_wuo TO ROLE <warehouse_name>_wfull;",
230"GRANT ROLE <warehouse_name>_wfull TO ROLE <database_name>_whadmin;",
231"USE ROLE <database_name>_whadmin;"
232];
233
234// Get warehouse grants
235select_sql = `select privilege, object_level, role_level, future_grant
236 from rbac_grant
237 where object_level = 'warehouse'
238 order by ordinal`;
239select_exec = {sqlText: select_sql};
240stmt = snowflake.createStatement(select_exec);
241rs = stmt.execute();
242
243// Add warehouse-level grants
244while (rs.next()) {
245 var privilege = rs.getColumnValue('PRIVILEGE');
246 var future_grant = rs.getColumnValue('FUTURE_GRANT');
247 var object_level = rs.getColumnValue('OBJECT_LEVEL');
248 var role_level = rs.getColumnValue('ROLE_LEVEL').toLowerCase();
249 var base_grant = `GRANT ${privilege} on ${object_level} <warehouse_name> to ROLE <warehouse_name>_${role_level};`;
250 base_access_role_wh_ddl.push(`${base_grant}`);
251}
252for (var warehouse_cnt = 0; warehouse_cnt < WAREHOUSE_ARR.length; warehouse_cnt++) {
253 var warehouse_name = WAREHOUSE_ARR[warehouse_cnt].toLowerCase();
254 for (var ddl_stmt_cnt = 0; ddl_stmt_cnt < base_access_role_wh_ddl.length; ddl_stmt_cnt++) {
255 json_row_ddl = {};
256 json_row_ddl["warehouse"] = warehouse_name;
257
258 var ddl = base_access_role_wh_ddl[ddl_stmt_cnt];
259 json_row_ddl["ddl_ordinal"] = ddl_stmt_cnt + 1;
260 var create_access_ddl = ddl.replace(/<warehouse_name>/g, warehouse_name).replace(/<database_name>/g, database_name.toLowerCase());
261 json_row_ddl["create_access_ddl"] = create_access_ddl;
262 json_row_ddl["ddl_executed"] = exec_grants_bool;
263 var execution_result = "";
264 var error_message = "";
265 if (exec_grants_bool) {
266 var ddl_rbac = {sqlText: create_access_ddl};
267 try {
268 var ddl_stmt = snowflake.createStatement(ddl_rbac);
269 //var ddl_rs = snowflake.execute( {sqlText: "use role sysadmin;"} );
270 var ddl_rs = ddl_stmt.execute();
271 execution_result = "Success";
272 } catch (err) {
273 execution_result = "Failure";
274 error_message = err;
275 }
276 }
277 json_row_ddl["ddl_execution_status"] = execution_result;
278 json_row_ddl["ddl_execution_message"] = error_message;
279 array_of_ddl_rows.push(json_row_ddl);
280 }
281}
282
283// Return the rows, which expects a JSON-compatible VARIANT.
284
285json_tbl_ddl = { "rbac_access_role_script" : array_of_ddl_rows };
286return json_tbl_ddl;
287$$
288;
289
290
291/****************************************************
292 STEP 04 - Grant permissions to pseudo-sysadmin role
293*****************************************************/
294use role cpdw_sbx_securityadmin;
295grant usage on database cpadmin_sbx to cpdw_sbx_sysadmin;
296grant usage on schema cpadmin_sbx.secadmin to cpdw_sbx_sysadmin;
297grant select on secadmin.rbac_grant to cpdw_sbx_sysadmin;
298grant usage on procedure SP_RBAC_CREATE_ACCESS_ROLE(ARRAY, ARRAY, boolean) to cpdw_sbx_sysadmin;
299
300
301/****************************************************
302 STEP 05 - Confirm access and usage
303*****************************************************/
304use role cpdw_sbx_sysadmin;
305use schema cpadmin_sbx.secadmin;
306use warehouse cpdw_sbx_generic_wh;
307call SP_RBAC_CREATE_ACCESS_ROLE(ARRAY_CONSTRUCT('CPDW_SBX'),
308 ARRAY_CONSTRUCT('CPDW_SBX_DS_WH',
309 'CPDW_SBX_ENGINEERING_WH',
310 'CPDW_SBX_GENERIC_WH',
311 'CPDW_SBX_INGESTION_WH',
312 'CPDW_SBX_BI_WH'),
313 false);
314
315
316select lf.value:create_access_ddl::string ddl
317 from table(result_scan(last_query_id())) q
318, lateral flatten( input => q.sp_rbac_create_access_role:rbac_access_role_script ) lf
319;
320
321
322{
323 "rbac_access_role_script": [
324 {
325 "create_access_ddl": "USE ROLE cpdw_sbx_roleadmin;",
326 "database": "CPDW_SBX",
327 "ddl_executed": 0,
328 "ddl_execution_message": "",
329 "ddl_execution_status": "",
330 "ddl_ordinal": 1,
331 "schema": "cl_freight"
332 },
333 {
334 "create_access_ddl": "CREATE or REPLACE ROLE cpdw_sbx_cl_freight_sr;",
335 "database": "CPDW_SBX",
336 "ddl_executed": 0,
337 "ddl_execution_message": "",
338 "ddl_execution_status": "",
339 "ddl_ordinal": 2,
340 "schema": "cl_freight"
341 },
342 {
343 "create_access_ddl": "CREATE or REPLACE ROLE cpdw_sbx_cl_freight_srw;",
344 "database": "CPDW_SBX",
345 "ddl_executed": 0,
346 "ddl_execution_message": "",
347 "ddl_execution_status": "",
348 "ddl_ordinal": 3,
349 "schema": "cl_freight"
350 },
351 {
352 "create_access_ddl": "CREATE or REPLACE ROLE cpdw_sbx_cl_freight_sfull;",
353 "database": "CPDW_SBX",
354 "ddl_executed": 0,
355 "ddl_execution_message": "",
356 "ddl_execution_status": "",
357 "ddl_ordinal": 4,
358 "schema": "cl_freight"
359 },
360 {
361 "create_access_ddl": "GRANT ROLE cpdw_sbx_cl_freight_sr TO ROLE cpdw_sbx_cl_freight_srw;",
362 "database": "CPDW_SBX",
363 "ddl_executed": 0,
364 "ddl_execution_message": "",
365 "ddl_execution_status": "",
366 "ddl_ordinal": 5,
367 "schema": "cl_freight"
368 },
369 {
370 "create_access_ddl": "GRANT ROLE cpdw_sbx_cl_freight_srw TO ROLE cpdw_sbx_cl_freight_sfull;",
371 "database": "CPDW_SBX",
372 "ddl_executed": 0,
373 "ddl_execution_message": "",
374 "ddl_execution_status": "",
375 "ddl_ordinal": 6,
376 "schema": "cl_freight"
377 },
378 {
379 "create_access_ddl": "GRANT ROLE cpdw_sbx_cl_freight_sfull TO ROLE cpdw_sbx_dbadmin;",
380 "database": "CPDW_SBX",
381 "ddl_executed": 0,
382 "ddl_execution_message": "",
383 "ddl_execution_status": "",
384 "ddl_ordinal": 7,
385 "schema": "cl_freight"
386 },
387 {
388 "create_access_ddl": "USE ROLE cpdw_sbx_dbadmin;",
389 "database": "CPDW_SBX",
390 "ddl_executed": 0,
391 "ddl_execution_message": "",
392 "ddl_execution_status": "",
393 "ddl_ordinal": 8,
394 "schema": "cl_freight"
395 },
396 {
397 "create_access_ddl": "GRANT USAGE ON DATABASE cpdw_sbx TO ROLE cpdw_sbx_cl_freight_sr;",
398 "database": "CPDW_SBX",
399 "ddl_executed": 0,
400 "ddl_execution_message": "",
401 "ddl_execution_status": "",
402 "ddl_ordinal": 9,
403 "schema": "cl_freight"
404 },
405 {
406 "create_access_ddl": "GRANT USAGE ON SCHEMA cpdw_sbx.cl_freight TO ROLE cpdw_sbx_cl_freight_sr;",
407 "database": "CPDW_SBX",
408 "ddl_executed": 0,
409 "ddl_execution_message": "",
410 "ddl_execution_status": "",
411 "ddl_ordinal": 10,
412 "schema": "cl_freight"
413 },
414 {
415 "create_access_ddl": "GRANT select on all tables in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sr;",
416 "database": "CPDW_SBX",
417 "ddl_executed": 0,
418 "ddl_execution_message": "",
419 "ddl_execution_status": "",
420 "ddl_ordinal": 11,
421 "schema": "cl_freight"
422 },
423 {
424 "create_access_ddl": "GRANT select on future tables in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sr;",
425 "database": "CPDW_SBX",
426 "ddl_executed": 0,
427 "ddl_execution_message": "",
428 "ddl_execution_status": "",
429 "ddl_ordinal": 12,
430 "schema": "cl_freight"
431 },
432 {
433 "create_access_ddl": "GRANT select on all views in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sr;",
434 "database": "CPDW_SBX",
435 "ddl_executed": 0,
436 "ddl_execution_message": "",
437 "ddl_execution_status": "",
438 "ddl_ordinal": 13,
439 "schema": "cl_freight"
440 },
441 {
442 "create_access_ddl": "GRANT select on future views in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sr;",
443 "database": "CPDW_SBX",
444 "ddl_executed": 0,
445 "ddl_execution_message": "",
446 "ddl_execution_status": "",
447 "ddl_ordinal": 14,
448 "schema": "cl_freight"
449 },
450 {
451 "create_access_ddl": "GRANT usage, read on all stages in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sr;",
452 "database": "CPDW_SBX",
453 "ddl_executed": 0,
454 "ddl_execution_message": "",
455 "ddl_execution_status": "",
456 "ddl_ordinal": 15,
457 "schema": "cl_freight"
458 },
459 {
460 "create_access_ddl": "GRANT usage, read on future stages in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sr;",
461 "database": "CPDW_SBX",
462 "ddl_executed": 0,
463 "ddl_execution_message": "",
464 "ddl_execution_status": "",
465 "ddl_ordinal": 16,
466 "schema": "cl_freight"
467 },
468 {
469 "create_access_ddl": "GRANT usage on all file formats in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sr;",
470 "database": "CPDW_SBX",
471 "ddl_executed": 0,
472 "ddl_execution_message": "",
473 "ddl_execution_status": "",
474 "ddl_ordinal": 17,
475 "schema": "cl_freight"
476 },
477 {
478 "create_access_ddl": "GRANT usage on future file formats in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sr;",
479 "database": "CPDW_SBX",
480 "ddl_executed": 0,
481 "ddl_execution_message": "",
482 "ddl_execution_status": "",
483 "ddl_ordinal": 18,
484 "schema": "cl_freight"
485 },
486 {
487 "create_access_ddl": "GRANT select on all streams in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sr;",
488 "database": "CPDW_SBX",
489 "ddl_executed": 0,
490 "ddl_execution_message": "",
491 "ddl_execution_status": "",
492 "ddl_ordinal": 19,
493 "schema": "cl_freight"
494 },
495 {
496 "create_access_ddl": "GRANT select on future streams in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sr;",
497 "database": "CPDW_SBX",
498 "ddl_executed": 0,
499 "ddl_execution_message": "",
500 "ddl_execution_status": "",
501 "ddl_ordinal": 20,
502 "schema": "cl_freight"
503 },
504 {
505 "create_access_ddl": "GRANT usage on all functions in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sr;",
506 "database": "CPDW_SBX",
507 "ddl_executed": 0,
508 "ddl_execution_message": "",
509 "ddl_execution_status": "",
510 "ddl_ordinal": 21,
511 "schema": "cl_freight"
512 },
513 {
514 "create_access_ddl": "GRANT usage on future functions in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sr;",
515 "database": "CPDW_SBX",
516 "ddl_executed": 0,
517 "ddl_execution_message": "",
518 "ddl_execution_status": "",
519 "ddl_ordinal": 22,
520 "schema": "cl_freight"
521 },
522 {
523 "create_access_ddl": "GRANT insert, update, delete, references on all tables in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_srw;",
524 "database": "CPDW_SBX",
525 "ddl_executed": 0,
526 "ddl_execution_message": "",
527 "ddl_execution_status": "",
528 "ddl_ordinal": 23,
529 "schema": "cl_freight"
530 },
531 {
532 "create_access_ddl": "GRANT insert, update, delete, references on future tables in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_srw;",
533 "database": "CPDW_SBX",
534 "ddl_executed": 0,
535 "ddl_execution_message": "",
536 "ddl_execution_status": "",
537 "ddl_ordinal": 24,
538 "schema": "cl_freight"
539 },
540 {
541 "create_access_ddl": "GRANT read, write on all stages in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_srw;",
542 "database": "CPDW_SBX",
543 "ddl_executed": 0,
544 "ddl_execution_message": "",
545 "ddl_execution_status": "",
546 "ddl_ordinal": 25,
547 "schema": "cl_freight"
548 },
549 {
550 "create_access_ddl": "GRANT read, write on future stages in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_srw;",
551 "database": "CPDW_SBX",
552 "ddl_executed": 0,
553 "ddl_execution_message": "",
554 "ddl_execution_status": "",
555 "ddl_ordinal": 26,
556 "schema": "cl_freight"
557 },
558 {
559 "create_access_ddl": "GRANT usage on all sequences in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_srw;",
560 "database": "CPDW_SBX",
561 "ddl_executed": 0,
562 "ddl_execution_message": "",
563 "ddl_execution_status": "",
564 "ddl_ordinal": 27,
565 "schema": "cl_freight"
566 },
567 {
568 "create_access_ddl": "GRANT usage on future sequences in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_srw;",
569 "database": "CPDW_SBX",
570 "ddl_executed": 0,
571 "ddl_execution_message": "",
572 "ddl_execution_status": "",
573 "ddl_ordinal": 28,
574 "schema": "cl_freight"
575 },
576 {
577 "create_access_ddl": "GRANT usage on all procedures in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_srw;",
578 "database": "CPDW_SBX",
579 "ddl_executed": 0,
580 "ddl_execution_message": "",
581 "ddl_execution_status": "",
582 "ddl_ordinal": 29,
583 "schema": "cl_freight"
584 },
585 {
586 "create_access_ddl": "GRANT usage on future procedures in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_srw;",
587 "database": "CPDW_SBX",
588 "ddl_executed": 0,
589 "ddl_execution_message": "",
590 "ddl_execution_status": "",
591 "ddl_ordinal": 30,
592 "schema": "cl_freight"
593 },
594 {
595 "create_access_ddl": "GRANT monitor, operate on all tasks in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_srw;",
596 "database": "CPDW_SBX",
597 "ddl_executed": 0,
598 "ddl_execution_message": "",
599 "ddl_execution_status": "",
600 "ddl_ordinal": 31,
601 "schema": "cl_freight"
602 },
603 {
604 "create_access_ddl": "GRANT monitor, operate on future tasks in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_srw;",
605 "database": "CPDW_SBX",
606 "ddl_executed": 0,
607 "ddl_execution_message": "",
608 "ddl_execution_status": "",
609 "ddl_ordinal": 32,
610 "schema": "cl_freight"
611 },
612 {
613 "create_access_ddl": "GRANT ownership on all tables in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sfull;",
614 "database": "CPDW_SBX",
615 "ddl_executed": 0,
616 "ddl_execution_message": "",
617 "ddl_execution_status": "",
618 "ddl_ordinal": 33,
619 "schema": "cl_freight"
620 },
621 {
622 "create_access_ddl": "GRANT ownership on future tables in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sfull;",
623 "database": "CPDW_SBX",
624 "ddl_executed": 0,
625 "ddl_execution_message": "",
626 "ddl_execution_status": "",
627 "ddl_ordinal": 34,
628 "schema": "cl_freight"
629 },
630 {
631 "create_access_ddl": "GRANT ownership on all views in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sfull;",
632 "database": "CPDW_SBX",
633 "ddl_executed": 0,
634 "ddl_execution_message": "",
635 "ddl_execution_status": "",
636 "ddl_ordinal": 35,
637 "schema": "cl_freight"
638 },
639 {
640 "create_access_ddl": "GRANT ownership on future views in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sfull;",
641 "database": "CPDW_SBX",
642 "ddl_executed": 0,
643 "ddl_execution_message": "",
644 "ddl_execution_status": "",
645 "ddl_ordinal": 36,
646 "schema": "cl_freight"
647 },
648 {
649 "create_access_ddl": "GRANT ownership on all stages in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sfull;",
650 "database": "CPDW_SBX",
651 "ddl_executed": 0,
652 "ddl_execution_message": "",
653 "ddl_execution_status": "",
654 "ddl_ordinal": 37,
655 "schema": "cl_freight"
656 },
657 {
658 "create_access_ddl": "GRANT ownership on future stages in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sfull;",
659 "database": "CPDW_SBX",
660 "ddl_executed": 0,
661 "ddl_execution_message": "",
662 "ddl_execution_status": "",
663 "ddl_ordinal": 38,
664 "schema": "cl_freight"
665 },
666 {
667 "create_access_ddl": "GRANT ownership on all file formats in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sfull;",
668 "database": "CPDW_SBX",
669 "ddl_executed": 0,
670 "ddl_execution_message": "",
671 "ddl_execution_status": "",
672 "ddl_ordinal": 39,
673 "schema": "cl_freight"
674 },
675 {
676 "create_access_ddl": "GRANT ownership on future file formats in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sfull;",
677 "database": "CPDW_SBX",
678 "ddl_executed": 0,
679 "ddl_execution_message": "",
680 "ddl_execution_status": "",
681 "ddl_ordinal": 40,
682 "schema": "cl_freight"
683 },
684 {
685 "create_access_ddl": "GRANT ownership on all streams in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sfull;",
686 "database": "CPDW_SBX",
687 "ddl_executed": 0,
688 "ddl_execution_message": "",
689 "ddl_execution_status": "",
690 "ddl_ordinal": 41,
691 "schema": "cl_freight"
692 },
693 {
694 "create_access_ddl": "GRANT ownership on future streams in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sfull;",
695 "database": "CPDW_SBX",
696 "ddl_executed": 0,
697 "ddl_execution_message": "",
698 "ddl_execution_status": "",
699 "ddl_ordinal": 42,
700 "schema": "cl_freight"
701 },
702 {
703 "create_access_ddl": "GRANT ownership on all procedures in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sfull;",
704 "database": "CPDW_SBX",
705 "ddl_executed": 0,
706 "ddl_execution_message": "",
707 "ddl_execution_status": "",
708 "ddl_ordinal": 43,
709 "schema": "cl_freight"
710 },
711 {
712 "create_access_ddl": "GRANT ownership on future procedures in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sfull;",
713 "database": "CPDW_SBX",
714 "ddl_executed": 0,
715 "ddl_execution_message": "",
716 "ddl_execution_status": "",
717 "ddl_ordinal": 44,
718 "schema": "cl_freight"
719 },
720 {
721 "create_access_ddl": "GRANT ownership on all functions in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sfull;",
722 "database": "CPDW_SBX",
723 "ddl_executed": 0,
724 "ddl_execution_message": "",
725 "ddl_execution_status": "",
726 "ddl_ordinal": 45,
727 "schema": "cl_freight"
728 },
729 {
730 "create_access_ddl": "GRANT ownership on future functions in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sfull;",
731 "database": "CPDW_SBX",
732 "ddl_executed": 0,
733 "ddl_execution_message": "",
734 "ddl_execution_status": "",
735 "ddl_ordinal": 46,
736 "schema": "cl_freight"
737 },
738 {
739 "create_access_ddl": "GRANT ownership on all sequences in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sfull;",
740 "database": "CPDW_SBX",
741 "ddl_executed": 0,
742 "ddl_execution_message": "",
743 "ddl_execution_status": "",
744 "ddl_ordinal": 47,
745 "schema": "cl_freight"
746 },
747 {
748 "create_access_ddl": "GRANT ownership on future sequences in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sfull;",
749 "database": "CPDW_SBX",
750 "ddl_executed": 0,
751 "ddl_execution_message": "",
752 "ddl_execution_status": "",
753 "ddl_ordinal": 48,
754 "schema": "cl_freight"
755 },
756 {
757 "create_access_ddl": "GRANT ownership on all tasks in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sfull;",
758 "database": "CPDW_SBX",
759 "ddl_executed": 0,
760 "ddl_execution_message": "",
761 "ddl_execution_status": "",
762 "ddl_ordinal": 49,
763 "schema": "cl_freight"
764 },
765 {
766 "create_access_ddl": "GRANT ownership on future tasks in schema cpdw_sbx.cl_freight to ROLE cpdw_sbx_cl_freight_sfull;",
767 "database": "CPDW_SBX",
768 "ddl_executed": 0,
769 "ddl_execution_message": "",
770 "ddl_execution_status": "",
771 "ddl_ordinal": 50,
772 "schema": "cl_freight"
773 },
774 {
775 "create_access_ddl": "USE ROLE cpdw_sbx_roleadmin;",
776 "ddl_executed": 0,
777 "ddl_execution_message": "",
778 "ddl_execution_status": "",
779 "ddl_ordinal": 1,
780 "warehouse": "cpdw_sbx_engineering_wh"
781 },
782 {
783 "create_access_ddl": "CREATE or REPLACE ROLE cpdw_sbx_engineering_wh_wu;",
784 "ddl_executed": 0,
785 "ddl_execution_message": "",
786 "ddl_execution_status": "",
787 "ddl_ordinal": 2,
788 "warehouse": "cpdw_sbx_engineering_wh"
789 },
790 {
791 "create_access_ddl": "CREATE or REPLACE ROLE cpdw_sbx_engineering_wh_wuo;",
792 "ddl_executed": 0,
793 "ddl_execution_message": "",
794 "ddl_execution_status": "",
795 "ddl_ordinal": 3,
796 "warehouse": "cpdw_sbx_engineering_wh"
797 },
798 {
799 "create_access_ddl": "CREATE or REPLACE ROLE cpdw_sbx_engineering_wh_wfull;",
800 "ddl_executed": 0,
801 "ddl_execution_message": "",
802 "ddl_execution_status": "",
803 "ddl_ordinal": 4,
804 "warehouse": "cpdw_sbx_engineering_wh"
805 },
806 {
807 "create_access_ddl": "GRANT ROLE cpdw_sbx_engineering_wh_wu TO ROLE cpdw_sbx_engineering_wh_wuo;",
808 "ddl_executed": 0,
809 "ddl_execution_message": "",
810 "ddl_execution_status": "",
811 "ddl_ordinal": 5,
812 "warehouse": "cpdw_sbx_engineering_wh"
813 },
814 {
815 "create_access_ddl": "GRANT ROLE cpdw_sbx_engineering_wh_wuo TO ROLE cpdw_sbx_engineering_wh_wfull;",
816 "ddl_executed": 0,
817 "ddl_execution_message": "",
818 "ddl_execution_status": "",
819 "ddl_ordinal": 6,
820 "warehouse": "cpdw_sbx_engineering_wh"
821 },
822 {
823 "create_access_ddl": "GRANT ROLE cpdw_sbx_engineering_wh_wfull TO ROLE cpdw_sbx_whadmin;",
824 "ddl_executed": 0,
825 "ddl_execution_message": "",
826 "ddl_execution_status": "",
827 "ddl_ordinal": 7,
828 "warehouse": "cpdw_sbx_engineering_wh"
829 },
830 {
831 "create_access_ddl": "USE ROLE cpdw_sbx_whadmin;",
832 "ddl_executed": 0,
833 "ddl_execution_message": "",
834 "ddl_execution_status": "",
835 "ddl_ordinal": 8,
836 "warehouse": "cpdw_sbx_engineering_wh"
837 },
838 {
839 "create_access_ddl": "GRANT usage on warehouse cpdw_sbx_engineering_wh to ROLE cpdw_sbx_engineering_wh_wu;",
840 "ddl_executed": 0,
841 "ddl_execution_message": "",
842 "ddl_execution_status": "",
843 "ddl_ordinal": 9,
844 "warehouse": "cpdw_sbx_engineering_wh"
845 },
846 {
847 "create_access_ddl": "GRANT operate, monitor on warehouse cpdw_sbx_engineering_wh to ROLE cpdw_sbx_engineering_wh_wuo;",
848 "ddl_executed": 0,
849 "ddl_execution_message": "",
850 "ddl_execution_status": "",
851 "ddl_ordinal": 10,
852 "warehouse": "cpdw_sbx_engineering_wh"
853 },
854 {
855 "create_access_ddl": "GRANT modify on warehouse cpdw_sbx_engineering_wh to ROLE cpdw_sbx_engineering_wh_wfull;",
856 "ddl_executed": 0,
857 "ddl_execution_message": "",
858 "ddl_execution_status": "",
859 "ddl_ordinal": 11,
860 "warehouse": "cpdw_sbx_engineering_wh"
861 },
862 {
863 "create_access_ddl": "USE ROLE cpdw_sbx_roleadmin;",
864 "ddl_executed": 0,
865 "ddl_execution_message": "",
866 "ddl_execution_status": "",
867 "ddl_ordinal": 1,
868 "warehouse": "cpdw_sbx_ingestion_wh"
869 },
870 {
871 "create_access_ddl": "CREATE or REPLACE ROLE cpdw_sbx_ingestion_wh_wu;",
872 "ddl_executed": 0,
873 "ddl_execution_message": "",
874 "ddl_execution_status": "",
875 "ddl_ordinal": 2,
876 "warehouse": "cpdw_sbx_ingestion_wh"
877 },
878 {
879 "create_access_ddl": "CREATE or REPLACE ROLE cpdw_sbx_ingestion_wh_wuo;",
880 "ddl_executed": 0,
881 "ddl_execution_message": "",
882 "ddl_execution_status": "",
883 "ddl_ordinal": 3,
884 "warehouse": "cpdw_sbx_ingestion_wh"
885 },
886 {
887 "create_access_ddl": "CREATE or REPLACE ROLE cpdw_sbx_ingestion_wh_wfull;",
888 "ddl_executed": 0,
889 "ddl_execution_message": "",
890 "ddl_execution_status": "",
891 "ddl_ordinal": 4,
892 "warehouse": "cpdw_sbx_ingestion_wh"
893 },
894 {
895 "create_access_ddl": "GRANT ROLE cpdw_sbx_ingestion_wh_wu TO ROLE cpdw_sbx_ingestion_wh_wuo;",
896 "ddl_executed": 0,
897 "ddl_execution_message": "",
898 "ddl_execution_status": "",
899 "ddl_ordinal": 5,
900 "warehouse": "cpdw_sbx_ingestion_wh"
901 },
902 {
903 "create_access_ddl": "GRANT ROLE cpdw_sbx_ingestion_wh_wuo TO ROLE cpdw_sbx_ingestion_wh_wfull;",
904 "ddl_executed": 0,
905 "ddl_execution_message": "",
906 "ddl_execution_status": "",
907 "ddl_ordinal": 6,
908 "warehouse": "cpdw_sbx_ingestion_wh"
909 },
910 {
911 "create_access_ddl": "GRANT ROLE cpdw_sbx_ingestion_wh_wfull TO ROLE cpdw_sbx_whadmin;",
912 "ddl_executed": 0,
913 "ddl_execution_message": "",
914 "ddl_execution_status": "",
915 "ddl_ordinal": 7,
916 "warehouse": "cpdw_sbx_ingestion_wh"
917 },
918 {
919 "create_access_ddl": "USE ROLE cpdw_sbx_whadmin;",
920 "ddl_executed": 0,
921 "ddl_execution_message": "",
922 "ddl_execution_status": "",
923 "ddl_ordinal": 8,
924 "warehouse": "cpdw_sbx_ingestion_wh"
925 },
926 {
927 "create_access_ddl": "GRANT usage on warehouse cpdw_sbx_ingestion_wh to ROLE cpdw_sbx_ingestion_wh_wu;",
928 "ddl_executed": 0,
929 "ddl_execution_message": "",
930 "ddl_execution_status": "",
931 "ddl_ordinal": 9,
932 "warehouse": "cpdw_sbx_ingestion_wh"
933 },
934 {
935 "create_access_ddl": "GRANT operate, monitor on warehouse cpdw_sbx_ingestion_wh to ROLE cpdw_sbx_ingestion_wh_wuo;",
936 "ddl_executed": 0,
937 "ddl_execution_message": "",
938 "ddl_execution_status": "",
939 "ddl_ordinal": 10,
940 "warehouse": "cpdw_sbx_ingestion_wh"
941 },
942 {
943 "create_access_ddl": "GRANT modify on warehouse cpdw_sbx_ingestion_wh to ROLE cpdw_sbx_ingestion_wh_wfull;",
944 "ddl_executed": 0,
945 "ddl_execution_message": "",
946 "ddl_execution_status": "",
947 "ddl_ordinal": 11,
948 "warehouse": "cpdw_sbx_ingestion_wh"
949 },
950 {
951 "create_access_ddl": "USE ROLE cpdw_sbx_roleadmin;",
952 "ddl_executed": 0,
953 "ddl_execution_message": "",
954 "ddl_execution_status": "",
955 "ddl_ordinal": 1,
956 "warehouse": "cpdw_sbx_bi_wh"
957 },
958 {
959 "create_access_ddl": "CREATE or REPLACE ROLE cpdw_sbx_bi_wh_wu;",
960 "ddl_executed": 0,
961 "ddl_execution_message": "",
962 "ddl_execution_status": "",
963 "ddl_ordinal": 2,
964 "warehouse": "cpdw_sbx_bi_wh"
965 },
966 {
967 "create_access_ddl": "CREATE or REPLACE ROLE cpdw_sbx_bi_wh_wuo;",
968 "ddl_executed": 0,
969 "ddl_execution_message": "",
970 "ddl_execution_status": "",
971 "ddl_ordinal": 3,
972 "warehouse": "cpdw_sbx_bi_wh"
973 },
974 {
975 "create_access_ddl": "CREATE or REPLACE ROLE cpdw_sbx_bi_wh_wfull;",
976 "ddl_executed": 0,
977 "ddl_execution_message": "",
978 "ddl_execution_status": "",
979 "ddl_ordinal": 4,
980 "warehouse": "cpdw_sbx_bi_wh"
981 },
982 {
983 "create_access_ddl": "GRANT ROLE cpdw_sbx_bi_wh_wu TO ROLE cpdw_sbx_bi_wh_wuo;",
984 "ddl_executed": 0,
985 "ddl_execution_message": "",
986 "ddl_execution_status": "",
987 "ddl_ordinal": 5,
988 "warehouse": "cpdw_sbx_bi_wh"
989 },
990 {
991 "create_access_ddl": "GRANT ROLE cpdw_sbx_bi_wh_wuo TO ROLE cpdw_sbx_bi_wh_wfull;",
992 "ddl_executed": 0,
993 "ddl_execution_message": "",
994 "ddl_execution_status": "",
995 "ddl_ordinal": 6,
996 "warehouse": "cpdw_sbx_bi_wh"
997 },
998 {
999 "create_access_ddl": "GRANT ROLE cpdw_sbx_bi_wh_wfull TO ROLE cpdw_sbx_whadmin;",
1000 "ddl_executed": 0,
1001 "ddl_execution_message": "",
1002 "ddl_execution_status": "",
1003 "ddl_ordinal": 7,
1004 "warehouse": "cpdw_sbx_bi_wh"
1005 },
1006 {
1007 "create_access_ddl": "USE ROLE cpdw_sbx_whadmin;",
1008 "ddl_executed": 0,
1009 "ddl_execution_message": "",
1010 "ddl_execution_status": "",
1011 "ddl_ordinal": 8,
1012 "warehouse": "cpdw_sbx_bi_wh"
1013 },
1014 {
1015 "create_access_ddl": "GRANT usage on warehouse cpdw_sbx_bi_wh to ROLE cpdw_sbx_bi_wh_wu;",
1016 "ddl_executed": 0,
1017 "ddl_execution_message": "",
1018 "ddl_execution_status": "",
1019 "ddl_ordinal": 9,
1020 "warehouse": "cpdw_sbx_bi_wh"
1021 },
1022 {
1023 "create_access_ddl": "GRANT operate, monitor on warehouse cpdw_sbx_bi_wh to ROLE cpdw_sbx_bi_wh_wuo;",
1024 "ddl_executed": 0,
1025 "ddl_execution_message": "",
1026 "ddl_execution_status": "",
1027 "ddl_ordinal": 10,
1028 "warehouse": "cpdw_sbx_bi_wh"
1029 },
1030 {
1031 "create_access_ddl": "GRANT modify on warehouse cpdw_sbx_bi_wh to ROLE cpdw_sbx_bi_wh_wfull;",
1032 "ddl_executed": 0,
1033 "ddl_execution_message": "",
1034 "ddl_execution_status": "",
1035 "ddl_ordinal": 11,
1036 "warehouse": "cpdw_sbx_bi_wh"
1037 }
1038 ]
1039}