-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathcreate-users.txt
142 lines (112 loc) · 5.6 KB
/
create-users.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
/*
##########################################################################
Copyright 2021 SAP SE or an SAP affiliate company
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
##########################################################################
*/
USEFUL LINKS:
-----------------
*** SSH Access: https://linuxhint.com/setup_ssh_without_passwords/
-----------------
-----------------
*** Info on mapping data types
*** DATA TYPES: https://kabilsapworld.blogspot.com/2018/02/oracle-datatype-to-hana-datatype-mapping.html
-----------------
*** Info on exporting blobs
*** BLOB DUMPING: https://community.oracle.com/thread/3551207
-----------------
-- ORACLE SETUP
-- Run this as SYS (or equivalent) on Oracle
-----------------
CREATE USER CSV_EXPORTER IDENTIFIED BY WELCOME01;
ALTER USER CSV_EXPORTER QUOTA UNLIMITED ON USERS;
-- Needed to login and create packages
GRANT CREATE SESSION TO CSV_EXPORTER;
GRANT CREATE PROCEDURE TO CSV_EXPORTER;
GRANT CREATE TABLE TO CSV_EXPORTER;
-- Needed to run jobs in the database and OS
GRANT CREATE JOB TO CSV_EXPORTER;
GRANT CREATE EXTERNAL JOB TO CSV_EXPORTER;
GRANT MANAGE SCHEDULER TO CSV_EXPORTER; -- To purge job history
-- Get access to the necessary packages..
GRANT EXECUTE ON DBMS_SCHEDULER TO CSV_EXPORTER;
GRANT EXECUTE ON SYS.UTL_FILE TO CSV_EXPORTER;
GRANT EXECUTE ON SYS.DBMS_SQL TO CSV_EXPORTER;
-- Working directory - this could be anything on
-- on the Oracle box.
CREATE OR REPLACE DIRECTORY LOADER_DIR AS '/u02/hhs';
GRANT READ,WRITE ON DIRECTORY LOADER_DIR TO CSV_EXPORTER;
-----------------------------
*** Run OS jobs from inside the database.
*** The following adjustment is required in the Oracle config to set the OS user
*** that Oracle uses to run jobs.
-----------------------------
EDIT THIS FILE: /u01/app/oracle/product/version/db_1/rdbms/admin/externaljob.ora
(your path may be different).
# $Header: externaljob.ora 16-dec-2005.20:47:13 rramkiss Exp $
#
# Copyright (c) 2005, Oracle. All rights reserved.
# NAME
# externaljob.ora
# FUNCTION
# This configuration file is used by dbms_scheduler when executing external
# (operating system) jobs. It contains the user and group to run external
# jobs as. It must only be writable by the owner and must be owned by root.
# If extjob is not setuid then the only allowable run_user
# is the user Oracle runs as and the only allowable run_group is the group
# Oracle runs as.
#
# NOTES
# For Porters: The user and group specified here should be a lowly privileged
# user and group for your platform. For Linux this is nobody
# and nobody.
# MODIFIED
# rramkiss 12/09/05 - Creation
#
##############################################################################
# External job execution configuration file externaljob.ora
#
# This file is provided by Oracle Corporation to help you customize
# your RDBMS installation for your site. Important system parameters
# are discussed, and default settings given.
#
# This configuration file is used by dbms_scheduler when executing external
# (operating system) jobs. It contains the user and group to run external
# jobs as. It must only be writable by the owner and must be owned by root.
# If extjob is not setuid then the only allowable run_user
# is the user Oracle runs as and the only allowable run_group is the group
# Oracle runs as.
run_user = oracle
run_group = oinstall
-----------------
-- SAP HANA SETUP
-----------------
/* HANA specific
*** First create the expected landing directory. This is specified when the
*** job is created and MUST exist at run time. NOTE: this could have been
*** added to the script - but was not required to finish the project.
*** The following creates the loader directory and the export sub-dir.
mkdir -p /usr/sap/HXE/HDB90/loader/export
*** Tell HANA where it can import CSV files from - this can be anywhere
*** the HANA processes (hdbadm) have privilege to read.
*/
hdbsql -i 90 -u system -p Welcome01 "ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'system') set ('import_export', 'enable_csv_import_path_filter') = 'false' with reconfigure"
hdbsql -i 90 -u system -p Welcome01 "ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'system') set ('import_export', 'csv_import_path_filter') = '/usr/sap/HXE/HDB90/loader' with reconfigure"
-- AS SYSTEM: Create the "technical user" we use to run our statements
-- NOTE: no csv-loader tools are placed in the target schema.
hdbsql HXE=> create user csvadm password Welcome01 NO FORCE_FIRST_PASSWORD_CHANGE;
0 rows affected (overall time 26.157 msec; server time 24.475 msec)
-- Create our target user/schema where load the Oracle data
hdbsql HXE=> create user target_schema password Welcome01 NO FORCE_FIRST_PASSWORD_CHANGE;
0 rows affected (overall time 26.439 msec; server time 24.754 msec)
--AS TARGET_SCHEMA: Let the technical user have access to the target schema - this could be more restricted.
hdbsql=> grant create any on schema target_schema to csvadm;
0 rows affected (overall time 11.636 msec; server time 9893 usec)