Skip to main content

Home

Configuring access for an Oracle database

Due to restrictions for accessing an Oracle database you need to create synonyms and select access for the Unravel user. Complete the following steps to allow access for a read-only user.

Notice

Substitute your hive database owner ID for HIVEUSER and the user id that unravel is using for unravelhive database.

As the Oracle DBA

  1. Run the following queries to output SQL scripts to grant read access to hive tables and create synonyms.

    1. Select 'grant select on '||table_name||' to unravelhive;' from SYS.ALL_TABLES where OWNER = HIVEUSER
    2. Select 'create synonym '||table_name||' for '||HIVEUSER||'.'||table_name||';' from SYS.ALL_TABLES where OWNER = HIVEUSER

  2. Create a database account for Unravel to use called unravelhive.

    grant connect to unravelhive;
grant resource to unravelhive;
grant create synonym to unravelhive;
Open a SQL*Plus connection then:
As the Hive database user

  1. Set the SQL*Plus formatting settings as shown:

    set pagesize 200;
set linesize 200;

column table_name format a30;
column owner format a30;

  2. As Hive database user (HIVEUSER), run Query 1 output; it should look similar to this example.

    grant select on TXNS to unravelhive;
grant select on TXN_COMPONENTS to unravelhive;
grant select on COMPLETED_TXN_COMPONENTS to unravelhive;
grant select on NEXT_TXN_ID to  unravelhive;

grant select on HIVE_LOCKS to unravelhive;
grant select on NEXT_LOCK_ID to unravelhive;
grant select on COMPACTION_QUEUE to unravelhive;
grant select on NEXT_COMPACTION_QUEUE_ID to unravelhive;
grant select on METASTORE_DB_PROPERTIES to unravelhive;
grant select on SEQUENCE_TABLE to unravelhive;
grant select on NUCLEUS_TABLES to unravelhive;
grant select on PART_COL_PRIVS to unravelhive;
grant select on CDS to unravelhive;
grant select on COLUMNS_V2 to unravelhive;
grant select on PARTITION_KEY_VALS to unravelhive;
grant select on DBS to unravelhive;
grant select on PARTITION_PARAMS to unravelhive;
grant select on SERDES to unravelhive;
grant select on TYPES to unravelhive;
grant select on PARTITION_KEYS to unravelhive;
grant select on ROLES to unravelhive;
grant select on PARTITIONS to unravelhive;
grant select on INDEX_PARAMS to unravelhive;
grant select on TBL_COL_;S to unravelhive;
grant select on IDXS to unravelhive;
grant select on BUCKETING_COLS to unravelhive;
grant select on TYPE_FIELDS to unravelhive;
grant select on SD_PARAMS to unravelhive;
grant select on GLOBAL_PRIVS to unravelhive;
grant select on SDS to unravelhive;
grant select on TABLE_PARAMS to unravelhive;
grant select on SORT_COLS to unravelhive;
grant select on TBL_PRIVS to unravelhive;
grant select on DATABASE_PARAMS to unravelhive;
grant select on ROLE_MAP to unravelhive;
grant select on SERDE_PARAMS to unravelhive;
grant select on PART_PRIVS to unravelhive;
grant select on DB_PRIVS to unravelhive;
grant select on TBLS to unravelhive;
grant select on PARTITION_EVENTS to unravelhive;
grant select on SKEWED_STRING_LIST to unravelhive;
grant select on SKEWED_STRING_LIST_VALUES to unravelhive;
grant select on SKEWED_COL_NAMES to unravelhive;
grant select on SKEWED_COL_VALUE_LOC_MAP to unravelhive;
grant select on MASTER_KEYS to unravelhive;
grant select on DELEGATION_TOKENS to unravelhive;
grant select on SKEWED_VALUES to unravelhive;
grant select on TAB_COL_STATS to unravelhive;
grant select on VERSION to unravelhive;
grant select on PART_COL_STATS to unravelhive;
grant select on FUNCS to unravelhive;
grant select on FUNC_RU to unravelhive;
grant select on NOTIFICATION_LOG to unravelhive;
grant select on NOTIFICATION_SEQUENCE to unravelhive;
As Unravel user (UNRAVELHIVE)

  • Run Query 2 output ; it should look similar to this example.

    create synonym TXNS for HIVEUSER.TXNS;
create synonym TXN_COMPONENTS for HIVEUSER.TXN_COMPONENTS;
create synonym COMPLETED_TXN_COMPONENTS for HIVEUSER.COMPLETED_TXN_COMPONENTS;
create synonym NEXT_TXN_ID for HIVEUSER.NEXT_TXN_ID;
create synonym HIVE_LOCKS for HIVEUSER.HIVE_LOCKS;
create synonym NEXT_LOCK_ID for HIVEUSER.NEXT_LOCK_ID;
create synonym COMPACTION_QUEUE for HIVEUSER.COMPACTION_QUEUE;
create synonym NEXT_COMPACTION_QUEUE_ID for HIVEUSER.NEXT_COMPACTION_QUEUE_ID;
create synonym METASTORE_DB_PROPERTIES for HIVEUSER.METASTORE_DB_PROPERTIES;
create synonym SEQUENCE_TABLE for HIVEUSER.SEQUENCE_TABLE;
create synonym NUCLEUS_TABLES for HIVEUSER.NUCLEUS_TABLES;
create synonym PART_COL_PRIVS for HIVEUSER.PART_COL_PRIVS;
create synonym CDS for HIVEUSER.CDS;
create synonym COLUMNS_V2 for HIVEUSER.COLUMNS_V2;
create synonym PARTITION_KEY_VALS for HIVEUSER.PARTITION_KEY_VALS;
create synonym DBS for HIVEUSER.DBS;
create synonym PARTITION_PARAMS for HIVEUSER.PARTITION_PARAMS;
create synonym SERDES for HIVEUSER.SERDES;
create synonym TYPES for HIVEUSER.TYPES;create synonym PARTITION_KEYS for HIVEUSER.PARTITION_KEYS;
create synonym ROLES for HIVEUSER.ROLES;
create synonym PARTITIONS for HIVEUSER.PARTITIONS;
create synonym INDEX_PARAMS for HIVEUSER.INDEX_PARAMS;
create synonym TBL_COL_PRIVS for HIVEUSER.TBL_COL_PRIVS;
create synonym IDXS for HIVEUSER.IDXS;
create synonym BUCKETING_COLS for HIVEUSER.BUCKETING_COLS;
create synonym TYPE_FIELDS for HIVEUSER.TYPE_FIELDS;
create synonym SD_PARAMS for HIVEUSER.SD_PARAMS;
create synonym GLOBAL_PRIVS for HIVEUSER.GLOBAL_PRIVS;
create synonym SDS for HIVEUSER.SDS;
create synonym TABLE_PARAMS for HIVEUSER.TABLE_PARAMS;
create synonym SORT_COLS for HIVEUSER.SORT_COLS;
create synonym TBL_PRIVS for HIVEUSER.TBL_PRIVS;
create synonym DATABASE_PARAMS for HIVEUSER.DATABASE_PARAMS;
create synonym ROLE_MAP for HIVEUSER.ROLE_MAP;
create synonym SERDE_PARAMS for HIVEUSER.SERDE_PARAMS;
create synonym PART_PRIVS for HIVEUSER.PART_PRIVS;
create synonym DB_PRIVS for HIVEUSER.DB_PRIVS;
create synonym TBLS for HIVEUSER.TBLS;
create synonym PARTITION_EVENTS for HIVEUSER.PARTITION_EVENTS;
create synonym SKEWED_STRING_LIST for HIVEUSER.SKEWED_STRING_LIST;
create synonym SKEWED_STRING_LIST_VALUES for HIVEUSER.SKEWED_STRING_LIST_VALUES;
create synonym SKEWED_COL_NAMES for HIVEUSER.SKEWED_COL_NAMES;
create synonym SKEWED_COL_VALUE_LOC_MAP for HIVEUSER.SKEWED_COL_VALUE_LOC_MAP;
create synonym MASTER_KEYS for HIVEUSER.MASTER_KEYS;
create synonym DELEGATION_TOKENS for HIVEUSER.DELEGATION_TOKENS;
create synonym SKEWED_VALUES for HIVEUSER.SKEWED_VALUES;
create synonym TAB_COL_STATS for HIVEUSER.TAB_COL_STATS;
create synonym VERSION for HIVEUSER.VERSION;create synonym PART_COL_STATS for HIVEUSER.PART_COL_STATS;
create synonym FUNCS for HIVEUSER.FUNCS;
create synonym FUNC_RU for HIVEUSER.FUNC_RU;create synonym NOTIFICATION_LOG for HIVEUSER.NOTIFICATION_LOG;
create synonym NOTIFICATION_SEQUENCE for HIVEUSER.NOTIFICATION_SEQUENCE;
In this section: