============================
General
============================
Scenario:
There are 2 schemas. CRM and Reports.
Automatically execute Grants to Reports schema from bash script
The Reports schema optionally defined in sql parameters file.
If it is not defined, use a dynamic default name.
============================
Code
============================
sync_reports_permisions.sh
sync_reports_permisions.sqlsync_reports_permisions.sh
#!/bin/bash
REPORTS_USER=`grep REPORTS_USER ../set_vipuser.sql |awk -F= '{print $2}'`
if [[ -z $REPORTS_USER ]]; then
REPORTS_USER=XXX
fi
sqlplus -S /nolog @./post_upgrade/sync_reports_permisions.sql ${REPORTS_USER}
@../set_vipuser.sql
CONNECT &&vipuser/&&vippass@&&connectstr
spool V710_upgrade.log append
SET FEEDBACK OFF
SET SERVEROUTPUT ON
SET VERIFY OFF
PROMPT
PROMPT =================================================
PROMPT Starting Optional Step of Grant Permissions to Reports Schema
DECLARE
v_sql VARCHAR2(4000);
BEGIN
v_sql := 'DROP TABLE TEMP_GRANTS_LIST';
EXECUTE IMMEDIATE v_sql;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
DECLARE
v_sql VARCHAR2(4000);
BEGIN
v_sql := 'CREATE TABLE TEMP_GRANTS_LIST(table_name VARCHAR2(30)) TABLESPACE IGT_TABLE';
EXECUTE IMMEDIATE v_sql;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
DECLARE
v_sql VARCHAR2(2000);
v_input_rep_user VARCHAR2(30);
v_rep_user VARCHAR2(30);
v_counter NUMBER(2);
BEGIN
BEGIN
SELECT '&&1' INTO v_input_rep_user FROM DUAL;
IF v_input_rep_user = 'XXX' THEN
SELECT USER||'_REPORTS' INTO v_rep_user FROM DUAL;
ELSE
v_rep_user := v_input_rep_user;
END IF;
IF v_input_rep_user = 'XXX' THEN
DBMS_OUTPUT.put_line('REPORTS_USER Parameter was NOT provided in set_vipuser.sql');
DBMS_OUTPUT.put_line('Working with Defaul User: '||v_rep_user);
ELSE
DBMS_OUTPUT.put_line('REPORTS_USER was provided in set_vipuser.sql: '||v_rep_user);
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
BEGIN
SELECT COUNT (*) INTO v_counter FROM ALL_USERS WHERE username = v_rep_user;
IF v_counter = 0 THEN
DBMS_OUTPUT.put_line('REPORTS User '||v_rep_user||' Does Not Exists');
DBMS_OUTPUT.put_line('Optional Step Of Grant Permissions to Reports Schema is Terminated');
RETURN;
END IF;
END;
DBMS_OUTPUT.put_line('Granting Permissions to REPORTS Schema '||v_rep_user);
BEGIN
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('IPN_NW_TYPES');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('IPN_IBR_CELL_REPORT_DATA');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('IPN_IBR_CELL_REPORT_DATA');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('DEBUG_GEN_W_SDR');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('DEBUG_GEN_W_SDR');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('IPN_PREFERRED_MODE_TYPES');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('DB_INSTALLED_VERSIONS');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('GSM_COUNTRIES');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('GSM_NETWORKS');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('GSM_IMSI_PREFIXES');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('SGA_OUT_COMM');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('PRIVATE_COMM');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('GA_ACCOUNTS');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('OVMD_SERVICE_PROPERTY_VALUES');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('IPN_SERVICE_PROPERTIES_VALUES');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('OVMD_PROFILES');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('GA_PRODUCT_TREE_CONF');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('GA_PROPERTY_VALUES_CONF');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('IPN_REJECTION_TYPES');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('IRM_OP_CODE_TYPES');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('PROV_PROPERTIES');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('PSMS_CAMPAIGNS');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('PSMS_CAMPAIGN_CATEGORIES');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('PSMS_CAMPAIGN_CATEGORY_TYPES');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('IPN_DEVICE_CONFIGURATION');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('GA_W_COUNTERS_HISTORY');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('IPN_PROFILES');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('IPN_DISTRIBUTION_PROFILES');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('IPN_ROAMING_DISTRIBUTION');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('IPN_NETWORK_PROFILES');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('RAF_ACCUM_RULES');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('RAF_RULE_AXIS');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('GSM_MERGE_NETWORKS');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('GSM_SERVICE_MERGED_NETWORKS');
INSERT INTO TEMP_GRANTS_LIST(table_name) VALUES ('GSM_MERGE_NW_MAPPING');
commit;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
FOR c_table_cur IN (SELECT table_name FROM TEMP_GRANTS_LIST ORDER BY table_name) LOOP
v_sql := 'GRANT SELECT ON '||c_table_cur.table_name||' TO '||v_rep_user;
EXECUTE IMMEDIATE v_sql;
IF c_table_cur.table_name IN ('IPN_IBR_CELL_REPORT_DATA', 'DEBUG_GEN_W_SDR') THEN
v_sql := 'GRANT DELETE ON '||c_table_cur.table_name||' TO '||v_rep_user;
EXECUTE IMMEDIATE v_sql;
END IF;
END LOOP;
BEGIN
v_sql := 'DROP TABLE TEMP_GRANTS_LIST';
EXECUTE IMMEDIATE v_sql;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/
PROMPT Grant Permissions Step Finished Successfully
PROMPT ======================================
PROMPT
spool off
exit
awk example
input:
. . exported "LAB_QANFV_ALLQQ"."VSSP" 10.03 KB 6 rows
output:
SELECT * FROM GG_REF_TABLES WHERE table_name ='VSSP';
Command examples
Parse to SELECT
less exp_gg_LAB_QANFV_ALLQQ.log | grep expo | awk '{print $4}' | sort -u | sed s/\"//g | sed s/\\.//g| sed s/LAB_QANFV_ALLQQ/"SELECT * FROM GG_REF_TABLES WHERE table_name =\\'"/ | awk '{print $0 "'\''"}' | awk -F: '{print $1}' | sort -u | awk '{print $0";"}' > ~iu/check_tables.sql
Parse to INSERT
less exp_gg_LAB_QANFV_ALLQQ.log | grep expo | awk '{print $4}' | sort -u | sed s/\"//g | sed s/\\.//g | sed s/LAB_QANFV_ALLQQ/"INSERT INTO GG_REF_EXPDP (table_name, is_replicated, expdp, impdp) VALUES (\\'"/ | awk '{print $0 "'\'',NULL,'\''Y'\'',NULL)"}' | awk -F: '{print $1}' | sort -u | awk '{print $0";"}'
