-- Usage @instgrants.sql 
--        
-- Description: This script grants access priviliges to the ODMRUSER role or to PUBLIC.
-- Priviliges contained in this script are broken into two groups
--  ODMRSYS object priviliges 
--  Other system or object priviliges.
--
--
-- Example:
--  @instgrants.sql

WHENEVER SQLERROR EXIT SQL.SQLCODE;

EXECUTE dbms_output.put_line('Start granting access to ODMRUSER and PUBLIC. ' || systimestamp);

-- grant rights on ODMRSYS OBJECT TYPEs
GRANT EXECUTE ON ODMR_TRANSFORM_SETTING TO PUBLIC;

GRANT EXECUTE ON ODMR_TRANSFORM_SETTINGS TO PUBLIC;

GRANT EXECUTE ON ODMR_OBJECT_IDS TO PUBLIC;

GRANT EXECUTE ON ODMR_OBJECT_NAMES TO PUBLIC;

GRANT EXECUTE ON ODMR_OBJECT_VALUES TO PUBLIC;


GRANT EXECUTE ON ODMR_NESTED_VARCHAR2 TO PUBLIC;

GRANT EXECUTE ON ODMR_NESTED_NVARCHAR2 TO PUBLIC;

GRANT EXECUTE ON ODMR_NESTED_VARCHAR2_EX TO PUBLIC;

GRANT EXECUTE ON ODMR_NESTED_NVARCHAR2_EX TO PUBLIC;

GRANT EXECUTE ON ODMR_NESTED_CHAR TO PUBLIC;

GRANT EXECUTE ON ODMR_NESTED_NCHAR TO PUBLIC;

GRANT EXECUTE ON ODMR_HISTOGRAM_POINT TO PUBLIC;

GRANT EXECUTE ON ODMR_HISTOGRAMS TO PUBLIC;

GRANT EXECUTE ON ODMR_HISTOGRAM_POINT_EX TO PUBLIC;

GRANT EXECUTE ON ODMR_HISTOGRAMS_EX TO PUBLIC;

GRANT EXECUTE ON ODMR_PREDICTION_CAT TO PUBLIC;

GRANT EXECUTE ON ODMR_PREDICTION_SET_CAT TO PUBLIC;

GRANT EXECUTE ON ODMR_PREDICTION_CAT_COST TO PUBLIC;

GRANT EXECUTE ON ODMR_PREDICTION_SET_CAT_COST TO PUBLIC;

GRANT EXECUTE ON ODMR_PREDICTION_NUM TO PUBLIC;

GRANT EXECUTE ON ODMR_PREDICTION_SET_NUM TO PUBLIC;

GRANT EXECUTE ON ODMR_PREDICTION_NUM_COST TO PUBLIC;

GRANT EXECUTE ON ODMR_PREDICTION_SET_NUM_COST TO PUBLIC;

GRANT EXECUTE ON ODMR_FEATURE_CAT TO PUBLIC;

GRANT EXECUTE ON ODMR_FEATURE_SET_CAT TO PUBLIC;

GRANT EXECUTE ON ODMR_FEATURE_NUM TO PUBLIC;

GRANT EXECUTE ON ODMR_FEATURE_SET_NUM TO PUBLIC;

GRANT EXECUTE ON ODMR_CLUSTER_CAT TO PUBLIC;

GRANT EXECUTE ON ODMR_CLUSTER_SET_CAT TO PUBLIC;

GRANT EXECUTE ON ODMR_CLUSTER_NUM TO PUBLIC;

GRANT EXECUTE ON ODMR_CLUSTER_SET_NUM TO PUBLIC;

GRANT EXECUTE ON ODMR_PREDICTION_NUM_COSTF TO PUBLIC;

GRANT EXECUTE ON ODMR_PREDICTION_NUM_COSTD TO PUBLIC;

GRANT EXECUTE ON ODMR_PREDICTION_NUM_COSTN TO PUBLIC;

GRANT EXECUTE ON ODMR_PREDICTION_NUM_F TO PUBLIC;

GRANT EXECUTE ON ODMR_PREDICTION_NUM_D TO PUBLIC;

GRANT EXECUTE ON ODMR_PREDICTION_NUM_N TO PUBLIC;

GRANT EXECUTE ON ODMR_PREDICTION_SET_NUM_COSTF TO PUBLIC;

