CODEWITHSUNDEEP
sasteradata
Viswanath Sitaraman
Viswanath Sitaraman

Reputation: 3

Switch Teradata Accounts

I want to be able to switch between 2 Teradata accounts if the password has expired for one user in SAS. e.g. connect to teradata (user="&terauser" password="&terapwd" account="&teraacct" server="&tdserver" mode=teradata);

terauser : Password expired terauser1 : Password valid

Couple of questions:

  1. How do I pre-warn the user to change password. Do I use the DBC.Users table.
  2. How do I swicth accounts, as I will know the status only in the logs.

Upvotes: 0

Views: 290

Answers (2)

Tom
Tom

Reputation: 51611

If you get connected you can ask Teradata when the password was last changed by querying DBC.USERSV. Then if you know what your expiration rules are you can send a message to the user in the SAS log. So if you passwords expire after 120 days your query and report might look like this:

select catx(' ','%put'
   , case when (days_left < 2) then 'ERROR:'
          when (days_left < 10) then 'WARNING:'
          else 'NOTE:'
     end
   ,'Your Teradata password expires in',days_left,'days.')
 into :tdpassword_message trimmed
from connection to teradata
 (select ((PasswordChgDate+ 120) - date) days_left from dbc.usersV )
;
*----------------------------------------------------------------------------;
* Write message to SAS log ;
*----------------------------------------------------------------------------;
&tdpassword_message;

Upvotes: 0

Richard
Richard

Reputation: 27508

Proc SQL maintains some automatic macro variables that you can examine:

Proc SQL;
  connect to ....;

  %put NOTE: SQLXRC=%superq(SQLXRC);
  %put NOTE: SQLXMSG=%superq(SQLXMSG);

If you want to automatically test and use the working account you could create a macro that performs try connection and switch account behavior. Be careful about security policies if you put user account info in source code or a plain-text file.

Example:

%macro myConnect ();
  %* To be used in the context of a Proc SQL statement;

  %* ... assign first account connection parameters to  macro variables;
  %let terauser=...;
  ...
  %let tdserver = ...;

  connect to teradata (user="&terauser" password="&terapwd" account="&teraacct" server="&tdserver" mode=teradata);

  %if &SQLXRC eq 0 %then %return;

  %* ... assign second account connection parameters to  macro variables;
  %let terauser=...;
  ...
  %let tdserver = ...;

  connect to teradata (user="&terauser" password="&terapwd" account="&teraacct" server="&tdserver" mode=teradata);

  %if &SQLXRC eq 0 %then %return;

  %put ERROR: Could not connect with current connection parameters;
  %abort cancel;
%mend;

Proc SQL can also connect using existing remote library references:

* create remote library references;
libname account1 Teradata ... connection parameters ...;
libname account2 Teradata ... connection parameters ...;

Proc SQL;
  connect using account1;  * connect using the account1

Upvotes: 1

Related Questions