SLC Managing Credentials in User Programs Part 2 of 2

mvallely

SLC Managing Credentials in User Programs Part 2 of 2

Use Case 3: Creation of a secure confidential JSON file

This use case involves the creation of the equivalent for an Oauth token which contains credentials and other connection parameters in the form of a JSON file. This file is placed in a secure location, like the protection one gives to storing private .ssh keys.

The benefit of this approach over the use of environment variables is that the JSON file format lends itself to creating key value pairs that are read all at once and using Autocall macros the entire connection/authentication string can be dynamically formed to attach to a given resource. End user programs call the Autocall macro and are shielded from having to make code adjustments, since all of the parameters form the correct resource connection string.

This also ensures uniformity across all users to enforce the organization resource consumption requirements. This can be extended to enforce any quota limits on resources. See Appendix C: cred_file_ora_access.sas

For this example, we securely store the JSON file as:

~/.oracle_creds

Its content:

{

"username": "trb",

"password": "{sas001}UG9saXRlcGlhbm8zNTEj"

}

The permissions are set to 600 (read/write for owner only) using:

chmod 600 ~/.oracle_creds

To ensure only the file owner (or user groups) can read or modify the credentials. The file should be owned by the user account that runs the SLC process, and the parent directory (~/) should have permissions of 700 or 755.

Additionally, the file should not be readable by groups or others, preventing unauthorized access to sensitive credentials, similar to how SSH private keys are protected with restrictive permissions to maintain system security.

As an example, see the snippet of the log, below:

85

86	%put NOTE: Oracle credentials have been loaded into global macro variables DB_USER

86	! and DB_PASS;

87

88	%mend read_oracle_creds;

89

90	/* Example usage of the macro */ 
91

%read_oracle_creds(credpath=~/.oracle_creds/.oracle_creds.json);


NOTE: The infile creds is:

File Name=/home/ec2-user/.oracle_creds/.oracle_creds.json, 
Access Permissions=-rw-------, Number of Links=1,

Owner Name=ec2-user, Group Name=ec2-user,

File Size=61, Last Modified=02:12:00 Aug 26 2025, 
Created=18:39:54 Aug 26 2025, Lrecl=32767, Recfm=V


Database User: trb

Password retrieved successfully

Optimizations:

We can make further improvements to these approaches by creating a series of Autocall Macros that users call into their SAS language programs where all of these connection details are provided. This level of abstraction makes it easier to have a standard and consistent method of making these database connection. See Appendix D: optimal_autocall_approach.sas.

Appendix A: PROC PWENCODE.sas

proc pwencode in='<your_password_string'; 
run;



proc sql;

   connect to oracle ( 
      user="trb"

      password="{sas001}UG9saXRlcGlhbm8zNTEj" 
      path="localhost:1521/ORCL"

   );
   select * from connection to oracle ( 
   select user, sysdate from dual

   );



   disconnect from oracle; 
quit;

Appendix B: slc_connect_ora_env_var.sas

data_null_;
   callsymputx('ora_password',sysget('SLC_GET_ORA_PW'),'G'); 
run;


proc sql;
   connect to oracle ( 
   user="trb" 
   password="&ora_password" 
   path="localhost:1521/ORCL"
);

Appendix C: cred_file_ora_access.sas

/*SASMacro:Read Oracle Credentials from JSON File*/
%macroread_oracle_creds(credpath=);


   /*Reads credentials from local file*/ 
   filename creds "&credpath";


/*Parse the JSON credentials into a global macro variable*/ 
data _null_;
   infilecreds; 
   input;
   callsymputx('json_creds',_infile_,'G'); 
   put "Credentials read from: &credpath";
run;


/*Extract credentials*/ 
filename jsonresp temp;


data_null_;
   file jsonresp;
   length json_string $200; 
   json_string=symget('json_creds'); 
   put json_string;
run;


/*JSON libname to parse the credentials*/ 
libname jsonlib JSON fileref=jsonresp;


/*Return extracted credentials from JSON*/ 
data _null_;
   setjsonlib.root;
   callsymputx('db_user',username,'G'); 
   callsymputx('db_pass',password,'G');


   put"DatabaseUser:"username;
   put"Passwordretrievedsuccessfully"; run;


/*Clean up JSON library and temp file*/ 
libname  jsonlib  clear;
filename jsonresp clear; 
filename creds    clear;


%put NOTE: Oracle credentials have been loaded into global macro variables DB_USER and DB_PASS;


%mendread_oracle_creds;


/*Example usage of the macro*/
%read_oracle_creds(credpath = ~/.oracle_creds/.oracle_creds.json);


/*Connect to Oracle using retrieved credentials*/ 
libname oracledb oracle
   user="&db_user" 
   password="&db_pass" 
   path="localhost:1521/ORCL" 
   schema="&db_user";


/*Test connection */
proc datasets library=oracledb;
   title "Oracle Connection Test with Local Credentials"; 
run;


/*Sample query - Using explicit connection syntax*/ 
proc sql;
      title"Oracle Database Connection Verification"; 
      connect to oracle (
      user="&db_user" 
      password="&db_pass" 
      path="localhost:1521/ORCL"
   );


   select * from connection to oracle(
      select user as current_user, sysdate as current_time from dual
   );


   disconnect from oracle; 
quit;


/*Cleanup*/
%symdeldb_userdb_passjson_creds; 
libname oracledb clear;

Appendix D: optimal_autocall_approach.sas

/* SAS Macro: Handling Oracle Credentials
================================================================
 USAGE EXAMPLES:
================================================================
Example 1: Using encoded password string from environment variable

%read_oracle_creds_env(envvar=SLC_GET_ORA_PW, username=trb);

%connect_oracle(host=localhost, port=1521, service=ORCL);

%test_oracle_connection();

%cleanup_oracle();


Example 2: Using different environment variable and user

%read_oracle_creds_env(envvar=PROD_ORA_PW, username=prod_user);

%connect_oracle(host=prod-server, port=1521, service=PROD);

%test_oracle_connection();

%cleanup_oracle();


Example 3: Multiple connections with different environment variables

%read_oracle_creds_env(envvar=DEV_ORA_PW, username=dev_user);

%connect_oracle(host=dev-server, port=1521, service=DEV, libname=dev_db);
%read_oracle_creds_env(envvar=TEST_ORA_PW, username=test_user);

%connect_oracle(host=test-server, port=1521, service=TEST, libname=test_db);
%cleanup_oracle(libname=dev_db);

%cleanup_oracle(libname=test_db);
================================================================ */


%macro read_oracle_creds_env(envvar=, username=);


/* Retrieve encoded password string from environment variable */

%let encoded_password = %sysget(&envvar);


/* Check if environment variable exists and has a value */
   %if &encoded_password = %then %do;

       %put ERROR: Environment variable &envvar is not set or is empty;

      %goto exit_macro;

   %end;


/* Set global macro variables for Oracle connection */

/ Use the encoded string directly - SLC handles it */

%global db_user db_pass;

%let db_user = &username;

%let db_pass = &encoded_password;



%put NOTE: Oracle credentials retrieved from environment variable &envvar;

%put NOTE: Database user set to: &db_user;

%put NOTE: Encoded password string retrieved successfully;
%exit_macro:



%mend read_oracle_creds_env;


/* Oracle Connection Macro */

%macro connect_oracle(host=localhost, port=1521, service=ORCL, libname=oracledb);


/* Verify credentials */

   %if &db_user = %then %do;

       %put ERROR: DB_USER macro variable is not set. Call credential retrieval macro first;

       %goto exit_connect;

   %end;


   %if &db_pass = %then %do;

       %put ERROR: DB_PASS macro variable is not set. Call credential retrieval macro first;

       %goto exit_connect;

   %end;



/* Connect to Oracle using retrieved credentials */ 
libname &libname oracle

   user="&db_user" 
   password="&db_pass" 
   path="&host:&port/&service" 
   schema="&db_user";



%put NOTE: Oracle connection established as libname &libname;

%put NOTE: Connection string: &host:&port/&service;
%exit_connect:


%mend connect_oracle;



/* Test Section             */

/ Run read_oracle_creds_env */

%read_oracle_creds_env(envvar=SLC_GET_ORA_PW, username=trb);


/* Run connect_oracle macro */

%connect_oracle(host=localhost, port=1521, service=ORCL, libname=oracledb)


/* Test test_oracle_connection Macro */

%macro test_oracle_connection(libname=oracledb);


/* Test connection with PROC DATASETS */ 
proc datasets library=&libname;

   title "Oracle Connection Test - Environment Variable Credentials";

run;



/ Verification query */ 
proc sql;

   title "Oracle Database Connection Verification"; 
   connect to oracle (

   user="&db_user" 
   password="&db_pass" 
   path="localhost:1521/ORCL"

);


select * from connection to oracle ( 
   select

      user as current_user, 
      sysdate as current_time,

      sys_context('USERENV','SESSION_USER') as session_user

 from dual

);


   disconnect from oracle; 
quit;


%mend test_oracle_connection;


/* Cleanup Macro */
%macro cleanup_oracle(libname=oracledb);


/* Clean up macro variables /

%symdel db_user db_pass;



/ Clear Oracle libname */ 
libname &libname clear;
%put NOTE: Oracle connection and credentials cleaned up;


%mend cleanup_oracle;