- Home
- Unravel 4.7.3.x Documentation
- Configurations
- Hive Metastore
- Configuring access for an Oracle database
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
Run the following queries to output SQL scripts to grant read access to hive tables and create synonyms.
Select 'grant select on '||table_name||' to unravelhive;' from SYS.ALL_TABLES where OWNER =
HIVEUSER
Select 'create synonym '||table_name||' for '||
HIVEUSER
||'.'||table_name||';' from SYS.ALL_TABLES where OWNER =HIVEUSER
Create a database account for Unravel to use called
unravelhive
.grant connect to
unravelhive
; grant resource tounravelhive
; grant create synonym tounravelhive
;
Open a SQL*Plus connection then:
Set the SQL*Plus formatting settings as shown:
set pagesize 200; set linesize 200; column table_name format a30; column owner format a30;
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 tounravelhive
; grant select on COMPLETED_TXN_COMPONENTS tounravelhive
; grant select on NEXT_TXN_ID tounravelhive
; grant select on HIVE_LOCKS tounravelhive
; grant select on NEXT_LOCK_ID tounravelhive
; grant select on COMPACTION_QUEUE tounravelhive
; grant select on NEXT_COMPACTION_QUEUE_ID tounravelhive
; grant select on METASTORE_DB_PROPERTIES tounravelhive
; grant select on SEQUENCE_TABLE tounravelhive
; grant select on NUCLEUS_TABLES tounravelhive
; grant select on PART_COL_PRIVS tounravelhive
; grant select on CDS tounravelhive
; grant select on COLUMNS_V2 tounravelhive
; grant select on PARTITION_KEY_VALS tounravelhive
; grant select on DBS tounravelhive
; grant select on PARTITION_PARAMS tounravelhive
; grant select on SERDES tounravelhive
; grant select on TYPES tounravelhive
; grant select on PARTITION_KEYS tounravelhive
; grant select on ROLES tounravelhive
; grant select on PARTITIONS tounravelhive
; grant select on INDEX_PARAMS tounravelhive
; grant select on TBL_COL_;S tounravelhive
; grant select on IDXS tounravelhive
; grant select on BUCKETING_COLS tounravelhive
; grant select on TYPE_FIELDS tounravelhive
; grant select on SD_PARAMS tounravelhive
; grant select on GLOBAL_PRIVS tounravelhive
; grant select on SDS tounravelhive
; grant select on TABLE_PARAMS tounravelhive
; grant select on SORT_COLS tounravelhive
; grant select on TBL_PRIVS tounravelhive
; grant select on DATABASE_PARAMS tounravelhive
; grant select on ROLE_MAP tounravelhive
; grant select on SERDE_PARAMS tounravelhive
; grant select on PART_PRIVS tounravelhive
; grant select on DB_PRIVS tounravelhive
; grant select on TBLS tounravelhive
; grant select on PARTITION_EVENTS tounravelhive
; grant select on SKEWED_STRING_LIST tounravelhive
; grant select on SKEWED_STRING_LIST_VALUES tounravelhive
; grant select on SKEWED_COL_NAMES tounravelhive
; grant select on SKEWED_COL_VALUE_LOC_MAP tounravelhive
; grant select on MASTER_KEYS tounravelhive
; grant select on DELEGATION_TOKENS tounravelhive
; grant select on SKEWED_VALUES tounravelhive
; grant select on TAB_COL_STATS tounravelhive
; grant select on VERSION tounravelhive
; grant select on PART_COL_STATS tounravelhive
; grant select on FUNCS tounravelhive
; grant select on FUNC_RU tounravelhive
; grant select on NOTIFICATION_LOG tounravelhive
; grant select on NOTIFICATION_SEQUENCE tounravelhive
;
UNRAVELHIVE
)Run Query 2 output ; it should look similar to this example.
create synonym TXNS for
HIVEUSER
.TXNS; create synonym TXN_COMPONENTS forHIVEUSER
.TXN_COMPONENTS; create synonym COMPLETED_TXN_COMPONENTS forHIVEUSER
.COMPLETED_TXN_COMPONENTS; create synonym NEXT_TXN_ID forHIVEUSER
.NEXT_TXN_ID; create synonym HIVE_LOCKS forHIVEUSER
.HIVE_LOCKS; create synonym NEXT_LOCK_ID forHIVEUSER
.NEXT_LOCK_ID; create synonym COMPACTION_QUEUE forHIVEUSER
.COMPACTION_QUEUE; create synonym NEXT_COMPACTION_QUEUE_ID forHIVEUSER
.NEXT_COMPACTION_QUEUE_ID; create synonym METASTORE_DB_PROPERTIES forHIVEUSER
.METASTORE_DB_PROPERTIES; create synonym SEQUENCE_TABLE forHIVEUSER
.SEQUENCE_TABLE; create synonym NUCLEUS_TABLES forHIVEUSER
.NUCLEUS_TABLES; create synonym PART_COL_PRIVS forHIVEUSER
.PART_COL_PRIVS; create synonym CDS forHIVEUSER
.CDS; create synonym COLUMNS_V2 forHIVEUSER
.COLUMNS_V2; create synonym PARTITION_KEY_VALS forHIVEUSER
.PARTITION_KEY_VALS; create synonym DBS forHIVEUSER
.DBS; create synonym PARTITION_PARAMS forHIVEUSER
.PARTITION_PARAMS; create synonym SERDES forHIVEUSER
.SERDES; create synonym TYPES forHIVEUSER
.TYPES;create synonym PARTITION_KEYS forHIVEUSER
.PARTITION_KEYS; create synonym ROLES forHIVEUSER
.ROLES; create synonym PARTITIONS forHIVEUSER
.PARTITIONS; create synonym INDEX_PARAMS forHIVEUSER
.INDEX_PARAMS; create synonym TBL_COL_PRIVS forHIVEUSER
.TBL_COL_PRIVS; create synonym IDXS forHIVEUSER
.IDXS; create synonym BUCKETING_COLS forHIVEUSER
.BUCKETING_COLS; create synonym TYPE_FIELDS forHIVEUSER
.TYPE_FIELDS; create synonym SD_PARAMS forHIVEUSER
.SD_PARAMS; create synonym GLOBAL_PRIVS forHIVEUSER
.GLOBAL_PRIVS; create synonym SDS forHIVEUSER
.SDS; create synonym TABLE_PARAMS forHIVEUSER
.TABLE_PARAMS; create synonym SORT_COLS forHIVEUSER
.SORT_COLS; create synonym TBL_PRIVS forHIVEUSER
.TBL_PRIVS; create synonym DATABASE_PARAMS forHIVEUSER
.DATABASE_PARAMS; create synonym ROLE_MAP forHIVEUSER
.ROLE_MAP; create synonym SERDE_PARAMS forHIVEUSER
.SERDE_PARAMS; create synonym PART_PRIVS forHIVEUSER
.PART_PRIVS; create synonym DB_PRIVS forHIVEUSER
.DB_PRIVS; create synonym TBLS forHIVEUSER
.TBLS; create synonym PARTITION_EVENTS forHIVEUSER
.PARTITION_EVENTS; create synonym SKEWED_STRING_LIST forHIVEUSER
.SKEWED_STRING_LIST; create synonym SKEWED_STRING_LIST_VALUES forHIVEUSER
.SKEWED_STRING_LIST_VALUES; create synonym SKEWED_COL_NAMES forHIVEUSER
.SKEWED_COL_NAMES; create synonym SKEWED_COL_VALUE_LOC_MAP forHIVEUSER
.SKEWED_COL_VALUE_LOC_MAP; create synonym MASTER_KEYS forHIVEUSER
.MASTER_KEYS; create synonym DELEGATION_TOKENS forHIVEUSER
.DELEGATION_TOKENS; create synonym SKEWED_VALUES forHIVEUSER
.SKEWED_VALUES; create synonym TAB_COL_STATS forHIVEUSER
.TAB_COL_STATS; create synonym VERSION forHIVEUSER
.VERSION;create synonym PART_COL_STATS forHIVEUSER
.PART_COL_STATS; create synonym FUNCS forHIVEUSER
.FUNCS; create synonym FUNC_RU forHIVEUSER
.FUNC_RU;create synonym NOTIFICATION_LOG forHIVEUSER
.NOTIFICATION_LOG; create synonym NOTIFICATION_SEQUENCE forHIVEUSER
.NOTIFICATION_SEQUENCE;
In this section: