Oracle applications - Surendranath Subramani: Admin
Showing posts with label Admin. Show all posts
Showing posts with label Admin. Show all posts

Saturday, November 28, 2015

File encryption with GPG method for EBS



Most of the company are exchanging PII (personal identification information) data (it could be routing number, credit card number, employee information etc) so to exchange files security we have to do 2 things:

Use gpg (or pgp) encrypt the file.
Use sftp send the file to the target system.

We will discuss more on how to encrypt file using gpg software.

Create encrypted file:

Source system: Generate file e.g.: CTX

Target system: Receive the file e.g.: Bank

a. First step is to create key

Go to your application server and run below syntax from the command line: 

————————————————
gpg --gen-key
————————————————

Note: Check with your admin if gpg software has been installed at your environment else they might need before we further go through the following steps.

A series of prompts directs you through the process. Press the Enter key to assign a default value if desired
——————————————————————————————————
Please select what kind of key you want:
   (1) DSA and Elgamal (default)
   (2) DSA (sign only)
   (5) RSA (sign only)
Your selection? 
——————————————————————————————————

Choose the key size, by default it would be 2048.

——————————————————————————————————
RSA keys may be between 1024 and 4096 bits long.
What keysize do you want? (2048) 
——————————————————————————————————

Specify key expiration period. For testing lets go with no expire.

——————————————————————————————————
Please specify how long the key should be valid.
         0 = key does not expire
      <n>  = key expires in n days
      <n>w = key expires in n weeks
      <n>m = key expires in n months
      <n>y = key expires in n years
Key is valid for? (0) 
——————————————————————————————————

Specify user ID, email address and the comments for the key.

——————————————————————————————————
You need a user ID to identify your key; the software constructs the user ID
from the Real Name, Comment and Email Address in this form:
    "Heinrich Heine (Der Dichter) <heinrichh@duesseldorf.de>"
——————————————————————————————————

You can give passphrase to the key. This is done for extra caution.

——————————————————————————————————
You need a Passphrase to protect your secret key.
——————————————————————————————————

key has been created.

b. We are done with key generation. once the key is generated use list keys command to list the key details.
——————————————————————————————————
gpg --list-keys
——————————————————————————————————

e.g.: It would look like below

pub   1024D/7FFAD482 2015-10-02
uid                  article (test key for the article) <article@abc.com>
sub   2048g/80859349 2015-10-02

c. Export public key.

Public key need to be exported so that it can be shared with target system.
Key can be exported in 2 formats.
1. gibberish format 
2. Alphanumeric (commonly used) format. armor command in below syntax.

——————————————————————————————————
gpg --output article.gpg.export --export article@abc.com
gpg --armor --export article@abc.com > article.gpg.export
——————————————————————————————————

d. Transfer key to target system.

After executing above command article.gpg.export file will be created. You now have to send the key to target system. You have 2 options.

1. Using gpg —send-key option you can send the key 
2. FTP the file from server to local and send the file to the contact person who is in-charge of target system through outlook email. 


e. Install gpg key at target system.

——————————————————————————————————
gpg --import article.gpg.export
——————————————————————————————————

The log should look like below. If you do not see "imported as 1" then you export of public key was not done properly.

gpg: Total number processed: 1
gpg:               imported: 1


f. encrypt file using the generated key

Lets assume article.txt is the original file and that need to be encrypted so using below command article.txt will be encrypted using public key.
——————————————————————————————————
gpg -s --no-tty --always-trust --passphrase "Testing" -u article@abc.com "article.txt"
——————————————————————————————————
when you define your key in the source system whatever passpharse was given the same as to be given in above command.
e.g.: “Testing” is the passpharse used.

send encrypted file to the target system.
Target system need to decrypt the file
——————————————————————————————————
gpg --no-tty --passphrase "Testing" -u article@abc.com "article.txt.gpg"
——————————————————————————————————
After executing decrypt command the encrypted file will be decrypted and the decrypted file will be processed.

g. How the encrypted files are exchanged between the system:

