251784

251784 NW-7GCNY Amend user ID used by interfaces

Copyright OBS Logistics © 2009

The information contained herein is the property of OBS Logistics and is supplied without liability for errors or omissions. No part may be reproduced or used except as authorised by contract or other written permission. The copyright and foregoing restriction on reproduction and use extend to all media in which the information may be embodied

FUNCTIONAL OVERVIEW

Project Background

Currently interfaces are received via ESI to specific directory. There are a number of crontab settings which in turn call shell script which has the username MTS_OWNER specified and the database login, the password is held in another file to verify login. All entries loaded into the MTS system therefore carry the user MTS_OWNER.

There are currently 4 shell scripts which control the loading of data into the MTS system they are;

• /usr/bin/hgp/hht_upload which loads HHT files.

• /usr/bin/hgp/mddasn_upload which loads BKG and ASN files

• /usr/bin/hgp/vpd_upload which load the VENDOR details file.

• /usr/bin/hgp/pocpod_upload which loads the POC/POD ACK files.

Scan and Load (SAL) is run via an oracle DBMS_JOB, currently this job is owned by MTS_OWNER.

Client Requirement

DHL wish to differentiate between records that have been amended or created in this way or via fixes applied by OBS. A change is required so that the MTS_INTERFACE user ID is used to process, create and update records from the SAP interfaces and the MTS_HHT (to be created) user ID is used to process, create and update records from the HHT interfaces (POC / POC Actuals and Scan and Load)

Solution

The user MTS_INTERFACE, which currently exists, will need to be given the same system privileges and the current MTS_OWNER and a new user MTS_HHT will need to be created also granted the same privileges.

The user MTS_INTERFACE will be associated with the following shell scripts

• /usr/bin/hgp/mddasn_upload which loads BKG and ASN files

• /usr/bin/hgp/vpd_upload which load the VENDOR details file.

• /usr/bin/hgp/pocpod_upload which loads the POC/POD ACK files.

A DBMS_JOB will be created using the user, MTS_INTERFACE, this will handle the Scan and Load interface (SAL).

The user MTS_HHT will be associated with the following shell script

• /usr/bin/hgp/hht_upload which loads HHT files

Assumptions

Crontab setting will be suspended during movement to new users.

Scope

This development will be applied to system version 10.4.7 on SARLV and once approved SARPF.

SET-UP

Pre-requisites

Both database users (MTS_INTERFACE and MTSHHT) will be created, with relevant privileges, before any changes made to the operating system shell scripts or crontab settings.

Menu Structure

No change as all interface are run from crontab

FUNCTIONAL DESCRIPTION

Create database users

DROP USER "MTS_INTERFACE";
CREATE USER "MTS_INTERFACE"  PROFILE "DEFAULT" 
 IDENTIFIED BY "******" DEFAULT TABLESPACE "ORION_DATA_LARGE" 
 TEMPORARY TABLESPACE "TS_ORION_TEMP" 
 ACCOUNT UNLOCK;
GRANT ALTER SYSTEM TO "MTS_INTERFACE";
GRANT ANALYZE ANY TO "MTS_INTERFACE";
GRANT CREATE VIEW TO "MTS_INTERFACE";
GRANT SELECT ANY DICTIONARY TO "MTS_INTERFACE";
GRANT SELECT ANY TABLE TO "MTS_INTERFACE";
GRANT UNLIMITED TABLESPACE TO "MTS_INTERFACE";
GRANT EXECUTE ON  "SYS"."DBMS_ALERT" TO "MTS_INTERFACE";
GRANT EXECUTE ON  "SYS"."DBMS_PIPE" TO "MTS_INTERFACE";
GRANT READ ON DIRECTORY "SYS"."BACKUP" TO "MTS_INTERFACE" WITH GRANT OPTION;
GRANT READ ON DIRECTORY "SYS"."EXTERNAL_TABLES_DIR" TO "MTS_INTERFACE" WITH GRANT OPTION;
GRANT "ACCOUNT_CREATOR" TO "MTS_INTERFACE";
GRANT "DBA" TO "MTS_INTERFACE";
GRANT "JAVASYSPRIV" TO "MTS_INTERFACE";
GRANT "JAVAUSERPRIV" TO "MTS_INTERFACE";
GRANT "MTS_USER" TO "MTS_INTERFACE";
GRANT "MTS_USER_READ_ONLY" TO "MTS_INTERFACE" WITH ADMIN OPTION;
GRANT "PASSWORD_CHANGE" TO "MTS_INTERFACE"; 

DROP USER "MTS_HHT";
CREATE USER "MTS_HHT"  PROFILE "DEFAULT" 
   IDENTIFIED BY "******" DEFAULT TABLESPACE "ORION_DATA_LARGE" 
   TEMPORARY TABLESPACE "TS_ORION_TEMP" 
   ACCOUNT UNLOCK;
GRANT ALTER SYSTEM TO "MTS_HHT";
GRANT ANALYZE ANY TO "MTS_HHT";
GRANT CREATE VIEW TO "MTS_HHT";
GRANT SELECT ANY DICTIONARY TO "MTS_HHT";
GRANT SELECT ANY TABLE TO "MTS_HHT";
GRANT UNLIMITED TABLESPACE TO "MTS_HHT";
GRANT EXECUTE ON  "SYS"."DBMS_ALERT" TO "MTS_HHT";
GRANT EXECUTE ON  "SYS"."DBMS_PIPE" TO "MTS_HHT";
GRANT READ ON DIRECTORY "SYS"."BACKUP" TO "MTS_HHT" WITH GRANT OPTION;
GRANT READ ON DIRECTORY "SYS"."EXTERNAL_TABLES_DIR" TO "MTS_HHT" WITH GRANT OPTION;
GRANT "ACCOUNT_CREATOR" TO "MTS_HHT";
GRANT "DBA" TO "MTS_HHT";
GRANT "JAVASYSPRIV" TO "MTS_HHT";
GRANT "JAVAUSERPRIV" TO "MTS_HHT";
GRANT "MTS_USER" TO "MTS_HHT";
GRANT "MTS_USER_READ_ONLY" TO "MTS_HHT" WITH ADMIN OPTION;
GRANT "PASSWORD_CHANGE" TO "MTS_HHT";

Alter operating scripts

An example of the changes needed is as follows; using the HHT files the shell script /usr/bin/hgp/hht_upload as an example. The changes below are; the red showing the current setting the blue showing the new settings.

function dba {                  # issue sqldba command (in $*)
 log "issuing SQL/DBA command  ${*}"
 #user=mts_owner
 user=mts_hht
 pass=$(${HGPHOME}/get_ora_pass $user)

Alter database packages

Database package DP_INT_MSG.READ_SAL_FILE runs the load of SCAN AND LOAD (SAL) files into MTS.

tv_interface_user      := ADM.GET_PARAM_VCHAR('SAL_INTERFACE_USER');

The user associated will be will be extracted form system_registry table

References

Ref No	Document Title & ID	Version	Date
1	EST-251784-NW-7GNCNY Amend HHT inboundv1.doc

Document History

Version	Date	Status	Reason	Initials
1a	07/07/2008	Draft	Draft version	CBW
1	10/07/2008	Issued	Reviewed and Issued	JAT

Authorised By

Dave Meir	Development Manager
Suk Sandhu	TMSCC MTS Product Manager