GRANT EXECUTE ON ODMR_PREDICTION_SET_NUM_COSTD TO PUBLIC;

GRANT EXECUTE ON ODMR_PREDICTION_SET_NUM_COSTN TO PUBLIC;

GRANT EXECUTE ON ODMR_PREDICTION_SET_NUM_F TO PUBLIC;

GRANT EXECUTE ON ODMR_PREDICTION_SET_NUM_D TO PUBLIC;

GRANT EXECUTE ON ODMR_PREDICTION_SET_NUM_N TO PUBLIC;

GRANT EXECUTE ON ODMR_PREDICTION_CAT_COSTPD TO PUBLIC;

GRANT EXECUTE ON ODMR_PREDICTION_CATPD TO PUBLIC;

GRANT EXECUTE ON ODMR_PREDICTION_SET_CAT_COSTPD TO PUBLIC;

GRANT EXECUTE ON ODMR_PREDICTION_SET_CATPD TO PUBLIC;

GRANT EXECUTE ON ODMR_FEATURE_NUMVD TO PUBLIC;

GRANT EXECUTE ON ODMR_FEATURE_SET_NUMVD TO PUBLIC;

GRANT EXECUTE ON ODMR_FEATURE_CATVD TO PUBLIC;

GRANT EXECUTE ON ODMR_FEATURE_SET_CATVD TO PUBLIC;

GRANT EXECUTE ON ODMR_CLUSTER_NUMPD TO PUBLIC;

GRANT EXECUTE ON ODMR_CLUSTER_SET_NUMPD TO PUBLIC;

GRANT EXECUTE ON ODMR_CLUSTER_CATPD TO PUBLIC;

GRANT EXECUTE ON ODMR_CLUSTER_SET_CATPD TO PUBLIC;

GRANT EXECUTE ON ODMR_NODE_REFERENCE TO PUBLIC;

GRANT EXECUTE ON ODMR_NODE_REFERENCE_SET TO PUBLIC;

GRANT EXECUTE ON ODMR_QUALIFIED_OBJECT_NAME TO PUBLIC;

GRANT EXECUTE ON ODMR_QUALIFIED_OBJECT_NAMES TO PUBLIC;

GRANT EXECUTE ON ODMR_HISTOGRAM_POINT2 TO PUBLIC;

GRANT EXECUTE ON ODMR_HISTOGRAMS2 TO PUBLIC;

GRANT EXECUTE ON ODMR_HISTOGRAM_POINT_EX2 TO PUBLIC;

GRANT EXECUTE ON ODMR_HISTOGRAMS_EX2 TO PUBLIC;

GRANT SELECT ON ODMR_WORKFLOW_OBJECT_NAME_SEQ TO PUBLIC;

DECLARE
  DB_VER  VARCHAR2(30);
BEGIN
  SELECT VERSION INTO DB_VER FROM PRODUCT_COMPONENT_VERSION WHERE PRODUCT LIKE 'Oracle Database%' OR PRODUCT LIKE 'Personal Oracle Database %' ;
  IF (DB_VER >= '12.1.0.2' ) THEN
    -- grant rights on VIEWs
    EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_DEFAULT_STOPLISTS TO ODMRUSER';
    EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_PROJECT_WORKFLOW TO ODMRUSER';
    EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_WORKFLOW_JOBS TO ODMRUSER';
    EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_WORKFLOW_LOG TO ODMRUSER';
    EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_RUNNING_WORKFLOW TO ODMRUSER';
    EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_PROJECT_WORKFLOW2 TO ODMRUSER';
    -- Only grant to PUBLIC, allows ODMR to check for installation of repository
    -- from an account that does not have ODMRUSER role.
    EXECUTE IMMEDIATE 'GRANT READ ON ODMR_REPOSITORY_PROPERTIES TO PUBLIC';
    EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_WORKFLOW_RUNNING TO ODMRUSER';
    EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_WORKFLOW_COMPLETE TO ODMRUSER';
    EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_WORKFLOW_ALL TO ODMRUSER';
    EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_WORKFLOW_ALL_POLL TO ODMRUSER';
    EXECUTE IMMEDIATE 'GRANT READ ON ODMR_MESSAGES TO ODMRUSER';
    EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_WORKFLOW_NODES TO ODMRUSER';
    EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_WORKFLOW_MODELS TO ODMRUSER';
    EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_WF_CLAS_TEST_RESULTS TO ODMRUSER';
    EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_WF_REGR_TEST_RESULTS TO ODMRUSER';    
    EXECUTE IMMEDIATE 'GRANT READ ON ODMR_USER_WF_TEST_RESULTS TO ODMRUSER';
  ELSE
    -- grant rights on VIEWs
    EXECUTE IMMEDIATE 'GRANT SELECT ON ODMR_USER_DEFAULT_STOPLISTS TO ODMRUSER';
    EXECUTE IMMEDIATE 'GRANT SELECT ON ODMR_USER_PROJECT_WORKFLOW TO ODMRUSER';
    EXECUTE IMMEDIATE 'GRANT SELECT ON ODMR_USER_WORKFLOW_JOBS TO ODMRUSER';
    EXECUTE IMMEDIATE 'GRANT SELECT ON ODMR_USER_WORKFLOW_LOG TO ODMRUSER';
    EXECUTE IMMEDIATE 'GRANT SELECT ON ODMR_USER_RUNNING_WORKFLOW TO ODMRUSER';
    EXECUTE IMMEDIATE 'GRANT SELECT ON ODMR_USER_PROJECT_WORKFLOW2 TO ODMRUSER';
    -- Only grant to PUBLIC, allows ODMR to check for installation of repository
    -- from an account that does not have ODMRUSER role.
    EXECUTE IMMEDIATE 'GRANT SELECT ON ODMR_REPOSITORY_PROPERTIES TO PUBLIC';
    EXECUTE IMMEDIATE 'GRANT SELECT ON ODMR_USER_WORKFLOW_RUNNING TO ODMRUSER';
    EXECUTE IMMEDIATE 'GRANT SELECT ON ODMR_USER_WORKFLOW_COMPLETE TO ODMRUSER';
    EXECUTE IMMEDIATE 'GRANT SELECT ON ODMR_USER_WORKFLOW_ALL TO ODMRUSER';
    EXECUTE IMMEDIATE 'GRANT SELECT ON ODMR_USER_WORKFLOW_ALL_POLL TO ODMRUSER';    
    EXECUTE IMMEDIATE 'GRANT SELECT ON ODMR_MESSAGES TO ODMRUSER';
    IF (DB_VER >= '11.2.0.4' ) THEN    
      EXECUTE IMMEDIATE 'GRANT SELECT ON ODMR_USER_WORKFLOW_NODES TO ODMRUSER';
      EXECUTE IMMEDIATE 'GRANT SELECT ON ODMR_USER_WORKFLOW_MODELS TO ODMRUSER';
      EXECUTE IMMEDIATE 'GRANT SELECT ON ODMR_USER_WF_CLAS_TEST_RESULTS TO ODMRUSER';
      EXECUTE IMMEDIATE 'GRANT SELECT ON ODMR_USER_WF_REGR_TEST_RESULTS TO ODMRUSER';    
      EXECUTE IMMEDIATE 'GRANT SELECT ON ODMR_USER_WF_TEST_RESULTS TO ODMRUSER';
    END IF;
  END IF;
EXCEPTION WHEN OTHERS THEN
  RAISE;
END;
/
-- grant rights on Packages
GRANT EXECUTE ON ODMR_CONSTANT TO ODMRUSER;

GRANT EXECUTE ON ODMR_PROJECT TO ODMRUSER;

GRANT EXECUTE ON ODMR_WORKFLOW TO ODMRUSER;

GRANT EXECUTE ON ODMR_UTIL TO ODMRUSER;

GRANT EXECUTE ON ODMR_ENGINE TO ODMRUSER;

GRANT EXECUTE ON ODMR_ENGINE_DATA TO ODMRUSER;

GRANT EXECUTE ON ODMR_ENGINE_MINING TO ODMRUSER;

GRANT EXECUTE ON ODMR_ENGINE_OUTPUT TO ODMRUSER;

GRANT EXECUTE ON ODMR_ENGINE_TRANSFORMS TO ODMRUSER;

GRANT EXECUTE ON ODMR_ENGINE_TEXT TO ODMRUSER;

GRANT EXECUTE ON ODMR_ENGINE_DYNAMIC_NODE TO ODMRUSER;

EXECUTE dbms_output.put_line('Finshed granting access to ODMRUSER and PUBLIC. ' || systimestamp);