The steps are explained in below article.

http://oracleappssuren.blogspot.com/2015/11/ssh-key-for-ebs-environment.html


Other scenario:

Lets take an example of travel card process:

In travel card process where source organization would send the public key to bank and bank encrypt and send the transaction file so now source organization will decrypt and load the transactions into Internet expense.

Source system could generate public key, share the key with target system and target system will generate file with applying encryption using the key shared by source system. Now source system get the encrypted file from target and decrypt the file and process it.

What ever steps we have followed above is going to be same.

The command for encrypt and decrypt going to be

——————————————————————————————————
Target system:
gpg --encrypt --recipient article@abc.com article.gpg.export

Source system:
gpg --no-tty -u article@abc.com article.txt.gpg 
——————————————————————————————————

gpg: decryption failed: secret key not available

When you get above error that means the you have shared the public key only to other party and system is expecting to share private key. So you have got 2 options. 

1. Share private key so that other party can import and then can encrypt the file.
2. or instead of encrypt sign the document.

Thanks for reading my blog. Have a wonderful day!!!


SSH key for EBS environment


Establish SSH connection between 2 servers to exchange files:

In Oracle EBS you may come across scenario to exchange files between 2 systems. 
Example: it could be sending CTX file to bank or receiving credit card transaction file from bank.

We will go over basic steps which is required to achieve the file transmission functionality.

Create SSH key

a. To start with we need to SSH Key in the source system.

Go to application server (middle tier) and type below command from command line. 
——————————————————————————————————
ssh-keygen
——————————————————————————————————
You will be prompted to supply file name (for saving the key pair) and passphrase.

Preferred location to store your key pair is ~/.ssh

after completion of generating key, now you see 2 files created 
> private key (without extension)
> Public key (with extension .pub)

b. Download public key to your local using scp or ftp.

c. Transfer public key to remote (target) system through email.

d. In the remote system: the key need to be installed.

Add the public key to authorized_key file located in ~/.ssh folder

Lets say your public key file name is id_rsa.pub then using below command the public key content will be added to authorized key file.

Authorized file will lets the server authenticate the client, if the public key is not added to this file then client can not connect to remote server.

——————————————————————————————————
cat ~/id_rsa.pub >> ~/.ssh/authorized_key 
——————————————————————————————————

e. Since we are all set with the remote server settings, now it is time to test the connection.


When you try to connect first time it will prompt to add the known_host file.
This file is located in ~/.ssh/known_host in the client (source) machine. This file will keep the record of different connection you establish. 

Known host will lets the client authenticate the server

If you have set passphrase while creating key then during connection you will be prompted to enter passphrase. 
If you have not set passphrase then you can connect without passphrase

Note: Since we are making secure FTP connection using key pair most of the time it is good to create key without passphrase so that it will be easy to programmatically connect and ftp the files between client and remote machine.


Thanks for reading my blog. Have a wonderful day!!!



Tuesday, October 13, 2015

How to get access to user management responsibility

While working in Oracle applications you may need to add roles and responsibility to user account.
How do you do that?

First thing which you can think of is, go to system administrator responsibility navigate to security, user, define.
Query the user and add responsibility. But how do you add role to the user?

Example: Access Approval management Engine (AME) role to work in building an approval process.

So how do you do that?

Login as SYSADMIN, go to user management responsibility, choose 'role' menu.
Query the user to which you want to access AME.

Click on update and add role called 'Security Administrator'

Click update once done. 

Now logout from SYSADMIN and login as the user (to which you have added the role in previous step). You should be able to access user managment responsibility.

Using user management you can even add responsibility.


Security administrator role added to user account





Tuesday, May 19, 2015

Oracle Patch Impact analysis

Objective:

Oracle always provides patch to fix bugs, add more features, improvements and so on.
So while implementing patch in development environment everyone wants to know the impact of the patch, what objects are impacted, how many files are added or changed etc.

Various method:

There are 2 methods this can be achieved.

  1. adpatch
  2. Patch wizard
1. adpatch:

You can install patch with apply mode=no (by default it is yes).
apply=no mode will provide a snap shot of what the patch will do (like introducing new file, changing exisitng files and so on) but it will not apply the patch.

The syntax for using this mode is: 
$ adpatch apply=no 

Problem with this method:

The major issue with this method is lets say if you are applying patch set which involves 500+ objects then reading log file (log file which generated from adpatch) will be a big hassle because you will have to go through each object one by one and get the complete list of impacted objects. Also it will not tell you whether it has modified or creating.

2. Patch wizard:

This method is easy to use and efficient way to get impact analyzed.

Steps to follow:

a. You can analyze patch either having Patch wizard itself download patch from metalink.oracle.com for you and then do the analysis [or] you can download patch manually and then have patch wizard do analyze for you.

We will talk more about how to analyze patch after downloading manually patch from metallic.oracle.com

a. Login as SYSADMIN and navigate to patch wizard

























b. Naviage to site map tab --> maintenance (sub tab) -->  patch wizard



c. Change preference (this is one time setup)






d. Provide staging directory in the preference.
Remember this location should have full access.
























e. create ad directory within staging location. <staging directory>/ad
Move patch.zip file to <staging directory>/ad
e.g.: /patches12/ad

f. Move InfoBundleR12.zip file to staging directory. Patch wizard requires this file because this stores the meta data for analyzes.

g. Give patch # and perform the analysis.




























h. Once analyse is done you can click on the details to find more about the impacts like
       a. No of file changed
       b. No of files newly added
       c. Object type
       e. Impacted modules, etc...



Using below query you can get the list of patches analyzed and other details:

select
adp.bug_number patch_name,
adp.product_family_abbreviation ,
ad_pa_validate_criteriaset.get_cs_prod_fam_abbr(adp.product_abbreviation) product_family_abbreviation,
nvl(adp.product_abbreviation, ' ') ,
adp.bug_description ,
INITCAP(NVL(adp.patch_type,'User Request')) Patch_Type ,
(SELECT COUNT(*) FROM ad_pa_anal_run_bug_prereqs aparbp
WHERE adb.analysis_run_bug_id = aparbp.analysis_run_bug_id ) prereq_cnt,
DECODE (adh.bug_number, NULL, 'N', 'Y'),
adt.name ,
adb.analysis_run_id ,
analysis_run_bug_id ,
ads.snapshot_id ,
ad_pa_validate_criteriaset.get_cs_prod_fam_name(adp.product_abbreviation) product_family_name ,
appi.product_name ,
(SELECT COUNT(*) FROM ad_pa_anal_run_bug_codelevels aparbc
WHERE adb.analysis_run_bug_id = aparbc.analysis_run_bug_id ) level_cnt ,
decode (adb.analysis_status,'MISSING','Missing','READY','Unapplied','APPLIED','Applied',adb.analysis_status) patch_status ,
nvl(decode(adp.has_msi_steps,'Y','Yes','N','No',adp.has_msi_steps), ' ') hasMsiSteps ,
adp.baseline ,
adp.entity_abbr ,
adp.patch_id
from
ad_pm_patches adp ,
ad_appl_tops adt ,
ad_pa_analysis_run_bugs adb ,
ad_pm_product_info appi ,
ad_snapshots ads
,ad_hidden_patches adh
where
1=1--adb.analysis_run_id = 402352 --<request_id>
--and adb.analysis_status in ('MISSING','READY','APPLIED')
--and ads.SNAPSHOT_TYPE = 'G'
--and adt.NAME ='GLOBAL'
and adb.appl_top_id = adt.APPL_TOP_ID
and ads.appl_top_id = adt.APPL_TOP_ID
and appi.product_abbreviation = adp.product_abbreviation
and adb.bug_number = adp.BUG_NUMBER
and adb.baseline = adp.baseline
--and adp.patch_metadata_key = 'DEFAULT'
--and adp.is_code_level = 'Y'
and adh.bug_number(+) = adp.BUG_NUMBER
order by patch_name;