Install SAS on a UNIX Computer
In this section:
- Modify SAS 9.4 and opa_settings on UNIX
- Install SAS on the Same UNIX Computer as Oracle Clinical
- Install SAS on a Different UNIX Computer from Oracle Clinical
Parent topic: Integrate SAS (Optional)
Modify SAS 9.4 and opa_settings on UNIX
Oracle Clinical supports SAS 9.4. You can validate your Oracle Clinical installation, and then upgrade to SAS 9.4 later. The SAS/ACCESS Interface to Oracle requires Oracle SQL*NET on the computer with the SAS software installation.
Follow these instructions on the SAS server—whether SAS is on the same computer or a different one from Oracle Clinical.
Parent topic: Install SAS on a UNIX Computer
Prepare the SAS Template File
Make the following modifications to the SAS template file:
Parent topic: Modify SAS 9.4 and opa_settings on UNIX
Edit the opa_settings File
To use SAS 9.3 with Oracle Clinical on a UNIX platform:
- Navigate to the following directory:
OPA_HOME/bin
- Open the opa_settings file.
- Change the value of the SASORA environment variable to V9. For example:
db_env_setting:_DEFAULT_:SASORA:V9
Parent topic: Modify SAS 9.4 and opa_settings on UNIX
Set Up a SAS Connection
You can set up a SAS connection to the Oracle database in two ways—using Oracle Wallet or SAS encryption. During installation, the database Installer prompts you for the SAS_CONNECTION value and adds it to the OCL_STATE reference code list. The short value is SAS_CONNECTION and the long value can be either ORACLE_WALLET or SAS_ENCRYPTION.
Oracle Wallet
This is the default option.
If your SAS server is the same as the database server, and you set ORACLE_WALLET as the long value, the SAS connection is set up automatically.
If your SAS server is on a different machine from the database and you want to use ORACLE_WALLET, it is not necessary to install the Oracle Clinical server on the SAS server machine as long as you are able to connect to the database after you log in as opapps.
On the SAS server:
- Log on as opapps.
- If the Oracle Clinical database server is not installed on the same machine, set the environment variables in .cshrc so you can connect to the database using SQL*Plus.
- If the Oracle Clinical database server is installed on the same machine,
- Set up the Oracle Wallet on the SAS server. See Create an Oracle Wallet and Generate a Certificate Request.
- Create an opapps account in the same was you did on Database Server with default shell C Shell. See Create the opapps Account and Add It to Groups.
- Enter the SAS server in the OCL_STATE reference codelist for short value REMOTE_SAS_SERV. See the Oracle Clinical Administrator's Guide for information.
SAS Encryption
To set up a SAS connection using SAS encryption:
- Log in to the database as the system user and create a database
account:
create user sas_proxy_user
For example,
abc123
- Grant proxy connection for the database user account:
alter user <oc user> grant connect through <sas_proxy_user>
; - Log in to the PSUB server as RXC_SAS_VIEW owner and run the
commands:
opa_setup <dbname> <codenv>
cd $RXC_SAS_VIEW
- Create a SAS file named pwd.sas with the following content:
filename pwfile 'sas_proxy_password_encoded_file'; proc pwencode in='<password>' out=pwfile method=sas002; run;
Where, <password> is the database password of the user sas_proxy_user.
- Run the file created in Step 4. This creates the sas_proxy_password_encoded_file which stores the encrypted password of the sas_proxy_user db user.
- Remove the file created in Step 4.
- Run the SAS files.
The sas_proxy_user does not have create session privileges and so cannot be directly connected through an SQL*Plus session.
Other OS users can run SAS jobs from their accounts if they have read access to the path $RXC_SAS_VIEW. In other words, if the OS user belongs to the oclsascr group.
Parent topic: Modify SAS 9.4 and opa_settings on UNIX
Install SAS on the Same UNIX Computer as Oracle Clinical
Oracle recommends installing SAS on the same server computer as the Oracle Clinical database server installation.
For more information, see:
Set REMOTE_OS_AUTHENT to FALSE
To set REMOTE_OS_AUTHENT to FALSE:
Parent topic: Install SAS on the Same UNIX Computer as Oracle Clinical
Set Up a SAS Connection
You can set up a SAS connection to the Oracle database in two ways—using Oracle Wallet or SAS encryption. The Oracle Clinical Installer sets the SAS_CONNECTION value in the OCL_STATE reference codelist to ORACLE_WALLET.
Oracle Wallet
This is the default option. The SAS connection is set up automatically.
SAS Encryption
Manually set SAS_ENCRYPTION as the SAS_CONNECTION long value in the OCL_STATE local reference codelist. In addition:
- Log in to the database as system user and create the sas_proxy_user
database account:
create user sas_proxy_user identified by <password>;
- Grant proxy connection for each database user account who needs to
submit SAS Data Extract jobs:
alter user <oc user> grant connect through sas_proxy_user;
OR
Run the script ocl_grant_revoke_sas_proxy_user.sql in the install directory to grant or revoke user connections through proxy account sas_proxy_user
-
Log in to the PSUB server as RXC_SAS_VIEW owner and set the environment:
- C
shell:
opa_setup database_name code_environment
- Bourne
shell:
p1 = database_name p2 = code_environment . opa_setup
- C
shell:
- Run the command:
cd $RXC_SAS_VIEW
- Create a temporary SAS file named pwd.sas to contain the password
for the sas_proxy_user account:
filename pwfile 'sas_proxy_password_encoded_file'; proc pwencode in='password' out=pwfile method=sas002; run;
- Run the file created in Step 4 to encrypt the file. This creates
the sas_proxy_password_encoded_file which stores the encrypted password
of the sas_proxy_user db user (where pwd.sas is the name of the temporary file
you created):
- In UNIX:
sas pwd.sas
- In Windows:
sas pwd.sas -sysin
- In UNIX:
- Remove the file created in Step 5.
- Run the SAS files.
Parent topic: Install SAS on the Same UNIX Computer as Oracle Clinical
Install SAS on a Different UNIX Computer from Oracle Clinical
Oracle recommends installing SAS on the same UNIX server computer as the Oracle Clinical database server installation.
If you choose to install SAS on a UNIX server computer different from that of the Oracle Clinical database server installation, it must be on the same intranet and you need to set up a connection to SAS.
For more information, see:
- Establish the Connection to SAS on a Different Computer
- Set Up SAS Security on a Different Computer
- Configure Private and Public Keys for Using SSH with SAS
Parent topic: Install SAS on a UNIX Computer
Establish the Connection to SAS on a Different Computer
Note:
The procedures in this section uses secure shell (ssh) to establish the connection to SAS.To set up SAS on a different UNIX server computer:
Network File System (NFS)
- Use Network File System (NFS) protocol to make the directory on the Oracle Clinical server pointed to by the $RXC_USER /sas_view visible to the SAS server.
- Export this directory with write privileges because the SAS scripts generated by Oracle Clinical produce SAS view descriptors that are created in this directory tree.
Note:
By default, the $RXC_USER environment variable is the opapps home directory.Parent topic: Establish the Connection to SAS on a Different Computer
Create opapps on the SAS Server
- Create the opapps UNIX user account on the SAS server.
- Create a group for opapps and put it in the group.
- Link
/etc/group
with/etc/logingroup
on the SAS server if it is not the primary group for opapps.
Parent topic: Establish the Connection to SAS on a Different Computer
Check init.ora
- Open the init.ora file.
- Verify that the REMOTE_OS_AUTHENT initialization parameter is set to FALSE for the Oracle Clinical database instance in the init.ora file:
REMOTE_OS_AUTHENT=FALSE
See the Table 2-3 table for more information.
Parent topic: Establish the Connection to SAS on a Different Computer
Create a Shell Script
Create a shell script that forces a "SAS" invocation on the Oracle Clinical database server to run as a remote shell on the SAS server that invokes the SAS engine, passing it the name of the SAS file:
- Create the shell script on the Oracle Clinical database server in a publicly visible directory, such as opapps/bin.
- Name the script
sas
. - Set the protection mode to
755
. - Insert code lines into the SAS
file.
#!/bin/sh RXC_LOG1=log_path_on_the_SAS_server SASDIR=`dirname $3` FILENAME=`basename $2` LOGNAME=$RXC_LOG1$FILENAME ssh server_name /bin/sh -c ". .profile;setenv TNS_ADMIN $HOME ;setenv ORACLE_HOME oracle_home_on_sas_server;cd $SASDIR ; path_to_sas_script_on_SAS_server -log $LOGNAME $3 $4 $5 $6 "
Where:
- RXC_LOG1 is the path of the log directory in the SAS server
- SASDIR is the directory RXC_SAS_VIEW where SAS view is created and folder is NFS mounted; comes as input
- FILENAME is the SAS log file name; comes as input
- server_name is the SAS server connected through ssh
- path_to_sas_script_on_SAS_server is the sas file created in the OPA_BIN directory on the SAS server
For example:
#!/bin/sh RXC_LOG1=/pharm/home/opapps/log SASDIR=`dirname $3` FILENAME=`basename $2` LOGNAME=$RXC_LOG1$FILENAME ssh opapps@server_name /bin/sh -c ". .profile;setenv TNS_ADMIN $HOME ;setenv ORACLE_HOME /u01/app/oracle/product/19.3.0/dbhome_1;cd $SASDIR ; /home/opapps/bin/sas -log $LOGNAME $3 $4 $5 $6 "
Parent topic: Establish the Connection to SAS on a Different Computer
Set Up SAS Security on a Different Computer
You can set up a SAS connection to the Oracle database in two ways—using Oracle Wallet or SAS encryption. The Oracle Clinical Installer sets the SAS_CONNECTION value in the OCL_STATE reference codelist to ORACLE_WALLET.
Oracle Wallet
This is the default option. You do not need to change the OCL_STATE reference codelist SAS_CONNECTION value, but you must enter the SAS server as the REMOTE_SAS_SERV value in OCL_STATE. See the Oracle Clinical Administrator's Guide for information.
And, on the SAS server:
- Create an opapps account in the same way you did on database server with default shell C Shell. See Create the opapps Account and Add It to Groups.
- Log on as opapps.
- Set the environment variables in .cshrc so you can connect to the database using SQL*Plus.
- Set up the Oracle Wallet on the SAS server. See Create an Oracle Wallet and Generate a Certificate Request You may use the same Wallet password that you used during
Oracle Clinical installation or a different
one:
mkstore -wrl wallet_location -create -nologo
Example wallet location: /pharm/home/opapps/wallet
- Enter your password, then enter it again.
- Add OCPSUB
credentials:
mkstore -wrl wallet_location -createCredential db_connect_string OCPSUB
- Enter information as follows at the prompts:
- Enter your secret password: Enter the OCPSUB password.
- Re-enter your secret password: Re-enter the OCPSUB password.
- Enter wallet password: Enter the Wallet password created above.
- Create sqlnet.ora in opapps home. Specify the Wallet path. For
example:
WALLET_LOCATION=(SOURCE =(METHOD = FILE)(METHOD_DATA =(DIRECTORY = /pharm/home/opapps/wallet)))SQLNET.WALLET_OVERRIDE = TRUE
where, /pharm/home/opapps/wallet is the wallet path.
- In the opapps home, add the following in .cshrc:
setenv TNS_ADMIN $HOME
- Test the Wallet connection:
- Open another telnet/putty session of SAS server and log in as opapps.
- Try connecting. It should not require specifying a
password.
sqlplus /@db_connect_string sql> show user
You should see the OCPSUB user.
SAS Encryption
To set up a SAS connection using SAS encryption, follow instructions for SAS Encryption in Set Up a SAS Connection.
Configure Private and Public Keys for Using SSH with SAS
If you are installing SAS on a different server on an intranet, do the following to establish an SSH connection from the PSUB server to the SAS server.
Oracle Clinical DB Server
Perform the following tasks on the Oracle Clinical UNIX database server computer:
- Log in to the Oracle Clinical UNIX database server computer as the opapps user.
- Use ssh-keygen to create a password-less set of identity keys:
ssh-keygen -t rsa -N ''
The system prompts for the file into which you want to save the set of identity keys.
- Press Return to accept the default location. This process creates two files in the user's home directory:
- ~/.ssh/id_rsa This file contains the private key that represents your identity on that particular machine. Note that the private key is neither world nor group readable. You should never transfer the private key from the machine or change its modes.
- ~/.ssh/id_rsa.pub This file contains the public key, which is world readable. The ssh program and other programs can use the public key to encrypt messages that only you can decrypt using the private key. The -N ' ' argument to the ssh-keygen command specifies that no passwords are associated with the public keys.
- Transport the file id_rsa.pub to a location on the SAS Server (for example, /tmp) using a secure method as defined by the policies of your organization.
SAS Server
Perform the following tasks on the SAS Server computer:
- Log in to the SAS Server computer as opapps.
- In the home directory Create the .ssh directory if it does not exist, and set the permission to 700:
mkdir .ssh chmod 700 .ssh cd ~/.ssh
- Append the contents of the id_rsa.pub file in the /tmp directory to the authorized_keys file in the GUEST1_HOME/.ssh directory. For example:
cat /tmp/id_rsa.pub >> authorized_keys
- Change the permission of the authorized_keys file to 600:
chmod 600 authorized_keys
Verify SSH
To test the ssh setup from the Oracle Clinical Database server: